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:
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.