Universal practices that are applied to all DBMS, including MySQL
Keep 3 copies of any important file: 1 primary and 2 backups
Keep the files on 2 different media types to protect against different types of hazards
Store 1 copy offsite (e.g., outside your home or business facility)
GFS (Grandfather-Father-Son) is a cycle-based backup and storage strategy where backups are made daily, weekly, and monthly (or on a similar schedule). Such a backup retention policy allows finding a sane balance between the data recoverability and costs spent on the backup infrastructure.
Compression. Backups are well compressed. Consider using 7zip. This utility allows you to fine-tune the consumed resources (number of threads, number of processors or compression algorithm).
Encryption. If your backup is encrypted, then if your storage is compromised, a hacker will not get access to your data.
Automation. The backups should be automated, and the automation scripts should send notifications if the backups are successful or failed.
Testing. It is very important to reproduce emergency cases in a test environment from time to time, and test recovery from backups.
Backup outsourcing. Performing a perfect backup yourself is possible, but it can take many hours both to write scripts and to maintain these scripts. You don't have to do everything yourself. There are many paid services that will take care of backups.
Specific good practices for MySQL
Choose a method how the backups will be created
mysqldump creates a logical backup in
.sql format. This is a standard utility that is installed along with MySQL Server. That tool is good for small databases. It supports Windows and Linux.
mysqlbackup creates a physical database backup. This utility is included in the MySQL Enterprise Edition package. Via the
mysqlbackup tool, the backups perform faster than via
mysqldump. These backups can only be restored to the same version of MySQL Server as the original database. It is well-suited for those who have already purchased the MySQL Enterprise Edition. It supports Windows and Linux.
XtraBackup creates a physical backup. Backups that are created via XtraBackup are much like those that are created via mysqlbackup. Developed by Percona and distributed free of charge. A comparison of utilities can be viewed here. Percona XtraBackup is good for large databases. It supports Linux only, but with some effort it can also be run on Windows via docker (link).
Perform Incremental Backups
Incremental backups allow you to significantly increase the number of restore points.
If you perform backups via
mysqldump, then it is recommended to backup binlogs as well. There are separate instructions for Windows and for Linux.
mysqlbackup supports incremental backups. link
XtraBackup supports incremental backups as well. link
Incremental backups go well with the GFS backup strategy. Full backups could be made weekly and monthly while incremental backups are made daily.
Consider the features of an engine:
InnoDB is the default engine for MySQL. Thanks to row-level locking, row versioning, and transaction support, this is an ideal engine for backups as well.
However, if you use tables with alternative engines, then learn how they work during backups. Perhaps for consistency, the read-only mode should be enabled while a backup is being performed.
A few words about replication
There are two kinds of problems that can be with a database:
- Damage to the physical media such as server failure
- Data corruption such as accidental deletion of data
Replication protects against the first problem only. If important data is deleted, this will immediately spread to the replica.
Backup protects from both failure scenarios.
The best thing is to perform a backup of the replica, this way you will offload the main server.