如何实现MySQL增量备份与恢复？

￡神魔★判官ぃ 2023-06-28 06:29 18阅读 0赞

### 文章目录 ###

*  前言
 *  一：数据库备份的分类
 *   *   *  1.1：数据丢失的原因有哪些？
         *  1.2：从物理与逻辑的角度，如何分类备份？
         *  1.3：从数据库的备份策略角度，如何分类备份？
 *  二：如何实现MySQL增量备份？
 *   *   *  2.1：什么是MySQL增量备份？
         *  2.2：使用mysqldump命令进行完全备份存在的问题
         *  2.3：增量备份有什么优缺点？
         *  2.4：如何实现MySQL增量备份？
 *  三：如何增量恢复MySQL数据库
 *   *   *  3.1：增量恢复的步骤是什么？
         *  3.2：有什么方式进行增量恢复？
         *   *   *  3.2.1：如何能查看到日志文件中的操作语句？
                 *  3.2.2：一般恢复
                 *  3.2.3：断点恢复
 *  四：实操增量备份
 *   *   *  4.1：物理冷备份，开启服务
         *  4.2：开启二进制日志文件
         *  4.3：创建库和表，进行完全备份和增量备份
         *  4.4：进行正常操作和误操作，进行增量备份
         *  4.5：查看增量备份文件
         *  4.6：模拟故障，删除表并进行恢复
         *  4.7：使用基于时间点的断点恢复
         *  4.8：使用基于位置的断点恢复

# 前言 #

*  在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果

# 一：数据库备份的分类 #

### 1.1：数据丢失的原因有哪些？ ###

*  程序错误
 *  人为错误（大部分原因）
 *  计算机失败
 *  磁盘失败
 *  灾难（如火灾，地震）和偷窃

### 1.2：从物理与逻辑的角度，如何分类备份？ ###

*  物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
    
     *  数据库在物理层面分成了三类文件，每一个文件的名字以表的名字开始，扩展名指出文件类型
        
         *  表结构文件：.frm文件存储表定义
         *  表数据文件的扩展名为.MYD（ MYData）
         *  表索引文件的扩展名是.MYI（ MYIndex）
     *  物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）
     *  冷备份：是在关闭数据库的时候进行的
     *  热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件
 *  逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份

### 1.3：从数据库的备份策略角度，如何分类备份？ ###

*  完全备份：每次对数据进行完整的备份
 *  差异备份：备份那些自从上次完全备份之后被修改过的文件
 *  增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

# 二：如何实现MySQL增量备份？ #

### 2.1：什么是MySQL增量备份？ ###

*  只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
 *  增量备份就是备份自上一次备份之后增加或变化的文件或者内容

### 2.2：使用mysqldump命令进行完全备份存在的问题 ###

*  备份数据中有重复数据
 *  备份时间与恢复时间长

### 2.3：增量备份有什么优缺点？ ###

*  优点：
    
     *  没有重复数据，效率高，空间利用率最大化
     *  备份量不大，时间短
 *  缺点：
    
     *  恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
     *  安全性较低

### 2.4：如何实现MySQL增量备份？ ###

*  MySQL没有提供直接的增量备份方法
 *  可以通过 MySQL提供的二进制日志（ binary logs）间接实现增量备份
    
        MySQL的配置文件的[mysqld]项中加入log-bin=filepath项（filepath是二进制文件的路径），如log-bin=mysql-bin，然后重启mysqld服务。
        
        二进制日志文件的默认路径为/usr/local/mysql/data
        
        [root@localhost ~]# vim /etc/my.cnf
        '//在[mysqld]项中加入配置 log-bin=mysql-bin'
        ...省略内容
        [mysqld]
        user = mysql
        basedir = /usr/local/mysql
        datadir=/usr/local/mysql/data
        port = 3306
        character_set_server=utf8
        pid-file = /usr/local/mysql/mysqld.pid
        socket = /usr/local/mysql/mysql.sock
        server-id = 1
        log-bin=mysql-bin	'//添加此句'
        ...省略内容
        [root@localhost ~]# systemctl restart mysqld '//重启服务'
        [root@localhost ~]# ls /usr/local/mysql/data/
        ...省略内容
        mysql-bin.000001	'//发现已经生成了二进制文件，设置成功'
        ...省略内容
 *  MySQL二进制日志对备份的意义
    
     *  二进制日志保存了所有更新或者可能更新数据库的操作
     *  二进制日志在启动 MySQL服务器后开始记录，并在文件达到max\_binlog\_size所设置的大小或者接收到 flush-logs命令后重新创建新的日志文件
        
            语法
            mysqladmin -u 用户名 -p [密码] flush-logs
     *  只需定时执行 flush-logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

# 三：如何增量恢复MySQL数据库 #

### 3.1：增量恢复的步骤是什么？ ###

