patternMinor
Merge difference MySQL DBs into a single new server
Viewed 0 times
dbsnewmergeintodifferencemysqlsingleserver
Problem
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
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
Solution
This consider all database names are different on all server.
As you said all servers are
Step 1 : Take dump from all old server like below
Everything written in one file: table structures, indexes, triggers, stored procedures, users, encrypted passwords.
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
As you said all servers are
mysql 5.1, You can take mysqldump on each server and restore it on new serverStep 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.gzStep 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.sqlCode Snippets
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.gzmysql -u... -p... < MySQLDataServer1.sql
mysql -u... -p... < MySQLDataServer2.sql
mysql -u... -p... < MySQLDataServer3.sql
mysql -u... -p... < MySQLDataServer4.sqlContext
StackExchange Database Administrators Q#59671, answer score: 5
Revisions (0)
No revisions yet.