5

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

4
  • Does MySQL Version is same on all older as well as on new servers ? Feb 26, 2014 at 9:21
  • yes, they are mysql 5.1
    – Bonn
    Feb 26, 2014 at 9:39
  • all database names are different on all server ? Feb 26, 2014 at 9:50
  • they're all unique (username, db)
    – Bonn
    Feb 26, 2014 at 10:01

1 Answer 1

5

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.