Description:
Hi, I have question while using mysql replication.
First. As far as i know is
binlog format statement is transfer to Slave in likely Query form binlog file.
binlog format row is transfer to Slave in row form binlog file.
And Mixed is mixed form binlog file.
Now is my question.
When i using stmt format. And i change to mixed format.
but my binlog file was still stmt form(query)
STMT BINLOG FILE:
# at 511
#171030 11:19:51 server id 4 end_log_pos 410 CRC32 0x7d32f69f Query thread_id=4 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1509329991/*!*/;
insert into type2 values(1, 'NEWDATA')
/*!*/;
# at 623
#171030 11:19:51 server id 4 end_log_pos 441 CRC32 0xfe95e074 Xid = 20
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
MIXED BINLOG FILE:
# at 555
#171030 11:22:41 server id 4 end_log_pos 398 CRC32 0x9f52b726 Write_rows: table id 219 flags: STMT_END_F
BINLOG '
8Yz2WRMEAAAAMwAAAFYBAAAAANsAAAAAAAEABHRlc3QABXR5cGUyAAIDDwL+AQMMG0Tm
8Yz2WR4EAAAAOAAAAI4BAAAAANsAAAAAAAEAAgAC//wBAAAADgBENUEwMEJDQzQ2ODg1RCa3Up8=
'/*!*/;
### INSERT INTO `test`.`type2`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='D5A00BCC46885D' /* VARSTRING(510) meta=510 nullable=1 is_null=0 */
# at 611
#171030 11:22:41 server id 4 end_log_pos 429 CRC32 0x0999ad34 Xid = 17
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*/;
As you can see, Mixed is follow the previous one.
How to repeat:
mysql 5.7.18 server1: Master
mysql 5.7.18 server2: Slave
Master setting:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=4
#binlog_format=statement
#binlog_format=mixed
binlog_format=statement
log-bin = mysql-bin
max_binlog_size = 100M
expire_logs_days = 7
max_allowed_packet = 1G
interactive_timeout = 600
wait_timeout = 600
net_read_timeout=600
net_write_timeout=600
connect_timeout=600
binlog_do_db = test
binlog_do_db = mysql
validate_password_policy=LOW
Slave setting:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=3
#binlog_format=mixed
binlog_format=statement
log-bin = mysql-bin
max_binlog_size = 100M
expire_logs_days = 7
And synchronization Master/Slave.
create test table and execute dml.
Check mysql binlog file.
Change binlog format: Mixed.
execute dml.
Check mysql binlog file.
can confirm that AAA is maintained: stmt form
