mysql restore for files on another server

Question

Hi,

I have a test database on a separate remote server than my production DB. Every once in awhile, I want to try and test things by uploading a copy of my production DB to my testing DB. Unfortunately, the backup file is now half a gig and I'm having trouble transferring it via FTP or SSH. Is there an easy way that I can use the mysql restore command between servers? Also, is there another way to move over large files that I'm not considering? Half a gig doesn't seem that big, I would imagine that people run into this issue frequently.

Thanks!

Answer 1

Are the servers accessible to each other?

If so, you can just pipe the data from one db to another without using a file.

ex: mysqldump [options] | mysql -h test -u username -ppasswd

Answer 2

Mysql allows you to connect to a remote database server to run sql commands. Using this feature, we can pipe the output from mysqldump and ask mysql to connect to the remote database server to populate the new database.

mysqldump -u root -p rootpass SalesDb | mysql --host=185.32.31.96 -C SalesDb

Answer 3

0.Please consider whether you really need production data (especially if it contains some sensitive information)

1.The simplest solution is to compress the backup on the source server (usually gzip), transfer it across the wire, then decompress on the target server.

http://www.techiecorner.com/44/how-to-backup-mysql-database-in-command-line-with-compression/

2.If you don't need the exact replica of production data (e.g. you don't need some application logs, errors, some other technical stuff) you can consider creating a backup and restore on a source server to a different DB name, delete all unnecessary data and THEN take a backup that you will use.

3.Restore full backup once on your reference server in your Dev environment and then copy transaction logs only (to replay them on the reference server). Depending on the usage pattern transaction logs may take a lot less space as the whole database.

Answer 4

Use an efficient transfer method, rather than ftp.

If you have a dump file created by mysqldump, on the test db server, and you update it every so often. I think you could save time (if not disk space) by using rsync to transfer it. Rsync will use ssh and compress data for the transfer, but I think both the local and remote files should/could be uncompressed.

Rsync will only transfer the changed portion of a file.

It may take some time to decide what, precisely, has changed in a dump file, but the transfer should be quick.

I must admit though, I've never done it with a half-gigabyte dump file.