Shulou(Shulou.com)06/01 Report--

Three servers are clustered with mysql PXC

172.31.217.182 bd-dev-mingshuo-182

172.31.217.183 bd-dev-mingshuo-183

172.31.217.89 bd-dev-vertica-89

Shut down 183 nodes normally

Mysqladmin-uroot-poracle-S / u01/mysql/3307/data/mysql.sock-P3307 shutdown

Close the node log:

2018-09-27T07:33:13.222079Z 0 [Note] WSREP: Received shutdown signal. Will sleep for 10 secs before initiating shutdown. Pxc_maint_mode switched to SHUTDOWN

2018-09-27T07:33:23.230509Z 0 [Note] WSREP: Stop replication

2018-09-27T07:33:23.230619Z 0 [Note] WSREP: Closing send monitor...

2018-09-27T07:33:23.230640Z 0 [Note] WSREP: Closed send monitor.

2018-09-27T07:33:23.230660Z 0 [Note] WSREP: gcomm: terminating thread

2018-09-27T07:33:23.230680Z 0 [Note] WSREP: gcomm: joining thread

2018-09-27T07:33:23.230827Z 0 [Note] WSREP: gcomm: closing backend

2018-09-27T07:33:23.231780Z 0 [Note] WSREP: Current view of cluster as seen by this node

View (view_id (NON_PRIM,12f1e199,11))

Memb {

12f1e199,0

}

Joined {

}

Left {

}

Partitioned {

2331d3d7,0

C05737fd,0

}

)

2018-09-27T07:33:23.231867Z 0 [Note] WSREP: Current view of cluster as seen by this node

View ((empty))

2018-09-27T07:33:23.232111Z 0 [Note] WSREP: gcomm: closed

2018-09-27T07:33:23.232165Z 0 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1

2018-09-27T07:33:23.232253Z 0 [Note] WSREP: Flow-control interval: [100,100]

2018-09-27T07:33:23.232260Z 0 [Note] WSREP: Trying to continue unpaused monitor

2018-09-27T07:33:23.232264Z 0 [Note] WSREP: Received NON-PRIMARY.

2018-09-27T07:33:23.232268Z 0 [Note] WSREP: Shifting SYNCED-> OPEN (TO: 27)

2018-09-27T07:33:23.232279Z 0 [Note] WSREP: Received self-leave message.

2018-09-27T07:33:23.232285Z 0 [Note] WSREP: Flow-control interval: [0,0]

2018-09-27T07:33:23.232288Z 0 [Note] WSREP: Trying to continue unpaused monitor

2018-09-27T07:33:23.232291Z 0 [Note] WSREP: Received SELF-LEAVE. Closing connection.

2018-09-27T07:33:23.232295Z 0 [Note] WSREP: Shifting OPEN-> CLOSED (TO: 27)

2018-09-27T07:33:23.232302Z 0 [Note] WSREP: RECV thread exiting 0: Success

2018-09-27T07:33:23.232383Z 2 [Note] WSREP: New cluster view: global state: c057dbc5-c16e-11e8-a1a6-825ed9079934:27, view#-1: non-Primary, number of nodes: 1, my index: 0, protocol version 3

2018-09-27T07:33:23.232394Z 2 [Note] WSREP: Setting wsrep_ready to false

2018-09-27T07:33:23.232400Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-09-27T07:33:23.232439Z 2 [Note] WSREP: New cluster view: global state: c057dbc5-c16e-11e8-a1a6-825ed9079934:27, view#-1: non-Primary, number of nodes: 0, my index:-1, protocol version 3

2018-09-27T07:33:23.232443Z 2 [Note] WSREP: Setting wsrep_ready to false

2018-09-27T07:33:23.232446Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-09-27T07:33:23.232472Z 2 [Note] WSREP: applier thread exiting (code:0)

2018-09-27T07:33:23.232479Z 0 [Note] WSREP: recv_thread () joined.

2018-09-27T07:33:23.232502Z 0 [Note] WSREP: Closing replication queue.

