Share this on (or read 50 comments/add one below):

50 comment

  5. I am trying your method to copy database from server A (sqldev100) to server B (sql101). I created a user refresh_oper on B with all privilege on database test. But failed for each of them.
    1) I got following error when running mysqldump -urefresh_oper -p -hlocalhost test | mysql -urefresh_oper -p -hsql101 test: Error 2003 (HY000): cannot connect to MYSQL server on ‘sql101’ (111).
    mysqldump Got error 32 on write.

    I am not sure what is the issue.
    2) I got prompted of password for [email protected] when I run mysqldump -urefresh_oper -p -hlocalhost test |ssh [email protected] mysql test.

    This appears ssh assume refresh_oper is os user?

    Can you help on these?

    Thanks.
    -Jason

    Reply Report comment

  7. Jason, (I know its about a year later, but this is in case someone else has this problem)
    I have had the same problem and its because you have an empty “-p” on both sides of your pipe. You will have to specify the password on at least one them.

    Reply Report comment

  8. Great tip! This morning I was doing this process manually step by step. And then I stumbled upon this page – Im learning more and more every day!

    Only downside is that I dont have any colleagues to impress as Im alone in the IT dept 😉

    Reply Report comment

  10. Hello, i was wondering, if it is possible to do a dump from a base i have on a website to my localhost.

    In other words, does mysqldump has an address and port parameters?

    I looked into mysql manual but didnt find anything, thank you for your help.
    =)

    Reply Report comment

  12. my friend told me that we also can backup mySQL database in .db extension instead of .sql

    as i can read from this page… i can this command in shell

    # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

    could you all help me on using command in shell to backup in .db

    thanks in advanced

    Reply Report comment

  16. I have a problem that is frustrating the crap out of me…. I’m trying to do a mysqldump to another server (obviously don’t want to keep the backup on the same server)…. I’m trying to do this through PHP like the following:

    $remote_backup = “/usr/bin/mysqldump $dbname –opt -h$host -u$user -p$password | gzip -c | ssh [email protected] ‘cat > /home/httpd/vhosts/domain.com/backups/2009-03-07-15-13-56.gz'”;
    system($remote_backup);

    This not working from PHP, however when I run it direct from SSH shell below it works perfectly.

    /usr/bin/mysqldump $dbname –opt -h$host -u$user -p$password | gzip -c | ssh [email protected] ‘cat > /home/httpd/vhosts/domain.com/backups/2009-03-07-15-13-56.gz’

    I setup key authentication so that SSH does not prompt for a password, but it is still not working from PHP.

    What am I doing wrong? I’ve spend tons of time searching for this solution…. any help would be greatly appreciated.

    Thanks.

    /usr/bin/mysqldump $dbname –opt -h$host -u$user -p$password | gzip -c | ssh [email protected] ‘cat > /home/httpd/vhosts/domain.com/backups/2009-03-07-15-13-56.gz’

    Reply Report comment

  20. We are using jasper reports and moved everything on Server A to Server B and sysadmin wiped out Server A. He did backup all files on Server A first. Now the developer wants the db from Server A restored (the jasper tables). Could we take the .frm files from the database directory and replace the ones on Server B with the ones from Server A? This was an application we didn’t know used mysql so we didn’t back anything up using mysqldump. Thanks.

    Reply Report comment

  22. This needs to be:

    mysqldump -u root –password=pwordhere puaSite | ssh [email protected] mysql -u root –password=pwordhere puaSite

    If you are prompted for passwords

    Reply Report comment

  26. I’m using exactly this to export live database to development server. Recently it has started to fail:

    [email protected]:~# mysqldump -q -u root -psecret1 –add-drop-database mydb | gzip -c | ssh [email protected] “gunzip | mysql -u root -psecret2 mydb”
    [email protected]‘s password:
    {{{ a few minutes of silence, then: }}}
    mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `cache` at row: 4004

    Reply Report comment

  30. Fix to Error 2013: Lost connection to MySQL server during query when dumping table:

    mysqldump -q -u root -pMYSQLROOTPASSWORD –max-allowed-packet=256M –add-drop-database MYDB | ssh [email protected] “cat – > /tmp/tmp.sql;mysql -u root -pDEVMYSQLROOTPASSWRD MYDB < /tmp/tmp.sql"

    Not as fast but very reliable.

    Reply Report comment

  37. what the hell does [email protected] mean? you’re sending it to his server’s email??? why can’t u linux pèople speak in simpler terms?

    Reply Report comment

  42. i have one Linux VPS and the hard drive on this VPS has been full because of IBdata1 file. now i want to take backup of Mysql database.

    is it possible that i can take backup from linux VPS to my windows system directly.

    Please Help on this.

    Reply Report comment

  43. Hi, I tried this and find that the tables of the database have their links removed and moreover all relationships are removed. I wonder what I could be doing wrong?
    Thanks

    Reply Report comment

  46. i tried the syntax $ mysqldump db-name | mysql -h remote.box.com db-name and it gave me an error ERROR 1045 (28000): Access denied for user what can be the exact syntax to copy a database from one server to another using command line thank you

    Reply Report comment

  48. ssh [email protected] mysql -u username -p'1234' db-name > /path/file.sql

    Reply Report comment

Leave a Comment

Tagged as: Tags , , , , , , , , , , , , , , , , ,