MySQL – Copying tables/databases without using mysqldump

Mysqldump must be one of the most frequently used MySQL utiliies. It’s very easy to use to peform backups with it and it’s reliable. And since its output is a set of SQL statements it’s easy to understand what’s happening and how to use it.

For smallish databases mysqldump it’s perfect, but once you’re getting bigger (like over 5-10 GB) things start to be a little slow:

export – mysqldump works by doing an external table scan, just like a sql client. This means it’s very slow, will block table access during that time and also will destroy any kind of caching temporarily. Performance is greatly affected and the database might even be offline because of the table lock.
import – this means executing the sql dump file with an mysql client, this translates to massive number of INSERT sql statements which you must have guessed by now that it seems very inefficient to use for loading massive amounts of data

We’ll be using innobackupex to perform the backup (5-10x faster than mysqdump) and then some basic file copying and mysql tricks for importing it (10-20x faster than importing a sql dump file).

Export
We’ll be using Percona XtraBackup so make sure you have it installed. Most linux distributions should have a package for “xtrabackup“, if not check https://www.percona.com/software/mysql-database/percona-xtrabackup.

I’m using the following shell script to automate the export process:

#!/bin/sh

# get a string with the current time
date=`date +%y%m%d%H%M`

# create the backup
innobackupex --user=DBUSER --password=DBPASSWORD --no-timestamp /db_backup/$date

# finalize the backup and replays the transactions that happened during the backup process, so everything is in sync
innobackupex --apply-log  --export --no-timestamp /db_backup/$date

# dump the database structure
mysqldump --opt --no-data -u DBUSER --password=DBPASSWORD DBNAME > /db_backup/$date/tables.sql

# create a .tar.bz2 archive of everything exported so far
tar -cjvf /db_backup/$date.tar.bz2 /db_backup/$date

# we have the .tar.bz2 archive, delete the temp directory
rm -rf /db_backup/$date

#!/bin/sh

# get a string with the current time

date=`date +%y%m%d%H%M`

# create the backup

innobackupex --user=DBUSER --password=DBPASSWORD --no-timestamp /db_backup/$date

# finalize the backup and replays the transactions that happened during the backup process, so everything is in sync

innobackupex --apply-log --export --no-timestamp /db_backup/$date

# dump the database structure

mysqldump --opt --no-data -u DBUSER --password=DBPASSWORD DBNAME > /db_backup/$date/tables.sql

# create a .tar.bz2 archive of everything exported so far

tar -cjvf /db_backup/$date.tar.bz2 /db_backup/$date

# we have the .tar.bz2 archive, delete the temp directory

rm -rf /db_backup/$date

The most important commands are the two innobackupex lines, the rest is optional.

Import
First you have to make sure you have an exact database structure match. All tables must already exist and be identical to the ones you’ll be importing. It doesn’t matter if they contain any data or none at all.
That’s one of the reasons my export script is saving the database structure in tables.sql. It makes it easier to just run tables.sql and all tables are re-created with the correct structure.

Next, we have to run the “DISCARD TABLESPACE” SQL statement for every table, eg:

ALTER TABLE users DISCARD TABLESPACE;
ALTER TABLE photos DISCARD TABLESPACE;
ALTER TABLE table99 DISCARD TABLESPACE;

ALTER TABLE users DISCARD TABLESPACE;

ALTER TABLE photos DISCARD TABLESPACE;

ALTER TABLE table99 DISCARD TABLESPACE;

This will detach the InnoDB data files and allow you to overwrite them.
Now you can go ahead and copy the exported cfg/exp/ibd files directly into the MySQL data directory.

Next, we’ll run “IMPORT TABLESPACE” SQL statement for every table, eg:

ALTER TABLE users IMPORT TABLESPACE;
ALTER TABLE photos IMPORT TABLESPACE;
ALTER TABLE table99 IMPORT TABLESPACE;

ALTER TABLE users IMPORT TABLESPACE;

ALTER TABLE photos IMPORT TABLESPACE;

ALTER TABLE table99 IMPORT TABLESPACE;

At this point I recommend running the “ANALYZE TABLE” SQL statement for every table. It sometimes happens that key distribution is not right and the existing indexes are not gonna be used properly, this will fix it. You can also take a shortcut and run “mysqlcheck -a DBNAME” which does it automatically for all tables, it’s the same thing.

And now we’re done, you should have an identical copy of the database at this point :).

Limitations
Only InnoDB tables are handled during the import since DISCARD/IMPORT TABLESPACE is a feature found only in the InnoDB storage engine. MyISAM usage should be pretty low considering starting with MySQL 5.5 the default storage engine is InnoDB.

Tips
If you have a lot of tables, it may seem daunting to manually create a big list of “import/discard tablespace” commands and mention each table. You can use the following SQL statements to generate them automatically:

SELECT CONCAT('ALTER TABLE ', table_name, ' DISCARD TABLESPACE;') AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DBNAME' AND ENGINE='InnoDB'
SELECT CONCAT('ALTER TABLE ', table_name, ' IMPORT TABLESPACE;') AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DBNAME' AND ENGINE='InnoDB'

SELECT CONCAT('ALTER TABLE ', table_name, ' DISCARD TABLESPACE;') AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DBNAME' AND ENGINE='InnoDB'

SELECT CONCAT('ALTER TABLE ', table_name, ' IMPORT TABLESPACE;') AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DBNAME' AND ENGINE='InnoDB'

References
https://www.percona.com/doc/percona-xtrabackup/2.1/index.html
https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/preparing_a_backup_ibk.html
https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_individual_tables_ibk.html
http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html

Leave a Reply Cancel reply