Percona tools have a fast and reliable utility to backup your database named
innobackupex. And the most important is you have not to stop the database to make your backup consistent (it means if you simply use
mysqlbackup without additional tricks on a running in production database, you may got a backup file where some tables have some changes and related tables don't have corresponding changes). So, let me show you some commands how to use the
innobackupex.
1. How to install innobackupex
For Debian and Ubuntu:
Add the Percona repository:
wget https://repo.percona.com/apt/percona-release_0.1-3.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-3.$(lsb_release -sc)_all.deb
sudo apt-get update
And install:
sudo apt-get install percona-xtrabackup
For CentOS and RedHat:
Add Percona YUM repository:
wget http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
rpm -ivH percona-release-0.1-3.noarch.rpm
And install the tool:
yum install percona-xtrabackup-22
2. How to backup
innobackupex --stream=xbstream --parallel=4 --compress --compress-threads=2 /var/lib/mysql > /backup/backup.xbstream
So, as you can see, the backup contains all the databases on the server. The params are self-explaining, so adjust as you wish. The "stream" param specify the output file to be something similar (and not compatible) to .tar.gz.
3. How to restore
It is much more trickier...
xbstream -x < /backup/backup.xbstream
for i in $(find -name "*.qp"); do qpress -vd $i $(dirname ${i}) && rm -f $i; done
innobackupex --apply-log ./
service mysql stop
rm -Rf /var/lib/mysql/*
innobackupex --copy-back ./
chown -Rf mysql.mysql /var/lib/mysql
service mysql start
As you can see, you need the qpress tool from the percona repository. At first, we decompress the backup to database files, apply the binary log to these files to make the backup consistent, and then we restore the database files in a hard way (removing and replacing everything there!).
Hope you will never need this. Good luck, anyway!
