MySQL日志之二进制日志(binlog)

日志格式

基于段的格式 binglog_format = STATEMENT

   优点 : 日志记录量相对较小,节约磁盘及网络I/O

   缺点 : 必须要记录上下文信息,保证语句在从服务器上执行结果和主服务器上相同.特定函数如UUID(),user()这样非确定性函数还是无法复制,可能造成复制的主从数据不一致.

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

mysql> set session binlog_format=statement;

Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'binlog_format';

+---------------+-----------+

| Variable_name | Value     |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.00 sec)


mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |     27338 |

| mysql-bin.000002 |   1034478 |

| mysql-bin.000003 |     26239 |

| mysql-bin.000004 |   1034478 |

| mysql-bin.000005 |      5650 |

| mysql-bin.000006 |       568 |

| mysql-bin.000007 |       497 |

| mysql-bin.000008 |  18456997 |

| mysql-bin.000009 |   5159640 |

| mysql-bin.000010 | 107607071 |

| mysql-bin.000011 |       107 |

| mysql-bin.000012 |       107 |

| mysql-bin.000013 |       150 |

| mysql-bin.000014 |       452 |

| mysql-bin.000015 |       107 |

+------------------+-----------+

15 rows in set (0.00 sec)


mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)


mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |     27338 |

| mysql-bin.000002 |   1034478 |

| mysql-bin.000003 |     26239 |

| mysql-bin.000004 |   1034478 |

| mysql-bin.000005 |      5650 |

| mysql-bin.000006 |       568 |

| mysql-bin.000007 |       497 |

| mysql-bin.000008 |  18456997 |

| mysql-bin.000009 |   5159640 |

| mysql-bin.000010 | 107607071 |

| mysql-bin.000011 |       107 |

| mysql-bin.000012 |       107 |

| mysql-bin.000013 |       150 |

| mysql-bin.000014 |       452 |

| mysql-bin.000015 |       150 |

| mysql-bin.000016 |       107 |

+------------------+-----------+

16 rows in set (0.00 sec)


mysql> insert into sakila.country(country) values('yhq');

Query OK, 1 row affected (0.00 sec)

[root@localhost data]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000016
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190927 10:47:39 server id 65  end_log_pos 107     Start: binlog v 4, server v 5.5.23-log created 190927 10:47:39
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
S3iNXQ9BAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#190927 10:53:59 server id 65  end_log_pos 177     Query    thread_id=13    exec_time=0    error_code=0
SET TIMESTAMP=1569552839/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 177
#190927 10:53:59 server id 65  end_log_pos 205     Intvar
SET INSERT_ID=110/*!*/;
# at 205
#190927 10:53:59 server id 65  end_log_pos 318     Query    thread_id=13    exec_time=0    error_code=0
use sakila/*!*/;
SET TIMESTAMP=1569552839/*!*/;
INSERT INTO `country` (`country`) VALUES ('yhq')
/*!*/;
# at 318
#190927 10:53:59 server id 65  end_log_pos 345     Xid = 106
COMMIT/*!*/;
# at 345
#190927 10:57:40 server id 65  end_log_pos 415     Query    thread_id=15    exec_time=0    error_code=0
SET TIMESTAMP=1569553060/*!*/;
BEGIN
/*!*/;
# at 415
#190927 10:57:40 server id 65  end_log_pos 443     Intvar
SET INSERT_ID=111/*!*/;
# at 443
#190927 10:57:40 server id 65  end_log_pos 556     Query    thread_id=15    exec_time=0    error_code=0
SET TIMESTAMP=1569553060/*!*/;
insert into `country` (`country`) values ('yhq')
/*!*/;
# at 556
#190927 10:57:40 server id 65  end_log_pos 583     Xid = 141
COMMIT/*!*/;
# at 583
#190927 10:58:01 server id 65  end_log_pos 653     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1569553081/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
BEGIN
/*!*/;
# at 653
#190927 10:58:01 server id 65  end_log_pos 681     Intvar
SET INSERT_ID=112/*!*/;
# at 681
#190927 10:58:01 server id 65  end_log_pos 795     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1569553081/*!*/;
insert into sakila.country(country) values('yhq')
/*!*/;
# at 795
#190927 10:58:01 server id 65  end_log_pos 822     Xid = 147
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

基于行的日志格式 binlog_format = row

 Row格式可以避免复制中出现主从不一致问题

   优点 : 主从复制更加安全,对每一行数据的修改比基于段的复制高效

   缺点 : 记录日志量较大

mysql> set session binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW   |

+---------------+-------+

1 row in set (0.00 sec)


mysql> flush logs;

Query OK, 0 rows affected (0.03 sec)


mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |     27338 |

| mysql-bin.000002 |   1034478 |

| mysql-bin.000003 |     26239 |

| mysql-bin.000004 |   1034478 |

| mysql-bin.000005 |      5650 |

| mysql-bin.000006 |       568 |

| mysql-bin.000007 |       497 |

| mysql-bin.000008 |  18456997 |

| mysql-bin.000009 |   5159640 |

| mysql-bin.000010 | 107607071 |

| mysql-bin.000011 |       107 |

| mysql-bin.000012 |       107 |

| mysql-bin.000013 |       150 |

| mysql-bin.000014 |       452 |

| mysql-bin.000015 |       150 |

| mysql-bin.000016 |       865 |

| mysql-bin.000017 |       339 |

| mysql-bin.000018 |       107 |

+------------------+-----------+

18 rows in set (0.00 sec)

[root@localhost data]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000018
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190927 14:06:32 server id 65  end_log_pos 107     Start: binlog v 4, server v 5.5.23-log created 190927 14:06:32
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
6KaNXQ9BAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#190927 14:06:54 server id 65  end_log_pos 177     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1569564414/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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 177
# at 229
#190927 14:06:54 server id 65  end_log_pos 229     Table_map: `sakila`.`country` mapped to number 41
#190927 14:06:54 server id 65  end_log_pos 269     Write_rows: table id 41 flags: STMT_END_F

BINLOG '

/qaNXRNBAAAANAAAAOUAAAAAACkAAAAAAAEABnNha2lsYQAHY291bnRyeQADAg8HApYAAA==

/qaNXRdBAAAAKAAAAA0BAAAAACkAAAAAAAEAA//4cgADcWho/qaNXQ==

'/!/;


at 269


190927 14:06:54 server id 65  end_log_pos 296     Xid = 164


COMMIT/!/;

DELIMITER ;


End of log file


ROLLBACK / added by mysqlbinlog /;

/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;

混合日志格式 binlog_format = mixed

 根据语句由系统决定是基于段的日志格式还是基于行的日志格式

 数据量大小由所执行的语句决定

 

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yhq-qhh/p/11218411.html