MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Innodb cluster - issues after creating cluster from scratch

3513493Oct 2 2017 — edited Oct 23 2017

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

BalazsT

Hi,

I have the same issue but only when I try to add an instance to my existing cluster, I have already an SR open with Oracle let's see what they reply.

If you have access to the Oracle Support site you will be able find a document how to setup an InnoDB Cluster from scratch.

Basically you can create 3 empty databases, make sure that no transactions are running or logged:

SET sql_log_bin = OFF;

CREATE USER clusteradmin@'%' IDENTIFIED BY 'xxx';

GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO clusteradmin@'%' WITH GRANT OPTION;

GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO clusteradmin@'%' WITH GRANT OPTION;

GRANT INSERT, UPDATE, DELETE ON mysql.* TO clusteradmin@'%' WITH GRANT OPTION;

SET sql_log_bin = ON;

RESET MASTER;

SELECT @@global.gtid_executed; --this must return no rows

Then you can use mysqlsh to setup the cluster.

Alexandre Almeida

Hi there,

Could you share all the config/option files? (my.cnf)

1 - 2

Added on Oct 2 2017

#mysql-user-groups

2 comments

1,928 views

MySQL Database

Innodb cluster - issues after creating cluster from scratch

Comments

Post Details