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?
- MySQL should be installed (If not run
sudo apt-get install mysql-server mysql-clientfor Ubuntu machines)
- It is assumed that the MySQL version is 5.1 or more
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.
- Start mysql server if it is not running. (
sudo service mysql startshould work in most GNU/Linux distros).
- Connect with the mysql server using
mysql -u username -p
- Create a sample database, say test.
- Create a sample table in test, say sample with
ENGINE=InnoDB(The tool works with MyISAM engine as well; just for sake of simplicity).
- Insert some values into the table.
- Disconnect the client by just saying
exitor just pressing Ctrl+C
- 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.
- 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.
- 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
sudo service mysql stop)
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:
In my next post I’ll explain how to perform incremental backups using XtraBackup tool.