How do i copy the content of mysql DB from one server into another?

[WorldNews]

Hi,

We have 2 servers:

s1.com
s2.com

with s1 having a db that lets say is called doxz
and s2 having the same db.

How do I then copy the data from doxz on s1 server to the s2 server?

I have issued this command but it does not work:

mysqldump -u root -p doxz | mysql -h s2.com -u root -p doxz

FYI: after I enter the above command it asks me for the password back to back on the same line which of course is impossible since I can enter the password for 1 mysql server at a time.

Giving me this error message:

ERROR 1045 (28000): Access denied for user 'root'@'64.34.11.161' (using password: YES)


mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
[root@server2 root]#



Regards,

[jonbey]

You could use phpmyadmin with export/import. Not the best way, but it will work

[WorldNews]

Hi,

For various reasons I much rather and I must use MySql directly to get this done.

Regards,

You could use phpmyadmin with export/import. Not the best way, but it will work

[hztonyt]

Hello,

mysqldump -u root -p doxz | mysql -h s2.com -u root -p doxz

You could use the following commands to take a dump and then restore the db in a single line.

mysqldump -h hostname -u username --password=passphrase dbname > dumpfile.sql ; mysql -h hostname -u username --password=passphrase dbname < dumpfile.sql
OR
mysqldump -h hostname -u username --password=passphrase dbname | mysql -h hostname -u username --password=passphrase

However there might be other issues to be taken care of in this procedure. IMO you better transfer the dumpfile to other server and restore it there. Can give a try though

Regards

[WorldNews]

Hi,

ThanX. The 2nd command does the job fine.

But I have a question, which I hope you can answer.
I am doing this copying of 1 DB from one server to another because
Replication between these 2 servers has stopped working and I need to
quickly bring the DB on the Slave server in synch with the DB on Master
sever. So will this copy command get this job done without any problems?

Regards,

Hello,



You could use the following commands to take a dump and then restore the db in a single line.

mysqldump -h hostname -u username --password=passphrase dbname > dumpfile.sql ; mysql -h hostname -u username --password=passphrase dbname < dumpfile.sql
OR
mysqldump -h hostname -u username --password=passphrase dbname | mysql -h hostname -u username --password=passphrase

However there might be other issues to be taken care of in this procedure. IMO you better transfer the dumpfile to other server and restore it there. Can give a try though

Regards

[WorldNews]

Hi,

And I have a follow up question

Once having issued the command to backup MySQL, that is having issued the mysqldump command, how does one stop this if one sees that the resulting backup file is becoming too big?

Regards,

Hello,


You could use the following commands to take a dump and then restore the db in a single line.

mysqldump -h hostname -u username --password=passphrase dbname > dumpfile.sql ; mysql -h hostname -u username --password=passphrase dbname < dumpfile.sql
OR
mysqldump -h hostname -u username --password=passphrase dbname | mysql -h hostname -u username --password=passphrase

However there might be other issues to be taken care of in this procedure. IMO you better transfer the dumpfile to other server and restore it there. Can give a try though

Regards

[hztonyt]

Replication between these 2 servers has stopped working

What is the cause of this? If it is some network issue then the other methods cannot help you even.

So will this copy command get this job done without any problems?

As this a master and slave configuration, I assume there are no incompatibilities between the packages being used on these servers. So this may not cause any probs as long as connection is not interrupted.

And I have a follow up question

Ctrl +c is what which can stop it. You can stop the backup as it doesn't make any changes to the existing db. However you need to restart the whole process again.
Interrupting restoration may cause issues though.

[WorldNews]

[QUOTE=hztonyt;4095702]What is the cause of this? If it is some network issue then the other methods cannot help you even.

No, the cause is no network issued between the Master and Slave servers.
It is something with the Slave server Table or Config files that has caused the replication to stop all of a sudden. FYI. this is the output of mytop on MySQL on this Slave server:


Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
12496 root localhost test 0 Query show full processlist
8671 system us 447393 Connec Has read all relay log; waiting for the slave I/O thread to update it


As you can see the Slave server is saying that it is "waiting for the slave I/O thread to update it" and it is just hanging at that point.

Regards,