Copy MySQL database from remote server to local

Question

Level 1

76 points

Copy MySQL database from remote server to local

Hi All,

I am trying to copy a MySQL database from a remote server to a local server. So far I find that this works in getting the database:

mysqldump -h Host -u dbUsername -pDBPassword | > /path/to/dir/testBackup.sql

It connects to the remote host and dumps a copy of the database locally.

Is there a way to add to that line to take that dumped database and import it to the local machine? I'm trying to backup databases from our hosting partner and import them locally in case our hosting partner goes down.

TIA - Vijay

Posted on Nov 4, 2011 1:00 PM

Reply

Answer 1

MrHoffman

Level 10

146,701 points

Nov 4, 2011 4:57 PM in response to Vijay-Kumar

For restoration, create a short bash script. Something like this:

#!/bin/bash

mysqladmin -u username -ppassword create databasename

mysql -u username -ppassword databasename < sqldatabasedump.sql

Here is an example of a nightly (periodic) archive of MySQL databases.

Reply

Answer 2

Vijay-Kumar Author

Level 1

76 points

Nov 7, 2011 8:14 AM in response to MrHoffman

Thanks... So if I were to create a bash script that looked like this:

#!/bin/bash

mysqldump -h Host -u dbUsername -pDBPassword | > /path/to/dir/testBackup.sql

mysqladmin -u dbUsername -pDBPassword create databasename

mysql -u dbUsername -pDBPassword databasename < testBackup.sql

I could do all three steps in the same script? And possibly repeat those steps in the same script for additional backups?

Reply

Answer 3

MrHoffman

Level 10

146,701 points

Nov 7, 2011 5:51 PM in response to Vijay-Kumar

Sure. But test it. You'll need to test it regardless, just to make sure it all works.

In general, I usually prefer to have the backup tool create the initial SQL database archive on the primary server, zip it, and to then have the backup server in a trusted network location pull the database archive over to the backup server. (It can then unzip it or process it as needed.) With this, the primary server doesn't have access to the backups, nor to the backup server. This intended to isolate the access that a breach of the primary server might permit.

Reply

Answer 4

Vijay-Kumar Author

Level 1

76 points

Nov 25, 2011 6:03 AM in response to MrHoffman

Thanks... This seems like it iwll work; however, some passwords throw this off course. Is there a way to use weird passwords with this way of doing things?

Thanks again - Vijay

Reply

Answer 5

MrHoffman

Level 10

146,701 points

Nov 25, 2011 7:31 AM in response to Vijay-Kumar

If the password happens to contain one or more characters that have specific meanings within bash, then you'll have to \-escape them, or equivalent bash-level processing. If that's not what is happening here, then you'll want to elaborate on what you mean by "weird" here.

Reply