How to correctly and quickly transfer a MySQL database from one server to another

Question

MySQL

There is a MySQL database: a hundred tables (InnoDB and MyISAM), 30 million records, 5 GB.
How to correctly and quickly transfer this database from one server to another?
On the old server MySQL 5.0.51a, on the new 5.1.56.
Is it possible to just copy the files? Or is mysqldump a better standard option?

Answer 1

mysqldump, yes.

Answer 2

The option with mysqldump is quite working. You can also try the method that was used when moving the habr (see the "Base" section)

Answer 3

I support bondbig
transferred the database to 17GB, mysqldump turned out to be the best option

Answer 4

If it is not possible to "stop" the database server during the transfer, you can try to replicate, in the case of mysqldump, there is a chance that the transferred database will not be completely up to date.

Answer 5

Just copying the files will not work, because. You have InnoDB tables.
Use mysqldump.

Answer 6

what about LOAD DATA INFILE? - must be fast

Answer 7

I would do the following
: we will lock the tables, we will
copy the data
from the second database with files, we will make a replica , we
will unlock the tables, we will start the
replica
and then we will replace the master at any convenient moment in a matter of seconds

Answer 8

if myisam, then the percon is not a percon, it won’t work without a lock, it’s more correct to remove the backup from the replica, so we don’t load our master in any way and don’t interrupt his work

Answer 9

For example, like this:
newserver# ssh -C oldserver mysqldump ... | pv | mysql -fbp

Answer 10

With Mysqldump, the database can take a very long time to unfold.
I recommend innobackupex from www.percona.com. Works much more efficiently than native mysqldump

Answer 11

sypex dumper is also a cool thing, fast and works even if there is no ssh access