mysql 5.6新機能slave_parallel_workersを設定する際の注意
mysql 5.6新機能slave_parallel_workersを設定する際の注意
Multi-threaded Slavesを使ってみようと設定したときにはまったのでそこを記載。
slaveのmy.cnfに下記を追加
relay_log_info_repository = TABLE master_info_repository = TABLE slave_checkpoint_group = 524281 slave_parallel_workers = 4
slaveを再起動するとエラーログに下記のエラーが出る
120331 12:18:52 [ERROR] Failed to open the relay log './inst2-5-4-6_1-relay-bin.000050' (relay_log_pos 275). 120331 12:18:52 [ERROR] Could not find target log during relay log initialization. 120331 12:18:52 [ERROR] Failed to initialize the master info structure
slave status
Slave_SQL_RunningがNoになりエラーメッセージでFailed during slave workers initializationが。
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: replicator Master_Port: 3307 Connect_Retry: 60 Master_Log_File: inst1-5-6-4_1-bin.000005 Read_Master_Log_Pos: 610703806 Relay_Log_File: inst2-5-4-6_1-relay-bin.000026 Relay_Log_Pos: 275 Relay_Master_Log_File: inst1-5-6-4_1-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: db1,db2,db3,db4 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1593 Last_Error: Failed during slave workers initialization Skip_Counter: 0 Exec_Master_Log_Pos: 610703806 Relay_Log_Space: 1110 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1593 Last_SQL_Error: Failed during slave workers initialization Replicate_Ignore_Server_Ids: Master_Server_Id: 3307 Master_UUID: f1ef071a-7630-11e1-bae5-00226862863d Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 120325 22:53:45 Master_SSL_Crl: Master_SSL_Crlpath: 1 row in set (0.00 sec)
relay-log.infoの削除
[Bug #64026]
[bzr push into mysql-trunk branch]
色々エラーメッセージを便りに調べてみたけどイマイチわからない。
Bug #64026の内容からもしかしてrelay_log_info_repository = TABLEにしたら
relay-log.infoっていらない?と思いslaveを停止(service mysql stop)し下のrelay-log.infoを削除(rm relay-log.info.*)
-rw-rw---- 1 mysql mysql 81 3月 25 22:56 relay-log.info.0 -rw-rw---- 1 mysql mysql 81 3月 25 22:56 relay-log.info.1 -rw-rw---- 1 mysql mysql 225 3月 31 10:41 relay-log.info.2 -rw-rw---- 1 mysql mysql 227 3月 31 10:43 relay-log.info.3
* slaveを起動
無事エラーなくslave_parallel_workers用のプロセスも無事起動された。
注:Checkpoint_group_bitmapだけ削除してます。
mysql> select * from mysql.slave_worker_info\G *************************** 1. row *************************** Master_id: 3308 Worker_id: 0 Relay_log_name: ./inst2-5-6-4_1-relay-bin.000016 Relay_log_pos: 180094449 Master_log_name: inst1-5-6-4_1-bin.000006 Master_log_pos: 681610195 Checkpoint_relay_log_name: ./inst2-5-6-4_1-relay-bin.000016 Checkpoint_relay_log_pos: 180094138 Checkpoint_master_log_name: inst1-5-6-4_1-bin.000006 Checkpoint_master_log_pos: 681609884 Checkpoint_seqno: 0 Checkpoint_group_size: 65535 *************************** 2. row *************************** Master_id: 3308 Worker_id: 1 Relay_log_name: ./inst2-5-6-4_1-relay-bin.000014 Relay_log_pos: 334663893 Master_log_name: inst1-5-6-4_1-bin.000006 Master_log_pos: 501515938 Checkpoint_relay_log_name: ./inst2-5-6-4_1-relay-bin.000014 Checkpoint_relay_log_pos: 334568336 Checkpoint_master_log_name: inst1-5-6-4_1-bin.000006 Checkpoint_master_log_pos: 501420381 Checkpoint_seqno: 510 Checkpoint_group_size: 65535 *************************** 3. row *************************** Master_id: 3308 Worker_id: 2 Relay_log_name: ./inst2-5-6-4_1-relay-bin.000016 Relay_log_pos: 180094138 Master_log_name: inst1-5-6-4_1-bin.000006 Master_log_pos: 681609884 Checkpoint_relay_log_name: ./inst2-5-6-4_1-relay-bin.000016 Checkpoint_relay_log_pos: 178505386 Checkpoint_master_log_name: inst1-5-6-4_1-bin.000006 Checkpoint_master_log_pos: 680021132 Checkpoint_seqno: 8495 Checkpoint_group_size: 65535 *************************** 4. row *************************** Master_id: 3308 Worker_id: 3 Relay_log_name: ./inst2-5-6-4_1-relay-bin.000016 Relay_log_pos: 195502175 Master_log_name: inst1-5-6-4_1-bin.000006 Master_log_pos: 697017921 Checkpoint_relay_log_name: ./inst2-5-6-4_1-relay-bin.000016 Checkpoint_relay_log_pos: 195501801 Checkpoint_master_log_name: inst1-5-6-4_1-bin.000006 Checkpoint_master_log_pos: 697017547 Checkpoint_seqno: 1 Checkpoint_group_size: 65535 4 rows in set (0.00 sec
slave status
こちらもSlave_SQL_RunningがYESとなりエラーなく稼働
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: replicator Master_Port: 3307 Connect_Retry: 60 Master_Log_File: inst1-5-6-4_1-bin.000006 Read_Master_Log_Pos: 884314089 Relay_Log_File: inst2-5-6-4_1-relay-bin.000016 Relay_Log_Pos: 382796286 Relay_Master_Log_File: inst1-5-6-4_1-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: db1,db2,db3,db4 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 884312032 Relay_Log_Space: 382798514 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3307 Master_UUID: f1ef071a-7630-11e1-bae5-00226862863d Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: 1 row in set (0.00 sec)