2018-09-27T07:33:23.232509Z 0 [Note] WSREP: Closing slave action queue.

2018-09-27T07:33:23.232517Z 0 [Note] Giving 2 client threads a chance to die gracefully

2018-09-27T07:33:25.232639Z 0 [Note] WSREP: Waiting for active wsrep applier to exit

2018-09-27T07:33:25.232758Z 1 [Note] WSREP: rollbacker thread exiting

2018-09-27T07:33:25.232994Z 0 [Note] Giving 0 client threads a chance to die gracefully

2018-09-27T07:33:25.233010Z 0 [Note] Shutting down slave threads

2018-09-27T07:33:25.233025Z 0 [Note] Forcefully disconnecting 0 remaining clients

2018-09-27T07:33:25.233044Z 0 [Note] Event Scheduler: Purging the queue. 0 events

2018-09-27T07:33:25.242788Z 0 [Note] WSREP: Service thread queue flushed.

2018-09-27T07:33:25.250399Z 0 [Note] WSREP: MemPool (SlaveTrxHandle): hit ratio: 0, misses: 0, in use: 0, in pool: 0

2018-09-27T07:33:25.250479Z 0 [Note] WSREP: Shifting CLOSED-> DESTROYED (TO: 27)

2018-09-27T07:33:25.259428Z 0 [Note] Binlog end

2018-09-27T07:33:25.261702Z 0 [Note] Shutting down plugin 'ngram'

2018-09-27T07:33:25.261721Z 0 [Note] Shutting down plugin 'partition'

2018-09-27T07:33:25.261726Z 0 [Note] Shutting down plugin 'ARCHIVE'

2018-09-27T07:33:25.261729Z 0 [Note] Shutting down plugin 'BLACKHOLE'

2018-09-27T07:33:25.261733Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'

2018-09-27T07:33:25.261736Z 0 [Note] Shutting down plugin 'INNODB_CHANGED_PAGES'

2018-09-27T07:33:25.261739Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'

2018-09-27T07:33:25.261741Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'

2018-09-27T07:33:25.261744Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'

2018-09-27T07:33:25.261746Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'

2018-09-27T07:33:25.261749Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'

2018-09-27T07:33:25.261751Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'

2018-09-27T07:33:25.261754Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'

2018-09-27T07:33:25.261756Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'

2018-09-27T07:33:25.261759Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'

2018-09-27T07:33:25.261761Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'

2018-09-27T07:33:25.261764Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'

2018-09-27T07:33:25.261766Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'

2018-09-27T07:33:25.261769Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'

2018-09-27T07:33:25.261771Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'

2018-09-27T07:33:25.261774Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'

2018-09-27T07:33:25.261776Z 0 [Note] Shutting down plugin 'INNODB_METRICS'

2018-09-27T07:33:25.261778Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'

2018-09-27T07:33:25.261781Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'

2018-09-27T07:33:25.261783Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'

2018-09-27T07:33:25.261785Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'

2018-09-27T07:33:25.261788Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'

2018-09-27T07:33:25.261790Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'

2018-09-27T07:33:25.261793Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'

2018-09-27T07:33:25.261795Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'

2018-09-27T07:33:25.261797Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'

2018-09-27T07:33:25.261800Z 0 [Note] Shutting down plugin 'INNODB_CMP'

2018-09-27T07:33:25.261802Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'

2018-09-27T07:33:25.261805Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'

2018-09-27T07:33:25.261807Z 0 [Note] Shutting down plugin 'INNODB_TRX'

2018-09-27T07:33:25.261809Z 0 [Note] Shutting down plugin 'XTRADB_ZIP_DICT_COLS'

2018-09-27T07:33:25.261812Z 0 [Note] Shutting down plugin 'XTRADB_ZIP_DICT'

2018-09-27T07:33:25.261814Z 0 [Note] Shutting down plugin 'XTRADB_RSEG'

2018-09-27T07:33:25.261817Z 0 [Note] Shutting down plugin 'XTRADB_INTERNAL_HASH_TABLES'

