MySQL introduces incremental backup and recovery

Free learning recommendation: mysql video tutorial

Article Directory

• 1. MySQL incremental backup
• • 1. The concept of incremental backup
  • • 1.1 Why use Incremental backup
    • 1.2 Characteristics of incremental backup
  • 2. Incremental backup example
• 2. MySQL incremental Recovery
• • 1. Incremental recovery scenario
  • 2. Recovery steps for data changed after losing a full backup
  • 3.Lost after a full backup Recovery steps for all data
  • 4. Time point and location based recovery
  • • 4.1 Time point based recovery
    • 4.1 Location based operation
  • 5. Ideas for specifying enterprise backup strategies

1. MySQL incremental backup

Incremental backup can reduce the size of backup files on the basis of full backup, thereby speeding up backup and recovery.

1. Incremental backup Concept

1.1 Why use incremental backup

• In the previous chapter, we mentioned that there are two methods for full backup. One is to use tar to package the data file. , the other is
  Use mysqldump for full backup
• The problem with full backup is easy to see. All data contents are backed up every time, and there is a large amount of duplicate data in the backup data. And the time for full backup and recovery is very long
• To solve the problem of full backup is to use incremental backup. Incremental backup is to back up files or content that have been added or changed since the last backup

1.2 Characteristics of incremental backup

• The advantages of incremental backup are that there is no duplicate data, the backup volume is small, and the time is short
• The disadvantage is also obvious. It requires the last full backup and all incremental backups after the full backup to restore, and all incremental backups are restored one by one, which is a complicated operation.
• MySQL does not provide direct Incremental backup method, but incremental backup can be achieved indirectly through MySQL’s binary logs (binary
  logs)

The meaning of binary logs for backup is as follows:

1. The binary log saves all operations that update or may update the database
2. The binary log starts recording after starting the MySQL server, and is re-created after the file reaches the size set by max_binlog_size or receives the flush logs command. Log files
3. You only need to execute the flush logs method regularly to re-create new logs, generate binary file sequences, and save these logs to a safe place in time to complete the incremental backup of a period of time

2. Incremental backup example

1. Enable binary log function

vim /etc/my.cnf...[mysqld]log-bin=mysql-binbinlog_format = MIXED#指定二进制日志(binlog)的记录格式为 MIXEDsystemctl restart mysqld.service#重启服务cd /usr/local/mysql/datals -l /usr/local/mysql/data/mysql-bin.*#查看二进制文件#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)#默认格式是 STATEMENT

2. Choose a time period every week when the server load is lighter, or when there are fewer user visits for backup

mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql#对表进行完全备份mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql#对库进行完全备份crontab -e#也可以使用计划性任务来执行30 3 * * 3 mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql30 3 * * 3 mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql每周三的凌晨 3:00 对数据库和表进行完全备份

3. Incremental backup operations can be performed every day to generate new binary log files, so that after inserting new data, the new binary files correspond to the changes in the database

ls /usr/local/mysql/datamysqladmin -uroot -p123123 flush-logs

4. Insert new data to simulate the addition or change of data

use SCHOOL;insert into CLASS01 values(3,'wangsan','woman','games');insert into CLASS01 values(4,'wangsi','man','runing');select * from CLASS01;

5. Generate a new binary file and view its contents

cd /usr/local/mysql/data/lsmysqladmin -uroot -p123123 flush-logs

cp mysql-bin.000002 /opt/#将记录变更的二进制文件02复制至/opt目录下cd /opt/lsmysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002#使用64位编码机制去解码，按行读取详细内容

2. MySQL incremental recovery

• Incremental recovery is more cumbersome than full recovery operation
• Each incremental backup is a separate individual, and the data is not repeated , need to be controlled more accurately

1. Incremental recovery scenario

• 当数据发送错误时，应根据实际情况选择使用完全备份恢复，还是增量备份
• 增量备份的场景是：
  • 人为的 SQL 语句破坏了数据库
  • 在进行下一次全备之前发送系统故障导致数据库数据丢失
  • 在主从架构中，主库数据发送了故障
• 根据数据丢失的情况可以分为两类：
  • 只丢失了完全备份之后更改的数据
  • 完全备份之后丢失所有的数据

2.丢失完全备份之后更改的数据的恢复步骤

• 当完全备份之后更改的数据丢失，需要把完全备份之后的所有增量备份文件逐个恢复
• 步骤如下：

mysql -uroot -p123123use SCHOOL;delete from CLASS1 where id=3;delete from CLASS1 where id=4;#删除插入的两条数据，模拟完全备份后数据丢失的故障select * from CLASS01;#检查quitmysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123#使用二进制文件进行恢复操作mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"#检查表内容是否恢复

3.完全备份之后丢失所有数据的恢复步骤

• 当完全备份和增量备份之后，所有的数据丢失，需要把完全备份和所有增量备份文件逐个恢复
• 步骤如下：

mysql -uroot -p123123use SCHOOL;drop table CLASS01;#直接删除整个表，假设完全备份后所有数据都丢失了quitmysql -uroot -p123123 SCHOOL <p><img src="https://img.php.cn/upload/article/000/000/052/c368a93d80781ae407afafe02a2ac327-11.png" alt="MySQL introduces incremental backup and recovery"><br><img src="https://img.php.cn/upload/article/000/000/052/1481568b3321e7dca79c49d509572b7b-12.png" alt="MySQL introduces incremental backup and recovery"></p><p><strong>4. 基于时间点与位置的恢复</strong></p>

• 利用二进制日志可实现基于时间点与位置的恢复，例如由于误操作删除了一张表，这时完全恢复是没有用的
• 因为日志里还有误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作的语句，再恢复后面操作的语句

4.1 基于时间点的恢复

• 基于时间点的恢复，就是将某个起始时间的二进制文件导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复
• 使用 mysqlbinlog 加上 --stop-datetime 选项，表示在哪个时间点结束，后面误操作的语句不执行
• –start-datetime 选项表示执行后面的语句
• 结合使用它们就可以跳过误操作的语句，完成恢复工作
• 需要注意的是，二进制文件中保存的日期格式需要调整为用“-”分割

#恢复用户“wangsan”的数据，而不恢复“wangsi”mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS01;"mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --stop-datetime='2021-02-06 15:58:39' /opt/mysql-bin.000002 |mysql -uroot -p123123mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

#恢复“wangsi”的数据mysqlbinlog --no-defaults --start-datetime='2021-02-06 15:58:39' /opt/mysql-bin.000002 |mysql -uroot -p

4.1 基于位置的操作

• 基于位置的恢复，就是使用基于时间点的恢复
• 可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，基于位置是一种更为精确的恢复方式

mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p#使用64位编码机制去解码并按行读取二进制文件02（增量备份）的详细内容......略

#仅恢复“1810”之前的数据，即不恢复“wangsi”的数据mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS01;"mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --stop-position='1810' /opt/mysql-bin.000002 | mysql -uroot -pmysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

#仅恢复“wangsi”的数据，跳过“wangsan”的数据恢复，即仅有第四条记录mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --start-position='1810' /opt/mysql-bin.000002 | mysql -uroot -p123123mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

5. 指定企业备份策略的思路

• 指定企业备份策略要根据企业数据库的实际读写的频繁性与数据的重要性进行
• 数据更新频繁，则应该进行较为频繁的备份
• 数据较为重要，则在有适当更新时进行备份
• 在数据库压力小的时段进行全备，如一周一次，然后每天增备
• 根据公司的规模，中小公司可一天一次全备，大公司可每周一次全备，每天进行一次增备，并且尽量为企业实现主从复制架构

相关免费学习推荐：mysql数据库(视频)

The above is the detailed content of MySQL introduces incremental backup and recovery. For more information, please follow other related articles on the PHP Chinese website!