mysql二进制日志

最新推荐文章于 2024-07-24 16:11:19 发布

施小赞

最新推荐文章于 2024-07-24 16:11:19 发布

阅读量177

点赞数 1

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/dyangel2013/article/details/106602336

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1、查看是否启用了二进制日志

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2、修改 mysqld.cnf

vim mysqld.cnf

添加以下内容

log_bin=mysql_bin
binlog-format=Row
server-id=1

binlog_format 指定二进制日志的类型
分别有STATEMENT、ROW、MIXED三种值。
MySQL 5.7.6之前默认为STATEMENT模式。
MySQL 5.7.7之后默认为ROW模式。
这个参数主要影响主从复制。
复制的模式有下面几种:基于SQL语句的复制(statement-based replication, SBR)，
基于行的复制(row-based replication, RBR)，
混合模式复制(mixed-based replication, MBR)。
再次查看是否启用二进制日志，可以发现

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (23.24 sec)

3、查看所有二进制日志文件的列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql_bin.000001 |       154 |
| mysql_bin.000002 |       154 |
+------------------+-----------+
2 rows in set (27.77 sec)

4、查看当前二进制日志文件的名称

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (25.13 sec)

5、 Linux系统二进制日志存放在 /var/lib/mysql

查看二进制日志

mysqlbinlog --base64-output='decode-rows' -v mysql_bin.000002

查看指定binlog文件的内容语法：

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

演示查看一个二进制文件中全部事务注：binlog文件实际上是由一连串的event组成的一个组，即事务组。一个row代表一个事务组

mysql> show binlog events in 'mysql_bin.000002'\G;
*************************** 1. row ***************************
  Log_name: mysql_bin.000002
       Pos: 4
Event_type: Format_desc
 Server_id: 1
End_log_pos: 123
      Info: Server ver: 5.7.23-log, Binlog ver: 4
*************************** 2. row ***************************
  Log_name: mysql_bin.000002
       Pos: 123
Event_type: Previous_gtids
 Server_id: 1
End_log_pos: 154
      Info: 
*************************** 3. row ***************************
  Log_name: mysql_bin.000002
       Pos: 154
Event_type: Anonymous_Gtid
 Server_id: 1
End_log_pos: 219
      Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

演示对一个二进制文件的事务做过滤查询

mysql> show binlog events in 'mysql_bin.000002' from 436 limit 2\G;
*************************** 1. row ***************************
   Log_name: mysql_bin.000002
        Pos: 436
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 501
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 2. row ***************************
   Log_name: mysql_bin.000002
        Pos: 501
 Event_type: Query
  Server_id: 1
End_log_pos: 579
       Info: BEGIN
2 rows in set (0.00 sec)

因为我的二进制日志 binlog-format=Row 所以在查询时加上了 --base64-output=decode-rows -v
①提取指定的binlog日志

root@9822395d4388:/var/lib/mysql# mysqlbinlog --base64-output=decode-rows -v mysql_bin.000002|grep INSERT
### INSERT INTO `tb_lock_db`.`account`
### INSERT INTO `tb_lock_db`.`account`

②提取指定position位置的binlog日志

root@9822395d4388:/var/lib/mysql# mysqlbinlog --base64-output=decode-rows -v --start-position="436" --stop-position="718" mysql_bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 436
#181229 14:57:20 server id 1  end_log_pos 501 CRC32 0x9448091a  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 501
#181229 14:57:20 server id 1  end_log_pos 579 CRC32 0x32540d15  Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1546095440/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
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 utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 579
#181229 14:57:20 server id 1  end_log_pos 638 CRC32 0xd0aad355  Table_map: `tb_lock_db`.`account` mapped to number 108
# at 638
#181229 14:57:20 server id 1  end_log_pos 687 CRC32 0xbf8dbd69  Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `tb_lock_db`.`account`
### SET
###   @1=6
###   @2=0.00000000
# at 687
#181229 14:57:20 server id 1  end_log_pos 718 CRC32 0x29d8700e  Xid = 52
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*/;

③查看指定时间的binlog日志

root@9822395d4388:/var/lib/mysql# mysqlbinlog --base64-output=decode-rows -v --start-datetime="2018-12-29 14:56:16" --stop-datetime="2018-12-29 14:59:16" mysql_bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181229 14:29:12 server id 1  end_log_pos 123 CRC32 0x3f86e4d2  Start: binlog v 4, server v 5.7.23-log created 181229 14:29:12 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 436
#181229 14:57:20 server id 1  end_log_pos 501 CRC32 0x9448091a  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 501
#181229 14:57:20 server id 1  end_log_pos 579 CRC32 0x32540d15  Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1546095440/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
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 utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 579
#181229 14:57:20 server id 1  end_log_pos 638 CRC32 0xd0aad355  Table_map: `tb_lock_db`.`account` mapped to number 108
# at 638
#181229 14:57:20 server id 1  end_log_pos 687 CRC32 0xbf8dbd69  Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `tb_lock_db`.`account`
### SET
###   @1=6
###   @2=0.00000000
# at 687
#181229 14:57:20 server id 1  end_log_pos 718 CRC32 0x29d8700e  Xid = 52
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*/;

④导出指定时间的binlog日志

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2018-12-29 14:56:16" --stop-datetime="2018-12-29 14:59:16" mysql_bin.000002 >mysqlbinlogsq

施小赞

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql二进制日志

1、查看是否启用了二进制日志mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | OFF |+---------------+-------+1 row in set (0.00 sec)2、修改 mysqld.cnfvim mysqld.cnf 添加以下内容
复制链接

扫一扫

专栏目录