MySQL(十) Binlog二进制日志恢复数据

MySQL Binlog二进制日志恢复数据

二进制日志配置管理

二进制日志：（逻辑层）

# 二进制日志到底记录了什么？
    已提交的数据记录，以event（事件）的形式记录到二进制文件中，所有的DDL、DCL、DML（insert、update、delete）

# 二进制日志记录格式有哪些？
    statement：  语句模式（就是记录执行的SQL语句），DDL、DCL只能以语句模式记录。
    row：        数据行模式（就是数据行的变化过程），只针对DML语句有效。
    mixed：      以上两者的混合模式

# binlog的作用？
    备份恢复，数据复制
    只要有全量的binlog文件，可以将数据库恢复到任意时间点

# row与statement优缺点？
    1、语句模式在批量操作时，日志量更少
    2、row模式记录数据更加严谨
    3、mixed不用

查看默认日志格式：

#5.7版本默认以ROW数据行格式记录
mysql> show variables like '%format%';
+---------------------------+-------------------+
| Variable_name             | Value             |
+---------------------------+-------------------+
| binlog_format             | ROW               |
| date_format               | %Y-%m-%d          |
| datetime_format           | %Y-%m-%d %H:%i:%s |
| default_week_format       | 0                 |
| innodb_default_row_format | dynamic           |
| innodb_file_format        | Barracuda         |
| innodb_file_format_check  | ON                |
| innodb_file_format_max    | Barracuda         |
| time_format               | %H:%i:%s          |
+---------------------------+-------------------+

重点：

二进制日志` 与 `慢日志

配置错误日志：

#配置文件内指定错误日志存放路径（默认存放在数据路径下）
log_error=/var/log/mysql.log

日志配置参数：（骚操作）

• log-bin=[base_name]：该参数表示是否开启binary log。默认情况下MySQL会使用host_name-bin.xxxx作为文件的名字，其中xxxx是以数字递增的后缀。如果该参数指定了 base_name，则二进制文件会以base_name.xxxx来命名。
• binlog-do-db=db_name： 该参数决定了哪些库下的修改会被记录到bin log中。其行为与replicate-do-db类型，在基于SQL语句复制的环境下，只记录在当前数据库下的修改。
• binlog-ignore-db=db_name：该参数决定了在bin log中忽略的数据库，其行为与 replicate-ignore-db类型。

开启MySQL二进制日志：

mkdir /usr/local/mysql/blog/ && chown mysql.mysql /usr/local/mysql/blog/
#通过配置文件开启binlog，并指定以row类型记录，启动binlog功能就要加入server_id，这是5.7版本的bug。（mysql-bin是日志的前缀名）
vim /usr/local/mysql/conf/my.cnf

log_bin=/usr/local/mysql/blog/mysql-bin        指定binlog日志存放路径及文件名前缀
binlog_format=row                              指定以row类型记录
sync_binlog=1                                  每次commit时都会刷新日志到磁盘
server_id=1
systemctl restart mysqld

binlog查询：

#前154个位置是二进制文件默认大小
#查询二进制存放的位置，或者通过配置文件查看
mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /usr/local/mysql/binlog/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
+---------------------------------+-----------------------------------------+

查看使用的binlog文件：

#可以刷新二进制文件，类似于nginx日志的切割
flush logs;
#这个命令可以看到一共有多少二进制日志文件
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
#这个命令可以看到二进制日志文件，并且可显示正在写入的二进制文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

events二进制日志事件：

对于DDL\DCL来讲，一条语句就是一个事件
对于DML来讲，是一个事务有多个事件，一个事务中有多个语句

每一个event，都有一个开始位置（position），结束位置（position）

查看binlog的事件信息：

#可以看到有两个事件
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         0 |         123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids |         0 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

测试binlog记录：

#创建一个good的库并创建一张表，然后查询二进制文件，查询类语句不会记录
mysql> create database good charset utf8;
mysql> use good;
mysql> create table t1 (id int)engine=innodb;

