Simple and secure MySQL database backup to gzip using mysqldump in Linux

As part of a larger daily backup cron job script I needed to quickly backup my MySQL databases to individual compressed “gzip” .GZ files. The command to do this is very easy, just run the command and pipe it to “gzip”:

mysqldump -u USERNAME -pPASSWORD DATABASENAME | gzip > OUTPUTFILE.gz

This requires you to actually put in the USERNAME and PASSWORD on the command line, which is obviously a bad idea due to logging of commands and other security reasons.

The MySQL recommended way of doing this is to instead use a separate file containing the login details. You use “mysqldump” with the argument “–defaults-extra-file” and specify the location of a configuration file such as “/root/mysqldetails.cnf”. It is a good idea to create this file and “chown” as root and “chmod” it to be “0400″ which will make it read-only by the “root” user.

chown root:root /root/mysqldetails.cnf
chmod 0400 /root/mysqldetails.cnf

The file itself is a very simple text file and just looks something like:

[client]
host = localhost
user = USERNAME
password = PASSWORD

So now this file has been created and the permissions set correctly, the mysqldump command looks like:

mysqldump –defaults-extra-file=/root/mysqldetails.cnf DATABASENAME | gzip > OUTPUTFILE.gz

The result is OUTPUTFILE.gz which is a compressed copy of your DATABASENAME database, without showing anyone the username and password required to access the database. The “mysqldump” command is very useful and more information can be found in the MySQL documentation.

One thought on “Simple and secure MySQL database backup to gzip using mysqldump in Linux

  1. Very useful! Used this to set up a cron job each day to backup my WordPress site DB – was looking at automysqlbackup but couldn’t get it working on my shared host :(

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>