Hi, i have this situation:
create a cluster from scratch, added node slave 1 and it's stucked in recovery mode, this is the mysql log:
MASTER iittor05dbxc010
SLAVE1 iittor05dbxc020
SLAVE2 iittor05dbxc030
"clusterName": "certcluster",
"defaultReplicaSet": {
"name": "default",
"primary": "iittor05dbxc010:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 2 members are not active",
"topology": {
"iittor05dbxc010:3306": {
"address": "iittor05dbxc010:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"iittor05dbxc020:3306": {
"address": "iittor05dbxc020:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "RECOVERING"
},
"iittor05dbxc030:3306": {
"address": "iittor05dbxc030:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "RECOVERING"
}
}
}
2017-10-02T12:12:22.488233Z 0 [Note] Plugin group_replication reported: 'Starting group replication recovery with view_id 15069407899844788:12'
2017-10-02T12:12:22.488317Z 0 [ERROR] Plugin group_replication reported: 'Group contains 2 members which is greater than group_replication_auto_increment_increment value of 1. This can lead to an higher rate of transactional aborts.'
2017-10-02T12:12:22.488468Z 23 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
2017-10-02T12:12:22.504467Z 23 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='iittor05dbxc010', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2017-10-02T12:12:22.519716Z 23 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor bb1a96af-9492-11e7-afa5-0050568c3445 at iittor05dbxc010 port: 3306.'
2017-10-02T12:12:22.520100Z 25 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-10-02T12:12:22.522238Z 26 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './iittor05dbxc020-relay-bin-group_replication_recovery.000001' position: 4
2017-10-02T12:12:22.669565Z 25 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'mysql_innodb_cluster_rp636224284@iittor05dbxc010:3306',replication started in log 'FIRST' at position 4
2017-10-02T13:40:28.495692Z 49 [Note] Aborted connection 49 to db: 'unconnected' user: 'root' host: 'iittor05dbxc020' (Got an error reading communication packets)
2017-10-02T13:50:37.232875Z 53 [Note] Aborted connection 53 to db: 'unconnected' user: 'root' host: 'iittor05dbxc010' (Got an error reading communication packets)
2017-10-02T13:52:06.668503Z 54 [Note] Aborted connection 54 to db: 'unconnected' user: 'root' host: 'iittor05dbxc010' (Got an error reading communication packets)
2017-10-02T13:54:43.322498Z 56 [Note] Aborted connection 56 to db: 'unconnected' user: 'root' host: 'iittor05dbxc010' (Got an error reading communication packets)
2017-10-02T13:57:38.391412Z 0 [Note] Plugin group_replication reported: 'getstart group_id 614e0daf'
2017-10-02T13:57:40.492458Z 0 [Note] Plugin group_replication reported: 'Marking group replication view change with view_id 15069407899844788:13'
2017-10-02T13:57:41.508909Z 57 [Note] Aborted connection 57 to db: 'unconnected' user: 'root' host: 'iittor05dbxc010' (Got an error reading communication packets)
mysql> SHOW SLAVE STATUS FOR CHANNEL 'group_replication_recovery'\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: iittor05dbxc010
Master_User: mysql_innodb_cluster_rp636224284
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: iittor05dbxc010-bin.000022
Read_Master_Log_Pos: 747411900
Relay_Log_File: iittor05dbxc020-relay-bin-group_replication_recovery.000048
Relay_Log_Pos: 519213911
Relay_Master_Log_File: iittor05dbxc010-bin.000016
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
for slave node 3 i tried to restore the dump from the master to speed up the recover but when i tried to add it it says it's in recovery status but if i check the log:
2017-10-02T14:03:39.822786Z 62 [ERROR] Slave SQL for channel 'group_replication_recovery': Error 'Can't create database 'mysql_innodb_cluster_metadata'; database exists' on query. Default database: 'mysql_innodb_cluster_metadata'. Query: 'CREATE DATABASE mysql_innodb_cluster_metadata', Error_code: 1007
2017-10-02T14:03:39.822822Z 62 [Warning] Slave: Can't create database 'mysql_innodb_cluster_metadata'; database exists Error_code: 1007
2017-10-02T14:03:39.822830Z 62 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'iittor05dbxc010-bin.000001' position 150
2017-10-02T14:03:39.822875Z 43 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2017-10-02T14:03:39.823108Z 61 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
2017-10-02T14:03:39.823139Z 61 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'iittor05dbxc010-bin.000001', position 89391
2017-10-02T14:03:39.867526Z 43 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='iittor05dbxc010', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2017-10-02T14:03:39.909112Z 43 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 8/10'
2017-10-02T14:04:39.989570Z 43 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='iittor05dbxc010', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2017-10-02T14:04:40.033702Z 43 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor bb1a96af-9492-11e7-afa5-0050568c3445 at iittor05dbxc010 port: 3306.'
2017-10-02T14:04:40.034255Z 64 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-10-02T14:04:40.038064Z 65 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './iittor05dbxc030-relay-bin-group_replication_recovery.000001' position: 4
2017-10-02T14:04:40.151021Z 64 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'mysql_innodb_cluster_rp636856255@iittor05dbxc010:3306',replication started in log 'FIRST' at position 4
2017-10-02T14:04:40.321059Z 65 [ERROR] Slave SQL for channel 'group_replication_recovery': Error 'Can't create database 'mysql_innodb_cluster_metadata'; database exists' on query. Default database: 'mysql_innodb_cluster_metadata'. Query: 'CREATE DATABASE mysql_innodb_cluster_metadata', Error_code: 1007
2017-10-02T14:04:40.321097Z 65 [Warning] Slave: Can't create database 'mysql_innodb_cluster_metadata'; database exists Error_code: 1007
2017-10-02T14:04:40.321104Z 65 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'iittor05dbxc010-bin.000001' position 150
2017-10-02T14:04:40.321165Z 43 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
2017-10-02T14:04:40.321344Z 64 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
2017-10-02T14:04:40.321360Z 64 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'iittor05dbxc010-bin.000001', position 1404333
2017-10-02T14:04:40.424533Z 43 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='iittor05dbxc010', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2017-10-02T14:04:40.572111Z 43 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 10/10'
mysql-js> dba.getCluster().status()
{
"clusterName": "certcluster",
"defaultReplicaSet": {
"name": "default",
"primary": "iittor05dbxc010:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 2 members are not active",
"topology": {
"iittor05dbxc010:3306": {
"address": "iittor05dbxc010:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"iittor05dbxc020:3306": {
"address": "iittor05dbxc020:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "RECOVERING"
},
"iittor05dbxc030:3306": {
"address": "iittor05dbxc030:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
}
}
}
So.. what can i do to fix both slave to be part of the cluster?
I don't understand:
Why slave 1 is stucked and doesn't replicate completely from the master and stay stucked in recovering?
Why slave 2 after dump from master and restore it says that he doesn't want to find the 'mysql_innodb_cluster_metadata' database?
Thanks