Backup your MySQL servers and restore them quickly

Tweet

It's easy to use the mysqldump command. Really?

I have a project with a database with more 5Gb (without indexes). Before, I used the following command to backup my database:

mysqldump --single-transaction \
          --opt \
          -Q \
          your_db_name > backup.sql

With a big MySQL database it's possible do not have a complete and consistent backup (eg: connection reset by server, etc.). Another disadvantage is having a huge file with all table definitions with datas and when you want restore a part of database it's necessary to edit the backup.sql file.

The solution#

I found better options for mysqldump to create 2 files per table: one for the SQL table definition and an other for flat datas of the table. With the flat file of datas the restoration is faster instead of one big SQL file.

mkdir -p /path/to/backup
chown mysql:mysql /path/to/backup
mysqldump --single-transaction \
          --quick \
          --skip-extended-insert \
          --tab=/path/to/backup \
          your_db_name

To restore your dump you can use this script:

#!/bin/bash

# to launch this script please
# go in dir of backup files and launch:
# restore.sh | mysql your_db_name

echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;"
for f in `ls -1 *.sql` ; do
    echo "source `pwd`/$f"
done
echo "COMMIT;"

for f in `ls -1 *.txt` ; do
    table=${f/.txt/}
    echo "load data infile '`pwd`/$f' INTO TABLE $table;"
done

echo "SET FOREIGN_KEY_CHECKS = 1;"
echo "COMMIT;"

And launch this command:

./restore.sh | mysql your_db_name

With this practice, the backup and the restoration will be much faster than before because the datas are saved in flat files.

Tip to restore just one table on your MySQL server#

Your backup contains 2 files by table, it's possible to restore just one table keeping the files of the table concerned.

source: LinuxFr.org