We have a
MySQL database (InnoDB) on our
live Ubuntu 12.04 server which has grown quite large (25gb+).
Every month or so we need to copy this database to our development machines to work on locally.
Previously we have exported the database with
MySQL Workbench, downloaded the
.sql file to our development machines, and then imported it, again with
MySQL Workbench. The problem is now the database is so large, we can’t do the restore quick enough for it to finish over the weekend! So on Monday morning we have a half-imported database to work with.
What do other people do in this situation?
The only solution I can think of so far, is doing the restore to an unused machine, then
copying the data to all our development machines (5 of them). Would this work?
You can take a backup from the live MySQL with Xtrabackup.
Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions:
Install the key:
# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
Add the source file:
# cat /etc/apt/sources.list.d/percona.list deb http://repo.percona.com/apt precise main deb-src http://repo.percona.com/apt precise main
Update the local cache:
# apt-get update
And install xtrabackup:
# apt-get install xtrabackup
To take a copy of the database run following:
# innobackupex .
It will create a directory like “2010-03-13_02-42-44”. Copy that directory to a development box, apply redo log:
# innobackupex --apply-log /data/backups/2010-03-13_02-42-44/
Then the directory is ready to use. Copy it back to MySQL datadir and fix ownership:
# cp /data/backups/2010-03-13_02-42-44/ /var/lib/mysql # chown -R mysql:mysql /var/lib/mysql
Then you may start MySQL.
More details you can find on http://www.percona.com/doc/percona-xtrabackup/2.1/how-tos.html
You may Also Like: