IT源码网

binlog

flyfish 2021年06月17日 数据库 340 0

binlog是一个二进制格式的文件,用于记录用户对数据库更新的SQL语句信息,但对库表等内容的查询不会记录

由于是二进制文件,需使用mysqlbinlog解析查看

主要作用:用于数据库的主从复制及数据的增量恢复

(1)binlog有三种模式:

  ROW(行模式):

      记录哪条数据修改了

      记录的是修改的那条记录的全部数据,即使只更新了一个字段,binlog里也会记录所有字段的数据

  Statement(语句模式): 

     每一条会修改数据的sql都会记录在binlog中

  Mixed(混合模式):

    上面两种混合使用

    一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式

(2)配置

  A.查看日志开启状态

show variables like 'log_%';  

+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | D:\log\mysql\mysql_log_err.txt |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
+---------------------------------+--------------------------------+

默认是关闭的

  B.开启

  修改mysql.ini(Windows上)

#设置日志格式 
binlog_format = mixed 
#设置日志路径,注意路经需要mysql用户有权限写 
log-bin = D:/log/mysql/mysql-bin.log 
#设置binlog清理时间 
expire_logs_days = 7 
#binlog每个日志文件大小 
max_binlog_size = 100m 
#binlog缓存大小 
binlog_cache_size = 4m 
#最大binlog缓存大小 
max_binlog_cache_size = 512m

重启mysql

会在指定目录下产生mysql-bin.000001和mysql-bin.index文件

查看mysql的binlog是否开启

show variables like 'log_bin';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+

查看mysql的binlog模式

show variables like 'binlog_format';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

获取binlog文件日志列表

show binary logs;

+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1225 |
| mysql-bin.000002 | 107 |
+------------------+-----------+

查看第一个binlog文件内容

show binlog events;

+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.53-log, Binlog ver: 4 |
| mysql-bin.000001 | 107 | Query | 1 | 176 | BEGIN |
| mysql-bin.000001 | 176 | Intvar | 1 | 204 | INSERT_ID=19 |
| mysql-bin.000001 | 204 | Query | 1 | 322 | use `ppm_c`; INSERT INTO `hospital_user` (`account`) VALUES ('123') |
……

查看指定binlog文件内容

show binlog events in 'mysql-bin.000002'; 

+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.53-log, Binlog ver: 4 |
| mysql-bin.000002 | 107 | Query | 1 | 176 | BEGIN |
| mysql-bin.000002 | 176 | Query | 1 | 306 | use `ppm_c`; UPDATE `hospital_user` SET `nickname`='测试31' WHERE (`id`='18') |
| mysql-bin.000002 | 306 | Xid | 1 | 333 | COMMIT /* xid=12 */ |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------+

(3)mysqlbinlog使用

  mysqlbinlog.exe 在mysql安装目录下bin中

mysqlbinlog >D:\log\mysql\test.txt

在test.txt中可以看到mysqlbinlog使用格式

-d ,--database=name  根据指定库拆分binlog 
 
-r ,--result-file=name  指定解析binlog输出SQL语句的文件 
 
--start-position=N  读取binlog的起始位置点,N是具体的位置点 
 
--stop-position=N   读取binlog的停止位置点,N是具体的位置点 
 
--start-datetime=X  读取binlog的起始位置点,X是具体的时间 
 
--stop-datetime=X   读取binlog的停止位置点,X是具体的时间

指定数据库截取binlog内容

mysqlbinlog -d ppm_c mysql-bin.000001 -r test.log
-d 指定解析ppm_c数据库 
-r 指定生成的文件

按照位置截取binlog内容

mysqlbinlog mysql-bin.000001 --start-position=639 --stop-position=755  -r test1.log

优点是精确,但是要花费时间选择位置

按时间位置截取binlog内容

mysqlbinlog mysql-bin.000001 --start-datetime="2019-08-08 00:25:00" --stop-datetime="2019-08-08 00:43:00"  -r test2.log

那输出的文件是什么样的呢?

eg:

  mysqlbinlog mysql-bin.000001 --start-datetime="2019-08-08 00:25:00" --stop-datetime="2019-08-08 00:25:56"  -r test2.log

test2.log的内容为

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 
/*!40019 SET @@session.max_insert_delayed_threads=0*/; 
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 
DELIMITER /*!*/; 
# at 4 
#190807 23:53:43 server id 1  end_log_pos 107     Start: binlog v 4, server v 5.5.53-log created 190807 23:53:43 at startup 
# Warning: this binlog is either in use or was not closed properly. 
ROLLBACK/*!*/; 
BINLOG ' 
B/RKXQ8BAAAAZwAAAGsAAAABAAQANS41LjUzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
AAAAAAAAAAAAAAAAAAAH9EpdEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== 
'/*!*/; 
# at 570 
#190808  0:25:55 server id 1  end_log_pos 639     Query    thread_id=3    exec_time=0    error_code=0 
SET TIMESTAMP=1565195155/*!*/; 
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=1342177280/*!*/; 
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=33/*!*/; 
SET @@session.lc_time_names=0/*!*/; 
SET @@session.collation_database=DEFAULT/*!*/; 
BEGIN 
/*!*/; 
# at 639 
#190808  0:25:55 server id 1  end_log_pos 748     Query    thread_id=3    exec_time=0    error_code=0 
use `ppm_c`/*!*/; 
SET TIMESTAMP=1565195155/*!*/; 
DELETE FROM `hospital_user` WHERE (`id`='19') 
/*!*/; 
# at 748 
#190808  0:25:55 server id 1  end_log_pos 775     Xid = 30 
COMMIT/*!*/; 
DELIMITER ; 
# End of log file 
ROLLBACK /* added by mysqlbinlog */; 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

注:

  mysql每次启动都会重新生成一个类似mysql-bin.000002的文件,数字依次增1

  phpstudy安装的MySQL里没有找到,可以去官网重新下载一个完整的mysql


评论关闭
IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

分布式系统中的缓存维护