Last active
November 13, 2023 06:58
-
-
Save mklooss/962543d362a605b12c78ebeb71669188 to your computer and use it in GitHub Desktop.
Export MySQL/MariaDB Database in Tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Written on Debian Based Systems | |
usage: mysql_[import|export].sh DATABASENAME | |
Splits Database in to multplie files, based on Table Names | |
schema.sql will be also created! | |
i've an my_import.cnf / my_export.cnf or exists, this files will be used as Credentails, default is the debian default file. | |
Files: schema.sql, routines_triggers.sql | |
export/*.sql |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[client] | |
user=DATABASE_USER | |
password=DATABASE_PASSWORD | |
host=DATABASE_HOST (localhost / 127.0.0.1) | |
port=DATABASE_PORT (Default: 3306) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
DATABASENAME=$1 | |
if [ -z "$DATABASENAME" ]; then | |
echo "no database defined" | |
exit 1; | |
fi | |
MYCNF=/etc/mysql/debian.cnf | |
if [ -f "my_export.cnf" ]; then | |
MYCNF=$(realpath my_export.cnf) | |
fi | |
LIST=$(mysql --defaults-file=$MYCNF $DATABASENAME -e "SHOW TABLES;" -N | xargs | sort) | |
mysqldump --defaults-file=$MYCNF --skip-triggers --no-data $DATABASENAME > schema.sql | |
mysqldump --defaults-file=$MYCNF --routines --triggers --no-create-info --no-data $DATABASENAME > routines_triggers.sql | |
rm -rf export | |
mkdir -p export | |
for t in $LIST; do | |
echo $t | |
mysqldump --defaults-file=$MYCNF --skip-triggers --no-create-info -e $DATABASENAME $t > export/${t}.sql | |
date | |
done |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
DATABASENAME=$1 | |
if [ -z "$DATABASENAME" ]; then | |
echo "no database defined" | |
exit 1; | |
fi | |
MYCNF=/etc/mysql/debian.cnf | |
if [ -f "my_import.cnf" ]; then | |
MYCNF=$(realpath my_import.cnf) | |
fi | |
mysql --defaults-file=$MYCNF $DATABASENAME < schema.sql | |
date | |
for t in $(find export -iname "*.sql" -type f | sort); do | |
echo $(basename $t) | |
mysql --defaults-file=$MYCNF $DATABASENAME < $t | |
date | |
done | |
mysql --defaults-file=$MYCNF $DATABASENAME < routines_triggers.sql |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment