Monthly Archives: May 2011

Faster backup of MySQL database with XtraBackup tool

The most common way to export and import databases is using mysqldump. This is fine for databases of small or medium size. What about huge databases, in case of a social networking site, for example? It takes hours to days to backup such databases. This is because the databases are not copied. Instead, SQL statements are generated to re-create the database. While importing, these statements are executed to create the database from scratch.

What is the alternative?

Instead of running SQL queries to create a database identical to the original one, the binary data can be copied, then crash-recovery can be performed on this to make it consistent. This is what Percona‘s XtraBackup utility (used in a lot of  web applications, including facebook! ) does exactly.

How to install?

The Free Software for most of the platforms can be downloaded from here. Installing them is not a thing to explain at all. Just double clicking the downloaded file should work fine.

Prerequisites

  • MySQL should be installed (If not run sudo apt-get install mysql-server mysql-client for Ubuntu machines)
  • It is assumed that the MySQL version is 5.1 or more

Step-by-step

The following steps may help you trying out this, without crashing your database by doing something wrong. A set of scripts to implement this is available here.

  1. Start mysql server if it is not running. (sudo service mysql start should work in most GNU/Linux distros).
  2. Connect with the mysql server using mysql -u username -p
  3. Create a sample database, say test.
  4. Create a sample table in test, say sample with ENGINE=InnoDB (The tool works with MyISAM engine as well; just for sake of simplicity).
  5. Insert some values into the table.
  6. Disconnect the client by just saying exit or just pressing Ctrl+C
  7. Now its time to backupour sample database. Run the following command:
    sudo innobackupex-1.5.1 --user=mysqlusername --password=mysqlpassword /data/backups/ --databases="test"

    Innobackupex is a wrapper script for xtrabackup for ease of use. The last two comman-line arguments above deserve special mention.

  1. The last argument is a list of databases that should be backed up. Ensure that this list has all databases having InnoDB tables. Otherwise all databases including MyISAM tables would be backed up.
  2. The last but one argument is the location where you want your backup data to be stored. It can be any path of your choice. In this location the backup files will be stored in a new directory whose name is the timestamp at which the above command was run. E.g. 2011-05-27_00-26-12
  • Now connect again with server and drop the database, so that it can be recovered from our backup.
  • Stop the mysql server (sudo service mysql stop)
  • Now run the following set of commands (first command to prepare the backup to be restored, the second one to restore the data)
    sudo innobackupex --apply-log /data/backups/2011-05-27_00-26-12
    sudo innobackupex --copy-back /data/backups/2011-05-27_00-26-12

    You may need to change the permissions of the following files/folders after executing above commands:

    • /var/lib/mysql/ibdata1
    • /var/lib/mysql/iblogfile0
    • /var/lib/mysql/iblogfile1
    • /var/lib/mysql/test/
  • Now start the MySQL server. MySQL will perform crash recovery over the data automatically to make it consistent.
  • Connect with the mysql and you can see your database alive back!
  • In my next post I’ll explain how to perform incremental backups using XtraBackup tool.
    Partial backups(backing up selected databases or tables) didn’t work fine for me. So I didn’t continue with XtraBackup hence didn’t post anything on incremental backups.