Backup retention planning is something that comes up whenever the need of backing up data arises: in this post, we will try to provide information about how to plan the retention of MySQL backups.
Backup Retention Explained
Backup retention, simply put, is a set of policies that define what data should be backed up, where should it reside, and for how long should it be archived. When you start thinking about backup retention, you might want to ask yourself these questions:
- What type of data is being backed up?
- What backup frequency is needed?
- How much space is needed?
- Where are backups saved (onsite, offsite)?
- How long the backups are kept?
- Are the backups destroyed? If yes, how? If no, why?
After you have answered these questions, you can start looking into available options.
To set up automatic backup retention for MySQL, you can use AutoMySQLBackup – the tool uses mysqldump to create the backup: by default, files are backed up as separate gzipped files. The utility will lock databases while the backup is being created.
The utility can be used by altering the contents of the automysqlbackup.conf file. The contents of the file explain what the variables do pretty well, but in a nutshell:
CONFIG_mysql_dump_usernamevariable depicts the username of your MySQL user;
CONFIG_mysql_dump_passwordvariable depicts the password of your MySQL user;
variabledepicts the host of your database (usually localhost);
CONFIG_db_names=()variable depicts the list of databases that need to be backed up;
CONFIG_backup_dirvariable depicts the backup directory (non-existing directories will be created automatically);
CONFIG_mail_addressvariable depicts the email address to send email notifications to;
CONFIG_do_weeklyvariable depicts which day weekly backups will be done;
CONFIG_encryptvariable depicts whether the backups will be encrypted;
CONFIG_encrypt_passwordvariable depicts the password with which the backups will be encrypted;
CONFIG_prebackupvariable depicts the command to run before backups;
CONFIG_postbackupvariable depicts the command to run after backups.
Run the script by typing
./automysqlbackup.sh. After you’ve ran the script, you can take a look at the backups directory which should contain three subdirectories – daily, weekly, and monthly. These directories will contain subdirectories named after the databases that are being backed up. You can also create a daily cron job for AutoMySQLBackup:
0 0 * * * /home/username/automysqlbackup.sh would run the script every day at midnight.
To uncompress a dump, utilize gunzip:
$ gunzip db_dbname_2020-01-01_12h00m.Monday.sql.gz
db_dbname_2020-01-01_12h00m.Monday.sql.gz with the name of your file, and finally, import the .sql file into your database.
Alternatively, you can look into ClusterControl – an older blog post on Severalnines should provide some insight on how to use ClusterControl to achieve your backup retention goals with MySQL. If you do not want to use either of those, search the web for some Shell scripts that can help you achieve your goals – they can also be useful.
Backup retention should be a part of any data backup plan. Before thinking about backup retention, answer some questions related to your data backup plan and weigh available options, also take space into consideration. Finally, whether you choose to use AutoMySQLBackup, ClusterControl, and/or Shell scripts, know the tools you choose to use well.