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.<\/p>\n
There are different ways to backup MySQL databases \u2013 using mysqldump, maatkit tools, ZFS snapshots, Xtrabackup, LVM, etc. Here we will cover three of them – mysqldump, maatkit and the Xtrabackup.<\/p>\n
The simplest way to backup your database is mysqldump<\/strong>. Just change parameters to your actual ones:<\/p>\n Restoration from dump is quite simple. Here is an example: Maatkit<\/a> is a powerful toolkit for working with MySQL created by Baron Schwartz from Percona<\/a>.<\/p>\n mk-parallel-dump<\/strong> 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<\/strong>.<\/p>\n Note!<\/strong><\/p>\n As stated in mk-parallel-dump documentation:<\/a><\/p>\n 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.<\/p><\/blockquote>\n So be careful with it.<\/strong><\/p>\n Firstly we should install mk-parallel-dump<\/strong>. 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<\/a>.<\/p>\n Let’s try to setup it on Fedora:<\/p>\n As you can see there is no Perl package Term::ReadKey on this server. Now we can set up maatkit<\/strong>.<\/p>\n To install .deb package on Ubuntu or Debian you should use dpkg<\/strong>. XtraBackup<\/a> is intended for making non-blocking backups for InnoDB, XtraDB, and MyISAM databases. And it really has \u00a0a lot of features.<\/p>\n Let’s try it. To install \u00a0XtraBackup<\/strong> pick out your operation system and download appropriate package from the download page<\/a>. Here we will use .rpm.<\/p>\n To backup all your databases run: Restoration isn’t harder than installation, but MySQL server should be stopped:<\/p>\n If there is a line “innobackupex-1.5.1: completed OK!” everything is okay and you can start server.<\/p>\n 1. \u00a0Unfortunately Maatkit<\/strong> is not ready for production use yet. So be careful \u00a0with it.<\/p>\n 2. \u00a0If your databases have small size use mysqldump<\/strong>. It is the best solution for almost all MySQL users.<\/p>\n 3. \u00a0If you have huge databases consider using Percona’s xtrabackup<\/strong> tools.<\/p>\n 1. mysqdump<\/a> description from\u00a0MySQL 5.1 Reference Manual;<\/p>\n 2. maatkit tools<\/a>;<\/p>\n#!\/bin\/sh\r\nDBPASS=realystrongpassword\r\nDBUSER=root\r\nBACKUP_DIR=\/home\/backup\r\nDAYS=60\r\nDATEPREFIX=$( date +%y%m%d-%H%M )\r\nDATABASES=$( mysql -u$DBUSER -p$DBPASS -Bse 'show databases' | grep -v information_schema )\r\nfor db in $DATABASES; do\r\nmysqldump --opt --quick -u $DBUSER -p$DBPASS $db | gzip -9 &amp;gt; $BACKUP_DIR\/mysql-$dbf-$DATEPREFIX.sql;\r\ndone\r\nfind $BACKUP_DIR -mtime +$DAYS -name *mysql-*.sql.gz -delete\r\n<\/pre>\n
How to restore<\/h3>\n
\n[root@playground ~]# gzip -d -c \/home\/backups\/mysql-zabbix-101126-0505.sql.gz | mysql -uroot -p zabbix<\/code><\/p>\nMaatkit mk-parallel-dump<\/h2>\n
How to backup<\/h3>\n
[root@playground ~]# rpm -ivh --test maatkit-7041-1.noarch.rpm\r\nerror: Failed dependencies:perl(Term::ReadKey) >= 2.10 is needed by maatkit-7041-1.noarch\r\n[root@playground ~]#<\/pre>\n
\n[root@playground ~]# yum install perl-TermReadKey<\/code><\/code>
\nIt’s possible you don’t have installed some other Perl libraries, for instance perl-IO-Compress. To fix that use \u201cyum search<\/strong>\u201d or \u201captitude search<\/strong>\u201d, depending on what operation system you use.<\/p>\n[root@playground ~]# rpm -ivh maatkit-7041-1.noarch.rpm<\/pre>\n
Preparing... ########################################### [100%]<\/pre>\n
1:maatkit ########################################### [100%]<\/pre>\n
[root@playground ~]#<\/pre>\n
\nAt finally we can try\u00a0mk-parallel-dump<\/strong>
\n[root@playground ~]#mk-parallel-dump --user root --password verystrongpassword --base-dir \/home\/backups\/dbbk --lock-tables -v<\/code>
\nOr use yet another simple script<\/a>:<\/p>\n#!\/bin\/sh\r\nDBUSER=root\r\nDBPASS=verystrongpassword\r\nBACKUP_DIR=\/home\/backups\/bk-$( date +%y%m%d-%H%M )\r\n&amp;amp;amp;amp;lt;pre&amp;amp;amp;amp;gt;mkdir $BACKUP_DIR || { echo \"Couldn't create $BACKUP_DIR.\" ; exit 1 ; }\r\nmk-parallel-dump --user $DBUSER --password $DBPASS --base-dir $BACKUP_DIR\/$DATEPREFIX --lock-tables --ignore-databases mysql\r\n<\/pre>\nHow to restore<\/h3>\n
[root@playground ~]#mk-parallel-restore --user=root --password=verystrongpassword \/home\/backups\/101126-1726\/ --databases=sakila<\/code><\/p>\nPercona XtraBackup<\/h2>\n
Installation is trivial:\r\n[root@playground ~]# rpm -ivh xtrabackup-1.4-74.rhel5.x86_64.rpm\r\nwarning: xtrabackup-1.4-74.rhel5.x86_64.rpm: Header V3 DSA\/SHA1 Signature, key ID cd2efd2a: NOKEY\r\nPreparing... ########################################### [100%]\r\n1:xtrabackup ########################################### [100%]\r\n[root@playground ~]#<\/pre>\n
How to backup<\/h3>\n
\n[root@playground ~]# innobackupex-1.5.1 --user=root --password=verystrongpassword \/home\/backups\/<\/code>
\ninnobackupex<\/strong> will create directory with timestamp and put all files there. So you don’t have to create it by yourself using it.<\/p>\nHow to restore<\/h3>\n
[root@playground ~]#innobackupex-1.5.1 --copy-back \/home\/backups\/xtrabackup\/2010-11-26_07-35-39<\/code><\/p>\n... a lot of lines ...<\/code><\/p>\n101126 07:48:52 innobackupex-1.5.1: completed OK!<\/code><\/p>\n[root@playground ~]#<\/code><\/p>\nConclusion<\/h2>\n
Further readings<\/h2>\n