Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@mklooss
Last active November 13, 2023 06:58
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mklooss/962543d362a605b12c78ebeb71669188 to your computer and use it in GitHub Desktop.
Save mklooss/962543d362a605b12c78ebeb71669188 to your computer and use it in GitHub Desktop.
Export MySQL/MariaDB Database in Tables
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
[client]
user=DATABASE_USER
password=DATABASE_PASSWORD
host=DATABASE_HOST (localhost / 127.0.0.1)
port=DATABASE_PORT (Default: 3306)
#!/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
#!/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