*  添加数据–进行完全备份–录入新的数据–进行增量备份–模拟故障–恢复操作
    
    ![mark][]

### 3.2：有什么方式进行增量恢复？ ###

##### 3.2.1：如何能查看到日志文件中的操作语句？ #####

*      mysqlbinlog --no-defaults --base64-output=decode-rows -v 日志文件名称 /opt/aaa.txt	'//使用64位解码器按行输出日志文件放到/opt/aaa.txt中'
        cat /opt/aaa.txt  '//查看日志文件的详细信息'

##### 3.2.2：一般恢复 #####

*      语法
        mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p

##### 3.2.3：断点恢复 #####

*  基于位置恢复
    
     *  就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复
     *      恢复数据到指定位置
            mysqlbinlog --stop-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码
        
            从指定的位置开始恢复数据
            mysqlbinlog --start-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码
 *  基于时间点恢复
    
     *  使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式
     *      从日志开头截止到某个时间点的恢复
            mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
     *      从某个时间点到日志结尾的恢复
            mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
     *      从某个时间点到某个时间点的恢复
            mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码

# 四：实操增量备份 #

### 4.1：物理冷备份，开启服务 ###

[root@localhost ~]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data
    [root@localhost ~]# ls /opt
    mysql-2020-01-08.tar.xz  mysql-5.7.17  rh
    [root@localhost ~]# systemctl start mysqld

### 4.2：开启二进制日志文件 ###

[root@localhost ~]# vim /etc/my.cnf
    '//在[mysqld]项中加入配置 log-bin=mysql-bin'
    ...省略内容
    [mysqld]
    user = mysql
    basedir = /usr/local/mysql
    datadir=/usr/local/mysql/data
    port = 3306
    character_set_server=utf8
    pid-file = /usr/local/mysql/mysqld.pid
    socket = /usr/local/mysql/mysql.sock
    server-id = 1
    log-bin=mysql-bin	'//添加此句'
    ...省略内容
    [root@localhost ~]# systemctl restart mysqld '//重启服务'
    [root@localhost ~]# ls /usr/local/mysql/data/
    ...省略内容
    mysql-bin.000001	'//发现已经生成了二进制文件，设置成功'
    ...省略内容

### 4.3：创建库和表，进行完全备份和增量备份 ###

[root@localhost data]# mysql -uroot -p 
    Enter password: 	'//输入密码登陆'
    mysql> create database yiku;	'//创建库'
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use yiku;	'//使用库'
    Database changed
    
    mysql> create table yibiao (id int(4) primary key,name char(10));	'//创建表'
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into yibiao values(1,'zhangsan'),(2,'lisi');	'//插入记录'
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from yibiao;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    +----+----------+
    2 rows in set (0.00 sec)
    mysql>exit
    [root@localhost data]# mysqldump -uroot -p yiku > /opt/yiku.sql '//进行完全备份'
    Enter password: 
    [root@localhost data]# mysqladmin -uroot -p flush-logs '//进行增量备份'
    Enter password: 
    [root@localhost data]# cd /usr/local/mysql/data/
    [root@localhost data]# ls
     mysql-bin.000001   mysql-bin.000002  ...省略内容  '//发现已经有了增量备份文件，刚刚备份的操作记录都存放在 mysql-bin.000001中， mysql-bin.000002是准备存放后续操作的'

### 4.4：进行正常操作和误操作，进行增量备份 ###

mysql> insert into yibiao values(3,'wangwu');	'//正常操作'
    Query OK, 1 row affected (0.00 sec)
    
    mysql> delete from yibiao where name='wangwu';	'//误操作'
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into yibiao values(4,'zhaoliu');	'//正常操作'
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from yibiao;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    |  4 | zhaoliu  |
    +----+----------+
    3 rows in set (0.00 sec)
    mysql>  exit
    Bye
    [root@localhost data]# mysqladmin -uroot -p flush-logs;	'//再次增量备份'
    Enter password: 
    [root@localhost data]# ls	'//发现再次生成新的增量备份文件ysql-bin.000003'
    mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 '//刚刚的正常操作和无操作都存放到了mysql-bin.000002中'