2018-09-27T07:33:25.261819Z 0 [Note] Shutting down plugin 'XTRADB_READ_VIEW'

2018-09-27T07:33:25.261822Z 0 [Note] Shutting down plugin 'InnoDB'

2018-09-27T07:33:25.261857Z 0 [Note] InnoDB: FTS optimize thread exiting.

2018-09-27T07:33:25.262097Z 0 [Note] InnoDB: Starting shutdown...

2018-09-27T07:33:25.362428Z 0 [Note] InnoDB: Dumping buffer pool (s) to / u01/mysql/3307/data/ib_buffer_pool

2018-09-27T07:33:25.363022Z 0 [Note] InnoDB: Buffer pool (s) dump completed at 180927 15:33:25

2018-09-27T07:33:25.562786Z 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool

2018-09-27T07:33:26.571050Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2569669

2018-09-27T07:33:26.574169Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2018-09-27T07:33:26.574193Z 0 [Note] Shutting down plugin 'MyISAM'

2018-09-27T07:33:26.574210Z 0 [Note] Shutting down plugin 'MRG_MYISAM'

2018-09-27T07:33:26.574222Z 0 [Note] Shutting down plugin 'CSV'

2018-09-27T07:33:26.574233Z 0 [Note] Shutting down plugin 'MEMORY'

2018-09-27T07:33:26.574254Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'

2018-09-27T07:33:26.574287Z 0 [Note] Shutting down plugin 'sha256_password'

2018-09-27T07:33:26.574296Z 0 [Note] Shutting down plugin 'mysql_native_password'

2018-09-27T07:33:26.574304Z 0 [Note] Shutting down plugin 'wsrep'

2018-09-27T07:33:26.574480Z 0 [Note] Shutting down plugin 'binlog'

Normal node log

2018-09-27T07:33:22.505216Z 0 [Note] WSREP: declaring c05737fd at tcp://172.31.217.89:4567 stable

2018-09-27T07:33:22.505345Z 0 [Note] WSREP: forgetting 12f1e199 (tcp://172.31.217.183:4567)

2018-09-27T07:33:22.511586Z 0 [Note] WSREP: Node 2331d3d7 state primary

2018-09-27T07:33:22.512245Z 0 [Note] WSREP: Current view of cluster as seen by this node

View (view_id (PRIM,2331d3d7,12))

Memb {

2331d3d7,0

C05737fd,0

}

Joined {

}

Left {

}

Partitioned {

12f1e199,0

}

)

2018-09-27T07:33:22.512303Z 0 [Note] WSREP: Save the discovered primary-component to disk

2018-09-27T07:33:22.512547Z 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2

