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 refresh_oper@sql101 when I run mysqldump -urefresh_oper -p -hlocalhost test |ssh refresh_oper@sql101 mysql test.

    This appears ssh assume refresh_oper is os user?

    Can you help on these?

    Thanks.
    -Jason

  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.

  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 😉

  9. it is very nice site & very much help ful to me I learned a lot from this website
    please send me updated query & resoluation for the same
    Regards,
    Rahul Bodhe

  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.
    =)

  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

  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 user@111.222.333.444 ‘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 user@111.222.333.444 ‘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 user@111.222.333.444 ‘cat > /home/httpd/vhosts/domain.com/backups/2009-03-07-15-13-56.gz’

    1. PHP probably doesn’t have the environment variables setup for your ssh-agent process (assuming you’re using one).

  18. I just want to copy with a where condition for a table ,but the same remote machines databases …….. What to do

  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.

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

    root@mybox:~# mysqldump -q -u root -psecret1 –add-drop-database mydb | gzip -c | ssh root@dev.local “gunzip | mysql -u root -psecret2 mydb”
    root@dev.local‘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

  27. mysqldump -u user -p db-name > db-name.out
    After this command, I can’t find the db-name.out in any directory,
    where is it?

  28. mysqldump -u user -p db-name > db-name.out
    After this command, I can’t find the db-name.out in any directory,
    where is it?

  29. hi i m omkar yadav your website is very nice and very powerfull
    this website is my lucky website

    thank you.
    omkar yadav

  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 root@mydevmachine.local “cat – > /tmp/tmp.sql;mysql -u root -pDEVMYSQLROOTPASSWRD MYDB < /tmp/tmp.sql"

    Not as fast but very reliable.

  33. This finally helped me figure out how to dump the stuff I needed into the remote database. There was stuff I looked at before, but you finally explained it correctly.
    Thanks!!

  38. what i mean is i wanted to copy a mysql database to another domain, and now yet again i will fail because you linux ppl don’t speak in moron friendly terms.

  41. Remember the –protocol=tcp parameter for mysqldump if you want to dump from locally forwarded remote port!

  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.

  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

    1. oh! I’ve understood:

      mysqldump dbname | gzip -c | ssh vs3front.pre "gzip -d | mysql dbname"

      Maybe will be useful to someone

  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

    Have a question? Post it on our forum!

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