1. Overview
In Linux environments, incremental backup of MySQL databases is a key strategy for ensuring data security and recovery. Due to the stability and openness of the Linux system, it provides us with many conveniences for efficient database backup.
2. Understand the importance of incremental backup
- Data change trackingIn practical application scenarios, the data of the database is constantly changing. For example, the database of an e-commerce website has operations such as new orders and user information updates every day. Incremental backups can accurately capture these changes and only backup newly inserted, modified or deleted data, which greatly reduces the amount of backup data compared to full backups.
- Resource optimizationIn the case of limited Linux server resources, incremental backups can effectively save disk space and backup time. Taking an Enterprise Resource Planning (ERP) system database with a large amount of data as an example, if a full backup is performed every time, it consumes a lot of I/O resources and disk space, and incremental backups can significantly reduce this burden.
3. Preparation
-
System permissions and environment settings
- Ensure that the user used for the backup operation has sufficient permissions. Usually, you need to have read permissions to the MySQL database and write permissions to the backup directory. In Linux systems, you can use
chownandchmodCommand to set users and permissions. For example, back up the directory/backupThe owner is set tobackupuser, and give read and write permissions:
- Ensure that the user used for the backup operation has sufficient permissions. Usually, you need to have read permissions to the MySQL database and write permissions to the backup directory. In Linux systems, you can use
chown backupuser:backupuser /backup chmod 700 /backup
- Install the backup tool
mysqldump: This is the backup tool that comes with MySQL. Although it is mainly used for logical backups (backup data in SQL statements), it can also be achieved with some tips to implement simple incremental backups. For example, by querying information such as the update timestamp of the database, it is determined to determine the data that needs to be backed up.
- Percona XtraBackup: This is a powerful open source backup tool, especially suitable for InnoDB and XtraDB storage engines. It can perform hot backups without blocking the normal operation of the database and has good support for incremental backups. Percona XtraBackup can be installed in CentOS/RHEL system through the following command:
yum install /yum/ yum install percona - xtrabackup - 34
-
Install other dependency packages: According to the requirements of the backup tool, some other dependency packages may also be required, such as
libaiowait. In CentOS/RHEL systems, you can useyum install libaioCome install.
4. Use mysqldump for incremental backup
-
Determine incremental data
- You can create a table in the database to record the time of data modification. For example, create a name called
data_change_logtable, includingtable_name(Table name),last_modified(Last modified time) and other fields. Each time the data is operated (insert, update, delete), update this table through triggers or application code.
- You can create a table in the database to record the time of data modification. For example, create a name called
-
Perform incremental backups
- according to
data_change_logRecords in tables, scripts to use mysqldump to back up tables modified since the last backup. For example, the following script can back up modified tables:
- according to
#!/bin/bash
LOG_TABLE="data_change_log"
BACKUP_DIR="/backup/mysqldump_incremental"
# Get the modified table name listTABLES=$(mysql -u username -p password -e "SELECT table_name FROM $LOG_TABLE WHERE last_modified > '$(cat last_backup_time.txt)'")
for TABLE in $TABLES
do
mysqldump -u username -p password $DATABASE_NAME $TABLE >> $BACKUP_DIR/$
done
# Update backup timestampdate +%F-%T > last_backup_time.txt
- This script first
data_change_logThe time of the last backup was obtained in the table (stored inlast_backup_time.txtThe table names modified after the file is in the file, and then use mysqldump to back up these tables and store the backup files in/backup/mysqldump_incrementalIn the directory, the backup timestamp is finally updated.
5. Use Percona XtraBackup for incremental backups
-
First full backup
- The command to use XtraBackup for full backup is as follows:
xtrabackup --backup --target - dir=/backup/xtrabackup_full
- After the backup is completed, the backup data needs to be "prepare" operation. This step is to ensure the consistency of the backup data so that the backup can be used for recovery. The command is as follows:
xtrabackup --prepare --target - dir=/backup/xtrabackup_full
-
Incremental backup operation
- Assuming that full backup has been completed, the next time you perform incremental backup, you need to specify a directory based on full backup. The command is as follows:
xtrabackup --backup --target - dir=/backup/xtrabackup_incremental --incremental - basedir=/backup/xtrabackup_full
- Similarly, after the incremental backup is completed, preparation operations are required:
xtrabackup --prepare --target - dir=/backup/xtrabackup_incremental
- When preparing incremental backup data, XtraBackup will automatically integrate the incremental backup data with the previous full backup (or previous incremental backup) to ensure the integrity of the data.
6. Storage and management of backup data
-
Storage media selection
- In Linux systems, backup data can be stored on local disks, network storage (such as NFS-mounted shared storage), or external storage devices (such as tape libraries). If the backup data is small and the recovery speed is high, you can choose local disk storage. If you need to share backup data between multiple servers or if there is a large amount of backup data that needs to be managed centrally, network storage is a good choice.
-
Backup directory structure and naming specifications
- Establishing a clear and reasonable backup directory structure helps manage backup data. For example, the directory can be divided according to factors such as backup type (full backup, incremental backup), backup date, etc. The name of the backup file should also contain relevant information, such as
full_backup_20241102.sqlorincremental_backup_20241102_1.sql(Indicates the first incremental backup on November 2, 2024).
- Establishing a clear and reasonable backup directory structure helps manage backup data. For example, the directory can be divided according to factors such as backup type (full backup, incremental backup), backup date, etc. The name of the backup file should also contain relevant information, such as
-
Lifecycle management of backup data
- In order to avoid the backup data taking up too much disk space, the life cycle of the backup data needs to be managed. You can formulate a retention strategy for backup data based on business needs, for example, only the incremental backups of the last week and the full backups of the last month are retained. Expired backup data can be cleaned regularly by writing scripts.
7. Data recovery
-
Backup data recovery using mysqldump
- If it is an incremental backup using mysqldump, it needs to be done in the order of backups when restoring. First restore the full backup (if any), and then restore the incremental backup one by one according to the backup time sequence. To restore full backup, you can use the following command:
mysql -u username -p password $DATABASE_NAME < full_backup.sql
- When restoring incremental backups, for each incremental backup file (
.sqlfile), use the samemysqlCommand to restore. -
Backup data recovery using Percona XtraBackup
- For backups using XtraBackup, when restoring full backups, copy the backup data to the MySQL data directory. Assume that the MySQL data directory is
/var/lib/mysql, the full backup directory is/backup/xtrabackup_full, you can use the following command:
- For backups using XtraBackup, when restoring full backups, copy the backup data to the MySQL data directory. Assume that the MySQL data directory is
rsync -avr /backup/xtrabackup_full/* /var/lib/mysql
- If there is an incremental backup, after restoring the full backup, copy the incremental backup data to the data directory in the backup order. For example, for incremental backup directory
/backup/xtrabackup_incremental：
rsync -avr /backup/xtrabackup_incremental/* /var/lib/mysql
- After the recovery is complete, you need to ensure that the MySQL service can start normally and that the data integrity is verified.
8. Monitoring and Maintenance
-
Backup task monitoring
- Can use Linux system
cronThe service performs backup tasks regularly and passesmailor other monitoring tools to receive reports on the execution of backup tasks. For example,cronAfter adding a backup script to the task, if an error occurs in the backup task,cronThe error message will be sent to the specified mailbox.
- Can use Linux system
-
Backup data verification
- Regular verification of backup data is key to ensuring data recovery. The quality of the backup data can be verified by restoring the backup data to a database in the test environment and running some data integrity check scripts or simple queries.
9. Summary
Incremental MySQL backup in Linux systems, you need to comprehensively consider the selection of backup tools, the formulation of backup policies, the storage and management of backup data, and data recovery. Through reasonable planning and operation, data in MySQL database can be efficiently protected, ensuring rapid recovery in the event of data loss or damage, and ensuring business continuity.
This is the end of this article about the implementation of incremental backup in MySQL. For more related MySQL incremental backup content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!