Backing up and restoring MySQL

Instructions and scripts for backing up and restoring MySQL databases.

---

If you are using MySQL and care about protecting your data, we always recommend taking backups with mysqldump.

Backing up MySQL

To include all databases in the backup, you can use the following script.

/usr/local/bin/mysqlbackup.sh

#!/bin/sh
if [ -z $3 ]; then
    echo "Wrong syntax..."
    echo "use: $0 <mysql_root> <mysql_password> <dump_dir>"
    exit
fi
echo Dumping MySQL database to $3.
umask 077
rm $3/*
mkdir $3 &>/dev/null
cd $3
for i in `echo "SHOW DATABASES" |/usr/bin/mysql -s -u $1 -p$2`; do
     /usr/bin/mysqldump --single-transaction -f -u $1 -p$2 $i >$i.sql
    if [ $? -ne 0 ] ; then
            echo ERROR: Fail when dumping $i
    fi
done
du -sh $3
echo Databasedump done

After creating the above file, you need to make it executable:

Command

sudo chmod +x /usr/local/bin/mysqlbackup.sh

To run this script every day at 22:00, you can add it to your crontab. To edit your crontab, run crontab -e. Then, add a line like the following:

crontab

0 22 * * * /usr/local/bin/mysqlbackup.sh root YOUR_DATABASE_PASSWORD /root/mysqldumps

Restoring MySQL

Assuming a system administrator has taken a proper backup with mysqldump, it can still be difficult to restore individual parts of a database. To make backups and restores as fast as possible, mysqldump optimizes the queries that are stored in the backup file.

To restore the entire database, run the following command:

Command

mysql -u -p < database.sql

If you want to extract and restore individual tables, you can do so using the following method.

Command

mysqldump --skip-extended-insert databas

This will output something similar to below:

Output

INSERT INTO `data1` VALUES (1,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (2,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (3,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (4,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (5,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (6,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (7,'abcdefghijklmn..');
INSERT INTO `data1` VALUES (8,'abcdefghijklmn..');

We do this because the regular mysqldump database command can easily become hard to read.

It’s fairly simple to solve, and if you want to restore specific rows, it’s also easier—you can pick those lines out of the dump file and execute them separately instead of reloading the entire database.