2018-09-27T07:33:22.513157Z 0 [Note] WSREP: forgetting 12f1e199 (tcp://172.31.217.183:4567)

2018-09-27T07:33:22.513241Z 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 9ccf0351-c227-11e8-ae6a-d3cac5b411a7

2018-09-27T07:33:22.514096Z 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 9ccf0351-c227-11e8-ae6a-d3cac5b411a7

2018-09-27T07:33:22.514647Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 9ccf0351-c227-11e8-ae6a-d3cac5b411a7 from 0 (bd-dev-mingshuo-182)

2018-09-27T07:33:22.514661Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 9ccf0351-c227-11e8-ae6a-d3cac5b411a7 from 1 (bd-dev-vertica-89)

2018-09-27T07:33:22.514669Z 0 [Note] WSREP: Quorum results:

Version = 4

Component = PRIMARY

Conf_id = 11

Members = 2 + 2 (primary/total)

Act_id = 27

Last_appl. = 0

Protocols = 0ram 8Comp3 (gcs/repl/appl)

Group UUID = c057dbc5-c16e-11e8-a1a6-825ed9079934

2018-09-27T07:33:22.514675Z 0 [Note] WSREP: Flow-control interval: [141,141]

2018-09-27T07:33:22.514679Z 0 [Note] WSREP: Trying to continue unpaused monitor

2018-09-27T07:33:22.514707Z 2 [Note] WSREP: New cluster view: global state: c057dbc5-c16e-11e8-a1a6-825ed9079934:27, view# 12: Primary, number of nodes: 2, my index: 0, protocol version 3

2018-09-27T07:33:22.514713Z 2 [Note] WSREP: Setting wsrep_ready to true

2018-09-27T07:33:22.514719Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-09-27T07:33:22.514727Z 2 [Note] WSREP: REPL Protocols: 8 (3,2)

2018-09-27T07:33:22.514747Z 2 [Note] WSREP: Assign initial position for certification: 27, protocol version: 3

2018-09-27T07:33:22.514830Z 0 [Note] WSREP: Service thread queue flushed.

2018-09-27T07:33:27.691129Z 0 [Note] WSREP: cleaning up 12f1e199 (tcp://172.31.217.183:4567)

182 nodes insert data

Mysql > insert into T1 values (4BI 4)

Query OK, 1 row affected (0.01sec)

Mysql > select * from T1

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 4 | 4 |

+-+ +

4 rows in set (0.00 sec)

Start 183 nodes

Mysql-S / u01/mysql/3307/data/mysql.sock-uroot-poracle-P3307

Mysql > select * from T1

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 4 | 4 |

+-+ +

4 rows in set (0.00 sec)

The incremental data has been synchronized.

The following is the log incremental application section, where you can see that a transaction has been received.

2018-09-27T08:05:50.785769Z 0 [Note] WSREP: Signalling provider to continue on SST completion.

2018-09-27T08:05:50.785808Z 0 [Note] WSREP: Initialized wsrep sidno 2

2018-09-27T08:05:50.785833Z 0 [Note] WSREP: SST received: c057dbc5-c16e-11e8-a1a6-825ed9079934:27

2018-09-27T08:05:50.785872Z 2 [Note] WSREP: Receiving IST: 1 writesets, seqnos 27-28

2018-09-27T08:05:50.785985Z 0 [Note]

2018-09-27T08:05:50.785985Z 0 [Note] WSREP: Receiving IST... 0. 0% (0 events) complete.

2018-09-27T08:05:50.877679Z 0 [Note] WSREP: Receiving IST...100.0% (1 events) complete.

2018-09-27T08:05:50.877904Z 2 [Note] WSREP: IST received: c057dbc5-c16e-11e8-a1a6-825ed9079934:28

2018-09-27T08:05:50.878589Z 0 [Note] WSREP: 1.0 (bd-dev-mingshuo-183): State transfer from 0.0 (bd-dev-mingshuo-182) complete.

2018-09-27T08:05:50.878603Z 0 [Note] WSREP: SST leaving flow control

2018-09-27T08:05:50.878608Z 0 [Note] WSREP: Shifting JOINER-> JOINED (TO: 28)

2018-09-27T08:05:50.879059Z 0 [Note] WSREP: Member 1.0 (bd-dev-mingshuo-183) synced with group.

2018-09-27T08:05:50.879072Z 0 [Note] WSREP: Shifting JOINED-> SYNCED (TO: 28)

2018-09-27T08:05:50.879101Z 2 [Note] WSREP: Synchronized with group, ready for connections

2018-09-27T08:05:50.879115Z 2 [Note] WSREP: Setting wsrep_ready to true

Now the test shuts down two nodes abnormally.

Kill the 182and183node processes directly kill-9

89 Survival Node:

Mysql > select * from ming.t1

ERROR 1047 (08S01): WSREP has not yet prepared node for application use

Mysql > insert into ming.t1 values (100.10)

ERROR 1047 (08S01): WSREP has not yet prepared node for application use

The surviving node has been unable to provide read and write services normally.

Mysql > show status where Variable_name IN ('wsrep_local_state_uuid','wsrep_cluster_conf_id','wsrep_cluster_size',' wsrep_cluster_status','wsrep_ready','wsrep_connected')

+-+-

| | Variable_name | Value |

+-+-

| | wsrep_local_state_uuid | c057dbc5-c16e-11e8-a1a6-825ed9079934 |

| | wsrep_cluster_conf_id | 18446744073709551615 | |

| | wsrep_cluster_size | 1 | |

| | wsrep_cluster_status | non-Primary |

| | wsrep_connected | ON |

| | wsrep_ready | OFF |

+-+-

6 rows in set (0.00 sec)

You can see that wsrep_cluster_size=1 represents the number of cluster nodes left only by itself.

Wsrep_cluster_status=non-Primary represents inconsistent cluster status.

Wsrep_connected=ON also accepts connections on behalf of the database

Wsrep_read=OFF represents that the database can no longer accept query services normally. The above select statement also supports this point.

Whether the surviving node can provide read service depends on the wsrep_dirty_reads parameter.

Mysql > show variables like 'wsrep_dirty_reads'

+-+ +

| | Variable_name | Value |

+-+ +

| | wsrep_dirty_reads | OFF |

+-+ +

1 row in set (0.00 sec)

Wsrep_dirty_reads can be adjusted dynamically. If set to ON, then when the node state is non-Primary

It can provide the service of reading. The written service also needs to upgrade the node to primary, which is set by other parameters.

We'll talk about it later.

The surviving node has been trying to connect the other two nodes

2018-09-28T02:57:37.209095Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') reconnecting to 2331d3d7 (tcp://172.31.217.182:4567), attempt 960

2018-09-28T02:58:12.714612Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') reconnecting to 252da778 (tcp://172.31.217.183:4567), attempt 900

2018-09-28T02:58:22.216078Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') reconnecting to 2331d3d7 (tcp://172.31.217.182:4567), attempt 990

2018-09-28T02:58:57.721850Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') reconnecting to 252da778 (tcp://172.31.217.183:4567), attempt 930

2018-09-28T02:59:07.223430Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') reconnecting to 2331d3d7 (tcp://172.31.217.182:4567), attempt 1020

The reason why I can't provide reading and writing is that PXC's judgment mechanism for cluster brain fissure is not perfect. For myself, I am the process of losing two nodes in kill.

But for PXC, the surviving node does not know the status of the other two nodes, it is possible that the other two nodes are dead, and it is possible that the other two nodes can continue to communicate with each other to provide services.

In this way, two isolated islands of information are formed, and they can not contact each other, so the survival nodes become unable to read or write.

Log of surviving nodes after pulling up two nodes

2018-09-28T03:04:57.003914Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') connection established to 252da778 tcp://172.31.217.183:4567

2018-09-28T03:05:03.215507Z 0 [Note] WSREP: declaring 252da778 at tcp://172.31.217.183:4567 stable

2018-09-28T03:05:03.216346Z 0 [Note] WSREP: Current view of cluster as seen by this node

View (view_id (NON_PRIM,252da778,30))

Memb {

252da778,0

725136c0,0

}

Joined {

}

Left {

}

Partitioned {

2331d3d7,0

}

)

2018-09-28T03:05:03.216630Z 0 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 1, memb_num = 2

2018-09-28T03:05:03.216710Z 0 [Note] WSREP: Flow-control interval: [141,141]

2018-09-28T03:05:03.216718Z 0 [Note] WSREP: Trying to continue unpaused monitor

2018-09-28T03:05:03.216723Z 0 [Note] WSREP: Received NON-PRIMARY.

2018-09-28T03:05:03.216794Z 1 [Note] WSREP: New cluster view: global state: c057dbc5-c16e-11e8-a1a6-825ed9079934:33, view#-1: non-Primary, number of nodes: 2, my index: 1, protocol version 3

2018-09-28T03:05:03.216822Z 1 [Note] WSREP: Setting wsrep_ready to false

2018-09-28T03:05:03.216833Z 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-09-28T03:05:04.277523Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') connection established to 2331d3d7 tcp://172.31.217.182:4567

2018-09-28T03:05:04.279018Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') connection established to 2331d3d7 tcp://172.31.217.182:4567

2018-09-28T03:05:04.776965Z 0 [Note] WSREP: declaring 2331d3d7 at tcp://172.31.217.182:4567 stable

2018-09-28T03:05:04.777019Z 0 [Note] WSREP: declaring 252da778 at tcp://172.31.217.183:4567 stable

2018-09-28T03:05:04.777487Z 0 [Note] WSREP: re-bootstrapping prim from partitioned components

2018-09-28T03:05:04.778262Z 0 [Note] WSREP: Current view of cluster as seen by this node

View (view_id (PRIM,2331d3d7,31))

Memb {

2331d3d7,0

252da778,0

725136c0,0

}

Joined {

}

Left {

}

Partitioned {

}

)

