MySQL 5.6.10之后完善了GTID,5.6.2开始支持GTID复制
这个参数是5.1.44 才引入的,从命名可理解为--非事务直接写binlog
对于非事务表(如:MyISAM),即使发起了事务begin,而结果不管提交commit,还是回滚rollback;对中间执行了的非事务表的写操作都会记录binlog。
我们开启他,这样不管任何情况对非事务表的操作都将记录binlog,规避主从数据不一致的操作。
主库上分析日志:
/usr/local/mysql/bin/mysqlbinlog -vv /data/mysql/mysql3306/logs/mysql-bin.000014 --start-position=194 --base64-output=DECODE-ROWS
5.7支持GTID在线切换,5.6不支持该功能
root@localhost [tcyang]>create table tb_slow as select * from information_schema.columns;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
root@localhost [tcyang]>
root@localhost [(none)]>show global variables like "%direct%";
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_direct_non_transactional_updates | OFF | row格式不用管该参数
| innodb_undo_directory | ./ |
+-----------------------------------------+-------+
2 rows in set (0.01 sec)
这个参数是5.1.44 才引入的,从命名可理解为--非事务直接写binlog
对于非事务表(如:MyISAM),即使发起了事务begin,而结果不管提交commit,还是回滚rollback;对中间执行了的非事务表的写操作都会记录binlog。
我们开启他,这样不管任何情况对非事务表的操作都将记录binlog,规避主从数据不一致的操作。
root@localhost [(none)]>show global variables like "%binlog%";
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF | *******5.6开始有该功能
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 268435456 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+
20 rows in set (0.00 sec)
root@localhost [(none)]>
set global binlog_rows_query_log_events=on; 可以在binlog_format=row中记录执行的SQL语句
root@localhost [(none)]>show binlog events in 'mysql-bin.000014' from 194;
+------------------+-----+------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+----------------------------------------------------------------------+
| mysql-bin.000014 | 194 | Gtid | 1003306 | 259 | SET @@SESSION.GTID_NEXT= '8170836d-8e48-11e7-ac68-000c29b48f84:1852' |
| mysql-bin.000014 | 259 | Query | 1003306 | 327 | BEGIN |
| mysql-bin.000014 | 327 | Table_map | 1003306 | 379 | table_id: 219 (tcyang.test) |
| mysql-bin.000014 | 379 | Write_rows | 1003306 | 419 | table_id: 219 flags: STMT_END_F |
| mysql-bin.000014 | 419 | Xid | 1003306 | 450 | COMMIT /* xid=13 */ |
+------------------+-----+------------+-----------+-------------+----------------------------------------------------------------------+
5 rows in set (0.00 sec)
主库上分析日志:
/usr/local/mysql/bin/mysqlbinlog -vv /data/mysql/mysql3306/logs/mysql-bin.000014 --start-position=194 --base64-output=DECODE-ROWS
[root@vm-test1 logs]# /usr/local/mysql/bin/mysqlbinlog -vv /data/mysql/mysql3306/logs/mysql-bin.000014 --start-position=194 --base64-output=DECODE-ROWS
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 194
#170904 11:18:50 server id 1003306 end_log_pos 259 CRC32 0xfe64b525 GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '8170836d-8e48-11e7-ac68-000c29b48f84:1852'/*!*/;
# at 259
#170904 11:18:50 server id 1003306 end_log_pos 327 CRC32 0x69fcbe1f Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1504495130/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
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 latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 327
#170904 11:18:50 server id 1003306 end_log_pos 379 CRC32 0xd89d80ef Table_map: `tcyang`.`test` mapped to number 219
# at 379
#170904 11:18:50 server id 1003306 end_log_pos 419 CRC32 0x33a54841 Write_rows: table id 219 flags: STMT_END_F
### INSERT INTO `tcyang`.`test`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2=NULL /* VARSTRING(150) meta=150 nullable=1 is_null=1 */
# at 419
#170904 11:18:50 server id 1003306 end_log_pos 450 CRC32 0x5506b022 Xid = 13
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*/;
[root@vm-test1 logs]#
root@localhost [(none)]>show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | OFF |
+------------------------------+-------+
1 row in set (0.01 sec)
====================================================================================================
root@localhost [(none)]>show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | ON |
+------------------------------+-------+
/usr/local/mysql/bin/mysqlbinlog -vv /data/mysql/mysql3306/logs/mysql-bin.000015 --start-position=194 --base64-output=DECODE-ROWS
[root@vm-test1 data]# /usr/local/mysql/bin/mysqlbinlog -vv /data/mysql/mysql3306/logs/mysql-bin.000015 --start-position=194 --base64-output=DECODE-ROWS
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 194
#170904 11:31:16 server id 1003306 end_log_pos 259 CRC32 0x9c29ea35 GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '8170836d-8e48-11e7-ac68-000c29b48f84:1853'/*!*/;
# at 259
#170904 11:31:16 server id 1003306 end_log_pos 327 CRC32 0x3eb7618c Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1504495876/*!*/;
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 latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 327
#170904 11:31:16 server id 1003306 end_log_pos 398 CRC32 0x171028e1 Rows_query
# update tcyang.test set name='dddddd' where id=2
# at 398
#170904 11:31:16 server id 1003306 end_log_pos 450 CRC32 0xf19b789f Table_map: `tcyang`.`test` mapped to number 219
# at 450
#170904 11:31:16 server id 1003306 end_log_pos 503 CRC32 0xdeb4d8ec Update_rows: table id 219 flags: STMT_END_F
### UPDATE `tcyang`.`test`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2=NULL /* VARSTRING(150) meta=150 nullable=1 is_null=1 */
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='dddddd' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
# at 503
#170904 11:31:16 server id 1003306 end_log_pos 534 CRC32 0xb0bd0f6e Xid = 8
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*/;
[root@vm-test1 data]#
===============================================GTID===============
root@localhost [(none)]>show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON | ********
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON | ******
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)
| log_bin | ON | ******
| log_bin_basename | /data/mysql/mysql3306/logs/mysql-bin |
| log_bin_index | /data/mysql/mysql3306/logs/mysql-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | ./error.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | ON | *****