Unexpected situations such as operating system crashes, power failures, file system crashes, and hardware failures can all cause the database we are using to malfunction, resulting in data inconsistencies. To ensure database security, it is necessary to back up the database regularly. Database backups can be categorized into full backups, log backups, incremental backups, and file backups. For a large database, performing full backups frequently may take too much time, and full backups often back up unchanged data multiple times, leading to resource wastage. The most commonly used database backup strategy today is to perform more frequent incremental backups based on full backups. For example, we can perform a full backup once a week during low-usage periods and then perform an incremental backup daily, capturing the operations that may have modified the database during that period.
Introduction to Incremental Backup
Incremental backup refers to backing up only the files that have been added or modified since the last full backup or the previous incremental backup. This means that the first incremental backup covers the files added or modified after the full backup; the second incremental backup covers the files added or modified after the first incremental backup, and so on. The most significant advantage of this backup method is that there is no duplicate backup data, resulting in a smaller backup size and shorter backup time. However, restoring data from incremental backups is more complicated. You must have the last full backup and all incremental backups (losing or damaging any incremental backup can cause the restore to fail), and they must be restored in chronological order from the full backup to each incremental backup, which significantly extends the recovery time.
For example, if we have a database with 20GB of data and an additional 10MB of data is added daily, performing a full backup every day would put a heavy load on the server. Instead, we can back up only the newly added data to reduce the server's burden.
Introduction to Binlog
MySQL does not provide a direct incremental backup method, but it can achieve incremental backups indirectly through MySQL binary logs.
The binlog is enabled by the log-bin option in the configuration file. The MySQL server will create two new files, “XXX-bin.001” and “xxx-bin.index”, in the data root directory. If no filename is specified in the configuration option, MySQL will use the hostname to name these two files. The “.index” file contains a list of all log files. MySQL records all modifications to database content and structure in “XXX-bin.n” files but does not record “SELECT” and “UPDATE” statements that do not actually update data.
When the MySQL database stops or restarts, the server logs to the next log file. MySQL generates a new binlog file upon restart, with an incrementing file number. Additionally, if a log file exceeds the limit set by the “max_binlog_size” system variable, a new log file is also created.
Incremental backup relies on MySQL's binlog mechanism. Every step of insert, update, delete, and query operations in the database is recorded in the binlog. By first performing a full backup and flushing the binlog at the same time, all subsequent operations will be recorded in new binlog files. For incremental backup, we only need to back up the newly generated binlog files, achieving a perfect backup of the continuously growing database.
The significance of binary logs for backups is as follows:
Binary logs store all operations that update or may update data.
Binary logs start recording after the MySQL server starts and create a new log file when the set file size is reached or when the “flush logs” command is executed.
By periodically executing the “flush logs” command, creating a new binary log file sequence, and storing these files securely, an incremental backup over a period is completed.
How to Perform MySQL Incremental Backup?
MySQL incremental backup can be achieved using MySQL logs. MySQL logs include three types: binary logs, transaction logs, and error logs. Among them, binary logs are MySQL’s primary logs, containing all data modification operations in binary format stored on disk. By backing up binary logs, MySQL incremental backup can be implemented.
Steps for MySQL Incremental Backup:
1. Enable MySQL Binary Log:
Set the following parameters in the MySQL configuration file (“my.cnf”):
[mysql]
log-bin=mysql-bin
server-id=1
The “log-bin” parameter specifies the binary log file prefix, and the “server-id” parameter uniquely identifies the MySQL instance.
2. Create a Backup User:
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your_password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE, PROCESS, SUPER, RELOAD ON *.* TO 'backup'@'localhost';
Here, “backup” is the username. The user must have the following permissions: “REPLICATION CLIENT”, “REPLICATION SLAVE”, “PROCESS”, “SUPER”, and “RELOAD”.
3. Perform a Full Backup:
Use the “mysqldump” command to back up the entire MySQL database to a file:
mysqldump -u root -P 3306 -h 127.0.0.1 -p123456 ks_flask > ks_flask.sql
Here, “localhost” is the MySQL host, “root” is the username, and “ks_flask” is the database name.
4. Record the Current Binary Log Position:
After performing the first full backup, record the current binary log file name and position for future incremental backups.
SHOW MASTER STATUS;
This command returns the current binary log file and position.
5. Perform Incremental Backup:
Use the “mysqlbinlog” command with “--start-datetime”, “--stop-datetime”, and “--start-position” parameters to specify the time range and previous backup position.
mysqlbinlog --start-datetime='2023-10-23 00:00:00'
--stop-datetime='2023-10-23 23:59:59'
--start-position=[previous position]
mysql-bin.000001 > incr_backup_2023-10-23.sql
Here, “[previous position]” is the recorded binary log position from the last backup.
6. Record the New Binary Log Position:
After executing the incremental backup, use “SHOW MASTER STATUS” to record the new binary log file and position for the next incremental backup.
SHOW MASTER STATUS;
7. Perform Incremental Backup Regularly:
Perform incremental backups daily based on requirements, ensuring the previous day’s changes are included. Automation scripts can be used to record and update binary log positions periodically.
8. Restore Backup Data:
Merge and restore full and incremental backups into MySQL:
mysql -hlocalhost -uroot -p your_database < ks_flask.sql
mysql -hlocalhost -uroot -p your_database < incr_backup_2023-10-23.sql
If there are multiple incremental backups, restore them in chronological order.
How to Recover from MySQL Incremental Backup?
There are three common methods for incremental recovery: general recovery, position-based recovery, and point-in-time recovery.
1. General Recovery
Restores all backed-up binary log contents.
mysqlbinlog [--no-defaults] incremental backup file | mysql -u username -p password
2. Position-Based Recovery
When a database administrator performs operations, both erroneous and correct operations may occur at the same time. Position-based recovery allows for more precise recovery.
Recovery to a Specific Position
mysqlbinlog --stop-position='operation ID' binary log | mysql -u username -p password
Recovery Starting from a Specific Position
mysqlbinlog --start-position='operation ID' binary log | mysql -u username -p password
3. Point-in-Time Recovery
Skips a specific erroneous time point to recover data. There are three methods:
1) Recovery from the Beginning of the Log to a Specific Time Point
mysqlbinlog [--no-defaults] --stop-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
2) Recovery from a Specific Time Point to the End of the Log
mysqlbinlog [--no-defaults] --start-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
3) Recovery from One Time Point to Another Time Point
mysqlbinlog [--no-defaults] --start-datetime='YYYY-MM-DD HH:MM:SS' --stop-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
An Easier Way to Create MySQL Incremental Backups
As you can see, the whole process of performing MySQL incremental backup and recovery using binary logs is complex and time-consuming. Since they are command-line operations, you will have no chance to go back if you make a mistake during the backup and restore process.
Luckily, the powerful MySQL backup software Vinchin Backup & Recovery provides a user-friendly interface to make things easier.
Vinchin Backup & Recovery offers a more automated, efficient, and secure solution, streamlining MySQL backup processes with centralized management, fast recovery, incremental backups, and flexible storage strategies to ensure long-term data availability. And it also provides flexible backup strategies for Oracle DB, SQL Server, SQL Server, PostgreSQL, Postgres Pro, and MariaDB, allowing automatic backup, differential backup and transaction log backup.
To create MySQL database incremental backup jobs, please go to Physical Backup > Database Backup > Backup page (Please note that the first backup must be a full backup):
1. Select the databases which need to be backed up.
2. Select a backup node on which you want the backup data to be processed and stored.
3. Configure backup strategies according to your needs.
Here you can choose incremental backup as scheduled.
4. Review and confirm the settings.
Click the button below to try Vinchin's 60-day free trial to experience an efficient and reliable data backup and recovery solution!
MySQL Incremental Backup FAQs
1. What happens if a binary log file is deleted before restoring an incremental backup?
If required binary log files are missing, recovery may be incomplete, leading to data loss. Always ensure binary logs are retained until they are no longer needed.
2. How long should I keep MySQL binary logs for incremental backups?
It depends on your backup policy. Typically, logs should be retained until a new full backup is completed and verified.
Conclusion
It is recommended to refresh incremental backup binary logs daily. This ensures that the database backup frequency is high enough to minimize the risk of data loss. By refreshing incremental backups daily, database changes can be managed more effectively, allowing restoration to the latest state when needed.