2018-09-28T03:05:04.778307Z 0 [Note] WSREP: Save the discovered primary-component to disk

2018-09-28T03:05:04.778588Z 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3

2018-09-28T03:05:04.778629Z 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.

2018-09-28T03:05:05.277931Z 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 838d4806-c2cb-11e8-8bb1-eeeae1741165

2018-09-28T03:05:05.278435Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 838d4806-c2cb-11e8-8bb1-eeeae1741165 from 0 (bd-dev-mingshuo-182)

2018-09-28T03:05:05.278463Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 838d4806-c2cb-11e8-8bb1-eeeae1741165 from 1 (bd-dev-mingshuo-183)

2018-09-28T03:05:05.278470Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 838d4806-c2cb-11e8-8bb1-eeeae1741165 from 2 (bd-dev-vertica-89)

2018-09-28T03:05:05.278490Z 0 [Warning] WSREP: Quorum: No node with complete state:

Version: 4

Flags: 0x1

Protocols: 0 / 8 / 3

State: NON-PRIMARY

Desync count: 0

Prim state: NON-PRIMARY

Prim UUID: 00000000-0000-0000-0000-000000000000

Prim seqno:-1

First seqno:-1

Last seqno: 33

Prim JOINED: 0

State UUID: 838d4806-c2cb-11e8-8bb1-eeeae1741165

