Backing up MySQL database

Backup using innobackupex

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

#!/bin/bash

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 backup.sh 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 https://www.percona.com/downloads/XtraBackup/XtraBackup-0.8/MacOS/xtrabackup-0.8-macos.tar.gz
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

 

Majd Arbash

...

Leave a Reply