This howto guide will step you through how to backup a MySQL database using the MySQL client command line tools on a Linux machine.
Backing up databases should be done regularly to prevent any data loss due to disk crashes, data corruption, accidental deletion, etc. The more use the database gets, the more regularly the backups should be done.
Backing up a MySQL database:
To backup a MySQL database to a plain text file, run the following command:
$ mysqldump -u username -h localhost -p database_name > database.sql
Change “database_name” to be the name of the database that you want to backup.
Change database.sql to the name of the backup file that will be created (and overwritten if it exists).
Change “username” to be the username that you want to connect to the MySQL database server using.
Using the “-p” parameter means that it will prompt you for the password before it does the backup. You can append a password directly after “-p” if you don’t want it to ask you for the password.
For example, to use a password of “pass123” you would use the -p parameter as “-ppass123”
If you want to connect to a MySQL server on another physical machine, you will need to modify the “-h” parameter. If you want to connect to localhost, this parameter cam be omitted, however I left it in there for the example above.
For example, to connect to a MySQL server called “DBSVR02” you would use the parameter “-h DBSVR02”.
Backup a MySQL database to a compressed file (gzip):
To backup a MySQL database to a compressed (gzip) file, run the following command:
$ mysqldump -u username -h localhost -p database_name | gzip > database.sql.gz
Backup a MySQL database to a compressed file, with timestamp:
If you want to include a timestamp on your backups, so you can keep multiple copies, you could use the following command:
$ mysqldump -u username -h localhost -p database_name | gzip -9 > database_`date +%Y%m%d%H%M%S`.sql.gz
This will create a file named “database_20130124144046.sql.gz” for example.
Change the date output formatting to suit your requirements.
