binlog日志

日志概述

什么是binlog日志

①也称做二进制日志
②MySQL服务日志文件的一种
③记录除查询之外的所有SQL命令
④可用于数据备份和恢复
⑤配置mysq|主从同步的必要条件

启用日志

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

....

log_ _bin　　/启用binlog日志

server. id= 100　　//指定id值

[root@dbsvr1 ~]# systemctl restart mysqld

.binlog相关文件

主机名 - bin.index 索引文件

主机名 - bin.000001 第1个二进制日志

主机名 - bin.000002 第2个二进制日志

......

清理日志

删除指定编号之前的binlog日志文件
Mysql> purge master logs to "binlog文件名";
删除所有binlog日志,重建新日志
Mysq|> reset master;

恢复数据

分析日志

查看日志当前记录格式

三种记录方式:

1. statement　　报表模式

2. row　　行模式。

3. mixed　　混合模式。

mysq|> show variables like "binlog_ format";

修改日志记录格式

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

....
binlog_ format= "名称”
[root@localhost ~ ]# systemctl restart mysqld

查看日志内容

mysqlbinlog [选项] binlog日志文件名

[root@dbsvr1 ~]# mysqlbinlog \
--start-datetime="2018- 06- 0815:30" /var/lib/mysql/mysql-bin.000001

恢复数据

基本思路
使用mysqlbinlog提取历史SQL操作
通过管道交给mysql命令执行
一mysqlbinlog 日志文件 | mysq| -uroot -p密码
应用示例
使用编号为1的日志文件恢复数据

]# cd /var/lib/mysql/

]# mysqlbinlog mysql-bin.000001 mysql -uroot -p123456

案例4：binlog日志

4.1 问题

启用binlog日志，具体要求如下：

启用binlog日志，把日志文件存放到系统的/mylog目录下，日志文件为db50
手动创建3个新的日志文件
删除编号3之前的日志文件

4.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：启用binlog日志

1）修改配置文件，并重启服务。

[root@dbsvr1 ~]# vim  /etc/my.cnf
[mysqld]
	server_id=1  //指定server_id
log-bin=/mylog/db50  //指定日志目录及名称                           
:wq
[root@dbsvr1 ~]# mkdir  /mylog   //创建目录
[root@dbsvr1 ~]# chown  mysql  /mylog   //修改所有者
[root@dbsvr1 ~]# systemctl  restart mysqld.service  //重启服务

2）查看日志信息

[root@dbsvr1 ~]# 
[root@localhost ~]# mysql -uroot -p123qqq...A //管理员登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status; //查看日志信息
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000001 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

3）手动创建3个新的日志文件

mysql>
mysql> flush logs;  //刷新日志
Query OK, 0 rows affected (0.14 sec)

mysql> flush logs; //刷新日志
Query OK, 0 rows affected (0.11 sec)

mysql> flush logs; //刷新日志
Query OK, 0 rows affected (0.12 sec)

mysql> system ls /mylog/  //查看日志文件
db50.000001  db50.000002  db50.000003  db50.000004  db50.index
mysql> 
mysql> show master status; //查看日志信息
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000004 |      154 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

4）删除编号3之前的日志文件

 mysql>

mysql> purge  master  logs  to  "db50.000003"; //删除日志
Query OK, 0 rows affected (0.05 sec)

mysql> system ls /mylog/    //查看日志文件
db50.000003  db50.000004  db50.index
mysql> 
mysql> system cat /mylog/db50.index //查看索引文件
/mylog/db50.000003
/mylog/db50.000004
mysql>

使用binlog日志恢复数据

5.1 问题

利用binlog恢复库表，要求如下：

启用binlog日志
创建db1库tb1表，插入3条记录
删除tb1表中刚插入的3条记录
使用mysqlbinlog恢复删除的3条记录

5.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：启用binlog日志

1）调整/etc/my.cnf配置，并重启服务

[root@dbsvr1 ~]# vim  /etc/my.cnf
[mysqld]
server_id=1  //定义server_id
log-bin=mysql-bin  //定义日志名
binlog_format=”mixed”     //定义日志格式                           
[root@dbsvr1 ~]# systemctl  restart mysqld.service //重启服务

2）确认binlog日志文件

新启用binlog后，每次启动MySQl服务都会新生成一份日志文件：

