Mysql : Master Slave, désynchronisation du slave
Par pepin le jeudi, septembre 20 2018, 09:41 - BDD (Sql, NOSql) - Lien permanent
Identification
Chercher dans les logs la cause de l'arrêt de la réplication :
grep mysql /var/log/syslog
server1:/home/admin# grep mysql /var/log/syslog May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate May 29 09:56:08 http2 mysqld[1380]: ^ISET thread.views = thread.views + aggregate.views May 29 09:56:08 http2 mysqld[1380]: ^IWHERE thread.threadid = aggregate.threadid', Error_code: 1146 May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001079' position 203015142 server1:/home/admin#
On voir ici à quelle position la réplication à stoppé.
On peut aussi vérifier cela directement avec mysql
mysql -u root -p
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event Master_Host: 1.2.3.4 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001079 Read_Master_Log_Pos: 269214454 Relay_Log_File: slave-relay.000130 Relay_Log_Pos: 100125935 Relay_Master_Log_File: mysql-bin.001079 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: mydb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146 Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'.
Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
SET thread.views = thread.views + aggregate.views WHERE thread.threadid = aggregate.threadid' Skip_Counter: 0 Exec_Master_Log_Pos: 203015142 Relay_Log_Space: 166325247 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
1 row in set (0.00 sec)
on voir ici qu'une requête n'est pas passé. Pour réparere la réplication, nous allons forcer le saut de cette requête.
Réparation
Arrêt du slave
mysql> STOP SLAVE;
Indiquer le nombre de reqûetes dont on va ignorer la réplication (ici 1, mais on pourrait en ignorer 2, 3, ... 100)
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Relancer le slave :
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G
mysql> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 1.2.3.4 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001079 Read_Master_Log_Pos: 447560366 Relay_Log_File: slave-relay.000130 Relay_Log_Pos: 225644062 Relay_Master_Log_File: mysql-bin.001079 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: mydb 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: 447560366 Relay_Log_Space: 225644062 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 1 row in set (0.00 sec)
Nous constatons que Slave_IO_Running and Slave_SQL_Running sont bien a YES maintenant.