Share this on:

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

  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 [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’

  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.

  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

  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

  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?

  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.

  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!!

  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?

  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

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

Comments are closed.

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