Monday, 25 February 2013

Replication checksums in MySQL 5.6

Replication checksums is one of the listed new features of MySQL 5.6 , but data integrity might break as documented here replication-checksum. In reality this feature is not completely new to MySQL, in MySQL 5.5, and earlier replication checksums can be used by using SSL replication as a workaround. You can learn more about SSL replication here. Similarly you can see my blog post about SSL connections with MySQL here.
Please note, by default replication checksums are enabled. However following variables can be used to enable/disable checksums with master/relay logs:

a) Binlog_checksum - Its a dynamic variable. The default value is CRC32.  This variable causes the master to write a checksum for each event in the binary log

When enabled binary log provides the following information:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
#130225 15:33:56 server id 1  end_log_pos 384 CRC32 0x280a5e52  Intvar
SET INSERT_ID=1/*!*/;
#130225 15:33:56 server id 1  end_log_pos 499 CRC32 0x37e25d0a  Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1361806436/*!*/;
insert into t1 values (null, 'mysql 5.6')
/*!*/;
# at 499
#130225 15:33:56 server id 1  end_log_pos 530 CRC32 0xf56d77c4  Xid = 17
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
The extra/new information that we see the above output such as
CRC32 0x280a5e52 

  
On the slave host:
b) slave_sql_verify_checksum: Its ENABLED by default. However, as per online docs (here),  this option is disabled by default, so I take it as documentation bug. When this option is ENABLED, slave host would examine and verify checksums when reading the relay log.
show global variables like 'slave_sql_verify_checksum';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| slave_sql_verify_checksum | ON    |
+---------------------------+-------+
1 row in set (0.00 sec)

1 comment:

  1. Thanks for posting this blog. This was very interesting blog, I felt comfortable while reading this post, thank you.

    MSBI Training in Dallas

    MSBI Training in Dallas

    ReplyDelete