Mysql – How to speed up thesql export and import

innodbmigrationmyisamMySQL

I need to perform a DB migration from Server A to Server B.

Server A: MySQL version installed 5.0.6

Server B: MySQL version installed 5.6.20

OS: Both are running RedHat Linux 64bit. 16 RAM memory.

There are MyISAM, InnoDB, Archive databases.

Total size about 4TB.

Largest MyISAM database = 3.2TB

Largest InnoDB database = 85GB

key_buffer_size=2GB.

I took a mysqldump on the largest InnoDB table (85GB)

time /usr/local/mysql/bin/mysqldump -uroot -p --extended-insert --single-transaction ABCEx > ABCEx.sql

After running for 5 hours, the dumpfile has grown to 145GB and yet to finish.

-rw-rw-r-- 1 mysql mysql 145019313099 Mar 2 05:12 ABCEx.sql

I am concern on the export time and also import. Any help to expedite this?

As for MyISAM tables migration, I read that I migrate them by coping the .myd .frm .myi files from Server A to B. Can provide some guide or link if any one has done before? We are talking of 3.2TB of MyISAM database.

Please help….much appreciated 🙂

Best Answer

The best way to migrate tables between servers is to do it in binary "native" format. Doing a serial logical dump (as mysqldump does) not only may take days on a very large database, but it will take even more for recovery.

If you need to maintain availability on the source server for InnoDB tables, the best way is using an utility like MySQL Enterprise Backup (part of Oracle subscription) or the open source similar-tool Percona XtraBackup. Please note that the last one will only work for Linux hosts.

If most of your data is in MyISAM/Archive format, I am afraid that you cannot guarantee availability and consistency at the same time for the performing of the backup. In that case, the best way is to perform a snapshot (your filesystem or environment -VM, LVM- must allow it). Tools like mylvmbackup simplify the process. Worst possible scenario, you can LOCK those tables -FLUSH TABLES WITH READ LOCK- (your are already doing that for the mysqldump), or even just shutdown the whole server, and just copy the .frm, MYI and MYD files away (for MyISAM). Please note that copying InnoDB (.ibd) files away does not simply work; you need MySQL 5.6 and the LOCK TABLES ... FOR EXPORT + ALTER TABLE...DISABLE/ENABLE TABLESPACE construction. Rsync may help lowering the time MySQL is unavailable.

Please note that binary files of MySQL are almost 100% compatible between different nodes, architectures and OSs.

Last option, if you do not have access to the filesystem, is to perform a logical backup in a more optimized way and do it in a parallel fashion. Tools like mydumper simplifies that greatly, and it is very used where XtraBackup or Enterprise backup do not work. It will be slower, but 5-10 times faster that mysqldump in a good scenario.

Related Question