[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index

其中mysql-bin.index文件记录了当前保持的二进制文件列表：

[root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
./mysql-bin.000001

重启MySQL服务程序，或者执行SQL操作“FLUSH LOGS;”，会生成一份新的日志：

[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index
/var/lib/mysql/mysql-bin.000002

[root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002

步骤二：利用binlog日志重做数据库操作

1）执行数据库表添加操作

创建db1·库tb1表，表结构自定义：

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.05 sec)

mysql> USE db1;
Database changed
mysql> CREATE TABLE tb1(
    -> id int(4) NOT NULL,name varchar(24)
    -> );
Query OK, 0 rows affected (0.28 sec)

插入3条表记录：

mysql> INSERT INTO tb1 VALUES
    -> (1,'Jack'),
    -> (2,'Kenthy'),
    -> (3,'Bob');
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

确认插入的表记录数据：

mysql> SELECT * FROM tb1;
+----+--------+
| id | name   |
+----+--------+
|  1 | Jack   |
|  2 | Kenthy |
|  3 | Bob    |
+----+--------+
3 rows in set (0.00 sec)

2）删除前一步添加的3条表记录

执行删除所有表记录操作：

mysql> DELETE FROM tb1;
Query OK, 3 rows affected (0.09  sec)

确认删除结果：

mysql> SELECT * FROM tb1;
Empty set (0.00 sec)

步骤三：通过binlog日志恢复表记录

binlog会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一部分数据操作，但对于启用binlog之前已经存在的库、表数据将不适用。

根据上述“恢复被删除的3条表记录”的需求，应通过mysqlbinlog工具查看相关日志文件，找到删除这些表记录的时间点，只要恢复此前的SQL操作（主要是插入那3条记录的操作）即可。

1）查看mysql-bin.000002日志内容

[root@dbsvr1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170412 12:05:32 server id 1  end_log_pos 123 CRC32 0x6d8c069c  Start: binlog v 4, server v 5.7.17-log created 170412 12:05:32 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
jKftWA8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACMp+1YEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AZwGjG0=
'/*!*/;
# at 123
#170412 12:05:32 server id 1  end_log_pos 154 CRC32 0x17f50164  Previous-GTIDs
# [empty]
# at 154
#170412 12:05:59 server id 1  end_log_pos 219 CRC32 0x4ba5a976  Anonymous_GTID  last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#170412 12:05:59 server id 1  end_log_pos 310 CRC32 0x5b66ae13  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1491969959/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE db1
/*!*/;
# at 310
#170412 12:06:23 server id 1  end_log_pos 375 CRC32 0x2967cc28  Anonymous_GTID  last_committed=1        sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 375
#170412 12:06:23 server id 1  end_log_pos 502 CRC32 0x5de09aae  Query   thread_id=3     exec_time=0     error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1491969983/*!*/;
CREATE TABLE tb1(
id int(4) NOT NULL,name varchar(24)
)
/*!*/;
# at 502
#170412 12:06:55 server id 1  end_log_pos 567 CRC32 0x0b8cd418  Anonymous_GTID  last_committed=2        sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 567
#170412 12:06:55 server id 1  end_log_pos 644 CRC32 0x7e8f2fa0  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1491970015/*!*/;
BEGIN
/*!*/;
# at 644
#170412 12:06:55 server id 1  end_log_pos 772 CRC32 0x4e3f728e  Query   thread_id=3     exec_time=0     error_code=0                            //插入表记录的起始时间点 
SET TIMESTAMP=1491970015/*!*/;
INSERT INTO tb1 VALUES(1,'Jack'),(2,'Kenthy'), (3,'Bob')
/*!*/;
# at 772
#170412 12:06:55 server id 1  end_log_pos 803 CRC32 0x6138b21f  Xid = 10
                                                      //确认事务的时间点 
COMMIT/*!*/;
# at 803
#170412 12:07:24 server id 1  end_log_pos 868 CRC32 0xbef3f472  Anonymous_GTID  last_committed=3        sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 868
#170412 12:07:24 server id 1  end_log_pos 945 CRC32 0x5684e92c  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1491970044/*!*/;
BEGIN
/*!*/;
# at 945
#170412 12:07:24 server id 1  end_log_pos 1032 CRC32 0x4c1c75fc         Query   thread_id=3     exec_time=0     error_code=0            //删除表记录的时间点
SET TIMESTAMP=1491970044/*!*/;
DELETE FROM tb1
/*!*/;
# at 1032
#170412 12:07:24 server id 1  end_log_pos 1063 CRC32 0xccf549b2         Xid = 12
COMMIT/*!*/;
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*/;

2）执行指定Pos节点范围内的sql命令恢复数据

根据上述日志分析，只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出，结合管道交给msyql命令执行导入重做：

[root@dbsvr1 ~]# mysqlbinlog \
    --start-datetime="2017-04-12 12:06:55" \ 
    --stop-datetime="2017-04-12 12:07:23" \
    /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:  								   //验证口令

3）确认恢复结果

mysql> SELECT * FROM db1.tb1;
+----+--------+
| id | name   |
+----+--------+
|  1 | Jack   |
|  2 | Kenthy |
|  3 | Bob    |
+----+--------+
3 rows in set (0.00 sec)

4 简述binlog日志格式。

参考答案

statement 、  row  、  mixed

posted @ 2022-02-23 21:37 东山有耳阅读(378) 评论(0) 收藏举报

刷新页面返回顶部

东山有耳

binlog日志

binlog日志

日志概述

什么是binlog日志

启用日志

启用日志

.binlog相关文件

......

清理日志

恢复数据

分析日志

查看日志当前记录格式

修改日志记录格式

查看日志内容

恢复数据

案例4：binlog日志

4.1 问题

4.2 步骤

使用binlog日志恢复数据

5.1 问题

5.2 步骤

4 简述binlog日志格式。

公告