SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SitePoint Evangelist jonbey's Avatar
    Join Date
    May 2007
    Posts
    508
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You could use phpmyadmin with export/import. Not the best way, but it will work
    My site: My Extension

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

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

    Regards,

    Quote Originally Posted by jonbey View Post
    You could use phpmyadmin with export/import. Not the best way, but it will work

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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
    Tony Mathew Thomas
    modVPS
    HostingZOOM Inc.
    POWER. SPEED. RELIABILITY.

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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,


    Quote Originally Posted by hztonyt View Post
    Hello,



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

    Code:
    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

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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,

    Quote Originally Posted by hztonyt View Post
    Hello,


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

    Code:
    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

    Anoox search engine volunteer

    www.anoox.com

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tony Mathew Thomas
    modVPS
    HostingZOOM Inc.
    POWER. SPEED. RELIABILITY.

  8. #8
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    [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,

    Anoox search engine volunteer

    www.anoox.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •