Best practices for backing up a MySQL DB

Question

I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience with MySQL DB's. Any best practices for using 'mysqldump' or any other DB backup tools?

I'll probably cron job the schedule so that it's done nightly and then backup the files with my backup system.

Thanks.

Answer 1

Best Practices to take MySQL server backup:

MySQL Replication

Setup Replication in MySQL. You will have to setup Master and Slave server. All read-writes to the DB could go to your Slave Server. Advantage of having Replication is you can take a backup from your slave server without interrupting Master server, Your application will continue to work on Master without any downtime.

Using MySQL Dump

If your data set is small (I realize "small" is a relative term.. to qualify it, let's say <10GB), then mysqldump will probably work great. It's easy, it's online and it's very flexible. Just a few things mysqldump can do: backup everything or just certain databases or tables backup only the DDL optimize the dump for a faster restore make the resultant sql file more compatible with other RDBMSes and many more things.

However, the most important options are related to the consistency of your backup. My favorite options are: --single-transaction : this option gives a consistent backup, if (and only if) the tables are using InnoDB storage engine. If you have any non-read-only MyISAM tables, then don't use this option when backing them up. --master-data=2 : this option will make sure your dump is consistent (by doing a lock-all-tables unless you've added the option --single-transaction). The --master-data option also records the binary log position in the resulting dump file (=2 causes this line to be a comment in the dump file)

Final note about mysqldump: keep in mind that the restore time may be significantly longer than the backup time. It will depend on several factors, for example, how many indexes you have.

LVM snapshot

For those that have larger datasets, a physical backup is the way to go. While you could take a cold backup (i.e., shutdown the MySQL service, copy the data directory, restart the service), many people do not want downtime. My favorite solution is snapshots. This can be hot (for InnoDB) or require a brief lock (for MyISAM). Don't forget to include all your data (include the ib_logfiles). Lenz provides a nice utility to help with this: http://www.lenzg.net/mylvmbackup/

Using MySQL Enterprise Backup

Advantages of using MySQL Enterprise Backup:

• "Hot" Backups of InnoDB tables takes place entirely online, without blocking Backup only particular tables or tablespaces
• Only backup the data that changed since a prior backup
• Compressed Backup - Saves storage upto 90% and many more..

Reference: http://www.mysql.com/products/enterprise/backup/features.html http://www.mysql.com/products/enterprise/backup.html

Answer 2

I would recommend setting up a dedicated replica to use for backup. This will let you perform any backup tasks without impacting the primary. As this does add complexity to your architecture, you'll want to monitor the replication lag to ensure everything is working.

As for the actual process, you have a couple options without any third-party tools. Snapshots can be taken using the mysqldump command (assuming you're using InnoDB): mysqldump --all-databases --single-transaction > all_databases.sql. Depending on the data size, it may be preferable to shut down MySQL and backup the data files directly. When the replica is restarted it will replay all the events the primary received in the duration it was down. If you're using MySQL Enterprise the mysqlbackup utility does this.

Incremental backups can be taken by enabling the binary log on the replica. Obviously, this only records events that mutate data so you'll need to combine this with the snapshots above.

Answer 3

Universal practices that are applied to all DBMS, including MySQL

• 3-2-1 rule. The Cybersecurity and Infrastructure Security Agency recommend that you:

   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.