Group UUID: c057dbc5-c16e-11e8-a1a6-825ed9079934

Name: 'bd-dev-mingshuo-182'

Incoming addr: '172.31.217.182 virtual 3307'

Version: 4

Flags: 00

Protocols: 0 / 8 / 3

State: NON-PRIMARY

Desync count: 0

Prim state: NON-PRIMARY

Prim UUID: 00000000-0000-0000-0000-000000000000

Prim seqno:-1

First seqno:-1

Last seqno: 33

Prim JOINED: 0

State UUID: 838d4806-c2cb-11e8-8bb1-eeeae1741165

Group UUID: c057dbc5-c16e-11e8-a1a6-825ed9079934

Name: 'bd-dev-mingshuo-183'

Incoming addr: '172.31.217.183Viru 3307'

Version: 4

Flags: 0x2

Protocols: 0 / 8 / 3

State: NON-PRIMARY

Desync count: 0

Prim state: SYNCED

Prim UUID: 19faf204-c2c7-11e8-b642-52dd65ccae43

Prim seqno: 26

First seqno: 33

Last seqno: 33

Prim JOINED: 2

State UUID: 838d4806-c2cb-11e8-8bb1-eeeae1741165

Group UUID: c057dbc5-c16e-11e8-a1a6-825ed9079934

Name: 'bd-dev-vertica-89'

Incoming addr: '172.31.217.89purl 3307'

2018-09-28T03:05:05.278511Z 0 [Note] WSREP: Partial re-merge of primary 19faf204-c2c7-11e8-b642-52dd65ccae43 found: 1 of 2.

2018-09-28T03:05:05.278520Z 0 [Note] WSREP: Quorum results:

Version = 4

Component = PRIMARY

Conf_id = 26

Members = 3ax 3 (primary/total)

