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:

  • The CONFIG_mysql_dump_username variable depicts the username of your MySQL user;
  • The CONFIG_mysql_dump_password variable depicts the password of your MySQL user;
  • The CONFIG_mysql_dump_host variable depicts the host of your database (usually localhost);
  • The CONFIG_db_names=() variable depicts the list of databases that need to be backed up;
  • The CONFIG_backup_dir variable depicts the backup directory (non-existing directories will be created automatically);
  • The CONFIG_mail_address variable depicts the email address to send email notifications to;
  • The CONFIG_do_weekly variable depicts which day weekly backups will be done;
  • The CONFIG_encrypt variable depicts whether the backups will be encrypted;
  • The CONFIG_encrypt_password variable depicts the password with which the backups will be encrypted;
  • The CONFIG_prebackup variable depicts the command to run before backups;
  • The CONFIG_postbackup variable 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

Replace the 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 ClusterControlan 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.

Summary

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.

 

Contributor
Comments to: MySQL Backup Retention Planning

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

Attach images - Only PNG, JPG, JPEG and GIF are supported.