2

There is a very good connection between the two machines (MySQL 5.5.28): 

mysqldump -u root -p'password' --all-databases | ssh root@SERVERNAME mysql -u root -p'password' --all-databases

So AFAIK this oneliner is like taking a MySQL dump on server#A then copy the dump to server#B then "import it on server#B.

Q1: nothing else gets in the DB? This is the fastest way of doing this? :) or are there any faster? Can anything go wrong when this is going?

Q2: how can I test that the sending was fully successful on server#B? How to check that the two DB's are "bit-by-bit" the same?

gasko peter
  • 5,514
  • 1
    They probably will not be bit-by-bit the same. You have 3 checks: Sense-checing: check that all the tables and databases exist, and that they have the right number of rows. Perform a few random queries and see that you get the same results on both servers. Testing: Configure a QA application pointing at the copied server. Run some reports on both servers and see that the results are the same. UAT: Set up a list of functions to test and let a group of users use the QA server. Finally swing your prod application to point to the new DB server and repeat the full user acceptance testing. – Johan Mar 12 '13 at 07:31

1 Answers1

1

First of all, you should be aware that it is very important which storage engines do you use.

Lets assume that both of your mysql servers have the same version. Then you could use mysqlhotcopy for MyISAM tables. This should be very fast, but this approach is restricted to MyISAM tables. Because mysqlhotcopy copies the files directly, you can very easiely answer your question, if the two DBs are "bit-by-bit" the same, with the aid of diff.

INNODB is the default storage engine from version 5.5.5. If you use INNODB storage engine the situation looks different. In that case you can't use mysqlhotcopy and you are left with mysqldump.

A few things can go wrong if you use mysqldump. For example, if the database on server#A has a view to an non existent table, mysql tries to create a view to a non existent table on the right side of your pipe and fails. You can avoid an abort in such cases by using the --force option.

mysql --force ...

I have copied a whole mysql database with mysqldump and I compared dumps from both servers and found the dumps differes in the timestamps only. This could be a solution for you.

You should also have a look at other options of mysqldump. For example I think of

--add-drop-database
--add-drop-table
--single-transaction    ( for transactional tables only (like INNODB))
Ronin Tom
  • 334