Act_id = 33

Last_appl. = 0

Protocols = 0ram 8Comp3 (gcs/repl/appl)

Group UUID = c057dbc5-c16e-11e8-a1a6-825ed9079934

2018-09-28T03:05:05.278540Z 0 [Note] WSREP: Flow-control interval: [173,173]

2018-09-28T03:05:05.278544Z 0 [Note] WSREP: Trying to continue unpaused monitor

2018-09-28T03:05:05.278548Z 0 [Note] WSREP: Restored state OPEN-> SYNCED (33)

2018-09-28T03:05:05.278593Z 1 [Note] WSREP: New cluster view: global state: c057dbc5-c16e-11e8-a1a6-825ed9079934:33, view# 27: Primary, number of nodes: 3, my index: 2, protocol version 3

2018-09-28T03:05:05.278612Z 1 [Note] WSREP: Setting wsrep_ready to true

2018-09-28T03:05:05.278621Z 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-09-28T03:05:05.278661Z 1 [Note] WSREP: REPL Protocols: 8 (3,2)

2018-09-28T03:05:05.278679Z 1 [Note] WSREP: Assign initial position for certification: 33, protocol version: 3

2018-09-28T03:05:05.278752Z 0 [Note] WSREP: Service thread queue flushed.

2018-09-28T03:05:05.278828Z 1 [Note] WSREP: Synchronized with group, ready for connections

2018-09-28T03:05:05.278849Z 1 [Note] WSREP: Setting wsrep_ready to true

2018-09-28T03:05:05.278863Z 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

0-09-28T03:05:05.279134Z 0 [Note] WSREP: Member 2018 (bd-dev-mingshuo-182) synced with group.

2018-09-28T03:05:05.279179Z 0 [Note] WSREP: Member 1.0 (bd-dev-mingshuo-183) synced with group.

2018-09-28T03:05:07.290328Z 0 [Note] WSREP: (725136c0, 'tcp://0.0.0.0:4567') turning message relay requesting off

Solution after the occurrence of cerebral fissure:

SET GLOBAL wsrep_provider_options='pc.bootstrap=YES'

Three nodes shut down normally

Turn off the three nodes of 183pr 182je 89 in turn.

Be sure to take a look at the contents of the grastate.dat file before starting

183 nodes

Root@bd-dev-mingshuo-183:/u01/mysql/3307/data#more grastate.dat

# GALERA saved state

Version: 2.1

Uuid: c057dbc5-c16e-11e8-a1a6-825ed9079934

Seqno: 51

Safe_to_bootstrap: 0

182 nodes

Root@bd-dev-mingshuo-182:/opt/mysql/3307/data#more grastate.dat

# GALERA saved state

Version: 2.1

Uuid: c057dbc5-c16e-11e8-a1a6-825ed9079934

Seqno: 51

Safe_to_bootstrap: 0

89 nodes

Root@bd-dev-vertica-89:/opt/mysql/3307/data#more grastate.dat

# GALERA saved state

Version: 2.1

Uuid: c057dbc5-c16e-11e8-a1a6-825ed9079934

Seqno: 51

Safe_to_bootstrap: 1

Note:

The node of the safe_to_bootstrap=1, indicating that this node can be safely started as the primary node. So when starting, you must start 89 nodes first.

Mysqld_safe-defaults-file=/etc/my.cnf-wsrep-new-cluster &

Mysqld_safe-- defaults-file=/etc/my3307.cnf &

Mysqld_safe-- defaults-file=/etc/my3307.cnf &

Question: does mysql PXC just validate the cluster according to grastate.dat 's safe_to_bootstrap when it starts?

This is easy to prove, just follow the above practice to shut down the cluster, and then change the safe_to_bootstrap value of grastate.dat to 1. 3.

The process of the experiment is omitted, but it does start the cluster.

What if the data changes after shutting down some nodes?

Insert data at 89 nodes after closing 183182 nodes

