Backing up with Percona's Xtrabackup

 Percona's backup allows for full and incremental backups and it is open source.  I tried it on a Redhat 6.2 server called Redhat62 and it worked great.   I installed a new version of MySQL and did a restore using files I had backed up using MySQL Enterprise backup and to my surprise innobackupex restored the data and it worked.  The only thing I had to do was change permissions before I started MySQL.

Here is what I did:

Create a backup user for MySQL backups.

mysql>; CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’password’;
mysql>; REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’@'localhost';
mysql>; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql>; FLUSH PRIVILEGES;

To do a full backup of MySQL run this command:
innobackupex --user=bkpuser --password=password --parallel=4 --use-memory=4G /path/to/BACKUP-DIR

My backup directory on Redhat62 is /storage/backups

innobackupex reads the my.cnf file so it knows where the MySQL data is located.  

By Default innobackupex uses only 100MB for running the backup.  To increase the speed of the backup  you need to allocate more memory.  Set the "--use-memory=" to most that  you have free.

You can increase the number of threads by using the parallel parameter.

YOU NEED TO PREPARE THE DATA FOR RESTORE

After the backup is completed you need to run the "--apply-log" before you can restore the data.  The "--apply-log" replays committed transaction in the log files and rolls back uncommitted transactions.

innobackupex  --apply-log /path/to/BACKUP-DIR/time_stamp

To restore you need to run this command:  MYSQL NEEDS TO BE SHUTDOWN DURING THE RESTORE

innobackupex  --copy-back /path/to/BACKUP-DIR

I had previously had databases in the /var/lib/mysql directory with the same name.  innobackupex failed with a message.  I deleted all of the files under /var/lib/mysql and ran it again.  It completed successfully.   I guess since it is doing a full restore it doesn't write over the files that are there.  You will need to clear our all of the files in the directory to which it is restoring.

After the restore is done run this command:

chown -R mysql:mysql /var/lib/mysql

You can do incremental backups with innobackupex.  First you need to do a Full backup and then you can run incrementals.   If you are going to use incremental backups then your Full backups you will need to have the --redo-only option added to it.  This is explained in Pecona's manual:

Preparing an Incremental Backup with innobackupex Preparing incremental backups is a bit different than full
ones. This is, perhaps, the stage where more attention is needed:
• First, only the committed transactions must be replayed on each backup. This will put the base full backup
and the incremental ones altogether.
• Then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup.
If you replay the commit ed transactions and rollback the uncommitted ones on the base backup, you will not be able
to add the incremental ones. If you do this on an incremental one, you won’t be able to add data from that moment
and the remaining increments.
Having this in mind, the procedure is very straight-forward using the --redo-only option, starting with the base
backup:

innobackupex --user=bkpuser --password=password --redo-only /storage/backups

To run an incremental you need to issue this command:

innobackupex  --user=bkpuser --password=password --incremental /storage/backups --incremental-basedir

What if you only want to restore 1 database or 1 table instead of the whole backup?  Here is what you need to do. 

Start up a second session of MySQL on the same server, but point it to your backup directory that is holding the data you want to restore.

To start a second session:

mysqld --basedir=/usr --user=mysql --log-error=/path/to/BACKUP-DIR --open-files-limit=4096 --pid-file=/path/to/BACKUPDIR/Time_Stamp/mysql.pid --port 3307 --datadir=/path/to/BACKUPDIR/Time_Stamp

Here is an example:

mysqld --basedir=/usr  --user=mysql --log-error=/storage/backups/mysql/mysql.log --open-files-limit=4096 --pid-file=/storage/backups/mysql/2012-09-14_10-01-55/mysql.pid --port 3307 --datadir=/storage/backups/mysql/2012-09-14_10-01-55

After you start your second session of MySQL you can now do a MySQLdump the database or table that you want to restore.

To restore a database, type this:

mysqldump -P 3307 -u bkpuser -p password databasename > databasename.sql

To restore a table, type this:

mysqldump -P 3307 -u bkpuser -p password databasename tablename > tablename.sql

I put this information from the following sources: Percona's xtrabackup pdf file found on their website,http://www.tekovic.com/mysql-hot-backup-with-xtrabackup-on-centos and  http://steveswanson.wordpress.com/2009/04/21/exporting-and-importing-an-individual-mysql-table/

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.