Merge difference MySQL DBs into a single new server

Question

I have 4 servers which has it's own MySQL server along with databases and users. Now we have a new server dedicated only for MySQL and we would like to merge those MySQL servers into this new one.

What is the most efficient way to move those MySQL servers data (databases, users) into our new server?

btw, we use centos for all of our servers, all databases are mysql 5.1, and users and db names are uniques

Answer 1

This consider all database names are different on all server.

As you said all servers are mysql 5.1, You can take mysqldump on each server and restore it on new server

Step 1 : Take dump from all old server like below

Everything written in one file: table structures, indexes, triggers, stored procedures, users, encrypted passwords.

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer1.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer2.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer3.sql.gz

mysqldump -u... -p... --routines --triggers --all-databases | gzip > MySQLDataServer4.sql.gz

Step 2 : Copy dump from all old servers to new server

Step 3 : Extract all zips on new server

Step 4 : Import extracted dump files on new server like below

mysql -u... -p... < MySQLDataServer1.sql
mysql -u... -p... < MySQLDataServer2.sql
mysql -u... -p... < MySQLDataServer3.sql
mysql -u... -p... < MySQLDataServer4.sql