469,643 Members | 2,061 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

Consolidating MySQL Servers

My organization currently has 2 different MySQL database servers. We
are in the process of moving the databases from Server A to Server B
and have Server B be our primary database server. I've done some
research on the web and believe the best option is to use the
"mysqldump" command to remotely recreate Server A's databases on to
Server B.

Can anyone please advise whether this is the best option? Also, using
the "mysqldump" command, are there any overwrite issues, such as
existing tables on Server B being overwritten by tables with the same
names from Server A. Would these tables be simply overwritten or
would there be any type of warning prompt? Thank you in advance for
any help.
Jul 20 '05 #1
2 1799
David wrote:
My organization currently has 2 different MySQL database servers. We
are in the process of moving the databases from Server A to Server B
and have Server B be our primary database server. I've done some
research on the web and believe the best option is to use the
"mysqldump" command to remotely recreate Server A's databases on to
Server B.

Can anyone please advise whether this is the best option? Also, using
the "mysqldump" command, are there any overwrite issues, such as
existing tables on Server B being overwritten by tables with the same
names from Server A. Would these tables be simply overwritten or
would there be any type of warning prompt? Thank you in advance for
any help.


Yes, I use this method when I move databases around. I do this
frequently, because I maintain a production and test instance of the
database, and also another instance of the database on a development server.

mysqldump produces as output a portable SQL script that can be used to
restore the data to any database. It's just a textual script containing
a series of CREATE TABLE and INSERT statements.

This is what I do when I move databases from one server to another:

On Server A:
mysqldump --opt mydatabase > mydata.sql
scp mydata.sql serverb:

Then on Server B:
mysqladmin create mynewdatabase
mysql mynewdatabase < mydata.sql

There's no risk of table overwriting, if you create a new database on
server b and use it for the import. But if you do accidentally import
on top of an existing database, you could destroy the existing data. So
do be careful to make sure the database doesn't exist. You can also
double-check that it's empty (i.e. just having been created) before
doing the import by doing:
mysql mynewdatabase -e 'show tables'
which should have empty output if the database is empty.

Regards,
Bill K.
Jul 20 '05 #2
Great, thanks a lot for your help, Bill.

Bill Karwin <bi**@karwin.com> wrote in message news:<cn********@enews4.newsguy.com>...
David wrote:
My organization currently has 2 different MySQL database servers. We
are in the process of moving the databases from Server A to Server B
and have Server B be our primary database server. I've done some
research on the web and believe the best option is to use the
"mysqldump" command to remotely recreate Server A's databases on to
Server B.

Can anyone please advise whether this is the best option? Also, using
the "mysqldump" command, are there any overwrite issues, such as
existing tables on Server B being overwritten by tables with the same
names from Server A. Would these tables be simply overwritten or
would there be any type of warning prompt? Thank you in advance for
any help.


Yes, I use this method when I move databases around. I do this
frequently, because I maintain a production and test instance of the
database, and also another instance of the database on a development server.

mysqldump produces as output a portable SQL script that can be used to
restore the data to any database. It's just a textual script containing
a series of CREATE TABLE and INSERT statements.

This is what I do when I move databases from one server to another:

On Server A:
mysqldump --opt mydatabase > mydata.sql
scp mydata.sql serverb:

Then on Server B:
mysqladmin create mynewdatabase
mysql mynewdatabase < mydata.sql

There's no risk of table overwriting, if you create a new database on
server b and use it for the import. But if you do accidentally import
on top of an existing database, you could destroy the existing data. So
do be careful to make sure the database doesn't exist. You can also
double-check that it's empty (i.e. just having been created) before
doing the import by doing:
mysql mynewdatabase -e 'show tables'
which should have empty output if the database is empty.

Regards,
Bill K.

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

39 posts views Thread by Mairhtin O'Feannag | last post: by
10 posts views Thread by Simon | last post: by
5 posts views Thread by linuxlover992000 | last post: by
8 posts views Thread by deko | last post: by
2 posts views Thread by 2401 members, members can post | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.