mysql之binlog日志

binlog日志(binary log)

1.开启mysql的binlog日志

1.1 查看binlog是否开启
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   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.01 sec)

如果没开启

1.2 编辑my.cnf
vim /etc/my.cnf

增加

#节点Id,注意集群中不能重复,单节点不配置也可以
server-id=123
#开启binlog日志,指定其存放位置
log-bin=/var/lib/mysql/mysql-bin
#开启binlog自动过期
expire_logs_days=3
1.3 重启数据库
service mysqld restart

然后

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

发现已开启binlog

/var/lib/mysql/mysql-bin是日志文件

/var/lib/mysql/mysql-bin.index是索引

1.4 查看日志索引和事件位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql-bin是日志文件

000004是索引

pos=154是事件位置也可以叫偏移值

2.binlog的常见命令

2.1 查看所有binlog日志列表
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      7083 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       467 |
| mysql-bin.000005 |      1218 |
+------------------+-----------+
5 rows in set (0.00 sec)
2.2查看master状态

即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.3flush刷新log日志,自此刻开始产生一个新编号的binlog日志文件
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      7083 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       467 |
| mysql-bin.000005 |      1265 |
| mysql-bin.000006 |       154 |
+------------------+-----------+
6 rows in set (0.00 sec)

注意:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

2.4重置(清空)所有binlog日志

mysql> reset master;

3.查看binlog日志的内容

3.1:使用mysqlbinlog自带查看命令法:

注意:

–>binlog是二进制文件,普通文件查看器cat、more、vim等都无法打开,必须使用自带的mysqlbinlog命令查看

–>binlog日志位置在my.cnf中配置 log-bin=/var/lib/mysql/mysql-bin

–>在MySQL5.5以下版本使用mysqlbinlog命令时如果报错,就加上 “–no-defaults”选项

mysqlbinlog mysql-bin.000004

mysqlbinlog常见的选项有以下几个:

--start-datetime:从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间

--stop-datetime:从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样

--start-position:从二进制日志中读取指定position 事件位置作为开始。

--stop-position:从二进制日志中读取指定position 事件位置作为事件截至

-vvv 显示sql

-d 指定数据库

mysql二进制日志有三种格式:statement、row、mixed
一般我们在主从复制过程中采用的格式都是mixed。这种格式兼具了基于行(row)和基于语句(statement)的优点,速度和效率是最高的。
但是在使用mysqlbinlog查看混合模式的二进制文件时,基于行的部分默认是以base64编码显示的,那么如何对其进行解码呢,方法如下:

--base64-output=decode-rows 

例如

[root@iZ2ze316xjjg0mvije7695Z mysql]# mysqlbinlog mysql-bin.000005  -vvv  --base64-output=decode-rows -v  --start-position 389  --stop-position  485
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 389
#220307 10:53:29 server id 1  end_log_pos 420 CRC32 0x63638b44 	Xid = 5568
COMMIT/*!*/;
# at 420
#220307 10:50:21 server id 1  end_log_pos 485 CRC32 0x19318c44 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
BEGIN /*added by mysqlbinlog */ /*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;
[root@iZ2ze316xjjg0mvije7695Z mysql]# mysqlbinlog mysql-bin.000005 --start-position 389  --stop-position 485
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220307 10:52:58 server id 1  end_log_pos 123 CRC32 0xed4fb7b6 	Start: binlog v 4, server v 5.7.37-log created 220307 10:52:58
BINLOG '
inMlYg8BAAAAdwAAAHsAAAAAAAQANS43LjM3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Aba3T+0=
'/*!*/;
# at 389
#220307 10:53:29 server id 1  end_log_pos 420 CRC32 0x63638b44 	Xid = 5568
COMMIT/*!*/;
# at 420
#220307 10:50:21 server id 1  end_log_pos 485 CRC32 0x19318c44 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
BEGIN /*added by mysqlbinlog */ /*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;
[root@iZ2ze316xjjg0mvije7695Z mysql]# mysqlbinlog mysql-bin.000005  -vvv  --base64-output=decode-rows  --start-position 389  --stop-position  485
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 389
#220307 10:53:29 server id 1  end_log_pos 420 CRC32 0x63638b44 	Xid = 5568
COMMIT/*!*/;
# at 420
#220307 10:50:21 server id 1  end_log_pos 485 CRC32 0x19318c44 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
BEGIN /*added by mysqlbinlog */ /*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;

server id 1 :服务器id

end_log_pos 123 :当前事务结束时的pos

3.2 mysqlbinlog读取出binlog日志的全文内容比较多,不容易分辨查看到pos点信息

下面介绍一种更为方便的查询命令:

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数解释:

IN ‘log_name’ :指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,] :偏移量(不指定就是0)

row_count :查询总条数(不指定就是所有行)

例1:查询mysql-bin.000001日志
show binlog events
例2:查询mysql-bin.000002日志

\G:可读性好点

show binlog events in 'mysql-bin.000002'\G;
例3:查询mysql-bin.000002日志,从154查起
show binlog events in 'mysql-bin.000002' from 154\G;
例4:查询mysql-bin.000002日志,从154查起,并且 跳过前两个,和sql的limit一样
show binlog events in 'mysql-bin.000002' from 154 limit 2,10\G;
  • 1
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值