Backup/Restore MySQL database via SSH

Backing up Database via command-line SSH: The command for backing up your MySQL database is the following:

mysqldump -u username -p database_name -hServerIP > /path/backup.sql

Type the above command in your command line replacing username, database_name, ServerIP and path with actual values & then press Enter. You will be prompted for your database user's password, thereafter process with complete and place database dump sql file at the path specified.



Restoring your Database via command-line SSH: The command for restoring your MySQL database is the following:

mysql -u username -p'Password' -hServerIP database_name < /path/backup.sql

Note the lack of a space between -p and Password. Also, the password is for the MySQL database user.



Compressed dumps/restores: In order to restore a MySQL database from a compressed sql dump file, the following command combines uncompressing your sql dump archive and transferring it's contents to MySQL database:

gunzip < backup.sql.gz | mysql -u blarg -p'Password' db_name


If you want to directly compress data from a MySQL database without having to gzip separately, enter the following command:

mysqldump -u username -p -hServerIP db_name | gzip > backup.sql.gz

After you type the above and press enter, you should be prompted for your password. Enter your password, and you should be all set.



Compress/Uncompress a sql db file: If you want to compress your existing .sql file, you will need to use the following command:

gzip -X /path/to/backup.sql

In the above command, X is a number between 1 and 9 that specifies the level of compression used. The higher your number, the more compressed (smaller) your file will be.

For example, this is the command I use to compress my data:

gzip -9 forum_backup.sql

Likewise, to uncompress a compressed file, you will use the following command:

gunzip backup.sql.gz



Automated periodic MySQL backups on a Windows PC can be done using a free tool available from http://mysqlbackupftp.com/