Archive for May, 2012

Backup & Compress All MySQL Databases

Posted in BASH, MySQL, Snippets on May 3, 2012 by anzuthechosen

Here’s another useful snippet of BASH script for backing up all your databases into one file.  This one-liner will dump all databases to a .sql file, then compresses that file using LZMA compression.  I chose to use LZMA because I’ve found it to have one of the best compression ratios and it is fast and efficient.

So, without further ado, here’s the snippet:

mysqldump -u root -p –all-databases | lzma -z -k -9 -c > /path/to/backups/db_$(date +%Y-%m-%d_%H%M).sql.lzma

Obviously, this is using the root account to dump the backup, and it’s going to prompt for a password.  While this is quick and easy, it is not suitable for scripting purposes.  I highly recommend creating a separate MySQL user account for making your backups, and creating a credentials file so that you don’t need to type the password in each time.

Here’s how to create a backup user with appropriate permissions for making backups:

grant select, lock tables, file, reload, super on *.* to ‘backupuser‘@’localhost’ identified by ‘passwordgoeshere‘;

This will create a user named backupuser with password passwordgoeshere

To setup that backup user so that they aren’t required to type in a password each time, you would create a file named “.my.cnf” in your OS user’s home directory containing the following:

[client]

user=backupuser

password=passwordgoeshere

Of course, make sure you give this file the appropriate filesystem permissions to prevent others from reading/accessing it:

chmod 600 .my.cnf

Now you’re all setup for creating your automated MySQL backup scripts!

😀