As a Sysadmin we often require backing up several databases to meet the SLA or system requirements. Sometimes we need this backing up process so frequently for a very short time period with selected tables only. In this post, we are going to learn & practice few ways and scope of backing databases using mysqldump mainly.
Take back-up to separate files of each database.
To take a back-up, run the mysqldump tool on each available database
mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; done
The result is a list of all your database files, in your current working directory, suffixed with the .sql file extension.
If you want to write to a particular directory, like /var/dump/databases/, you can change the output of the command like this.
mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /var/dump/databases/"$dbname".sql; done
Import files to mysql from each .SQL file
Now that you have a directory full of database files, with the database name in the SQL file, how can you import them all again?
The following for-loop will read all files, strip the “.sql” part from the filename and import to that database.
Warning: this overwrites your databases, without prompting for confirmation. Use with caution!
for sql in *.sql; do dbname=${sql/\.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; done
Backing while ignoring one/multiple tables
We all know how to dump whole database or selected table, Here we are going to ignore few one/multiple tables from our backup.
mysqldump -u root -p DB_NAME --ignore-table=DB_NAME.table1 --ignore-table=DB_NAME.table3 > database.sql
If you want to ignore multiple tables you can use a simple script like this
#!/bin/bash
PASSWORD=XXXXXX
HOST=XXXXXX
USER=XXXXXX
DATABASE=databasename
DB_FILE=dump.sql
EXCLUDED_TABLES=(
table1
table2
table3
table4
tableN
)
IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done
echo "Dump structure"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data --routines ${DATABASE} > ${DB_FILE}
echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info --skip-triggers ${IGNORED_TABLES_STRING} >> ${DB_FILE}