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.