#然后查看二进制文件内容，发现多出来四条新记录，记录的第二条与第四条操作的是语句
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                              |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         0 |         123 | Server ver: 5.7.22-log, Binlog ver: 4             |
| mysql-bin.000002 | 123 | Previous_gtids |         0 |         154 |                                                   |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         0 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'              |
| mysql-bin.000002 | 219 | Query          |         0 |         326 | create database good charset utf8                 |
| mysql-bin.000002 | 326 | Anonymous_Gtid |         0 |         391 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'              |
| mysql-bin.000002 | 391 | Query          |         0 |         502 | use `good`; create table t1 (id int)engine=innodb |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------+

查看文件最大自动滚动大小值：

##### mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+

binlog什么时候会滚动出一个新文件？

#刷新会自动滚动出新文件
flush logs;

#重启MySQL时会自动滚动新文件
systemctl restart mysqld

#达到max_binlog_size伐值时，会自动生成新文件
mysql> show variables like '%%max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)

#备份时，加入-F会自动滚动新文件

binlog文件保存的时间？

# 默认保留 30天的binlog日志文件
mysql> show variables like '%binlog_ex%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
1 row in set (0.00 sec)

清空所有binlog文件：

#清空所有binlog文件，从000001开始重新记录，一般是在全量备份之后可以执行
reset master;

根据名称清理binlog文件：

• 可以在 master中执行这条指令 , 将 binlog 文件删除至 mysql-bin.000009 前

purge master logs to 'mysql-bin.000009';

根据时间清理binlog文件：

• 将 binlog文件删除至此日期前

purge master logs before '2014-11-18 00:00:00';

查看binlog详细内容：

#这是mysql的命令，不是SQL语句，后面跟上那个文件就看那个文件的详细内容
mysqlbinlog /usr/local/mysql/binlog/mysql-bin.000002

#常用参数使用（类似于以人类可读形式显示）
mysqlbinlog --base64-output=decode-rows -vvv /usr/local/mysql/binlog/mysql-bin.000002

#指定查看olda库详细的binlog日志语句
mysqlbinlog --base64-output=decode-rows -vvv -d olda /usr/local/mysql/binlog/mysql-bin.000003

查看relay-log详细内容：

mysqlbinlog -vvv /usr/local/mysql-8.0.16/data/olda-study0-relay-bin.000002 

基于时间点恢复数据

Binlog时间恢复：

• 根据dump全量备份全量和二进制日志增量恢复数据到指定时间点。
• 模拟每天晚上的12点整进行mysqldump全量备份
• 在第二天早上 9点出现数据故障，需要基于全量备份恢复 12点到 9点的数据
• 当前为普通模式，没有开启GTID模式，否则恢复无效？暂定疑问？？？

模拟前天数据：

mysql> create database course charset utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> use course
Database changed
mysql> create table temp(id int,name varchar(64));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into temp values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

数据库全量备份：

• 当前时间为晚上12点整，进行了数据库全量备份。

[root@db01 ~]# mysqldump -uroot -p123456 -B course -R --triggers --master-data=2 --single-transaction > /tmp/full.sql

模拟数据变化：

• 当前是前天晚上12点整 - 今天早上9点的数据变化。

mysql> insert into temp values(3,'a'),(4,'b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update temp set name='cc' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from temp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | cc   |
|    4 | b    |
+------+------+
4 rows in set (0.01 sec)

模拟删除course库：

• 早上9点时误操作删除了course数据库

mysql> drop database course;
Query OK, 1 row affected (0.03 sec)

---

全量恢复：

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

mysql> source /tmp/full.sql

mysql> use course
Database changed

mysql> select * from temp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

增量恢复

• 利用二进制日志恢复数据到早上9点的状态
• 在截断日志时要把最后一个事务的commit操作也包含进去，不然数据恢复失败。

[root@db01 data]# mysqlbinlog -v --start-datetime="2019-11-05 00:00:00" --stop-datetime="2019-11-05 09:00:00" binlog.000001  | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from temp;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | cc   |
|    4 | b    |
+------+------+
4 rows in set (0.00 sec)

---

重要（部分恢复）：

如果一个生产库中的某一个表损坏了，那就截取对应库的binlog日志，然后在一个新数据库实例中恢复整个库，最后把损坏的表导出，然后再导入到生产库
#导出db库中的t1表
mysqldump -uroot -predhat db t1 >/tmp/t1.sql

删除binlog：
reset master
清空所有binlog,从000001开始重新记录,一般是在全备份之后可以执行。
(2)过期时间设置
SET GLOBAL expire logs days =7;
注意:这个时间设置,要考虑,全备的周期。
(3)删除到几天之前
PURGE BINARY LOGS BEFORE now()-INTERVAL 3 day;
(4)根据文件名删除日志:
PURGE BINARY LOGS TO 'mysql-bin.000010;

基于position位置点恢复数据

模拟数据及删除：

刷新日志：

flush logs;

创建一个db的库：

create database db charset utf8;

创建一张表：

use db;
create table t1(id int) engine=innodb;

插入3条数据：

insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);

