networked day to day technical issues

25Feb/120

mysql backups using mysqldump

I keep encountering all sort of bad attempts or at least not optimal attempts at doing Mysql full db backups. While it looks like a trivial task using the mysqldump tool, there are several things one needs to take into account:

  • if you backup all databases into a file (--all-databases) then when you will need to restore only one database from the backup you will be in trouble as in order to restore it you need to restore all databases on a staging server and afterward dump just the needed one or remove all of the rest of databases but with the second approach you still have the "mysql" database changed; or use some tool which can extract from a full dbdump just the needed one (it's basically a text file so you could scrip around it). Update: you can use mysql (mysql -D db_name -o < dump_file.sql) to restore a particular db from a dump done with --all-databases , just take care to have db_name created before attempting the restore
  • if you backup separately each database to it's own dump file then you will quickly learn that you should have also backed up the usernames and passwords which are allowed to access/modify the database
  • if you run a cron script each night which creates the dump(s) and overwrites the previous night's backup file(s) then you may learn it the hard way that a 0 bytes dump or incomplete dump will leave you not only without today's backup but also possibly without yesterday's valid backup (in case it wasn't bad too). So in this case the advice is keep more then tonight's and/or yesterday's backup. I generally keep at least 7 of them if done nightly as generally by the time someone realizes they need a backup a day might have passed. Also it is recommended to have a real backup infrastructure in place (with the associated retention policy)


Below is a bash script which I run nightly , keeps two weeks of db dumps, creates a dump file for each database and also in a separate file (backup_db_PRIVILEGES_and_DB-CREATE_statements.sql) keeps the statements in order to recreated db access privileges and credentials . PS: the "grant privileges" code was taken a long time a go from some ware I really don't remember so if someone feels they need the credits let me know and I'll state it or put a link in.

You may notice that the mysql user password (mysql root user in the below case) is no ware mentioned in the script. It is a really bad practice to put it in the script as it can be seen by someone running "ps" when the backup is in progress. The best practice is to create a .my.cnf file in the home of the user running the backup and add in this file the username and password. Example

# cat /root/.my.cnf
[client]
user=root
password=MY-REALLY-SECRET-PASSWORD

So here is the backup script, edit BACKUP_PREFIX variable and adjust as needed with the path where the backups should be done.

#!/bin/sh
BACKUP_PREFIX="/data/backups"
BACKUP_DIR="${BACKUP_PREFIX}/db-backup-`date +%d-%m-%Y`"
DBUSERNAME='root'
TODAY=`date +%d-%m-%Y`
TWOWEEKSAGO=`date --date="2 weeks ago" +%d-%m-%Y`
mkdir -p $BACKUP_DIR
for i in `mysql --skip-column-names --batch -u root -e 'show databases' | egrep -v '^information_schema$|^mysql$'`; do echo "CREATE DATABASE \`$i\`;";done \
        > ${BACKUP_DIR}/backup_db_PRIVILEGES_and_DB-CREATE_statements.sql
echo >> ${BACKUP_DIR}/backup_db_PRIVILEGES_and_DB-CREATE_statements.sql
  mysql --batch --skip-column-names -u root -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql -u root | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)//;/##/{x;p;x;}' >> ${BACKUP_DIR}/backup_db_PRIVILEGES_and_DB-CREATE_statements.sql
 
for i in `mysql -u root --skip-column-names -B -e 'SHOW DATABASES'| egrep -v 'information_schema'`; do mysqldump -u $DBUSERNAME --opt $i > ${BACKUP_DIR}/backup_db_${TODAY}_${i}.sql; done
mysqldump -u $DBUSERNAME --single-transaction information_schema > ${BACKUP_DIR}/backup_db_${TODAY}_information_schema.sql
tar -cjpf ${BACKUP_DIR}.tar.bz2 $BACKUP_DIR
 
#remove backups older than 2 weeks
rm -f ${BACKUP_PREFIX}/db-backup-${TWOWEEKSAGO}
#remove backup dir
rm -rf $BACKUP_DIR
Filed under: Linux, mysql Leave a comment