Everybody knows – backups are very important. Today a lot of web projects use MySQL to keep data. So you need to know how to set up reliable but simple backup of all your databases. And even if you are only developing you might probably need some tools to make quick dumps and to restore databases.
There are different ways to backup MySQL databases – using mysqldump, maatkit tools, ZFS snapshots, Xtrabackup, LVM, etc. Here we will cover three of them – mysqldump, maatkit and the Xtrabackup.
mysqldump
How to backup
The simplest way to backup your database is mysqldump. Just change parameters to your actual ones:
#!/bin/sh DBPASS=realystrongpassword DBUSER=root BACKUP_DIR=/home/backup DAYS=60 DATEPREFIX=$( date +%y%m%d-%H%M ) DATABASES=$( mysql -u$DBUSER -p$DBPASS -Bse 'show databases' | grep -v information_schema ) for db in $DATABASES; do mysqldump --opt --quick -u $DBUSER -p$DBPASS $db | gzip -9 > $BACKUP_DIR/mysql-$dbf-$DATEPREFIX.sql; done find $BACKUP_DIR -mtime +$DAYS -name *mysql-*.sql.gz -delete
How to restore
Restoration from dump is quite simple. Here is an example:
[root@playground ~]# gzip -d -c /home/backups/mysql-zabbix-101126-0505.sql.gz | mysql -uroot -p zabbix
Maatkit mk-parallel-dump
How to backup
Maatkit is a powerful toolkit for working with MySQL created by Baron Schwartz from Percona.
mk-parallel-dump is a tool to dump MySQL tables in parallel. It’s especially important if your databases are huge and it takes a lot of time to backup it with mysqldump.
Note!
As stated in mk-parallel-dump documentation:
mk-parallel-dump is not a backup program! It is only designed for fast data exports, for purposes such as quickly loading data into test systems. Do not use mk-parallel-dump for backups.
So be careful with it.
Firstly we should install mk-parallel-dump. If you are using Fedora or Centos download .rpm package, or .deb package for Debian and Ubuntu from http://code.google.com/p/maatkit/downloads/list.
Let’s try to setup it on Fedora:
[root@playground ~]# rpm -ivh --test maatkit-7041-1.noarch.rpm error: Failed dependencies:perl(Term::ReadKey) >= 2.10 is needed by maatkit-7041-1.noarch [root@playground ~]#
As you can see there is no Perl package Term::ReadKey on this server.
[root@playground ~]# yum install perl-TermReadKey
It’s possible you don’t have installed some other Perl libraries, for instance perl-IO-Compress. To fix that use “yum search” or “aptitude search”, depending on what operation system you use.
Now we can set up maatkit.
[root@playground ~]# rpm -ivh maatkit-7041-1.noarch.rpm
Preparing... ########################################### [100%]
1:maatkit ########################################### [100%]
[root@playground ~]#
To install .deb package on Ubuntu or Debian you should use dpkg.
At finally we can try mk-parallel-dump
[root@playground ~]#mk-parallel-dump --user root --password verystrongpassword --base-dir /home/backups/dbbk --lock-tables -v
Or use yet another simple script:
#!/bin/sh
DBUSER=root
DBPASS=verystrongpassword
BACKUP_DIR=/home/backups/bk-$( date +%y%m%d-%H%M )
<pre>mkdir $BACKUP_DIR || { echo "Couldn't create $BACKUP_DIR." ; exit 1 ; }
mk-parallel-dump --user $DBUSER --password $DBPASS --base-dir $BACKUP_DIR/$DATEPREFIX --lock-tables --ignore-databases mysql
How to restore
[root@playground ~]#mk-parallel-restore --user=root --password=verystrongpassword /home/backups/101126-1726/ --databases=sakila
Percona XtraBackup
XtraBackup is intended for making non-blocking backups for InnoDB, XtraDB, and MyISAM databases. And it really has a lot of features.
Let’s try it. To install XtraBackup pick out your operation system and download appropriate package from the download page. Here we will use .rpm.
Installation is trivial: [root@playground ~]# rpm -ivh xtrabackup-1.4-74.rhel5.x86_64.rpm warning: xtrabackup-1.4-74.rhel5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY Preparing... ########################################### [100%] 1:xtrabackup ########################################### [100%] [root@playground ~]#
How to backup
To backup all your databases run:
[root@playground ~]# innobackupex-1.5.1 --user=root --password=verystrongpassword /home/backups/
innobackupex will create directory with timestamp and put all files there. So you don’t have to create it by yourself using it.
How to restore
Restoration isn’t harder than installation, but MySQL server should be stopped:
[root@playground ~]#innobackupex-1.5.1 --copy-back /home/backups/xtrabackup/2010-11-26_07-35-39
... a lot of lines ...
101126 07:48:52 innobackupex-1.5.1: completed OK!
[root@playground ~]#
If there is a line “innobackupex-1.5.1: completed OK!” everything is okay and you can start server.
Conclusion
1. Unfortunately Maatkit is not ready for production use yet. So be careful with it.
2. If your databases have small size use mysqldump. It is the best solution for almost all MySQL users.
3. If you have huge databases consider using Percona’s xtrabackup tools.
Further readings
1. mysqdump description from MySQL 5.1 Reference Manual;
2. maatkit tools;
Didn’t find the answer to your question? Ask it our administrators to reply we will publish on website.