提交数据：

commit;

删除db库：

drop database db;

---

恢复数据：

需求：恢复到drop database db之前

截取binlog日志：

根据创建语句的起始位到提交的结束位进行binlog日志截取。
#经过分析后，从创建db库到删除db库之前是219~907日志位置

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------+
| mysql-bin.000004 |   4 | Format_desc    |         0 |         123 | Server ver: 5.7.22-log, Binlog ver: 4           |
| mysql-bin.000004 | 123 | Previous_gtids |         0 |         154 |                                                 |
| mysql-bin.000004 | 154 | Anonymous_Gtid |         0 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'            |
| mysql-bin.000004 | 219 | Query          |         0 |         320 | create database db charset utf8                 |
| mysql-bin.000004 | 320 | Anonymous_Gtid |         0 |         385 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'            |
| mysql-bin.000004 | 385 | Query          |         0 |         492 | use `db`; create table t1(id int) engine=innodb |
| mysql-bin.000004 | 492 | Anonymous_Gtid |         0 |         557 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'            |
| mysql-bin.000004 | 557 | Query          |         0 |         627 | BEGIN                                           |
| mysql-bin.000004 | 627 | Table_map      |         0 |         670 | table_id: 105 (db.t1)                           |
| mysql-bin.000004 | 670 | Write_rows     |         0 |         710 | table_id: 105 flags: STMT_END_F                 |
| mysql-bin.000004 | 710 | Table_map      |         0 |         753 | table_id: 105 (db.t1)                           |
| mysql-bin.000004 | 753 | Write_rows     |         0 |         793 | table_id: 105 flags: STMT_END_F                 |
| mysql-bin.000004 | 793 | Table_map      |         0 |         836 | table_id: 105 (db.t1)                           |
| mysql-bin.000004 | 836 | Write_rows     |         0 |         876 | table_id: 105 flags: STMT_END_F                 |
| mysql-bin.000004 | 876 | Xid            |         0 |         907 | COMMIT /* xid=16 */                             |
| mysql-bin.000004 | 907 | Anonymous_Gtid |         0 |         972 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'            |
| mysql-bin.000004 | 972 | Query          |         0 |        1058 | drop database db                                |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------+

截取之前数据日志：（Postion恢复）

#指定导出db库被截取的binglog日志信息
mysqlbinlog -d db --start-position=219 --stop-position=907 /usr/local/mysql/binlog/mysql-bin.000004 > /tmp/recover.sql

临时关闭记录binlog：

• （恢复时不建议记录binlog）

#恢复时也是产生SQL语句，这个时候不建议恢复语句也被记录到binlog日志中，不方便以后分析起始与结束
mysql> set sql_log_bin=0;

恢复db数据库：

mysql> source /tmp/recover.sql

查看是否恢复：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db                 |
| good               |
| mysql              |
| olda               |
| performance_schema |
| sys                |
| world              |
+--------------------+
mysql> use db;
Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

---

重要（部分恢复）：

如果一个生产库中的某一个表损坏了，那就截取对应库的binlog日志，然后在一个新数据库实例中恢复整个库，最后把损坏的表导出，然后再导入到生产库
#导出db库中的t1表
mysqldump -uroot -predhat db t1 >/tmp/t1.sql

删除binlog：
reset master
清空所有bin1og,从000001开始重新记录,一般是在全备份之后可以执行。
(2)过期时间设置
SET GLOBAL expire logs days =7;
注意:这个时间设置,要考虑,全备的周期。
(3)删除到几天之前
PURGE BINARY LOGS BEFORE now()-INTERVAL 3 day;
(4)根据文件名删除日志:
PURGE BINARY LOGS TO 'mysql-bin.000010;