How to Backup MySQL database using Percona's innobackupex

Backup using innobackupex

This script backups your database and uploads it to Amazon S3:


timestamp=`date “+%Y-%m-%d_%H-%M-%S”`

innobackupex –user=[username] –password=[password] –parallel=3 –safe-slave-backup –slave-info –no-timestamp /data/backup/${timestamp}_slave innobackupex –user=[username] –password=[password] –apply-log /data/backup/${timestamp}_slave/

tar czf /data/backup/${timestamp}_slave.tgz /data/backup/${timestamp}_slave

/usr/local/bin/aws –region [region] s3 cp /data/backup/${timestamp}_slave.tgz s3://[bucket]/[backup-path]/`date “+%Y”`/`date “+%m”`/`date “+%d”`/

rm -rf /data/backup/${timestamp}_slave rm -f /data/backup/${timestamp}_slave.tgz

You can easily write this script in and schedule a cronjob using crontab -e for performing this backup on the daily basis

In order to restore the backup downloaded you have to:

# extracting backup contents to BACKUP-DIR tar -xzvf /data/backup/${timestamp}_slave.tgz innobackupex –copy-back /path/to/BACKUP-DIR

innobackupex: Finished copying back files.

111225 01:08:13 innobackupex: completed OK!

You may need to change ownership of files from: chown -R mysql:mysql /var/lib/mysql

Downloading innobackupex on Mac:

cd /tmp curl -O tar -xzf xtrabackup-*-macos.x86_64.tar.gz cd xtrabackup*

checking the files downloaded

ls cp * /usr/local/bin

Now you can call the backup perl script, using innobackupex-1.5.1 command

# will restore the backup to MySQL datadir (determined by value is mysql my.cnf) innobackupex –copy-back [path/to/Backup-dir]

change the ownership in data-dir to make sure it’s correctly set up

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

specify the configuration file location

innobackupex –copy-back –defaults-file=/etc/my.cnf –[path/to/backup-dir]

Backup using mysqldump

# dumping the database mysqldump -u[user] -p[password] -h[hostname] [database] > /tmp/backup.sql

restoring the database

mysql -u[user] -p[password] -h[hostname] [database] < /tmp/backup.sql