Backup and Restore using mysqlbackup from MySQL Enterprise

All this was done in a lab.  Don't do this on a production server.  There is my warning and disclaimer.

I did a full backup of my MySQL instance which resides under /var/lib/mysql.   I then delete all the data, all the ib* files, and mysql.   After deleting those files I ran a full recovers.  Here is how I did it.

Full Backup

Run the following command to do a full backup.   What this will do is backup everything in /var/lib/mysql and with the backup-and-apply-log it will go back and grab anything that has changed during the backup.   Also, that argument prepares the backup for a restore.

./mysqlbackup --user=root --password  --backup-dir=/storage/backups backup-and-apply-log

 

You need to specify a backup directory that is empty of mysqlbackup will complain.

When the backup completes you will have a message at the end telling you that it was successful.

I cd to the location of my backup and everything looked like it was there.   I ran a du -sh * and results looked the same.

Full Restore

./mysqlbackup --defaults-file=/storage/backups/backup-my.cnf --datadir=/var/lib/mysql --backup-dir=/storage/backups copy-back

Under "--default-file=" I originally put /etc/my.cnf thinking that I should use the my.cnf file I created.  However, part of the backup creates a "backup-my.cnf" with a few lines in it.  You should use this file for the restore or you could put these lines in your restore command.  It is much easier to just use the file that was created.

I started mysql /etc/init.d/mysql start, but it failed with the PID error.   I looked at my restored directory and noticed many of the files was owned by root, so I ran this command on the mysql directory.

chown -R mysql:mysql mysql

Now MySQL starts and every thing looks great.