Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer
    
Perfect, thanks very much sir!!! –  Bonn Feb 26 at 10:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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