Backup & Compress All MySQL Databases

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!

😀

Advertisements

One Response to “Backup & Compress All MySQL Databases”

  1. Hi every one, here every one is sharing such familiarity, therefore it’s fastidious to read this blog, and I used to visit this website every day.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: