阐述MySQL数据库备份与恢复及日志文件

一、备份的目的与数据丢失的原因

1.1 备份的重要性与目的

• 因为在生成环境中，数据的安全性至关重要，所以任何数据的丢失都可以能产生严重的后果
• 备份的主要目的是为了灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等
• 在备份与恢复中，日志起到了很重要的作用

1.2 数据丢失的原因

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
• 自然灾害和盗窃

二、MySQL日志文件

• 常用的日志文件默认在/usr/local/mysql/data/中，可通过/etc/my.cnf中的mysqld客户端配置修改路径
  

2.1 错误日志

• 用于记录MySQL启动、停止或运行时产生的错误信息（默认开启）
• 可通过在配置文件中添加log-error=/usr/local/mysql/data/mysql_error.log进行修改

【在配置文件的mysqld项的最后添加该字段用来指定日志的保存位置和文件名】
【因为是默认开启所以不用添加开启选项】
[root@localhost data]# vim /etc/my.cnf
 12 [mysqld]
 27 log-error=/usr/local/mysql/data/mysql_error.log
[root@localhost data]# systemctl restart mysqld.service 
[root@localhost data]# ls
auto.cnf  ib_buffer_pool  ib_logfile0  ibtmp1  mysql_error.log     qz
bbs       ibdata1         ib_logfile1  mysql   performance_schema  sys
[root@localhost data]# cat mysql_error.log 
2021-04-14T11:37:13.073491Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 10240)
2021-04-14T11:37:13.073606Z 0 [Warning] Changed limits: table_open_cache: 1471 (requested 2000)
2021-04-14T11:37:13.225193Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprec

2.2 二进制日志（binlog）

• 用来记录所有更新了数据或者已经潜在更新了数据的语句
• 记录了数据的更改，可用于数据恢复
• 在生产环境中默认需要开启
• 手动开启方式有两种，都是在/etc/my.cnf中的mysqld选项添加修改
  • log-bin=mysql-bin
  • 或者
  • log_bin=mysql-bin

2.3 中继日志

• 一般情况下只在MySQL主从同步、读写分离集群的从节点开启
• 主节点一般不需要这个日志

2.4 慢查询日志

• 用来记录所有执行时间超过long_query_time秒的语句
• 可以通过慢查询日志找到执行时间过长的语句，以便于优化
• 默认关闭
• 数据库里也可开启慢查询日志，但是临时开启，配置文件里开启则是永久的
• 需要在/etc/my.cnf中的mysqld选项添加修改开启并指定日志的保存位置和文件名
  slow_query_log=ON
  slow_query_log_file=/usr/local/mysql/data/mysql_slow_query_log （指定文件路径和名称）
  long_query_time=15 （设置语句执行超过15秒则会被记录，默认为10秒）

2.5 通用查询日志

• 用来记录MySQL的所有连接和语句
• 默认是关闭的
• 需要在/etc/my.cnf中的mysqld选项添加修改开启并指定日志的保存位置和文件名
  general_log=ON
  general_log_file=/usr/local/mysql/data/mysql_general.log

2.6 查询命令

• 查看二进制日志是否开启

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.06 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            | OFF                                      |
| slow_query_log_file       | /usr/local/mysql/data/localhost-slow.log |
+---------------------------+------------------------------------------+
5 rows in set (0.00 sec)

• 查看慢查询时间设置

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

• 在数据库中开启或关闭慢查询（临时）

mysql> set global slow_query_log=OFF;
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            | OFF                                      |
| slow_query_log_file       | /usr/local/mysql/data/localhost-slow.log |
+---------------------------+------------------------------------------+
5 rows in set (0.01 sec)

2.7 开启查看二进制文件

• mysql-bin.000001：开启二进制日志会产生一个索引文件

[root@localhost data]# systemctl restart mysqld.service 
[root@localhost data]# ls
auto.cnf        ibdata1      ibtmp1            mysql-bin.000002  performance_schema
bbs             ib_logfile0  mysql             mysql-bin.index   qz
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql_error.log   sys
[root@localhost data]# cat mysql-bin.000001                                           _þbinzڶ`w{5.7.20-logzڶ`8

**4Nwk&zڶ`#jᮔڶ`±½Bk[root@localhost data]# 

2.8 索引文件的作用及刷新方式

• 索引文件作用：记录更新语句
• 索引文件刷新方式
  1.重启mysql的时候会更新索引文件，用于记录新的更新语句
  2.刷新二进制日志

三、数据库备份类型

3.1 从物理与逻辑的角度分类

• 数据库备份可以分为物理备份和逻辑备份
• 物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
• 出现问题时需要快速恢复的大型重要数据库适合使用物理备份
• 物理本分又分为冷备份（脱机备份）、热备份（联机备份）和温备份

3.1.1 冷备份

• 在数据库关闭状态下进行备份操作（tar）

3.1.2 热备份

• 在数据库处于运行状态时进行备份操作（mysqldump）
• 该备份方法依赖于数据库的日志文件

3.1.3 温备份

• 数据库锁定表格（不可写，但可读）的状态下进行备份操作

3.1.4 逻辑备份

• 逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份
• 表示为逻辑数据库结构（create database，create table语句）和内容（insert语句或分隔文本文件）的信息
• 这种类型的备份适用于可以编辑数值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据

3.2 从备份策略角度分类

3.2.1 完全备份

• 每次都对数据进行完整的备份，也就是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库，也是差异备份和增量备份的基础
• 完全备份的备份与恢复操作都非常的简单方便，但是数据会存在大量的重复，从而会占用大量的磁盘空间。所以备份过程的用时也很长

3.2.2 差异备份

• 备份哪些自从上次完全备份之后又被修改过的所有文件
• 备份的时间节点是从上次完整备份起，备份数据量会越来越大
• 恢复数据时，只需要恢复上次的完全备份与最近的一次差异备份

3.3.3 增量备份

• 只有在上次完全备份或者增量备份后又被修改的文件才会被备份
• 以上次完整备份或者上次增量备份的时间为时间点，仅备份这之间的数据变化，所以备份的数据量小，占用空间小，备份速度快
• 但是在恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如果中间有某次的备份数据损坏，将会导致数据的丢失

3.3 备份的方法

• MySQL数据库的备份可以采用很多方式，例如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等等

3.3.1 物理冷备份

• 物理冷备份时需要在数据库处于关闭的状态下进行，能够较好地保证数据库的完整性。
• 物理冷备份一般用于非核心业务。因为这类业务一般都允许中断，物理冷备份的特点就是速度快，并且恢复时也是最为简单的
• 通常通过直接打包数据库文件夹（/usr/local/mysql/data）来实现

3.3.2 专用备份工具mysqldump或者mysqlhotcopy

• mysqldump程序和mysqlhotcopy都可以做备份
• mysqldump是客户端常用的逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL语句
• mysqldump可以转存储一个到多个MySQL数据库，并对其进行备份或者传输到远程SQL服务器
• mysqldumo更为通用的原因是因为它可以备份各种表，而mysqlhotcopy仅适用于某些存储引擎

3.3.3 通过启用二进制日志进行增量备份

• 支持增量备份，进行增量备份时必须启用二进制日志
• 二进制日志为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复
• 如果进行增量备份（包含自上次完全备份或增量备份以后发送的数据修改），需要刷新二进制日志

3.3.4 通过第三方工具备份

• Percona XtraBackup是一个免费的MySQL热备份软件，支持在线热备份Innodb和XtraDB
• 也可以支持MySQL表备份，但是MyISAM表的备份则需要在表锁的情况下进行

四、各种方式备份与恢复实验操作

4.1 实验准备（创建表结构与表数据）

mysql> create database qz;
mysql> use qz;
mysql> create table if not exists 员工薪资 (工号 int(6)not null auto_increment,姓名 varchar(10) not null,薪资 char(10)not null,部门 varchar(10),primary key(工号));
mysql> insert into 员工薪资 values(001,'张悦悦','5000','一部');
mysql> insert into 员工薪资 values(002,'陈悦悦','6000','一部');
mysql> select * from 员工薪资;
+--------+-----------+--------+--------+
| 工号   | 姓名      | 薪资   | 部门   |
+--------+-----------+--------+--------+
|      1 | 张悦悦    | 5000   | 一部   |
|      2 | 陈悦悦    | 6000   | 一部   |
+--------+-----------+--------+--------+

4.2 MySQL完全备份

• InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt（表属性文件）、表名.frm（表结构文件）、表名.ibd（表数据文件）

4.2.1 物理冷备份与恢复

mysql> exit
Bye
[root@localhost ~]# systemctl stop mysqld.service 

【安装xz压缩工具】
[root@localhost ~]# yum -y install xz                
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf  bbs  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  mysql_error.log  performance_schema  qz  sys

【将/usr/local/mysql/data/目录压缩到/mnt/目录下以mysql_all_命名并加上时间戳】
[root@localhost mnt]# tar Jcvf /mnt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
[root@localhost data]# cd /mnt/
[root@localhost mnt]# ls
mysql_all_2021-04-19.tar.xz


【解压恢复】
[root@localhost mnt]# tar Jxvf /mnt/mysql_all_2021-04-19.tar.xz -C /usr/local/mysql/data/

4.2.2 mysqldump备份与恢复

• 完全备份一个或多个完整的库（也包括其中所有的表）
• 格式：mysqldump -u root -p[密码] --databases 库名1 [库名2] > /备份路径/备份文件名.sql

【备份数据库里qz库】
[root@localhost ~]# mysqldump -uroot -p5514 --databases qz > /opt/qz.sql
[root@localhost ~]# cd /opt/


【备份数据库里mysql和bbs库】
[root@localhost opt]# mysqldump -uroot -p5514 --databases mysql bbs > /opt/mysql-bbs.sql



【备份数据库里所有库的表结构】
[root@localhost opt]# mysqldump -u root -p5514 -d --all-databases > /opt/mysql-all.sql
【-d：只保存数据库的表结构。不使用-d选项说明表数据也进行备份】



[root@localhost opt]# ls
dis  Discuz_X3.4_SC_UTF8.zip  mysql-5.7.20  mysql-all.sql  mysql-bbs.sql  nginx-1.12.2  nginx-1.12.2.tar.gz  php-7.1.10  php-7.1.10.tar.bz2  qz.sql  rh


【cat进行备份的查看】
[root@localhost opt]# cat qz.sql 
【也可通过grep过滤查看】
[root@192 ~]# grep -v "^--" /opt/qz.sql | grep -v "^/" | grep -v "^$"

4.2.3 MySQL完全备份恢复

【-e：用于连接MySQL数据库后执行的命令，命令执行完后自动退出】
[root@192 ~]# mysql -u root -p -e 'drop database qz;'
Enter password: 
[root@192 ~]# mysql -u root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@192 ~]# mysql -u root -p < /opt/qz.sql 
Enter password: 
[root@192 ~]# mysql -u root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| qz                 |
| sys                |
+--------------------+

• 在数据库里操作

mysql> drop database qz;
mysql> source /opt/qz.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| qz                 |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

4.2.4 数据表的备份与恢复

【将数据库qz库里的员工薪资表备份到/opt目录下的员工薪资.sql】
[root@192 ~]# mysqldump -uroot -p5514 qz 员工薪资 > /opt/员工薪资.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@192 ~]# cd /opt/
[root@192 opt]# ls
dis                      mysql-5.7.20   mysql-bbs.sql  nginx-1.12.2.tar.gz  php-7.1.10.tar.bz2  rh
Discuz_X3.4_SC_UTF8.zip  mysql-all.sql  nginx-1.12.2   php-7.1.10           qz.sql              员工薪资.sql

【通过-e选项执行连接数据库后的删除和查看命令】
[root@192 opt]# mysql -uroot -p5514 -e 'drop table qz.员工薪资;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@192 opt]# mysql -uroot -p5514 -e 'show tables from qz;'
mysql: [Warning] Using a password on the command line interface can be insecure.

【将/opt/员工薪资.sql 恢复到数据库的qz库里】
[root@192 opt]# mysql -u root -p qz < /opt/员工薪资.sql 
Enter password: 
[root@192 opt]# mysql -uroot -p5514 -e 'show tables from qz;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| Tables_in_qz |
+--------------+
| 员工薪资     |
+--------------+

• 在数据库里操作

mysql> show tables;
+--------------+
| Tables_in_qz |
+--------------+
| 员工薪资     |
+--------------+
1 row in set (0.00 sec)
mysql> drop table 员工薪资;
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> source /opt/员工薪资.sql
mysql> show tables;
+--------------+
| Tables_in_qz |
+--------------+
| 员工薪资     |
+--------------+
1 row in set (0.00 sec)

4.3 MySQL增量备份与恢复

• 增量备份需要开启二进制日志功能

[root@192 ~]# vim /etc/my.cnf

 27 log-bin=mysql-bin
 28 binlog-format=MIXED     【可选。指定二进制日志（binlog）的记录格式为MIXED】


【配置完后需重启数据库】
[root@192 ~]# systemctl restart mysqld.service 
[root@192 ~]# cd /usr/local/mysql/data/

【重启后查看生成二进制日志mysql-bin.000001】
[root@192 data]# ls
auto.cnf  bbs  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql-bin.000001  mysql-bin.index  performance_schema  qz  sys

• 二进制日志（binlog）有3种不同的记录格式（默认STATEMENT）
  • STATEMENT（基于SQL语句）、ROW（基于行）、MIXED（混合模式）
• binlog_format=STATEMENT
  • 每一条涉及到被修改的sql都会记录在binlog中
  • 缺点是日志量过大。例如sleep（）函数，last_insert_id()>，以及user-defined functions(udf)、主从复制等架构记录日志时会出现问题
• binlog_format=ROW
  • 值记录变动的记录，不记录sql的上下文环境
  • 缺点是如果遇到updata…set…where true 你们binlog的数据量则会变大
• binlog_format=MIXED
  • 推荐使用
  • 一般的语句使用statement，函数则使用ROW方式存取

4.3.1 MySQL增量备份

【开启完二进制日志后再在数据库进行相应操作】
mysql> create database qz;
Query OK, 1 row affected (0.05 sec)

mysql> use qz;
Database changed
mysql> create table if not exists 员工薪资 (工号 int(6)not null auto_increment,姓名 varchar(10) not null,薪资 char(10)not null,部门 varchar(10),primary key( 工号));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into 员工薪资 values(001,'张悦悦','5000','一部');
Query OK, 1 row affected (0.01 sec)

mysql> insert into 员工薪资 values(002,'陈悦悦','6000','二部');
Query OK, 1 row affected (0.01 sec)



【可每周对数据库或表进行完全备份（增量备份是基于完全备份，所以这里我们直接备份数据库）】
[root@192 data]# mysqldump -uroot -p5514 qz 员工薪资 > /opt/qz_员工薪资$(date +%F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@192 data]# mysqldump -uroot -p5514 qz > /opt/qz$(date +%F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@192 data]# ls /opt/
dis                      mysql-5.7.20   mysql-bbs.sql  nginx-1.12.2.tar.gz  php-7.1.10.tar.bz2  qz.sql                     qz_员工薪资.sql  员工薪资.sql
Discuz_X3.4_SC_UTF8.zip  mysql-all.sql  nginx-1.12.2   php-7.1.10           qz2021-04-19.sql    qz_员工薪资2021-04-19.sql  rh




【可以每天进行增量备份操作，生成新的二进制日志文件（这里例如mysql-bin.000002）】
[root@192 data]# mysqladmin -uroot -p5514 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@192 data]# ls
auto.cnf  ib_buffer_pool  ib_logfile0  ibtmp1  mysql-bin.000001  mysql-bin.index     qz
bbs       ibdata1         ib_logfile1  mysql   mysql-bin.000002  performance_schema  sys




【这时再生成一个新的二进制文件mysql-bin.000002用于记录后续模拟错误数据】
[root@192 data]# mysqladmin -uroot -p5514 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@192 data]# ls
auto.cnf  ib_buffer_pool  ib_logfile0  ibtmp1  mysql-bin.000001  mysql-bin.index     qz
bbs       ibdata1         ib_logfile1  mysql   mysql-bin.000002  performance_schema  sys


【删除qz库，用于模拟数据丢失以变更（该操作是记录在二进制文件mysql-bin.000002里）】
mysql> drop database qz;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


【查看二进制文件的内容】
[root@192 data]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/
[root@192 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001
【--base64-output=decode-rows：使用64位编码机制去解码并按行读取】
【-v：显示详细内容】
【--no-defaults：不加的话会报UTF-8的错】

4.3.2 MySQL增量恢复

4.3.2.1 一般恢复

[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p5514
mysql: [Warning] Using a password on the command line interface can be insecure.

【因为恢复的是二进制文件mysql-bin.000001里的数据，所以删除qz库的操作不存在】
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| qz                 |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_qz |
+--------------+
| 员工薪资     |
+--------------+
1 row in set (0.00 sec)

mysql> select * from 员工薪资;
+--------+-----------+--------+--------+
| 工号   | 姓名      | 薪资   | 部门   |
+--------+-----------+--------+--------+
|      1 | 张悦悦    | 5000   | 一部   |
|      2 | 陈悦悦    | 6000   | 二部   |
+--------+-----------+--------+--------+
2 rows in set (0.01 sec)

4.3.2.2 丢失所有数据的恢复

【生成新的二进制日志用于记录新的操作】
[root@localhost ~]# mysqladmin -uroot -p5514 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /usr/local/mysql/data/
auto.cnf  ib_buffer_pool  ib_logfile0  ibtmp1  mysql-bin.000001  mysql-bin.000003  performance_schema  sys
bbs       ibdata1         ib_logfile1  mysql   mysql-bin.000002  mysql-bin.index   qz

【在数据库qz里新进行一步操作（该操作将被记录在二进制日志文件mysql-bin.000003里）】
mysql> insert into 员工薪资 values(004,'吴悦悦','4500','一部');


【这时模拟数据库表格数据丢失】
mysql> drop table 员工薪资;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)



【先将之前备份的完全备份恢复（完全备份里只有“工号1和工号2的操作记录”）】
【而二进制日志文件mysql-bin.000003记录了最新的操作步骤（工号4的操作记录）】
【所以这里恢复二进制日志文件mysql-bin.000003】
[root@localhost ~]# mysql -uroot -p5514 qz < /opt/qz_员工薪资2021-04-19.sql 
[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000003 /opt/
[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p5514

mysql> show tables;
+--------------+
| Tables_in_qz |
+--------------+
| 员工薪资     |
+--------------+
1 row in set (0.00 sec)

mysql> select * from 员工薪资;
+--------+-----------+--------+--------+
| 工号   | 姓名      | 薪资   | 部门   |
+--------+-----------+--------+--------+
|      1 | 张悦悦    | 5000   | 一部   |
|      2 | 陈悦悦    | 6000   | 二部   |
|      4 | 吴悦悦    | 4500   | 一部   |
+--------+-----------+--------+--------+
3 rows in set (0.00 sec)

4.3.2.2 断点恢复（基于位置和时间）

• 如果恢复某条SQL语句之前的数据，就STOP在这个语句的位置节点（at）或者时间点
• 如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start
• 有同样时间点的话直接跳过，到下一个不一样时间时进行
• 基于位置点恢复（at）

【刷新生成一个新的二进制日志mysql-bin.000010用于记录】
[root@192 data]# mysqladmin -uroot -p5514 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@192 data]# ls
auto.cnf        ibdata1      ibtmp1            mysql-bin.000002  mysql-bin.000005  mysql-bin.000008  mysql-bin.index     sys
bbs             ib_logfile0  mysql             mysql-bin.000003  mysql-bin.000006  mysql-bin.000009  performance_schema
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.000004  mysql-bin.000007  mysql-bin.000010  qz




【新建一个表并插入4条记录并查看相对应的位置点及时间点】
mysql> create table qqq (id int(40));
mysql> insert into qqq values(001);
mysql> insert into qqq values(002);
mysql> insert into qqq values(003);
mysql> insert into qqq values(004);
mysql> show tables;
+--------------+
| Tables_in_qz |
+--------------+
| 员工薪资     |
| q            |
| qa           |
| qq           |
| qqq          |
| qw           |
+--------------+



【查看后发现需要跳过的操作节点开始于994，停止与1091】
[root@192 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000010
# at 994
#210420 12:23:45 server id 1  end_log_pos 1091 CRC32 0xecdfe8b5 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1618892625/*!*/;
insert into qqq values(003)
/*!*/;



【再刷新一个新的二进制日志文件mysql-bin.000011用于记录后面删表操作】
【如果不生成新二进制日志文件则再恢复时会把下面删表的操作也执行】
[root@192 data]# mysqladmin -uroot -p5514 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@192 data]# ls
auto.cnf        ibdata1      ibtmp1            mysql-bin.000002  mysql-bin.000005  mysql-bin.000008  mysql-bin.000011    qz
bbs             ib_logfile0  mysql             mysql-bin.000003  mysql-bin.000006  mysql-bin.000009  mysql-bin.index     sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.000004  mysql-bin.000007  mysql-bin.000010  performance_schema




【恢复数据停止到节点994。因为插入数据3的操作记录开始于994，停止于1091】
[root@192 data]# mysqlbinlog --no-defaults --stop-position='994' mysql-bin.000010 | mysql -uroot -p5514
mysql> show tables;
+--------------+
| Tables_in_qz |
+--------------+
| 员工薪资     |
| q            |
| qa           |
| qq           |
| qqq          |
| qw           |
+--------------+
mysql> select * from qqq;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+




【开始于节点1091，则跳过插入3的操作步骤】
[root@192 data]# mysqlbinlog --no-defaults --start-position='1091' mysql-bin.000010 | mysql -uroot -p5514
mysql> select * from qqq;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
+------+

• 基于时间点恢复

【查看二进制日志文件找到插入数据3的时间开始于停止节点】
[root@192 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000010
# at 994
#210420 12:23:45 server id 1  end_log_pos 1091 CRC32 0xecdfe8b5 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1618892625/*!*/;
insert into qqq values(003)
/*!*/;
# at 1091
#210420 12:23:45 server id 1  end_log_pos 1122 CRC32 0xfc8d8f38 	Xid = 146
COMMIT/*!*/;
# at 1122
#210420 12:23:47 server id 1  end_log_pos 1187 CRC32 0xe7e85633 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;


【恢复到时间节点2021-04-20 12:23:45前的数据】
[root@192 data]# mysqlbinlog --no-defaults --stop-datetime='2021-04-20 12:23:45' mysql-bin.000010 | mysql -uroot -p5514
mysql: [Warning] Using a password on the command line interface can be insecure.


mysql> show tables;
+--------------+
| Tables_in_qz |
+--------------+
| 员工薪资     |
| q            |
| qa           |
| qq           |
| qqq          |
| qw           |
+--------------+
6 rows in set (0.00 sec)
mysql> select * from qqq;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)


【跳过时间节点2021-04-20 12:23:45的数据再开始恢复】
[root@192 data]# mysqlbinlog --no-defaults --start-datetime='2021-04-20 12:23:47' mysql-bin.000010 | mysql -uroot -p5514
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from qqq;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
+------+
3 rows in set (0.00 sec)

五、总结

5.1 备份的目的

• 备份的主要目的是为了灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等

5.2 MySQL日志文件分类及作用

• 错误日志：于记录MySQL启动、停止或运行时产生的错误信息（默认开启）
• 二进制日志（binlog）：用来记录所有更新了数据或者已经潜在更新了数据的语句，可用于数据恢复
• 中继日志：一般情况下只在MySQL主从同步、读写分离集群的从节点开启
• 慢查询日志：用来记录所有执行时间超过long_query_time秒的语句
• 通用查询日志：用来记录MySQL的所有连接和语句，默认关闭

5.3 数据库备份类型

5.3.1 从物理与逻辑的角度分类

• 分为冷备份（脱机备份）:在数据库关闭状态下进行备份操作（tar）
• 热备份（联机备份）:在数据库处于运行状态时进行备份操作（mysqldump）
• 温备份:数据库锁定表格（不可写，但可读）的状态下进行备份操作
• 逻辑备份:逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份

5.3.2 从备份策略角度分类

• 完全备份: 每次都对数据进行完整的备份，也就是对整个数据库、数据库结构和文件结构的备份
• 差异备份:备份哪些自从上次完全备份之后又被修改过的所有文件
• 增量备份:只有在上次完全备份或者增量备份后又被修改的文件才会被备份

5.4 备份的方法

• 物理冷备份
• 备份工具mysqldump或者mysqlhotcopy
• 二进制日志进行增量备份
• 第三方工具备份