Mysql > insert into ming.t1 values (165.16)

Query OK, 1 row affected (0.01sec)

Then close the 89 nodes. At this point, all the clusters are closed.

Modify the grastate.dat of the node of 183

Root@bd-dev-mingshuo-183:/u01/mysql/3307/data#more grastate.dat

# GALERA saved state

Version: 2.1

Uuid: c057dbc5-c16e-11e8-a1a6-825ed9079934

Seqno: 51

Safe_to_bootstrap: 1

To start the cluster, start 183 nodes first:

Mysqld_safe-defaults-file=/etc/my3307.cnf-wsrep-new-cluster &

Mysql > select * from ming.t1

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 4 | 4 |

| | 5 | 5 |

| | 6 | 6 |

| | 7 | 7 |

| | 8 | 8 |

| | 9 | 9 |

| | 10 | 10 |

| | 11 | 11 |

| | 12 | 12 |

| | 13 | 13 |

| | 14 | 14 |

| | 15 | 15 |

+-+ +

15 rows in set (0.00 sec)

16 that line of data is lost.

Start the 89 nodes again to see if the lost data can be recovered.

2018-09-28T08:13:33.156722Z 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:group_post_state_exchange (): 322: Reversing history: 52-> 51, this member has applied 1 more events than the primary component.Data loss is possible. Aborting.

The log sequence of 89 nodes has reached 52, more than 51. 5% of other nodes. Modify 89-node log sequence 51, and then try to start 89-node

Mysqld_safe-- defaults-file=/etc/my.cnf &

Mysql > select * from ming.t1

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 4 | 4 |

| | 5 | 5 |

| | 6 | 6 |

| | 7 | 7 |

| | 8 | 8 |

| | 9 | 9 |

| | 10 | 10 |

| | 11 | 11 |

| | 12 | 12 |

| | 13 | 13 |

| | 14 | 14 |

| | 15 | 15 |

| | 16 | 16 |

+-+ +

16 rows in set (0.01 sec)

But there are still 15 pieces of data for 183 nodes.

183 nodes delete a piece of data

Mysql > delete from ming.t1 where axi11

Query OK, 1 row affected (0.00 sec)

Both surviving nodes delete the data 11.

Start 182nodes, can start normally, check the data after startup, the data is consistent with 183. it is speculated that the donor node of the data is selected as 183s.

2018-09-28T08:21:19.486736Z 2 [Note] WSREP: Check if state gap can be serviced using IST

2018-09-28T08:21:19.486832Z 2 [Note] WSREP: IST receiver addr using tcp://172.31.217.182:4568

2018-09-28T08:21:19.487026Z 2 [Note] WSREP: Prepared IST receiver, listening at: tcp://172.31.217.182:4568

2018-09-28T08:21:19.487050Z 2 [Note] WSREP: State gap can be likely serviced using IST. SST request though present would be void.

2018-09-28T08:21:19.487984Z 0 [Note] WSREP: may fallback to sst. Ist_seqno [51] < safe_ist_seqno [52]

2018-09-28T08:21:19.488006Z 0 [Note] WSREP: Member 2.0 (bd-dev-mingshuo-182) requested state transfer from'* any*'. Selected 0.0 (bd-dev-mingshuo-183) (SYNCED) as donor.

As can be seen in the log, the 182node is selected as IST receiver and listens on port 4568. Select 183 nodes as the

Donor of the data. It is not surprising, then, that the figures are consistent with 183.

At this time, the data are inconsistent, how to solve it?

You can delete the files in the node data directory, and then click start, and recover all the data through SST.

When Pxc starts, you can manually select the doner node of the data.

Wsrep_sst_donor parameter

Shut down two nodes and restart with the wsrep_sst_donor parameter

Mysqld_safe-defaults-file=/etc/my3307.cnf-wsrep_sst_donor=172.31.217.89 &

Mysqld_safe-defaults-file=/etc/my3307.cnf-wsrep_sst_donor=172.31.217.89 &

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.