### 4.5：查看增量备份文件 ###

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt
    [root@localhost data]# vim /opt/bak.txt 	'//发现能够查看到刚刚的操作语句'
    ...省略内容
    # at 343	'//正常操作语句段'
    #200108  0:29:18 server id 1  end_log_pos 390 CRC32 0x9ada4ac9  Write_rows: table id 219 flags: STMT_END_F
    ### INSERT INTO `yiku`.`yibiao`	
    ### SET
    ###   @1=3
    ###   @2='wangwu'
    # at 390
    ...省略内容
    # at 558	'//误操作语句段，558为误操作语句的开始位置id，200108 0:29:56 为误操作语句的开始位置时间，也是上一语句的结束时间'
    #200108  0:29:56 server id 1  end_log_pos 610 CRC32 0xb990c664  Table_map: `yiku`.`yibiao` mapped to number 219
    # at 610	
    #200108  0:29:56 server id 1  end_log_pos 657 CRC32 0x1738ac5d  Delete_rows: table id 219 flags: STMT_END_F
    ### DELETE FROM `yiku`.`yibiao`
    ### WHERE
    ###   @1=3
    ###   @2='wangwu'
    # at 657
    ...省略内容
    # at 825	'//正常操作语句段，825为正常操作语句的开始位置id，200108 0:30:11 为正常操作语句的开始位置时间，也是上一语句的结束时间'
    #200108  0:30:11 server id 1  end_log_pos 877 CRC32 0x89f71cba  Table_map: `yiku`.`yibiao` mapped to number 219
    # at 877	
    #200108  0:30:11 server id 1  end_log_pos 925 CRC32 0xae064160  Write_rows: table id 219 flags: STMT_END_F
    ### INSERT INTO `yiku`.`yibiao`
    ### SET
    ###   @1=4
    ###   @2='zhaoliu'
    # at 925
    #200108  0:30:11 server id 1  end_log_pos 956 CRC32 0xe6213e68  Xid = 57
    ...省略内容

### 4.6：模拟故障，删除表并进行恢复 ###

[root@localhost data]# mysql -uroot -p 
    Enter password: 
    mysql> drop table yibiao;	'//删除原有的表'
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show tables;
    Empty set (0.00 sec)
    
    mysql> source /opt/yiku.sql;	'//恢复库'
    
    mysql> show tables;	'//恢复成功'
    +----------------+
    | Tables_in_yiku |
    +----------------+
    | yibiao         |
    +----------------+
    1 row in set (0.00 sec)
    
    mysql> select * from yibiao;	'//恢复成功'
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    +----+----------+
    2 rows in set (0.00 sec)

### 4.7：使用基于时间点的断点恢复 ###

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-01-08 0:29:56' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p '//第一个正常操作的结尾时间是2020-01-08 00:29:56，所以此处用此时间，表示恢复到此时间的操作'
    '//时间在4.5查看增量备份文件中有写'
    Enter password: 
    [root@localhost data]# mysql -u root -p
    Enter password: 
    mysql> use yiku;
    mysql> select * from yibiao;	'//查看表，发现恢复成功了'
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    |  3 | wangwu   |
    +----+----------+
    3 rows in set (0.00 sec)
    [root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-01-08 00:30:11' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p '//第二个十误操作，我们要跳过。第三个正常操作的结尾时间是2020-01-08 00:30:11，所以此处用此时间，表示从此时间恢复到结尾的操作'
    '//时间在4.5查看增量备份文件中有写'
    Enter password: 
    [root@localhost data]# mysql -u root -p
    Enter password: 
    mysql> use yiku;
    mysql> select * from yibiao;	'//查看表，发现恢复成功了'
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    |  3 | wangwu   |
    |  4 | zhaoliu  |
    +----+----------+
    4 rows in set (0.00 sec)

### 4.8：使用基于位置的断点恢复 ###

'//先删除刚刚恢复的两个表记录'
    mysql> delete from yibiao where id=3;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> delete from yibiao where id=4;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from yibiao;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    +----+----------+
    2 rows in set (0.00 sec)
    mysql> exit
    [root@localhost data]# mysqlbinlog --no-defaults --stop-position='558' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p '//错误操作真正开始的位置是558，而不是610，所以要恢复到558位置结束'
    '//位置在4.5查看增量备份文件中有写'
    Enter password: 
    [root@localhost data]# mysql -u root -p
    Enter password: 
    mysql> use yiku;
    mysql> select * from yibiao;	'//查看表，发现恢复成功了'
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    |  3 | wangwu   |
    +----+----------+
    3 rows in set (0.00 sec)
    [root@localhost data]# mysqlbinlog --no-defaults --start-position='825' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p '//正常操作真正开始的位置是825，而不是877，所以要恢复到825位置结束'
    '//位置在4.5查看增量备份文件中有写'
    Enter password: 
    [root@localhost data]# mysql -u root -p
    Enter password: 
    mysql> use yiku;
    mysql> select * from yibiao;	'//查看表，发现恢复成功了'
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    |  3 | wangwu   |
    |  4 | zhaoliu  |
    +----+----------+
    4 rows in set (0.00 sec)

[mark]: https://imgconvert.csdnimg.cn/aHR0cDovL3Rhbmd6aGVuZy1waWNnby5vc3MtY24taGFuZ3pob3UuYWxpeXVuY3MuY29tL2ltZy8yMDIwMDEwNy8xOTA1MjgxNTMucG5n?x-oss-process=image/format,png