lundi 2 mars 2015

How to speed up mysql export and import


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






Aucun commentaire:

Enregistrer un commentaire