Sunday, July 20, 2014

Mysql Replication Crash Safe Slaves Replication Feature introduced in 5.6

What to fix:

2014-07-21 06:29:58 3850 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

Current Settings

root@server1> select table_name,engine from information_schema.tables
    -> where table_name in ('slave_relay_log_info','slave_master_info');
+----------------------+--------+
| table_name           | engine |
+----------------------+--------+
| slave_master_info    | InnoDB |
| slave_relay_log_info | InnoDB |
+----------------------+--------+
2 rows in set (0.00 sec)

root@server1> show variables like '%info_repository';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository    | FILE  |
| relay_log_info_repository | FILE  |
+---------------------------+-------+
2 rows in set (0.00 sec)

Add 2 lines to server 1/2 my.cnf


master_info_repository=TABLE
relay_log_info_repository=TABLE

Restart mysqld
Verify the Setting

root@server1> show variables like '%info_repository';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository    | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.00 sec)

root@server1> select * from mysql.slave_master_info \G
*************************** 1. row ***************************
       Number_of_lines: 23
       Master_log_name: bin-log.000008
        Master_log_pos: 120
                  Host: 127.0.0.1
             User_name: repl_user
         User_password: repl_pass
                  Port: 3307
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca:
            Ssl_capath:
              Ssl_cert:
            Ssl_cipher:
               Ssl_key:
Ssl_verify_server_cert: 0
             Heartbeat: 1800
                  Bind:
    Ignored_server_ids: 0
                  Uuid: 37f99013-0fa4-11e4-ae2f-e13c88f43118
           Retry_count: 86400
               Ssl_crl:
           Ssl_crlpath:
Enabled_auto_position: 0
1 row in set (0.00 sec)

root@server1> select * from mysql.slave_relay_log_info \G
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: /mysql56/mysql-server1/slave/relay-bin.000021
    Relay_log_pos: 281
  Master_log_name: bin-log.000008
   Master_log_pos: 120
        Sql_delay: 0
Number_of_workers: 0
               Id: 1
1 row in set (0.00 sec)