Blog

Three ways to make MySQL database dump

On 25.11.2010, in mysql, by Oleksii Tykhonov
0

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

That’s all. Try it out. You can download this simple script from here.

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;

3. Percona XtraBackup.

Didn't find the answer to your question? Ask it our administrators to reply we will publish on website.

Tagged with:
 

Leave a Reply