MySQL InnoDB – Fastest Way to Create Local Copy of Live Database

innodbMySQLmysql-5.5mysql-workbenchrestore

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?

Best Answer

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

Related Question