4

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 :)

Improve this question
1
  • The dump file is running almost at disk speed. You need a lot more than 5 hours just to read the TBs you have. And if you are writing to the same machine, then you need to consider the time taken to write approximately the same number of TBs for the dump file. So, you were not even 10% of the way through the dump. The reload may take much longer because the indexes will need to be rebuilt.
    – Rick James
    Mar 18, 2015 at 0:19

2 Answers 2

Reset to default
4

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.

Improve this answer
4
  • We are planning to do it with a scheduled downtime of probably not more than 12hours. Can elaborate more on the copying of InnoDB with LOCK TABLES ...FOR EXPORT + ALTER TABLE ...DISABLE/ENABLE TABLESPACE. If I can shutdown mysqld, do I still have to lock InnoDB tables? Can't I just copied them over? I read that I have to treat InnoDB different hence copying (.idb) might not work. I'd explore Xtrabackup or mysqldumper.
    – Haans
    Mar 3, 2015 at 13:18
  • I found this >dev.mysql.com/doc/refman/5.1/en/innodb-backup.html Does that mean that I can do native backup of Innodb database provided I can shutdown mysql instance?
    – Haans
    Mar 4, 2015 at 11:43
  • @user3886311 you can "clone" the server (perform a full backup) that way, not a per-table copy. For the latter, MySQL must be running and use this: dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html
    – jynus
    Mar 4, 2015 at 12:19
  • For MyISAM tables, I could use the copying .frm .myd files method. I can't use the filesystem method as the source is not configured with LVM. My concern is the copying .frm will replicate tables over to a slave server.
    – Haans
    Mar 12, 2015 at 2:46
0

I you can't or don't want to make "native" binary backups than mydumper is a good alternative. dumping a loading is much faster than regular mysqldump backups.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.