What are the best practices for backing up and recovering your MySQL databases?
- Determine your backup needs. As with anything, you would need to plan for your backups. Start with knowing when you need to backup your database, what are the things that need to be replicated (database content, log files and configuration data), and how are these backups done. Then you would also need to figure out where you should store the backups and other details.
- Know what your options are. The beauty with working with MySQL is that there are a lot of methods for backing up and restoring your MySQL databases. You should know what types of backups are available, such as logical or physical, incremental or full, and other parameters. You should be aware of the different methods for creating backups in order to know which one to use for different situations and setups. You should also know the different recovery methods, including point in time recovery. You should also know backup compression, encryption and scheduling. Furthermore, you should know how to maintain your tables so that you could easily recover data in case it gets corrupted.
- But master the most widely used techniques. If you are going to work largely with MySQL database, there are three techniques that you should be an expert in. What are these?
- MySQL Async Replication: To be used if you want to replicate your database using MySQL replication.
- MySQL database dump: Use mysqldump for dumping the databases for transfer or backup to another SQL server.
- Point-in-time binlog backup and recovery: This will give you the binary logs that would be used to replicate changes to the databases.
Typical Scenario: You can use mysqldump to create snapshots of your database, while you can create incremental backups by having the binary log on the slave. You combine the snapshots and the binary log to make sure that your backups contain the same data as the master.
- Consider using a dedicated slave. If you have a production database that you need to backup, then consider setting up a dedicated slave for your backup needs. This will allow you to do your backup tasks without affecting your master. The downside is that it will make your architecture a little bit more complex, so you will need to monitor the lag that your backups and replications are having to make sure that everything works the way it should.
- Consider using the tools available for you. The good thing with MySQL is that you have a choice to use tools in order to make your life easier. There are several tools available out there that you could use, such as Percona XtraBackup, AutoMySQLBackup Tool, and Zmanda Recovery Manager. Starting with MySQL version 6, you also have the option to use the MySQL Online Backup API.
- Always be aware of the trade offs. There is no backup solution that you could use for every database and for every scenario. You would need to know what tools are available for you and what you prefer: do you want a fast backup or do you value portability. For instance, mysqldump is portable to just about any DBMS, but it is much slower than other file copying methods. Also, restoring from logs may prove to be very tricky, but full backups are expensive.