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;
innobackupex --user=bkpuser --password=password --redo-only /storage/backups
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
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:
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/
