How to Backup/restore MySQL Db Remotely

Recipe # | posted in Howto | Comments

1 – Problem Description

We need to backup MySQL database remotely using mysqldump.

2 – Solution

It’s as simple as this:

mysqldump -u [username] -p --host=[hostname] [db_name] | gzip > output.sql.gz

We will be prompted to enter MySQL user’s password and after that we will wait a bit (depending on the size of db) and we will have gzipped backup.

In case we don’t want to gzip backup file or don’t have gzip installed just enter:

mysqldump -u [username] -p --host=[hostname] [db_name] > output.sql

After huge public demand :P we will show you how to restore MySQL database:

mysql -u root -p --host=[hostname]
create database youporn;
mysql -u root -p --host=[hostname] youporn < output.sql

If you have already created database, you just need to:

mysqldump -u [username] -p --host=[hostname] youporn < output.sql

Easy huh ? :)

3 – References

[1] Compressing mysqldump output – mysql, gzip, bzip2 and LZMA (7z)

[2] Backup and Restore MySQL Database Using mysqldump