|
Here is a basic question, what is the fastest way to transfer a MySQL database?
Here are the variables at play:
1) Database is just shy of 3GB
2) Transfer is between two servers running different versions of MySQL
3) There will not be usage of the database during transfer, I can even shut down MySQL if needs be.
4) The database is mostly MYISAM but with a few InnoDBs and a couple Memory (VBulletin)
5) Both servers are Linux based and I have root access on both.
I've done the transfer using a mysql dump, but the import took ages (more than a working day).
I've done the transfer using a hotcopy, but it's no good when going across MySQL versions, all the tables are corrupt and need repair, and even after that it doesn't work quite right, plus I haven't found any bath repair function, I have to repair tables one by one, that's no good.
I've also tried turning off MySQL and copying the raw database files directly, similar issues as with hotcopy, can't do that across different versions.
I've got Navicat and using that to do a data transfer solves the mismatched version issue, but like a dump, it takes a long time to transfer. During tests, still over 7 hours.
Here are some questions then:
I'd like to use Navicat, or some type of database manager, because I have multiple databases to manage/backup etc.. and am looking for a good tool. So that being said I'm not sure if I can make Navicat any faster, what to put the settings on (lock or unlock, use transaction, complete and/or extended inserts, etc)
Maybe this is normal but doing a data transfer for this under 3GB database is over 7 hours. That seems ridiculous give that some DBs are way larger than this and transferring data seems like such a normal task. I'm kind of lost. Is it because my work computer is a "middle man" in that I'm using it to funnel data between two servers? Is there a way to go on one server and "pull" the data in directly and save time? Can I optimize Navicat transfer settings so that it's faster? Can I import data from a hotcopy directly on the one server but have it "update" the structure and tables for the newer version of MySQL automatically?
I'm looking for options here!
Thanks for the help, I'm on a deadline and don't really want a 7+ hour transfer for this!
|
