• MySQL日志管理和完全备份增量备份与恢复

    文章目录

    前言

    由于日志文件是掌握数据库的运行的重要参考；，因此日志文件的维护也有十分重要的意义。MySQL的日志类型有错误日志、通用查询日志、二进制日志、慢查询日志。
    请添加图片描述

    一、日志分类

    MySQL 的日志默认保存位置为**/usr/local/mysql/data**
    MySQL 的日志配置文件为/etc/my.cnf，里面有个**[mysqld]**项。

     vim /etc/my.cnf
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log	 #指定日志的保存位置和文件名

##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin #也可以 log_bin=mysql-bin

##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5	#设置超过5秒执行的语句被记录，缺省时为10秒


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    1.1错误日志

    ①错误日志主要记录当MySQL启动和停止时，以及在运行过程中发生任何错误时的相关信息。错误日志默认保存在MySQL的安装路径data文件夹下，后缀名是.err。

    ②在MySQL的配置文件中，可以指定日志文件的保存位置和日志的文件名。log-error=file_name选项来指定保存报错日志文件的位置，file-name指定日志文件名，如果没有指定文件名，使用host-name.err作为文件名。

    [root@localhost data]# vim /etc/my.cnf
//省略内容
log-error=/usr/local/mysql/data/mysql_error.log	 #指定日志的保存位置和文件名
//省略内容

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    ③重启MySQL后，生成mysql_error.log用来记录错误日志。

    [root@localhost data]# systemctl restart mysqld
[root@localhost data]# ls
 mysql_error.log
 //省略内容  
 

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    注意：MySQL必须指定的文件夹有读写的权限，否则无法操作文件。

    1.2通用查询日志

    ①通用查询日志用来记录MySQL的所有连接和语句，默认是关闭的。使用SHOW语句可以查询出日志的信息。

    mysql> show variables like 'general%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | OFF                                 |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    在这里插入图片描述
    general_log对应的时通用查询日志，它的值是OFF，表示是关闭的。

    ②修改MySQL配置文件的general_log=ON选项，可以打开通用日志查询日志，general_log_file=file_name定义通用查询日志的位置，如果没有指定file_name的值，默认名是host_name.log。

    [root@localhost data]# vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log


    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    ③重启mysql后，通用查询日志生效。

    [root@localhost data]# systemctl restart mysqld
[root@localhost data]# ls
mysql_general.log 

    • 1
    • 2
    • 3

    在这里插入图片描述

    ④使用SHOW语句查看通用查询日志已经打开，general_log对应的值是ON。

    mysql> show variables like 'general%';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | ON                                      |
| general_log_file | /usr/local/mysql/data/mysql_general.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    在这里插入图片描述

    1.3二进制日志

    ①二进制日志用来记录所以更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，主要目的是在恢复数据时能够最大程度地恢复数据库。二进制日志默认是开启的，在data文件夹下，以mysql-bin命名，数据量大时，它会自动分割成多个日志文件，以数字作为扩展名。

    [root@localhost data]# vim /etc/my.cnf
      log-bin=mysql-bin 
      

    • 1
    • 2
    • 3

    在这里插入图片描述

    [root@localhost data]# systemctl restart mysqld
[root@localhost data]# ls
    mysql-bin.000001 

    • 1
    • 2
    • 3

    在这里插入图片描述
    ②二进制日志可以使用MySQL的工具mysqlbinlog查看二进制文件。

    [root@localhost data]# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220622  3:23:29 server id 1  end_log_pos 123 CRC32 0xcdd06d5a 	Start: binlog v 4, server v 5.7.17-log create
d 220622  3:23:29 at startup# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ccOyYg8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABxw7JiEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVpt0M0=
'/*!*/;
# at 123
#220622  3:23:29 server id 1  end_log_pos 154 CRC32 0xeec34259 	Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    解释在本机里有一条事务操作rollback，操作时间是22年6月22日3点23分39秒这就是二进制文件的内容格式，MySQL根据这样的信息做的恢复数据库操作。

    1.4慢查询日志

    慢查询日志记录所有执行时间超过long_query_time秒的SQL语句，用于找到哪些查询语句执行时间长，以便于对其优化。默认慢查询日志是关闭的，slow_query_log是慢查询的选项， 默认是OFF。

    mysql> show variables like '%slow%';//查看MysSQL慢查询功能是否开启
+---------------------------+------------------------------------------+
| Variable_name             | Value                                    |
+---------------------------+------------------------------------------+
| log_slow_admin_statements | OFF                                      |
| log_slow_slave_statements | OFF                                      |
| slow_launch_time          | 2                                        |
| slow_query_log            | OFF    //状态为OFF                                  |
| slow_query_log_file       | /usr/local/mysql/data/localhost-slow.log |
+---------------------------+------------------------------------------+
5 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    在这里插入图片描述

    ①可以使用 SQL语句开启MySQL慢查询功能。

    mysql> set global slow_query_log=ON; //开启MySQL慢查询功能
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow%';
+---------------------------+------------------------------------------+
| Variable_name             | Value                                    |
+---------------------------+------------------------------------------+
| log_slow_admin_statements | OFF                                      |
| log_slow_slave_statements | OFF                                      |
| slow_launch_time          | 2                                        |
| slow_query_log            | ON   //状态为ON                                    |
| slow_query_log_file       | /usr/local/mysql/data/localhost-slow.log |
+---------------------------+------------------------------------------+
5 rows in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    在这里插入图片描述

    慢查询时间设置默认为10秒，记录10秒内的查询，可以通用global long_query_time选项进行修改。

    mysql> show variables like 'long_query_time';//查看慢查询时间设置
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    mysql> set  long_query_time=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述
    ②也可以在MySQL的配置文件中进行设置，指定slow_query_log=ON选项启动慢查询功能，使用slow_query_log_file=filename指定存放慢查询日志位置，如果没有filename值，默认为hostname-slow.log，如果没有给出绝对路径，文件保存在data目录下。long_query_time=1设置了超过1秒执行的语句被记录。

    slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

    • 1
    • 2
    • 3

    在这里插入图片描述
    重启后MySQL后，慢查询日志mysql_slow_query.log被创建。经常检查慢日志，当有有查询日志被记录，就需要对其进行优化处理了。

    [root@localhost data]# systemctl restart mysqld
[root@localhost data]# ls
 mysql_slow_query.log


    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    ③进行慢查询测试生成慢查询日志。

    mysql> select sleep(6);
+----------+
| sleep(6) |
+----------+
|        0 |
+----------+
1 row in set (6.00 sec)

mysql> show global status like  '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 1     |
+---------------------+-------+
2 rows in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    在这里插入图片描述
    在这里插入图片描述
    查看生成的慢查询日志。

    [root@localhost data]# cat /usr/local/mysql/data/mysql_slow_query.log
/usr/local/mysql/bin/mysqld, Version: 5.7.17-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /usr/local/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2022-06-22T09:20:30.882505Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 6.001033  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1655889630;
select sleep(6);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    在这里插入图片描述

    二、数据库备份的重要性与分类

    在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果。数据库备份的重要性主要体现在：

    • 提高系统的高可用性和灾难可恢复性，在数据库崩溃时，没有数据库备份就没法找到数据。
    • 使用数据库备份还原数据库，是数据库系统崩溃时提供最小代价的最优方案，如果让用户重新添加数据，代价未免太大。
    • 没有数据就没有一切，数据库备份是一种防范灾难的强力手段。
      使用数据库的过程中，有多种原因造成数据丢失：
      备份的主要目的是灾难恢复

    在生产环境中，数据的安全性至关重要

    任何数据的丢失都可能产生严重的后果

    造成数据丢失的原因

    • 程序错误
    • 人为操作错误
    • 运算错误
    • 磁盘故障
    • 灾难(如火灾、地震)和盗窃

    2.1数据库备份的分类

    备份的方式分为很多种，从物理与逻辑的角度，备份分为以下几类：

    • 物理备份：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）。

      • 冷备份：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性。

      • 热备份：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。

    • 逻辑备份：指对数据库逻辑组件（如表等数据库对象）的备份。

    从数据库的备份策略角度，备份可以分为完全备份、差异备份和增量备份。

    • 完全备份：每次对数据进行完整版的备份。可以备份整个数据库，包括用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多时间和空间，所以，做一次完全备份的周期要长些。

    • 差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分的内容。它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。

    • 增量备份：只有那些在上次备份或者增量备份后被修改的文件才会被备份。
      MySQL 完全备份
      InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

    2.2完全备份演练

    mysql数据库的备份可以采用两种方式，因为数据库实际上就是文件，直接打包数据库文件夹，或者是使用专用备份工具mysqldump都可以进行备份工作。

    2.2.1使用tar打包文件夹备份

    MySQL的数据库文件默认都是保存在安装目录的data文件夹下面，可以直接保存data文件夹，但是占用的空间较大，可以使用tar打包压缩进行保存。

    mysql -u root -p #进入
create database caosong #建库
use school;#进库
create table if not exists class1 (
id int(4) not null auto_increment,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50),
primary key (id));
 #创表并设id为主键
insert into class1 values(1,'caosong',18,'sleep');插入数据
insert into class1 values(2,'liu',18,'run');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    ①数据库文件很大，可以使用压缩率较大的xz格式压缩，首先需要安装xz压缩格式工具。

    systemctl stop mysqld#冷备前关闭数据库服务
yum -y install xz#安装xz压缩工具，它速度快

    • 1
    • 2

    ②对数据库文件夹/usr/local/mysql/data/进行打包操作。

    #压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/#使用tar压缩，并加入压缩的日期

    • 1
    • 2

    在这里插入图片描述
    在这里插入图片描述
    ③使用ls命令，查看打包内容是否运行成功，已经生成了备份文件。
    在这里插入图片描述
    ④如果数据库文件损坏数据丢失，可以解压缩备份文件，相当于做了数据恢复。

    drop database caosong;#删除数据库
quit#退出数据库
tar Jxvf /opt/mysql_all_2022-06-22.tar.xz -C /usr/local/mysql/data/
#恢复数据
systemctl restart mysqld.service#重启服务查看被删除的库

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    2.2.2 mysqldump 备份与恢复

    前面介绍的对MySQL整个数据库目录压缩的方式，是备份数据库中所有的内容。使用mysqldump可以更加灵活地控制备份的内容，比如某几个表或库都可以单独备份。
    ①使用mysqldump命令对某些表进行完全备份，命令格式如下：

    mysqldump -u 用户名 -p[密码] [选项] [数据库名] [数据表名] … > /备份路径/备份文件名.sql

    • 1

    例：

    mysqldump -uroot -p123456 [-d] school class > /opt/school_class.sql
#不使用“-d”选项，说明表数据也进行备份
#使用“-d”选项，说明只保存数据库的表结构


    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    查看备份文件

    cat school_class1_school2.sql |grep -v '^/'|grep -v '^$'|grep -v '^-'

    • 1

    在这里插入图片描述

    ②使用mysqldump命令对数据库进行完全备份

    #完全备份单个库（包含其中所有的表）
mysqldump -u 用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名.sql #导出的就是数据库脚本文件
#备份多个库
mysqldump -u 用户名 -p[密码] [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
#完全备份所有库
mysqldump -u 用户名 -p[密码] [选项] --all-databases  > /备份路径/备份文件名.sql


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    在这里插入图片描述
    在这里插入图片描述

    2.3完全恢复

    前面介绍了数据库的完全备份，使用mysqldump工具可以把数据库的创建语句保存在备份的脚本文件中，当数据出现错误时可以使用以下几种方式对他进行恢复操作。
    当需要恢复整库的时候，可以使用source命令和mysql命令。

    2.3.1使用source命令整库恢复

    主要命令格式：
    先登录MySQL后，可以进行source命令进行数据库整库恢复，命令格式如下：

    source 备份脚本的路径

    • 1

    整体操作

    #先备份school数据库
mysqldump -uroot -p --databases school > /opt/school.sql
#查看数据库
show databases;
#删除school数据库
drop database school;
#使用source 恢复数据
source /opt/school.sql
 
 
当备份时加--databases 表示针对于school库
当备份时不加--databases 表示针对school库下的所有表
当你使用不加--databases 备份时，你恢复数据时需先创建一个当时库同名的库在进行淮阜否则会报错
主要原因在于俩种方式的备份：
前者会从create databases开始
后者全是针对表格进行操作

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    分布操作

    演示：
    ①首先对数据库进行备份

    mysqldump -uroot -p123456 --databases caosong > /opt/caosong.sql 将数据库备份到/opt/目录下并指定格式为sql

    • 1

    在这里插入图片描述

    ② 假设数据损坏，删除数据库

    mysql -uroot  -p123456 #先登录数据库
show databases;#查看现有数据库
drop database caosong；#删除数据库
show databases;#看库

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    ③登录MySQL，使用source命令恢复数据库

    source /opt/caosong.sql #恢复，可以理解为执行了一个脚本，脚本里面都是之前创库的步骤命令

    • 1

    在这里插入图片描述
    在这里插入图片描述
    使用source命令表恢复（和恢复库步骤一样）

    #先备份school数据库里的class1数据表
mysqldump -uroot -p school class1 > /opt/school_class1.sql
#进入数据库
use school;
#删除school数据库里的class1表
drop table class1;
#使用source 恢复数据
source /opt/school_class1.sql
#查看所有字段
select * from class1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    2.3.2 使用mysql命令恢复

    恢复数据库

    mysql -u root -p -e 'drop database school;'
#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出
mysql -u root -p -e 'show databases;'
 
#恢复school数据库
mysql -u root -p < /opt/school.sql		
mysql -u root -p -e 'show databases;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    ①（先备份数据）删除数据库
    在这里插入图片描述
    ②恢复库
    在这里插入图片描述

    2.4为什么使用增量备份

    前面章节讲到了完全备份有两种方式，一种是使用 tar 打包数据文件，另一种是使用 mysqldump 进行完全各份。完全备份存在的问题很容易看到，每次都是把所有的数据内容进行备份，各份数据中有大量的重复数据，并且完全备份的时间与恢复的时间很长。解决完全备份存在的问题就是使用增量各份的方式，增量各份就是备份自上一次备份之后增加或改变的文件或者内容。

    2.4.1增量备份的特点

    增量备份的优点是没有重复数据，备份量不大，时间短。缺点也很明显，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且对所有增量备份进行逐个反推恢复，操作较为繁锁。
    MySQL 没有提供直接的增量备份方法，但是可以通过MySQL 的二进制日志(binarylogs）间接实现增量备份。二进制日志对备份的意义如下：
    (1)二进制日志保存了所有更新或者可能更新数据库的操作。
    (2）二进制日志在启动 MySQL 服务器后开始记录，并在文件达到 max binlogsize 所设置的大小或者接收到 fush logs 命令后重新创建新的日志文件。
    (3）只需要定时执行 fush logs 方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

    2.5 增量备份实战演练

    ①增量备份的前提是完全备份

    mysqldump -u root -p caosong school2 > /opt/caosong_school2_$(date +%F).sql				
mysqldump -u root -p --all-databases caosong > /opt/caosong_$(date +%F).sql

    • 1
    • 2

    ② 要进行 MySQL 增量备份，首先要开启二进制日志功能。开启二进制日志功
    能有两种方法：
    ③在MySQL配置文件的imysaldj项中加入 log-bin-filepath 项，
    如 log-bin=mysql-bin，然后重启mysqld 服务。开启二进制日志功能

    vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
server-id = 1
#二进制日志有三种不同的记录格式：STATEMENT（基于SQL语句）、ROW（基于行）、MIXED（混合模式），默认为STATEMENT
#server-id 为服务的序号，在MySQL主备、高可用中需要指定服务的序号
#改为配置文件需要重启服务
systemctl restart mysqld

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    在这里插入图片描述
    ③或者使用mysqld --log-bin=filepath重新启动MySQL服务。开启二进制日志功能

    mysqld -log-bin=filepath
systemctl restart mysqld

    • 1
    • 2

    STATEMENT（基于sql语句）：

    每一条涉及到被修改的sql 都会记录在binlog中

    缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user- definedfuctions（udf）、主从复制等架构记录日志时会出现问题

    总结:增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们想想的恢复可能你先删除或者在修改，可能会倒过来。准确率低

    ROW（基于行）：

    只记录变动的记录，不记录sql的上下文环境

    缺点:如果遇到update. . . . …set. . . .where true那么binlog的数据量会越来越大

    总结: update、delete以多行数据起作用，来用行记录下来，只记录变动的记录，不记录sql的上下文环境，比如sql语句记录一行，但是Row就可能记录10行，但是准确性高，高并发的时候由于操作量，性能变低比较大所以记录都记下来，

    MIXED（混合模式）：

    一般的语句使用statement，函数使用ROW方式存储

    #只要重启就会自动生成二进制文件
systemctl restart mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

    • 1
    • 2
    • 3

    在这里插入图片描述

    mysqlbinlog --no-defaults 二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件
#--base64-output=decode-rows 使用64位编码机制去解码；decode按行读取rows
#-v 显示详细内容
#--no-defaults 默认字符集（不加会报UTF-8错误）
#将解码后的文件导出为txt文件，重定向输出
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件 > 文件.txt
#刷新日志文件
mysqladmin -uroot -p flush-logs

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    加粗样式

    at ：开始的位置点
    end_log_pos：结束的位置
    时间戳： 210712 11:50:30
    SQL语句

    2.5.1增量备份的一般恢复

    模拟丢失更改的数据的恢复操作步骤（直接使用恢复即可）

    将所有备份的二进制日志内容全部恢复

    ①模拟丢失更改的数据的恢复步骤

    mysql -uroot -p123456#进入
use school;#切换
delete from class1 where id=3;#删除数据
delete from class1 where id=4;
select * from class1;#查看
quit#退出
 
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p
#还原，记得还原一定要用上一次刷新的文件
mysql -uroot -p123456 -e "select * from caosong.class1;"
#查看

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    在这里插入图片描述
    在这里插入图片描述

    2.5.2增量备份基于位置恢复

    数据库在某一时间点可能既有错误的操作也有正确的操作
    可以基于精准的位置跳过错误的操作
    发生错误节点之前的一个节点，上一次正确操作的位置点停止

    在数据库中插入数据并查看

    use caosong
insert into school2 values(1,'lili',25,'swim')；
insert into school2 values(7,'hhh',27,'sleep');
insert into school2 values(7,'hhh',27,'run');
delete from school2 where name=lili

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述

    # 刷新生成新的二进制日志文件
mysqladmin -u root -p flush-logs
 
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.0000007
#查看日志

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    在这里插入图片描述

    在这里插入图片描述

    在这里插入图片描述

    2.5.3增量备份基于时间点恢复

    跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始
 
模拟时间点恢复，刷新日志，生成新的日志文件
 
 
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
 
#仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-2216:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
 
如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    修改表内数据

    在这里插入图片描述

    备份日志文件，并查看
    在这里插入图片描述

    在这里插入图片描述

    根据时间恢复数据内容

    mysqlbinlog --no-defaults --stop-datetime='2022-06-22 23:20:51' /opt/mysql-bin.000008 |mysql -uroot -p
 
 
mysql -uroot -p -e 'select * from test.bin;'

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    三、总结

    在增量备份恢复时，要先从完全备份恢复，再到二进制日志1、日志2…逐一恢复，如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点，如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start，全备库中source针对库mysql针对库中的表，备份时使用–database或者-B使恢复时source和mysql效果一致。

  • 相关阅读:
    前端SVG的学习
    【Mybatis源码分析】动态代理的使用（Javassist、CGLIB、JDK动态代理）
    启明云端ESP32 C3 模组WT32C3通过 MQTT 连接 AWS
    Test Module的创建及使用
    电脑前置耳机没声音怎么办
    十四、Java SPI
    2022年安全员-C证考试模拟100题模拟考试平台操作
    Spring BeanDefinition详解
    VR云游：让游客足不出户享受旅行的乐趣
    STM32 BSRR BRR ODR 寄存器解析(F4系列已经去掉BRR寄存器了)
  • 原文地址：https://blog.csdn.net/qq_45547688/article/details/125408030