在PXC中重新扩展加掉线节点

 

 

Preface

Preface

 

 

    Yesterday I implemented a three-nodes
PXC,but there were some errors when proceeding it.So I decide to
research what’s wrong with it.The operations and evidences shows
below.

    When we add a new node into PXC
structure,it will estimate the mothed(IST/SST) to transfer data to the
new node.It’s no wonder that sst will be automatically choosed.What if a
dropped node in PXC by accident want to rejoin the cluster?Especially it
has been a long period of time after it was dropped out.We should avoid
starting up the node directly.As the SST(either xtrabackup-v2 or rsync
mode) will be choosed to resynchronize the dropped node instead of
IST.Generally speaking,SST may cause to decline the perormance of the
whole cluster.It’s recommend to use another way to deal with that kind
of issue.That is,slave replication.

 

 

Procedure

Framework

 

 

Fisrt of all,I find out that yesterday’s
error is due to lack of “socat” package on first node
zlm2.

Hostname IP Port OS Version MySQL Version Xtrabackup version
zlm2 192.168.1.101 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm3 192.168.1.102 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm4 192.168.1.103 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
 1 //Here's the error log of it.
 2 2018-08-08T08:48:18.198487+01:00 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21')
 3 2018-08-08T08:48:18.200272+01:00 2 [Note] WSREP: DONOR thread signaled with 0
 4 which: no socat in (/usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin)
 5     2018-08-08T06:48:18.782344Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
 6     2018-08-08T06:48:18.784043Z WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
 7     2018-08-08T06:48:18.785926Z WSREP_SST: [ERROR] ****************************************************** 
 8 2018-08-08T08:48:18.786496+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21': 2 (No such file or directory)
 9 2018-08-08T08:48:18.786587+01:00 0 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21'
10 2018-08-08T08:48:18.787610+01:00 0 [Warning] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) failed: -2 (No such file or directory)
11 
12 [root@zlm2 08:53:31 /data/mysql/mysql3308/data]
13 #rpm -qa|grep socat
14 
15 [root@zlm2 09:11:08 /data/mysql/mysql3308/data]
16 #yum install socat
17 ... //Omitted.
18 
19 Installed:
20   socat.x86_64 0:1.7.3.2-2.el7                                                                                                                       
21 
22 Complete!

 

 

Procedure

Restart the
PXC mysqld on zlm2.

 

 1 [root@zlm2 09:21:38 /data/mysql/mysql3308/data]
 2 #mysqladmin -P3308 shutdown
 3 
 4 [root@zlm2 09:22:10 /data/mysql/mysql3308/data]
 5 #ps aux|grep mysqld
 6 mysql     6295  0.1 22.4 1565212 227920 pts/0  Sl   08:47   0:02 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
 7 root      6835  0.0  0.0 112640   956 pts/3    R+   09:22   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm2 09:22:15 /data/mysql/mysql3308/data]
10 #ps aux|grep mysqld
11 root      6841  0.0  0.0 112640   960 pts/3    R+   09:22   0:00 grep --color=auto mysqld
12 
13 [root@zlm2 09:30:04 /data/mysql/mysql3308/data]
14 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster &
15 [1] 7055
16 
17 [root@zlm2 09:30:20 /data/mysql/mysql3308/data]
18 #ps aux|grep mysqld
19 mysql     7055  0.4 22.1 1564952 224900 pts/3  Sl   09:30   0:02 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
20 root      7583  0.0  0.0 112640   956 pts/3    R+   09:39   0:00 grep --color=auto mysqld

欧洲杯竞猜平台 ,what’s show in error log of
initiating SST/IST transfer operation on a new added node.

 

 1 2018-08-09T07:23:32.568794+01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4433' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
 2 2018-08-09T07:23:33.225673+01:00 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.1.103:4444/xtrabackup_sst//1
 3 2018-08-09T07:23:33.225697+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 3) (Increment: 1 -> 3)
 4 2018-08-09T07:23:33.225704+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 5 2018-08-09T07:23:33.225721+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
 6 2018-08-09T07:23:33.225760+01:00 0 [Note] WSREP: Service thread queue flushed.
 7 2018-08-09T07:23:33.226619+01:00 2 [Note] WSREP: Check if state gap can be serviced using IST
 8 2018-08-09T07:23:33.226638+01:00 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 9 2018-08-09T07:23:33.226677+01:00 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST
10 2018-08-09T07:23:33.226683+01:00 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (bd5525ab-9a15-11e8-aa0f-4b830c783fc7): 1 (Operation not permitted)
11      at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.
12 2018-08-09T07:23:33.228003+01:00 0 [Note] WSREP: Member 2.0 (zlm4) requested state transfer from '*any*'. Selected 0.0 (zlm2)(SYNCED) as donor.
13 2018-08-09T07:23:33.228029+01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 22)
14 2018-08-09T07:23:33.228088+01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 0
15 2018-08-09T07:23:33.228108+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
16     2018-08-09T05:23:33.781389Z WSREP_SST: [INFO] Proceeding with SST.........
17     2018-08-09T05:23:33.808866Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!

It
still show the ERROR output when node zlm3 startup.

 

 1 2018-08-08T09:32:14.789691+01:00 0 [Note] WSREP: (e783b5a9, 'tcp://0.0.0.0:4567') connection established to 2bcb8dba tcp://192.168.1.102:4567
 2 2018-08-08T09:32:14.790669+01:00 0 [Note] WSREP: (e783b5a9, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 3 2018-08-08T09:32:15.289238+01:00 0 [Note] WSREP: declaring 2bcb8dba at tcp://192.168.1.102:4567 stable
 4 2018-08-08T09:32:15.290161+01:00 0 [Note] WSREP: Node e783b5a9 state primary
 5 2018-08-08T09:32:15.291214+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 6 view (view_id(PRIM,2bcb8dba,2)
 7 memb {
 8     2bcb8dba,0
 9     e783b5a9,0
10     }
11 joined {
12     }
13 left {
14     }
15 partitioned {
16     }
17 )
18 2018-08-08T09:32:15.291254+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
19 2018-08-08T09:32:15.291707+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
20 2018-08-08T09:32:15.291726+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
21 2018-08-08T09:32:15.791844+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 2c64cc25-9add-11e8-bdeb-67bbb141911b
22 2018-08-08T09:32:15.793169+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2c64cc25-9add-11e8-bdeb-67bbb141911b from 0 (zlm3)
23 2018-08-08T09:32:15.793198+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2c64cc25-9add-11e8-bdeb-67bbb141911b from 1 (zlm2)
24 2018-08-08T09:32:15.793205+01:00 0 [Note] WSREP: Quorum results:
25     version    = 4,
26     component  = PRIMARY,
27     conf_id    = 1,
28     members    = 1/2 (primary/total),
29     act_id     = 21,
30     last_appl. = 0,
31     protocols  = 0/8/3 (gcs/repl/appl),
32     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
33 2018-08-08T09:32:15.793211+01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
34 2018-08-08T09:32:15.793213+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
35 2018-08-08T09:32:15.793314+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
36 2018-08-08T09:32:15.793324+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21, view# 2: Primary, number of nodes: 2, my index: 1, protocol version 3
37 2018-08-08T09:32:15.793327+01:00 2 [Note] WSREP: Setting wsrep_ready to true
38 2018-08-08T09:32:15.793330+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 2) (Increment: 1 -> 2)
39 2018-08-08T09:32:15.793332+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
40 2018-08-08T09:32:15.793337+01:00 2 [Note] WSREP: Assign initial position for certification: 21, protocol version: 3
41 2018-08-08T09:32:15.793351+01:00 0 [Note] WSREP: Service thread queue flushed.
42 2018-08-08T09:32:16.392574+01:00 0 [Note] WSREP: Member 0.0 (zlm3) requested state transfer from '*any*'. Selected 1.0 (zlm2)(SYNCED) as donor.
43 2018-08-08T09:32:16.392599+01:00 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 21)
44 2018-08-08T09:32:16.392659+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
45 2018-08-08T09:32:16.392879+01:00 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21')
46 2018-08-08T09:32:16.396838+01:00 2 [Note] WSREP: DONOR thread signaled with 0
47 2018-08-08T09:32:17.917352+01:00 0 [Note] WSREP: (e783b5a9, 'tcp://0.0.0.0:4567') turning message relay requesting off
48     2018-08-08T07:32:27.003790Z WSREP_SST: [INFO] Streaming the backup to joiner at 192.168.1.102 4444
49 2018-08-08T09:32:27.124206+01:00 4 [Note] Access denied for user 'sst'@'192.168.1.101' (using password: YES)
50 2018-08-08T09:32:27.138281+01:00 5 [Note] Aborted connection 5 to db: 'unconnected' user: 'sst' host: 'localhost' (Got an error reading communication packets)
51     2018-08-08T07:32:27.139336Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
52     2018-08-08T07:32:27.140923Z WSREP_SST: [ERROR] xtrabackup finished with error: 1.  Check /data/mysql/mysql3308/data//innobackup.backup.log
53 --------------- innobackup.backup.log (START) --------------------
54 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3308/data --server-id=1013308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_group_home_dir=./ --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT --defaults_group=mysqld --parallel=4 
55 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3308/data --server-id=1013308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_group_home_dir=./ --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT --defaults_group=mysqld --parallel=4 --port=3308 --user=sst --password=* --socket=/tmp/mysql3308.sock --lock-ddl=1 --backup=1 --galera-info=1 --binlog-info=ON --stream=xbstream --target-dir=/tmp/pxc_sst_sn2p/donor_xb_qh5x 
56 180808 09:32:27  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3308;mysql_socket=/tmp/mysql3308.sock' as 'sst'  (using password: YES).
57 Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3308;mysql_socket=/tmp/mysql3308.sock','sst',...) failed: Access denied for user 'sst'@'192.168.1.101' (using password: YES) at - line 1314.
58 180808 09:32:27 Connecting to MySQL server host: localhost, user: sst, password: set, port: 3308, socket: /tmp/mysql3308.sock
59 Using server version 5.7.22-22-29.26-log
60 180808 09:32:27 Executing LOCK TABLES FOR BACKUP...
61 Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
62 --------------- innobackup.backup.log (END) ----------------------
63     2018-08-08T07:32:27.143691Z WSREP_SST: [ERROR] ****************************************************** 
64     2018-08-08T07:32:27.145779Z WSREP_SST: [ERROR] Cleanup after exit with status:22
65 2018-08-08T09:32:27.158932+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21': 22 (Invalid argument)
66 2018-08-08T09:32:27.159041+01:00 0 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21'
67 2018-08-08T09:32:27.160430+01:00 0 [Warning] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) failed: -22 (Invalid argument)
68 
69 //Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation.
70 //It seams the user sst@localhost is lack of privilege of "PROCESS".

**Check the PXC status at
first.**

 

 1 (zlm@192.168.1.101 3308)[(none)]>show global status like '%wsrep%';
 2 +----------------------------------+----------------------------------------------------------+
 3 | Variable_name                    | Value                                                    |
 4 +----------------------------------+----------------------------------------------------------+
 5 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
 6 | wsrep_protocol_version           | 8                                                        |
 7 | wsrep_last_applied               | 22                                                       |
 8 | wsrep_last_committed             | 22                                                       |
 9 | wsrep_replicated                 | 0                                                        |
10 | wsrep_replicated_bytes           | 0                                                        |
11 | wsrep_repl_keys                  | 0                                                        |
12 | wsrep_repl_keys_bytes            | 0                                                        |
13 | wsrep_repl_data_bytes            | 0                                                        |
14 | wsrep_repl_other_bytes           | 0                                                        |
15 | wsrep_received                   | 21                                                       |
16 | wsrep_received_bytes             | 2733                                                     |
17 | wsrep_local_commits              | 0                                                        |
18 | wsrep_local_cert_failures        | 0                                                        |
19 | wsrep_local_replays              | 0                                                        |
20 | wsrep_local_send_queue           | 0                                                        |
21 | wsrep_local_send_queue_max       | 1                                                        |
22 | wsrep_local_send_queue_min       | 0                                                        |
23 | wsrep_local_send_queue_avg       | 0.000000                                                 |
24 | wsrep_local_recv_queue           | 0                                                        |
25 | wsrep_local_recv_queue_max       | 2                                                        |
26 | wsrep_local_recv_queue_min       | 0                                                        |
27 | wsrep_local_recv_queue_avg       | 0.047619                                                 |
28 | wsrep_local_cached_downto        | 0                                                        |
29 | wsrep_flow_control_paused_ns     | 0                                                        |
30 | wsrep_flow_control_paused        | 0.000000                                                 |
31 | wsrep_flow_control_sent          | 0                                                        |
32 | wsrep_flow_control_recv          | 0                                                        |
33 | wsrep_flow_control_interval      | [ 173, 173 ]                                             |
34 | wsrep_flow_control_interval_low  | 173                                                      |
35 | wsrep_flow_control_interval_high | 173                                                      |
36 | wsrep_flow_control_status        | OFF                                                      |
37 | wsrep_cert_deps_distance         | 0.000000                                                 |
38 | wsrep_apply_oooe                 | 0.000000                                                 |
39 | wsrep_apply_oool                 | 0.000000                                                 |
40 | wsrep_apply_window               | 0.000000                                                 |
41 | wsrep_commit_oooe                | 0.000000                                                 |
42 | wsrep_commit_oool                | 0.000000                                                 |
43 | wsrep_commit_window              | 0.000000                                                 |
44 | wsrep_local_state                | 4                                                        |
45 | wsrep_local_state_comment        | Synced                                                   |
46 | wsrep_cert_index_size            | 0                                                        |
47 | wsrep_cert_bucket_count          | 22                                                       |
48 | wsrep_gcache_pool_size           | 1712                                                     |
49 | wsrep_causal_reads               | 0                                                        |
50 | wsrep_cert_interval              | 0.000000                                                 |
51 | wsrep_ist_receive_status         |                                                          |
52 | wsrep_ist_receive_seqno_start    | 0                                                        |
53 | wsrep_ist_receive_seqno_current  | 0                                                        |
54 | wsrep_ist_receive_seqno_end      | 0                                                        |
55 | wsrep_incoming_addresses         | 192.168.1.101:3308,192.168.1.102:3308,192.168.1.103:3308 |
56 | wsrep_desync_count               | 0                                                        |
57 | wsrep_evs_delayed                |                                                          |
58 | wsrep_evs_evict_list             |                                                          |
59 | wsrep_evs_repl_latency           | 0/0/0/0/0                                                |
60 | wsrep_evs_state                  | OPERATIONAL                                              |
61 | wsrep_gcomm_uuid                 | 13eae368-9b79-11e8-9053-338307f4c6cc                     |
62 | wsrep_cluster_conf_id            | 11                                                       |
63 | wsrep_cluster_size               | 3                                                        | //It means I've got three nodes in PXC.
64 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
65 | wsrep_cluster_status             | Primary                                                  |
66 | wsrep_connected                  | ON                                                       |
67 | wsrep_local_bf_aborts            | 0                                                        |
68 | wsrep_local_index                | 0                                                        |
69 | wsrep_provider_name              | Galera                                                   |
70 | wsrep_provider_vendor            | Codership Oy <info@codership.com>                        |
71 | wsrep_provider_version           | 3.26(r)                                                  |
72 | wsrep_ready                      | ON                                                       |
73 +----------------------------------+----------------------------------------------------------+
74 68 rows in set (0.00 sec)

Modify the
privilege of user sst@localhost.

 

 1 (zlm@192.168.1.101 3308)[(none)]>select user,host from mysql.user;
 2 +---------------+-------------+
 3 | user          | host        |
 4 +---------------+-------------+
 5 | zlm           | 192.168.1.% |
 6 | mysql.session | localhost   |
 7 | mysql.sys     | localhost   |
 8 | root          | localhost   |
 9 | sst           | localhost   |
10 +---------------+-------------+
11 5 rows in set (0.02 sec)
12 
13 (zlm@192.168.1.101 3308)[(none)]>show grants for sst@localhost;
14 +---------------------------------------------------------------------------+
15 | Grants for sst@localhost                                                  |
16 +---------------------------------------------------------------------------+
17 | GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' |
18 +---------------------------------------------------------------------------+
19 1 row in set (0.00 sec)
20 
21 (zlm@192.168.1.101 3308)[(none)]>grant process on *.* to 'sst'@'localhost';
22 ERROR 1045 (28000): Access denied for user 'zlm'@'192.168.1.%' (using password: YES)
23 (zlm@192.168.1.101 3308)[(none)]>exit
24 Bye
25 
26 [root@zlm2 10:07:32 ~]
27 #mysql -uroot -pPassw0rd -hlocalhost -S /tmp/mysql3308.sock
28 mysql: [Warning] Using a password on the command line interface can be insecure.
29 Welcome to the MySQL monitor.  Commands end with ; or \g.
30 Your MySQL connection id is 9
31 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
32 
33 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
34 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
35 
36 Oracle is a registered trademark of Oracle Corporation and/or its
37 affiliates. Other names may be trademarks of their respective
38 owners.
39 
40 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
41 
42 (root@localhost mysql3308.sock)[(none)]>grant process on *.* to sst@localhost;
43 Query OK, 0 rows affected, 1 warning (0.01 sec)
44 
45 (root@localhost mysql3308.sock)[(none)]>show grants for sst@localhost;
46 +------------------------------------------------------------------------------------+
47 | Grants for sst@localhost                                                           |
48 +------------------------------------------------------------------------------------+
49 | GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' |
50 +------------------------------------------------------------------------------------+
51 1 row in set (0.00 sec)

Shutdown
MySQL instance on zlm4.

 

 1 [root@zlm4 09:02:18 /data/mysql/mysql3308]
 2 #!ps
 3 ps aux|grep mysqld
 4 mysql     5367  0.2 21.9 1574708 223476 pts/0  Sl   08:00   0:07 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
 5 root      5809  0.0  0.0 112640   960 pts/0    R+   09:02   0:00 grep --color=auto mysqld
 6 
 7 [root@zlm4 09:02:22 /data/mysql/mysql3308]
 8 #pkill mysqld
 9 
10 [root@zlm4 09:02:43 /data/mysql/mysql3308]
11 #!ps
12 ps aux|grep mysqld
13 root      5827  0.0  0.0 112640   960 pts/0    R+   09:02   0:00 grep --color=auto mysqld
14 [1]+  Done                    mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf

Restart PXC
again on zlm2.

 

[root@zlm2 10:11:10 ~]
#ps aux|grep mysqld
mysql     7055  0.1 22.2 1564952 225952 pts/3  Sl   09:30   0:04 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
root      7661  0.0  0.0 112640   960 pts/0    R+   10:11   0:00 grep --color=auto mysqld

[root@zlm2 10:11:28 ~]
#mysqladmin -uzlm -pzlmzlm -h192.168.1.101 -P3308 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[root@zlm2 10:11:50 ~]
#ps aux|grep mysqld
root      7672  0.0  0.0 112640   960 pts/0    R+   10:12   0:00 grep --color=auto mysqld

[root@zlm2 09:39:30 /data/mysql/mysql3308/data]
#mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster &
[2] 7690
[1]   Done                    mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster

[root@zlm2 10:12:44 /data/mysql/mysql3308/data]
#ps aux|grep mysqld
mysql     7690  5.8 20.6 1556488 210296 pts/3  Sl   10:12   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
root      7729  0.0  0.0 112640   960 pts/3    R+   10:13   0:00 grep --color=auto mysqld

//No ERROR messages were found in error log.

Check
the error log on node zlm3.

 

 1 [root@zlm3 09:01:42 /data/mysql/mysql3308/data]
 2 #tail -f error.log 
 3 2018-08-09T09:02:44.880772+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.103:4567 
 4 2018-08-09T09:02:44.880866+01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
 5 2018-08-09T09:02:44.880877+01:00 0 [Note] WSREP: forgetting 74f3db69 (tcp://192.168.1.103:4567)
 6 2018-08-09T09:02:44.880899+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting off
 7 2018-08-09T09:02:44.884451+01:00 0 [Note] WSREP: Node 13eae368 state primary
 8 2018-08-09T09:02:44.887086+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 9 view (view_id(PRIM,13eae368,12)
10 memb {
11     13eae368,0
12     23fb8f7a,0
13     }
14 joined {
15     }
16 left {
17     }
18 partitioned {
19     74f3db69,0
20     }
21 )
22 2018-08-09T09:02:44.887205+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
23 2018-08-09T09:02:44.887402+01:00 0 [Note] WSREP: forgetting 74f3db69 (tcp://192.168.1.103:4567)
24 2018-08-09T09:02:44.887688+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
25 2018-08-09T09:02:44.887705+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
26 2018-08-09T09:02:44.888444+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe
27 2018-08-09T09:02:44.889150+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe from 0 (zlm2)
28 2018-08-09T09:02:44.889187+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe from 1 (zlm3)
29 2018-08-09T09:02:44.889198+01:00 0 [Note] WSREP: Quorum results:
30     version    = 4,
31     component  = PRIMARY,
32     conf_id    = 11,
33     members    = 2/2 (primary/total),
34     act_id     = 22,
35     last_appl. = 0,
36     protocols  = 0/8/3 (gcs/repl/appl),
37     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
38 2018-08-09T09:02:44.889206+01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
39 2018-08-09T09:02:44.889210+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
40 2018-08-09T09:02:44.889304+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
41 2018-08-09T09:02:44.889315+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 12: Primary, number of nodes: 2, my index: 1, protocol version 3
42 2018-08-09T09:02:44.889319+01:00 2 [Note] WSREP: Setting wsrep_ready to true
43 2018-08-09T09:02:44.889324+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 2 -> 2) (Increment: 3 -> 2)
44 2018-08-09T09:02:44.889328+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
45 2018-08-09T09:02:44.889336+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
46 2018-08-09T09:02:44.889355+01:00 0 [Note] WSREP: Service thread queue flushed.
47 2018-08-09T09:02:50.000210+01:00 0 [Note] WSREP:  cleaning up 74f3db69 (tcp://192.168.1.103:4567)

 Restart PXC
again on zlm3 and check the output of error log.

 

 1 [root@zlm3 10:27:10 ~]
 2 #ps aux|grep mysqld
 3 root      6694  0.0  0.0 112640   960 pts/1    R+   10:43   0:00 grep --color=auto mysqld
 4 [2]+  Exit 1                  mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
 5 
 6 [root@zlm3 10:30:01 /data/mysql/mysql3308/data]
 7 #cat error.log |grep ERROR
 8     2018-08-08T08:27:19.819918Z WSREP_SST: [ERROR] Cleanup after exit with status:1
 9 2018-08-08T10:27:19.826527+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '6021' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 1 (Operation not permitted)
10 2018-08-08T10:27:19.826557+01:00 0 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
11 2018-08-08T10:27:19.826565+01:00 0 [ERROR] WSREP: SST script aborted with error 1 (Operation not permitted)
12 2018-08-08T10:27:19.826592+01:00 0 [ERROR] WSREP: SST failed: 1 (Operation not permitted)
13 2018-08-08T10:27:19.826595+01:00 0 [ERROR] Aborting

**Do some DML operations on node
zlm3.**

 

 1 [root@zlm3 09:07:05 /data/mysql/mysql3308/data]
 2 #mysql -uzlm -pzlmzlm -h192.168.1.102 -P3308
 3 mysql: [Warning] Using a password on the command line interface can be insecure.
 4 Welcome to the MySQL monitor.  Commands end with ; or \g.
 5 Your MySQL connection id is 5
 6 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
 7 
 8 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 9 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
10 
11 Oracle is a registered trademark of Oracle Corporation and/or its
12 affiliates. Other names may be trademarks of their respective
13 owners.
14 
15 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16 
17 (zlm@192.168.1.102 3308)[(none)]>show databases;
18 +--------------------+
19 | Database           |
20 +--------------------+
21 | information_schema |
22 | mysql              |
23 | performance_schema |
24 | sys                |
25 +--------------------+
26 4 rows in set (0.01 sec)
27 
28 (zlm@192.168.1.102 3308)[(none)]>create database zlm;
29 Query OK, 1 row affected (0.01 sec)
30 
31 (zlm@192.168.1.102 3308)[(none)]>use zlm;
32 Database changed
33 (zlm@192.168.1.102 3308)[zlm]>create table t1(
34     -> id int,
35     -> name char(10)
36     -> ) engine=innodb charset=utf8mb4;
37 Query OK, 0 rows affected (0.04 sec)
38 
39 (zlm@192.168.1.102 3308)[zlm]>insert into t1 values(1,'MySQL'),(2,'Oracle'),(3,'PostgreSQL');
40 ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (zlm.t1) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER //DML of tables need explicit primary key in PXC.
41 (zlm@192.168.1.102 3308)[zlm]>alter table t1 add primary key(id);
42 Query OK, 0 rows affected (0.08 sec)
43 Records: 0  Duplicates: 0  Warnings: 0
44 
45 (zlm@192.168.1.102 3308)[zlm]>insert into t1 values(1,'MySQL'),(2,'Oracle'),(3,'PostgreSQL');
46 Query OK, 3 rows affected (0.01 sec)
47 Records: 3  Duplicates: 0  Warnings: 0
48 
49 (zlm@192.168.1.102 3308)[zlm]>select * from t1;
50 +----+------------+
51 | id | name       |
52 +----+------------+
53 |  1 | MySQL      |
54 |  2 | Oracle     |
55 |  3 | PostgreSQL |
56 +----+------------+
57 3 rows in set (0.00 sec)

Check
the error log on donor node zlm2 begin with the time of starting mysqld
on joiner zlm3.

 

  1 2018-08-08T10:54:47.539174+01:00 0 [Note] WSREP: (d4006db4, 'tcp://0.0.0.0:4567') connection established to b3de093b tcp://192.168.1.102:4567
  2 2018-08-08T10:54:47.539620+01:00 0 [Note] WSREP: (d4006db4, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
  3 2018-08-08T10:54:48.038167+01:00 0 [Note] WSREP: declaring b3de093b at tcp://192.168.1.102:4567 stable
  4 2018-08-08T10:54:48.038656+01:00 0 [Note] WSREP: Node d4006db4 state primary
  5 2018-08-08T10:54:48.039842+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
  6 view (view_id(PRIM,b3de093b,8)
  7 memb {
  8     b3de093b,0
  9     d4006db4,0
 10     }
 11 joined {
 12     }
 13 left {
 14     }
 15 partitioned {
 16     }
 17 )
 18 2018-08-08T10:54:48.039890+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 19 2018-08-08T10:54:48.040138+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
 20 2018-08-08T10:54:48.040154+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
 21 2018-08-08T10:54:48.540351+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: b47737d3-9ae8-11e8-81e0-7bfd4d57e067
 22 2018-08-08T10:54:48.540644+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: b47737d3-9ae8-11e8-81e0-7bfd4d57e067 from 0 (zlm3)
 23 2018-08-08T10:54:48.541101+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: b47737d3-9ae8-11e8-81e0-7bfd4d57e067 from 1 (zlm2)
 24 2018-08-08T10:54:48.541140+01:00 0 [Note] WSREP: Quorum results:
 25     version    = 4,
 26     component  = PRIMARY,
 27     conf_id    = 7,
 28     members    = 1/2 (primary/total),
 29     act_id     = 22,
 30     last_appl. = 0,
 31     protocols  = 0/8/3 (gcs/repl/appl),
 32     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 33 2018-08-08T10:54:48.541150+01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
 34 2018-08-08T10:54:48.541155+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
 35 2018-08-08T10:54:48.541224+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
 36 2018-08-08T10:54:48.541234+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 8: Primary, number of nodes: 2, my index: 1, protocol version 3
 37 2018-08-08T10:54:48.541238+01:00 2 [Note] WSREP: Setting wsrep_ready to true
 38 2018-08-08T10:54:48.541241+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 2) (Increment: 1 -> 2)
 39 2018-08-08T10:54:48.541245+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 40 2018-08-08T10:54:48.541252+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
 41 2018-08-08T10:54:48.541264+01:00 0 [Note] WSREP: Service thread queue flushed.
 42 2018-08-08T10:54:49.007421+01:00 0 [Note] WSREP: Member 0.0 (zlm3) requested state transfer from '*any*'. Selected 1.0 (zlm2)(SYNCED) as donor.
 43 2018-08-08T10:54:49.007441+01:00 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 22)
 44 2018-08-08T10:54:49.007480+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 45 2018-08-08T10:54:49.007626+01:00 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22')
 46 2018-08-08T10:54:49.012384+01:00 2 [Note] WSREP: DONOR thread signaled with 0
 47 2018-08-08T10:54:50.911071+01:00 0 [Note] WSREP: (d4006db4, 'tcp://0.0.0.0:4567') turning message relay requesting off
 48     2018-08-08T08:54:59.535108Z WSREP_SST: [INFO] Streaming the backup to joiner at 192.168.1.102 4444
 49 2018-08-08T10:54:59.626864+01:00 8 [Note] Access denied for user 'sst'@'192.168.1.101' (using password: YES)
 50 2018-08-08T10:55:05.537365+01:00 0 [Note] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) complete.
 51 2018-08-08T10:55:05.537399+01:00 0 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 22)
 52 2018-08-08T10:55:05.542864+01:00 0 [Note] WSREP: Member 1.0 (zlm2) synced with group.
 53 2018-08-08T10:55:05.542949+01:00 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 22)
 54 2018-08-08T10:55:05.543015+01:00 2 [Note] WSREP: Synchronized with group, ready for connections
 55 2018-08-08T10:55:05.543022+01:00 2 [Note] WSREP: Setting wsrep_ready to true
 56 2018-08-08T10:55:05.543027+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 57 2018-08-08T10:55:22.477724+01:00 0 [Note] WSREP: forgetting b3de093b (tcp://192.168.1.102:4567)
 58 2018-08-08T10:55:22.479542+01:00 0 [Note] WSREP: Node d4006db4 state primary
 59 2018-08-08T10:55:22.479612+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 60 view (view_id(PRIM,d4006db4,9)
 61 memb {
 62     d4006db4,0
 63     }
 64 joined {
 65     }
 66 left {
 67     }
 68 partitioned {
 69     b3de093b,0
 70     }
 71 )
 72 2018-08-08T10:55:22.479621+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 73 2018-08-08T10:55:22.479763+01:00 0 [Note] WSREP: forgetting b3de093b (tcp://192.168.1.102:4567)
 74 2018-08-08T10:55:22.479875+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 1
 75 2018-08-08T10:55:22.479976+01:00 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: c8aef280-9ae8-11e8-80c9-d74e514aa266
 76 2018-08-08T10:55:22.479976+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: c8aef280-9ae8-11e8-80c9-d74e514aa266
 77 2018-08-08T10:55:22.479976+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: c8aef280-9ae8-11e8-80c9-d74e514aa266 from 0 (zlm2)
 78 2018-08-08T10:55:22.479978+01:00 0 [Note] WSREP: Quorum results:
 79     version    = 4,
 80     component  = PRIMARY,
 81     conf_id    = 8,
 82     members    = 1/1 (primary/total),
 83     act_id     = 22,
 84     last_appl. = 0,
 85     protocols  = 0/8/3 (gcs/repl/appl),
 86     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 87 2018-08-08T10:55:22.479983+01:00 0 [Note] WSREP: Flow-control interval: [100, 100]
 88 2018-08-08T10:55:22.480000+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
 89 2018-08-08T10:55:22.480032+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
 90 2018-08-08T10:55:22.480037+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 9: Primary, number of nodes: 1, my index: 0, protocol version 3
 91 2018-08-08T10:55:22.480040+01:00 2 [Note] WSREP: Setting wsrep_ready to true
 92 2018-08-08T10:55:22.480043+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 2 -> 1) (Increment: 2 -> 1)
 93 2018-08-08T10:55:22.480046+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 94 2018-08-08T10:55:22.480051+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
 95 2018-08-08T10:55:22.480060+01:00 0 [Note] WSREP: Service thread queue flushed.
 96 2018-08-08T10:55:26.085271+01:00 0 [Note] WSREP: (d4006db4, 'tcp://0.0.0.0:4567') connection established to b3de093b tcp://192.168.1.102:4567
 97 2018-08-08T10:55:26.085306+01:00 0 [Warning] WSREP: discarding established (time wait) b3de093b (tcp://192.168.1.102:4567) 
 98 2018-08-08T10:55:27.948973+01:00 0 [Note] WSREP:  cleaning up b3de093b (tcp://192.168.1.102:4567)
 99 
100 //There's no ERROR messages at all this time.

**Check the table on node
zlm2.**

 

 1 [root@zlm2 09:08:15 ~]
 2 #mysql -uzlm -pzlmzlm -h192.168.1.101 -P3308
 3 mysql: [Warning] Using a password on the command line interface can be insecure.
 4 Welcome to the MySQL monitor.  Commands end with ; or \g.
 5 Your MySQL connection id is 9
 6 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
 7 
 8 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 9 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
10 
11 Oracle is a registered trademark of Oracle Corporation and/or its
12 affiliates. Other names may be trademarks of their respective
13 owners.
14 
15 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16 
17 (zlm@192.168.1.101 3308)[(none)]>select * from zlm.t1;
18 +----+------------+
19 | id | name       |
20 +----+------------+
21 |  1 | MySQL      |
22 |  2 | Oracle     |
23 |  3 | PostgreSQL |
24 +----+------------+
25 3 rows in set (0.00 sec)
26 
27 //The PXC is working normally now.

Suplemented on August 9.

 

 

**Generate a backup set by
Xtrabackup on node zlm3.**

    The error above was due to the
parameter of “innodb_log_group_home_dir”,after I
omitted it,there was no error message any more.

 1 [root@zlm3 09:25:44 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3308/my3308.cnf --user=root --password=Passw0rd --host=localhost -S /tmp/mysql3308.sock --port=3308 /data/backup
 3 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3308/data --server-id=1023308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT 
 4 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3308/data --server-id=1023308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT 
 5 180809 09:26:05 innobackupex: Starting the backup operation
 6 
 7 ... //Omitted.
 8 
 9 180809 09:26:17 Finished backing up non-InnoDB tables and files
10 180809 09:26:17 Executing LOCK BINLOG FOR BACKUP... //Here's the difference of lock mode between community and percona version of MySQL.
11 180809 09:26:17 [00] Writing /data/backup/2018-08-09_09-26-05/xtrabackup_binlog_info
12 180809 09:26:17 [00]        ...done
13 180809 09:26:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
14 xtrabackup: The latest check point (for incremental): '2626843'
15 xtrabackup: Stopping log copying thread.
16 .180809 09:26:17 >> log scanned up to (2626852)
17 
18 180809 09:26:17 Executing UNLOCK BINLOG
19 180809 09:26:17 Executing UNLOCK TABLES
20 180809 09:26:17 All tables unlocked
21 180809 09:26:17 [00] Copying ib_buffer_pool to /data/backup/2018-08-09_09-26-05/ib_buffer_pool
22 180809 09:26:17 [00]        ...done
23 180809 09:26:17 Backup created in directory '/data/backup/2018-08-09_09-26-05/'
24 MySQL binlog position: filename 'mysql-bin.000023', position '1107', GTID of the last change '42aada54-65ea-ee17-55f0-b47cf387c038:1-23'
25 180809 09:26:17 [00] Writing /data/backup/2018-08-09_09-26-05/backup-my.cnf
26 180809 09:26:17 [00]        ...done
27 180809 09:26:17 [00] Writing /data/backup/2018-08-09_09-26-05/xtrabackup_info
28 180809 09:26:17 [00]        ...done
29 xtrabackup: Transaction log of lsn (2626827) to (2626852) was copied.
30 180809 09:26:17 completed OK!

 

 

Evidence on
node zlm2.

**Copy the backup set to node
zlm4.**

 

1 [root@zlm3 09:31:07 ~]
2 #scp -r /data/backup/2018-08-09_09-26-05/ zlm4:/data/backup/
3 root@zlm4's password: 
4 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
5 ibdata1                                                                                                            100%  100MB  33.3MB/s   00:03    
6 plugin.ibd                                                                                                         100%   96KB  96.0KB/s   00:00    
7 servers.ibd                                                                                                        100%   96KB  96.0KB/s   00:00
8 ... //Omitted.
 1 //Start the PCX on node zlm2.
 2 [root@zlm2 04:06:43 ~]
 3 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster &
 4 [1] 4116
 5 
 6 //The error log of node zlm2 when node zlm3 is joining the cluster.
 7 2018-08-09T04:08:43.185049+01:00 0 [Note] WSREP: (13eae368, 'tcp://0.0.0.0:4567') connection established to 23fb8f7a tcp://192.168.1.102:4567
 8 2018-08-09T04:08:43.185703+01:00 0 [Note] WSREP: (13eae368, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 9 2018-08-09T04:08:43.683941+01:00 0 [Note] WSREP: declaring 23fb8f7a at tcp://192.168.1.102:4567 stable
10 2018-08-09T04:08:43.684321+01:00 0 [Note] WSREP: Node 13eae368 state primary
11 2018-08-09T04:08:43.684954+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
12 view (view_id(PRIM,13eae368,2)
13 memb {
14     13eae368,0
15     23fb8f7a,0
16     }
17 joined {
18     }
19 left {
20     }
21 partitioned {
22     }
23 )
24 2018-08-09T04:08:43.684992+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
25 2018-08-09T04:08:43.685286+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2
26 2018-08-09T04:08:43.685750+01:00 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 24481910-9b79-11e8-b078-6a52474ad103
27 2018-08-09T04:08:43.688438+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 24481910-9b79-11e8-b078-6a52474ad103
28 2018-08-09T04:08:43.688976+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 24481910-9b79-11e8-b078-6a52474ad103 from 0 (zlm2)
29 2018-08-09T04:08:44.194166+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 24481910-9b79-11e8-b078-6a52474ad103 from 1 (zlm3)
30 2018-08-09T04:08:44.194203+01:00 0 [Note] WSREP: Quorum results:
31     version    = 4,
32     component  = PRIMARY,
33     conf_id    = 1,
34     members    = 1/2 (primary/total),
35     act_id     = 22,
36     last_appl. = 0,
37     protocols  = 0/8/3 (gcs/repl/appl),
38     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
39 2018-08-09T04:08:44.194210+01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
40 2018-08-09T04:08:44.194213+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
41 2018-08-09T04:08:44.194278+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
42 2018-08-09T04:08:44.194285+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 2: Primary, number of nodes: 2, my index: 0, protocol version 3
43 2018-08-09T04:08:44.194288+01:00 2 [Note] WSREP: Setting wsrep_ready to true
44 2018-08-09T04:08:44.194315+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 1) (Increment: 1 -> 2)
45 2018-08-09T04:08:44.194320+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
46 2018-08-09T04:08:44.194327+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
47 2018-08-09T04:08:44.194337+01:00 0 [Note] WSREP: Service thread queue flushed.
48 2018-08-09T04:08:44.870138+01:00 0 [Note] WSREP: Member 1.0 (zlm3) requested state transfer from '*any*'. Selected 0.0 (zlm2)(SYNCED) as donor.
49 2018-08-09T04:08:44.870160+01:00 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 22)
50 2018-08-09T04:08:44.870209+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
51 2018-08-09T04:08:44.870406+01:00 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22')
52 2018-08-09T04:08:44.872063+01:00 2 [Note] WSREP: DONOR thread signaled with 0
53 2018-08-09T04:08:46.270023+01:00 0 [Note] WSREP: (13eae368, 'tcp://0.0.0.0:4567') turning message relay requesting off
54     2018-08-09T02:08:55.527214Z WSREP_SST: [INFO] Streaming the backup to joiner at 192.168.1.102 4444
55 2018-08-09T04:08:55.751008+01:00 4 [Note] Access denied for user 'sst'@'192.168.1.101' (using password: YES)
56 2018-08-09T04:09:03.636264+01:00 0 [Note] WSREP: 0.0 (zlm2): State transfer to 1.0 (zlm3) complete.
57 2018-08-09T04:09:03.636295+01:00 0 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 22)
58 2018-08-09T04:09:03.647735+01:00 0 [Note] WSREP: Member 0.0 (zlm2) synced with group.
59 2018-08-09T04:09:03.647805+01:00 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 22)
60 2018-08-09T04:09:03.647828+01:00 2 [Note] WSREP: Synchronized with group, ready for connections
61 2018-08-09T04:09:03.647833+01:00 2 [Note] WSREP: Setting wsrep_ready to true
62 2018-08-09T04:09:03.647837+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
63 2018-08-09T04:09:23.739120+01:00 0 [Note] WSREP: 1.0 (zlm3): State transfer from 0.0 (zlm2) complete.
64 2018-08-09T04:09:23.739901+01:00 0 [Note] WSREP: Member 1.0 (zlm3) synced with group.

 

 

**Restore backup on node
zlm4.**

Evidence on
node zlm3
(using xtrabackup-v2 mothed of sst).**

 

  1 //Start the PCX on node zlm3.
  2 [root@zlm3 04:08:37 /data/mysql/mysql3308]
  3 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
  4 [1] 3893
  5 
  6 [root@zlm3 04:08:42 /data/mysql/mysql3308]
  7 #ps aux|grep mysqld
  8 mysql     3893  3.1 21.1 1574664 215668 pts/0  Sl   04:08   0:02 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
  9 root      4537  0.0  0.0 112640   956 pts/0    R+   04:09   0:00 grep --color=auto mysqld
 10 
 11 //The intact error log of node zlm3 when joining the cluster.
 12 [root@zlm3 04:08:31 /data/mysql/mysql3308/data]
 13 #tail -f error.log 
 14 
 15 2018-08-09T04:08:43.084230+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 16 2018-08-09T04:08:43.089848+01:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
 17 2018-08-09T04:08:43.089906+01:00 0 [Note] mysqld (mysqld 5.7.22-22-29.26-log) starting as process 3893 ...
 18 2018-08-09T04:08:43.109751+01:00 0 [Note] WSREP: Setting wsrep_ready to false
 19 2018-08-09T04:08:43.109768+01:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
 20 2018-08-09T04:08:43.109772+01:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
 21 2018-08-09T04:08:43.129981+01:00 0 [Note] WSREP: wsrep_load(): Galera 3.26(r) by Codership Oy <info@codership.com> loaded successfully.
 22 2018-08-09T04:08:43.130124+01:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
 23 2018-08-09T04:08:43.130414+01:00 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1
 24 2018-08-09T04:08:43.141875+01:00 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/mysql3308/data/; base_host = 192.168.1.102; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /data/mysql/mysql3308/data/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/mysql3308/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = 1; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 8; socket.checksum = 2; socket.recv_buf_size = 212992; 
 25 2018-08-09T04:08:43.179573+01:00 0 [Note] WSREP: GCache history reset: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:0 -> 00000000-0000-0000-0000-000000000000:-1
 26 2018-08-09T04:08:43.183306+01:00 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
 27 2018-08-09T04:08:43.183338+01:00 0 [Note] WSREP: Preparing to initiate SST/IST
 28 2018-08-09T04:08:43.183342+01:00 0 [Note] WSREP: Starting replication
 29 2018-08-09T04:08:43.183355+01:00 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
 30 2018-08-09T04:08:43.183507+01:00 0 [Note] WSREP: Using CRC-32C for message checksums.
 31 2018-08-09T04:08:43.183568+01:00 0 [Note] WSREP: gcomm thread scheduling priority set to other:0 
 32 2018-08-09T04:08:43.183674+01:00 0 [Warning] WSREP: Fail to access the file (/data/mysql/mysql3308/data//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
 33 2018-08-09T04:08:43.183679+01:00 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
 34 2018-08-09T04:08:43.184132+01:00 0 [Note] WSREP: GMCast version 0
 35 2018-08-09T04:08:43.184337+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
 36 2018-08-09T04:08:43.184344+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
 37 2018-08-09T04:08:43.184483+01:00 0 [Note] WSREP: EVS version 0
 38 2018-08-09T04:08:43.184957+01:00 0 [Note] WSREP: gcomm: connecting to group 'pxc_wubx', peer '192.168.1.101:,192.168.1.102:,192.168.103:'
 39 2018-08-09T04:08:43.186863+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') connection established to 23fb8f7a tcp://192.168.1.102:4567
 40 2018-08-09T04:08:43.186895+01:00 0 [Warning] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.102:4567' points to own listening address, blacklisting
 41 2018-08-09T04:08:43.188042+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') connection established to 13eae368 tcp://192.168.1.101:4567
 42 2018-08-09T04:08:43.188190+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 43 2018-08-09T04:08:43.686789+01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
 44 2018-08-09T04:08:43.687321+01:00 0 [Note] WSREP: Node 13eae368 state primary
 45 2018-08-09T04:08:43.690123+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 46 view (view_id(PRIM,13eae368,2)
 47 memb {
 48     13eae368,0
 49     23fb8f7a,0
 50     }
 51 joined {
 52     }
 53 left {
 54     }
 55 partitioned {
 56     }
 57 )
 58 2018-08-09T04:08:43.690159+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 59 2018-08-09T04:08:43.690256+01:00 0 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.0.103:4567
 60 2018-08-09T04:08:43.690263+01:00 0 [Note] WSREP: discarding pending addr proto entry 0x28b0b70
 61 2018-08-09T04:08:44.185955+01:00 0 [Note] WSREP: gcomm: connected
 62 2018-08-09T04:08:44.186036+01:00 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
 63 2018-08-09T04:08:44.186098+01:00 0 [Note] WSREP: Waiting for SST/IST to complete.
 64 2018-08-09T04:08:44.186339+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
 65 2018-08-09T04:08:44.186355+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
 66 2018-08-09T04:08:44.186383+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 24481910-9b79-11e8-b078-6a52474ad103
 67 2018-08-09T04:08:44.186388+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 24481910-9b79-11e8-b078-6a52474ad103 from 0 (zlm2)
 68 2018-08-09T04:08:44.189234+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 24481910-9b79-11e8-b078-6a52474ad103 from 1 (zlm3)
 69 2018-08-09T04:08:44.189263+01:00 0 [Note] WSREP: Quorum results:
 70     version    = 4,
 71     component  = PRIMARY,
 72     conf_id    = 1,
 73     members    = 1/2 (primary/total),
 74     act_id     = 22,
 75     last_appl. = -1,
 76     protocols  = 0/8/3 (gcs/repl/appl),
 77     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 78 2018-08-09T04:08:44.189270+01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
 79 2018-08-09T04:08:44.189273+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
 80 2018-08-09T04:08:44.189277+01:00 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 22)
 81 2018-08-09T04:08:44.189468+01:00 2 [Note] WSREP: State transfer required: 
 82     Group state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
 83     Local state: 00000000-0000-0000-0000-000000000000:-1
 84 2018-08-09T04:08:44.189478+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
 85 2018-08-09T04:08:44.189485+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 2: Primary, number of nodes: 2, my index: 1, protocol version 3
 86 2018-08-09T04:08:44.189489+01:00 2 [Note] WSREP: Setting wsrep_ready to true
 87 2018-08-09T04:08:44.189491+01:00 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
 88 2018-08-09T04:08:44.189493+01:00 2 [Note] WSREP: Setting wsrep_ready to false
 89 2018-08-09T04:08:44.192795+01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '3893' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
 90     2018-08-09T02:08:44.755132Z WSREP_SST: [WARNING] Found a stale sst_in_progress file: /data/mysql/mysql3308/data//sst_in_progress
 91 2018-08-09T04:08:44.870937+01:00 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.1.102:4444/xtrabackup_sst//1
 92 2018-08-09T04:08:44.871047+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 2) (Increment: 1 -> 2)
 93 2018-08-09T04:08:44.871090+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 94 2018-08-09T04:08:44.871140+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
 95 2018-08-09T04:08:44.871282+01:00 0 [Note] WSREP: Service thread queue flushed.
 96 2018-08-09T04:08:44.871758+01:00 2 [Note] WSREP: Check if state gap can be serviced using IST
 97 2018-08-09T04:08:44.871782+01:00 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 98 2018-08-09T04:08:44.871834+01:00 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST
 99 2018-08-09T04:08:44.871842+01:00 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (bd5525ab-9a15-11e8-aa0f-4b830c783fc7): 1 (Operation not permitted)
100      at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.
101 2018-08-09T04:08:44.872523+01:00 0 [Note] WSREP: Member 1.0 (zlm3) requested state transfer from '*any*'. Selected 0.0 (zlm2)(SYNCED) as donor.
102 2018-08-09T04:08:44.872545+01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 22)
103 2018-08-09T04:08:44.872679+01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 0
104 2018-08-09T04:08:44.872720+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
105     2018-08-09T02:08:45.589512Z WSREP_SST: [INFO] WARNING: Stale temporary SST directory: /data/mysql/mysql3308/data//.sst from previous state transfer. Removing
106     2018-08-09T02:08:45.608370Z WSREP_SST: [INFO] Proceeding with SST.........
107 removed ‘/data/mysql/mysql3308/logs/mysql-bin.000015’
108     2018-08-09T02:08:45.637863Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!
109 2018-08-09T04:08:46.189014+01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting off
110     2018-08-09T02:09:03.636108Z WSREP_SST: [INFO] Preparing the backup at /data/mysql/mysql3308/data//.sst
111 2018-08-09T04:09:03.649250+01:00 0 [Note] WSREP: 0.0 (zlm2): State transfer to 1.0 (zlm3) complete.
112 2018-08-09T04:09:03.659780+01:00 0 [Note] WSREP: Member 0.0 (zlm2) synced with group.
113     2018-08-09T02:09:21.887135Z WSREP_SST: [INFO] Moving the backup to /data/mysql/mysql3308/data/
114     2018-08-09T02:09:22.003073Z WSREP_SST: [INFO] Galera co-ords from recovery: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
115 2018-08-09T04:09:22.015642+01:00 0 [Note] WSREP: SST complete, seqno: 22
116 2018-08-09T04:09:22.020459+01:00 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
117 2018-08-09T04:09:22.020620+01:00 0 [Note] InnoDB: PUNCH HOLE support available
118 2018-08-09T04:09:22.020665+01:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
119 2018-08-09T04:09:22.020674+01:00 0 [Note] InnoDB: Uses event mutexes
120 2018-08-09T04:09:22.020679+01:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
121 2018-08-09T04:09:22.020684+01:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
122 2018-08-09T04:09:22.020688+01:00 0 [Note] InnoDB: Using Linux native AIO
123 2018-08-09T04:09:22.020708+01:00 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 4 to 1 since innodb_buffer_pool_size is less than 1024 MiB
124 2018-08-09T04:09:22.021332+01:00 0 [Note] InnoDB: Number of pools: 1
125 2018-08-09T04:09:22.021561+01:00 0 [Note] InnoDB: Using CPU crc32 instructions
126 2018-08-09T04:09:22.024636+01:00 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
127 2018-08-09T04:09:22.027579+01:00 0 [Note] InnoDB: Completed initialization of buffer pool
128 2018-08-09T04:09:22.031734+01:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
129 2018-08-09T04:09:22.056548+01:00 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /data/mysql/mysql3308/data/xb_doublewrite
130 2018-08-09T04:09:22.058938+01:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
131 2018-08-09T04:09:22.176951+01:00 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/mysql3308/data/xb_doublewrite, size 3932160 bytes
132 2018-08-09T04:09:22.311846+01:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
133 2018-08-09T04:09:22.311867+01:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
134 2018-08-09T04:09:22.311914+01:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
135 2018-08-09T04:09:22.753228+01:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
136 2018-08-09T04:09:22.753891+01:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
137 2018-08-09T04:09:22.753902+01:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
138 2018-08-09T04:09:22.754607+01:00 0 [Note] InnoDB: Waiting for purge to start
139 2018-08-09T04:09:22.805815+01:00 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.22-rel22 started; log sequence number 2605608
140 2018-08-09T04:09:22.806243+01:00 0 [Note] Plugin 'FEDERATED' is disabled.
141 2018-08-09T04:09:22.808992+01:00 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3308/data/ib_buffer_pool
142 2018-08-09T04:09:22.854750+01:00 0 [Note] InnoDB: Buffer pool(s) load completed at 180809  4:09:22
143 2018-08-09T04:09:22.865970+01:00 0 [Note] Recovering after a crash using /data/mysql/mysql3308/logs/mysql-bin
144 2018-08-09T04:09:22.866002+01:00 0 [Note] WSREP: Before binlog recovery (wsrep position bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22)
145 2018-08-09T04:09:22.866021+01:00 0 [Note] Starting crash recovery...
146 2018-08-09T04:09:22.866046+01:00 0 [Note] Crash recovery finished.
147 2018-08-09T04:09:22.866057+01:00 0 [Note] WSREP: After binlog recovery (wsrep position bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22)
148 2018-08-09T04:09:22.875798+01:00 0 [Note] Salting uuid generator variables, current_pid: 3893, server_start_time: 1533780523, bytes_sent: 0, 
149 2018-08-09T04:09:22.875871+01:00 0 [Note] Generated uuid: '3ba41aa7-9b79-11e8-ad75-080027de0e0e', server_start_time: 1095782085868368224, bytes_sent: 46954208
150 2018-08-09T04:09:22.875894+01:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e.
151 2018-08-09T04:09:23.579815+01:00 0 [Note] Auto generated SSL certificates are placed in data directory.
152 2018-08-09T04:09:23.580319+01:00 0 [Warning] CA certificate ca.pem is self signed.
153 2018-08-09T04:09:23.708603+01:00 0 [Note] Auto generated RSA key files are placed in data directory.
154 2018-08-09T04:09:23.708726+01:00 0 [Note] Server hostname (bind-address): '*'; port: 3308
155 2018-08-09T04:09:23.708750+01:00 0 [Note] IPv6 is available.
156 2018-08-09T04:09:23.708759+01:00 0 [Note]   - '::' resolves to '::';
157 2018-08-09T04:09:23.708775+01:00 0 [Note] Server socket created on IP: '::'.
158 2018-08-09T04:09:23.715377+01:00 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
159 2018-08-09T04:09:23.715477+01:00 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
160 2018-08-09T04:09:23.715490+01:00 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
161 2018-08-09T04:09:23.715499+01:00 0 [Warning] 'user' entry 'sst@localhost' ignored in --skip-name-resolve mode.
162 2018-08-09T04:09:23.715539+01:00 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
163 2018-08-09T04:09:23.715548+01:00 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
164 2018-08-09T04:09:23.715559+01:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
165 2018-08-09T04:09:23.716731+01:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
166 2018-08-09T04:09:23.716749+01:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
167 2018-08-09T04:09:23.739950+01:00 0 [Note] Event Scheduler: Loaded 0 events
168 2018-08-09T04:09:23.740507+01:00 0 [Note] WSREP: Signalling provider to continue on SST completion.
169 2018-08-09T04:09:23.740531+01:00 0 [Note] WSREP: Initialized wsrep sidno 2
170 2018-08-09T04:09:23.740553+01:00 0 [Note] WSREP: SST received: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
171 2018-08-09T04:09:23.740631+01:00 0 [Note] mysqld: ready for connections.
172 Version: '5.7.22-22-29.26-log'  socket: '/tmp/mysql3308.sock'  port: 3308  Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
173 2018-08-09T04:09:23.741168+01:00 0 [Note] WSREP: 1.0 (zlm3): State transfer from 0.0 (zlm2) complete.
174 2018-08-09T04:09:23.741191+01:00 0 [Note] WSREP: SST leaving flow control
175 2018-08-09T04:09:23.741196+01:00 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 22)
176 2018-08-09T04:09:23.742029+01:00 0 [Note] WSREP: Member 1.0 (zlm3) synced with group.
177 2018-08-09T04:09:23.742059+01:00 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 22)
178 2018-08-09T04:09:23.742077+01:00 2 [Note] WSREP: Synchronized with group, ready for connections
179 2018-08-09T04:09:23.742082+01:00 2 [Note] WSREP: Setting wsrep_ready to true
180 2018-08-09T04:09:23.742087+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 1 [root@zlm4 09:43:26 /data/backup]
 2 #innobackupex --apply-log /data/backup/2018-08-09_09-26-05/
 3 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1023308 --redo-log-version=1 --innodb_buffer_pool_filename=ib_buffer_pool 
 4 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1023308 --redo-log-version=1 --innodb_buffer_pool_filename=ib_buffer_pool 
 5 180809 09:45:56 innobackupex: Starting the apply-log operation
 6 
 7 ... //Omitted.
 8 
 9 InnoDB: Waiting for purge to start
10 InnoDB: 5.7.19 started; log sequence number 2627605
11 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
12 InnoDB: page_cleaner: 1000ms intended loop took 17036ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
13 InnoDB: FTS optimize thread exiting.
14 InnoDB: Starting shutdown...
15 InnoDB: Shutdown completed; log sequence number 2627624
16 180809 09:46:17 completed OK!
17 
18 [root@zlm4 09:52:44 /data/mysql/mysql3308/data]
19 #ls -l
20 total 540792
21 -rw-r----- 1 mysql mysql        56 Aug  9 08:00 auto.cnf
22 -rw------- 1 mysql mysql      1680 Aug  9 08:00 ca-key.pem
23 -rw-r--r-- 1 mysql mysql      1120 Aug  9 08:00 ca.pem
24 -rw-r--r-- 1 mysql mysql      1120 Aug  9 08:00 client-cert.pem
25 -rw------- 1 mysql mysql      1680 Aug  9 08:00 client-key.pem
26 -rw-r----- 1 mysql mysql     38775 Aug  9 09:02 error.log
27 -rw-r----- 1 mysql mysql 134219048 Aug  9 09:02 galera.cache
28 -rw-r----- 1 mysql mysql       113 Aug  9 09:02 grastate.dat
29 -rw-r----- 1 mysql mysql       280 Aug  9 09:02 ib_buffer_pool
30 -rw-r----- 1 mysql mysql 104857600 Aug  9 09:02 ibdata1
31 -rw-r----- 1 mysql mysql 104857600 Aug  9 09:02 ib_logfile0
32 -rw-r----- 1 mysql mysql 104857600 Aug  9 08:00 ib_logfile1
33 -rw-r----- 1 mysql mysql 104857600 Aug  9 08:00 ib_logfile2
34 drwxr-x--- 2 mysql mysql      4096 Aug  9 08:00 mysql
35 drwxr-x--- 2 mysql mysql      8192 Aug  9 08:00 performance_schema
36 -rw------- 1 mysql mysql      1676 Aug  9 08:00 private_key.pem
37 -rw-r--r-- 1 mysql mysql       452 Aug  9 08:00 public_key.pem
38 -rw-r--r-- 1 mysql mysql      1120 Aug  9 08:00 server-cert.pem
39 -rw------- 1 mysql mysql      1676 Aug  9 08:00 server-key.pem
40 -rw-r----- 1 mysql mysql       227 Aug  9 08:00 slow.log
41 drwxr-x--- 2 mysql mysql      8192 Aug  9 08:00 sys
42 
43 [root@zlm4 09:57:51 /data/mysql/mysql3308/data]
44 #rm -rf *
45 
46 [root@zlm4 09:59:35 /data/mysql/mysql3308/data]
47 #innobackupex --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back /data/backup/2018-08-09_09-26-05/
48 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3308/data --server-id=1033308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT 
49 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3308/data --server-id=1033308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT 
50 180809 10:00:09 innobackupex: Starting the copy-back operation
51 
52 [root@zlm4 10:01:16 /data/mysql/mysql3308/data]
53 #ls -l
54 total 421936
55 -rw-r----- 1 root root       293 Aug  9 10:00 ib_buffer_pool
56 -rw-r----- 1 root root 104857600 Aug  9 10:00 ibdata1
57 -rw-r----- 1 root root 104857600 Aug  9 10:00 ib_logfile0
58 -rw-r----- 1 root root 104857600 Aug  9 10:00 ib_logfile1
59 -rw-r----- 1 root root 104857600 Aug  9 10:00 ib_logfile2
60 -rw-r----- 1 root root  12582912 Aug  9 10:00 ibtmp1
61 drwxr-x--- 2 root root      4096 Aug  9 10:00 mysql
62 drwxr-x--- 2 root root      8192 Aug  9 10:00 performance_schema
63 drwxr-x--- 2 root root      8192 Aug  9 10:00 sys
64 -rw-r----- 1 root root        22 Aug  9 10:00 xtrabackup_binlog_pos_innodb
65 -rw-r----- 1 root root        39 Aug  9 10:00 xtrabackup_galera_info
66 -rw-r----- 1 root root       650 Aug  9 10:00 xtrabackup_info
67 -rw-r----- 1 root root         1 Aug  9 10:00 xtrabackup_master_key_id
68 drwxr-x--- 2 root root        45 Aug  9 10:00 zlm
69 
70 [root@zlm4 10:02:23 /data/mysql/mysql3308/data]
71 #chown -R mysql.mysql *

 

 

 Evidence
on node zlm4
(using rsync mothed of sst).

Modify the
my3308.cnf to omit the PXC relevant parameter and startup
mysqld.

 

 1 [root@zlm4 10:05:33 /data/mysql/mysql3308]
 2 #cat my3308.cnf |grep wsrep
 3 #loose-wsrep_cluster_name=pxc_wubx 
 4 #loose-wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.1.103
 5 #loose-wsrep_node_address=192.168.1.103
 6 #loose-wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
 7 #loose-wsrep_sst_method=xtrabackup-v2
 8 #loose-wsrep_sst_method=rsync
 9 #loose-wsrep_sst_auth=sst:zlmzlm
10 #loose-wsrep_debug=on
11 #loose-wsrep_provider_options="debug=on"
12 
13 [root@zlm4 10:08:04 /data/mysql/mysql3308]
14 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
15 [1] 6094
16 
17 [root@zlm4 10:08:07 /data/mysql/mysql3308]
18 #!ps
19 ps aux|grep mysqld
20 mysql     6094 24.3 17.9 1238476 182592 pts/0  Sl   10:08   0:00 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
21 root      6128  0.0  0.0 112640   956 pts/0    R+   10:08   0:00 grep --color=auto mysqld
  1 //Change the parameter "wsrep_sst_method" into "rysnc" in my3308.cnf file.
  2 #loose-wsrep_sst_method=xtrabackup-v2
  3 loose-wsrep_sst_method=rsync
  4 
  5 [root@zlm4 07:51:38 /data/mysql/mysql3308/data]
  6 #tail -f error.log 
  7 2018-08-09T07:52:21.038606+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  8 2018-08-09T07:52:21.041387+01:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
  9 2018-08-09T07:52:21.041490+01:00 0 [Note] mysqld (mysqld 5.7.22-22-29.26-log) starting as process 5205 ...
 10 2018-08-09T07:52:21.044740+01:00 0 [Note] WSREP: Setting wsrep_ready to false
 11 2018-08-09T07:52:21.044761+01:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
 12 2018-08-09T07:52:21.044767+01:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
 13 2018-08-09T07:52:21.050035+01:00 0 [Note] WSREP: wsrep_load(): Galera 3.26(r) by Codership Oy <info@codership.com> loaded successfully.
 14 2018-08-09T07:52:21.050186+01:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
 15 2018-08-09T07:52:21.050565+01:00 0 [Warning] WSREP: Could not open state file for reading: '/data/mysql/mysql3308/data//grastate.dat'
 16 2018-08-09T07:52:21.050585+01:00 0 [Warning] WSREP: No persistent state found. Bootstraping with default state
 17 2018-08-09T07:52:21.050637+01:00 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1
 18 2018-08-09T07:52:21.055209+01:00 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/mysql3308/data/; base_host = 192.168.1.103; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /data/mysql/mysql3308/data/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/mysql3308/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = 1; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 8; socket.checksum = 2; socket.recv_buf_size = 212992; 
 19 2018-08-09T07:52:21.069257+01:00 0 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 00000000-0000-0000-0000-000000000000:-1
 20 2018-08-09T07:52:21.071550+01:00 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
 21 2018-08-09T07:52:21.071584+01:00 0 [Note] WSREP: Preparing to initiate SST/IST
 22 2018-08-09T07:52:21.071589+01:00 0 [Note] WSREP: Starting replication
 23 2018-08-09T07:52:21.071606+01:00 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
 24 2018-08-09T07:52:21.071812+01:00 0 [Note] WSREP: Using CRC-32C for message checksums.
 25 2018-08-09T07:52:21.071888+01:00 0 [Note] WSREP: gcomm thread scheduling priority set to other:0 
 26 2018-08-09T07:52:21.072021+01:00 0 [Warning] WSREP: Fail to access the file (/data/mysql/mysql3308/data//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
 27 2018-08-09T07:52:21.072030+01:00 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
 28 2018-08-09T07:52:21.072800+01:00 0 [Note] WSREP: GMCast version 0
 29 2018-08-09T07:52:21.072942+01:00 0 [Note] WSREP: (61aad7a7, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
 30 2018-08-09T07:52:21.072942+01:00 0 [Note] WSREP: (61aad7a7, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
 31 2018-08-09T07:52:21.073357+01:00 0 [Note] WSREP: EVS version 0
 32 2018-08-09T07:52:21.073926+01:00 0 [Note] WSREP: gcomm: connecting to group 'pxc_wubx', peer '192.168.1.101:,192.168.1.102:,192.168.1.103:'
 33 2018-08-09T07:52:21.076189+01:00 0 [Note] WSREP: (61aad7a7, 'tcp://0.0.0.0:4567') connection established to 61aad7a7 tcp://192.168.1.103:4567
 34 2018-08-09T07:52:21.076220+01:00 0 [Warning] WSREP: (61aad7a7, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.103:4567' points to own listening address, blacklisting
 35 2018-08-09T07:52:21.078146+01:00 0 [Note] WSREP: (61aad7a7, 'tcp://0.0.0.0:4567') connection established to 13eae368 tcp://192.168.1.101:4567
 36 2018-08-09T07:52:21.078312+01:00 0 [Note] WSREP: (61aad7a7, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 37 2018-08-09T07:52:21.080311+01:00 0 [Note] WSREP: (61aad7a7, 'tcp://0.0.0.0:4567') connection established to 23fb8f7a tcp://192.168.1.102:4567
 38 2018-08-09T07:52:21.583579+01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
 39 2018-08-09T07:52:21.583614+01:00 0 [Note] WSREP: declaring 23fb8f7a at tcp://192.168.1.102:4567 stable
 40 2018-08-09T07:52:21.584818+01:00 0 [Note] WSREP: Node 13eae368 state primary
 41 2018-08-09T07:52:21.589478+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 42 view (view_id(PRIM,13eae368,9)
 43 memb {
 44     13eae368,0
 45     23fb8f7a,0
 46     61aad7a7,0
 47     }
 48 joined {
 49     }
 50 left {
 51     }
 52 partitioned {
 53     }
 54 )
 55 2018-08-09T07:52:21.589519+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 56 2018-08-09T07:52:22.075191+01:00 0 [Note] WSREP: gcomm: connected
 57 2018-08-09T07:52:22.075272+01:00 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
 58 2018-08-09T07:52:22.075348+01:00 0 [Note] WSREP: Waiting for SST/IST to complete.
 59 2018-08-09T07:52:22.075642+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3
 60 2018-08-09T07:52:22.075658+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
 61 2018-08-09T07:52:22.075689+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 61f621e1-9b98-11e8-a772-bfae300f8d05
 62 2018-08-09T07:52:22.075697+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 61f621e1-9b98-11e8-a772-bfae300f8d05 from 0 (zlm2)
 63 2018-08-09T07:52:22.075704+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 61f621e1-9b98-11e8-a772-bfae300f8d05 from 1 (zlm3)
 64 2018-08-09T07:52:22.077812+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 61f621e1-9b98-11e8-a772-bfae300f8d05 from 2 (zlm4)
 65 2018-08-09T07:52:22.077845+01:00 0 [Note] WSREP: Quorum results:
 66     version    = 4,
 67     component  = PRIMARY,
 68     conf_id    = 8,
 69     members    = 2/3 (primary/total),
 70     act_id     = 22,
 71     last_appl. = -1,
 72     protocols  = 0/8/3 (gcs/repl/appl),
 73     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 74 2018-08-09T07:52:22.077853+01:00 0 [Note] WSREP: Flow-control interval: [173, 173]
 75 2018-08-09T07:52:22.077858+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
 76 2018-08-09T07:52:22.077864+01:00 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 22)
 77 2018-08-09T07:52:22.077926+01:00 2 [Note] WSREP: State transfer required: 
 78     Group state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
 79     Local state: 00000000-0000-0000-0000-000000000000:-1
 80 2018-08-09T07:52:22.077935+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
 81 2018-08-09T07:52:22.077943+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 9: Primary, number of nodes: 3, my index: 2, protocol version 3
 82 2018-08-09T07:52:22.077948+01:00 2 [Note] WSREP: Setting wsrep_ready to true
 83 2018-08-09T07:52:22.077951+01:00 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
 84 2018-08-09T07:52:22.077954+01:00 2 [Note] WSREP: Setting wsrep_ready to false
 85 2018-08-09T07:52:22.078033+01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_rsync --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '5205' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
 86 which: no lsof in (/usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/root/perl5/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin)
 87 2018-08-09T07:52:22.254544+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_rsync --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '5205' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
 88     Read: ''lsof' not found in PATH' //Here's the important message of error but has not "ERROR" keyword in the same line.So I've almost missed it.
 89 2018-08-09T07:52:22.254571+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_rsync --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '5205' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
 90 2018-08-09T07:52:22.254631+01:00 2 [ERROR] WSREP: Failed to prepare for 'rsync' SST. Unrecoverable.
 91 2018-08-09T07:52:22.254639+01:00 2 [ERROR] Aborting
 92 
 93 2018-08-09T07:52:22.254643+01:00 2 [Note] Giving 2 client threads a chance to die gracefully
 94 2018-08-09T07:52:24.254907+01:00 2 [Note] WSREP: Closing send monitor...
 95 2018-08-09T07:52:24.254992+01:00 2 [Note] WSREP: Closed send monitor.
 96 2018-08-09T07:52:24.255028+01:00 2 [Note] WSREP: gcomm: terminating thread
 97 2018-08-09T07:52:24.255042+01:00 2 [Note] WSREP: gcomm: joining thread
 98 2018-08-09T07:52:24.255061+01:00 1 [Note] WSREP: rollbacker thread exiting
 99 2018-08-09T07:52:24.255377+01:00 2 [Note] WSREP: gcomm: closing backend
100 2018-08-09T07:52:24.260692+01:00 2 [Note] WSREP: Current view of cluster as seen by this node
101 view (view_id(NON_PRIM,13eae368,9)
102 memb {
103     61aad7a7,0
104     }
105 joined {
106     }
107 left {
108     }
109 partitioned {
110     13eae368,0
111     23fb8f7a,0
112     }
113 )
114 2018-08-09T07:52:24.260770+01:00 2 [Note] WSREP: (61aad7a7, 'tcp://0.0.0.0:4567') turning message relay requesting off
115 2018-08-09T07:52:24.260814+01:00 2 [Note] WSREP: Current view of cluster as seen by this node
116 view ((empty))
117 2018-08-09T07:52:24.261439+01:00 2 [Note] WSREP: gcomm: closed
118 2018-08-09T07:52:24.261690+01:00 0 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
119 2018-08-09T07:52:24.261741+01:00 0 [Note] WSREP: Flow-control interval: [100, 100]
120 2018-08-09T07:52:24.261746+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
121 2018-08-09T07:52:24.261753+01:00 0 [Note] WSREP: Received NON-PRIMARY.
122 2018-08-09T07:52:24.261758+01:00 0 [Note] WSREP: Shifting PRIMARY -> OPEN (TO: 22)
123 2018-08-09T07:52:24.261767+01:00 0 [Note] WSREP: Received self-leave message.
124 2018-08-09T07:52:24.261774+01:00 0 [Note] WSREP: Flow-control interval: [0, 0]
125 2018-08-09T07:52:24.261777+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
126 2018-08-09T07:52:24.261780+01:00 0 [Note] WSREP: Received SELF-LEAVE. Closing connection.
127 2018-08-09T07:52:24.261784+01:00 0 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 22)
128 2018-08-09T07:52:24.261794+01:00 0 [Note] WSREP: RECV thread exiting 0: Success
129 2018-08-09T07:52:24.262101+01:00 2 [Note] WSREP: recv_thread() joined.
130 2018-08-09T07:52:24.262113+01:00 2 [Note] WSREP: Closing replication queue.
131 2018-08-09T07:52:24.262117+01:00 2 [Note] WSREP: Closing slave action queue.
132 2018-08-09T07:52:24.262162+01:00 0 [Note] WSREP: Waiting for active wsrep applier to exit
133 2018-08-09T07:52:24.262170+01:00 0 [Note] WSREP: Service disconnected.
134 2018-08-09T07:52:24.262173+01:00 0 [Note] WSREP: Waiting to close threads......
135 2018-08-09T07:52:29.262290+01:00 0 [Note] WSREP: Some threads may fail to exit.
136 2018-08-09T07:52:29.262337+01:00 0 [Note] Binlog end
137 2018-08-09T07:52:29.262596+01:00 0 [Note] mysqld: Shutdown complete
138 
139 //Install lsof tool.
140 [root@zlm4 07:58:19 /data/mysql/mysql3308]
141 #yum -y install lsof
142 ... //Omitted.
143 
144 Installed:
145   lsof.x86_64 0:4.87-5.el7                                                                                                                           
146 
147 Complete!
148 
149 //Start the node again.
150 [root@zlm4 07:59:47 /data/mysql/mysql3308]
151 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
152 [1] 5367
153 
154 [root@zlm4 08:00:02 /data/mysql/mysql3308]
155 #jobs
156 [1]+  Running                 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
157 
158 //Check the error log again.
159 [root@zlm4 08:59:38 /data/mysql/mysql3308/data]
160 #tail -f error.log 
161 2018-08-09T08:00:02.899495+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
162 2018-08-09T08:00:02.900253+01:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
163 2018-08-09T08:00:02.900326+01:00 0 [Note] mysqld (mysqld 5.7.22-22-29.26-log) starting as process 5367 ...
164 2018-08-09T08:00:02.902758+01:00 0 [Note] WSREP: Setting wsrep_ready to false
165 2018-08-09T08:00:02.902772+01:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
166 2018-08-09T08:00:02.902776+01:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
167 2018-08-09T08:00:02.906503+01:00 0 [Note] WSREP: wsrep_load(): Galera 3.26(r) by Codership Oy <info@codership.com> loaded successfully.
168 2018-08-09T08:00:02.906567+01:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
169 2018-08-09T08:00:02.906904+01:00 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1
170 2018-08-09T08:00:02.911237+01:00 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/mysql3308/data/; base_host = 192.168.1.103; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /data/mysql/mysql3308/data/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/mysql3308/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = 1; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 8; socket.checksum = 2; socket.recv_buf_size = 212992; 
171 2018-08-09T08:00:02.921480+01:00 0 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 00000000-0000-0000-0000-000000000000:-1
172 2018-08-09T08:00:02.923600+01:00 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
173 2018-08-09T08:00:02.923636+01:00 0 [Note] WSREP: Preparing to initiate SST/IST
174 2018-08-09T08:00:02.923639+01:00 0 [Note] WSREP: Starting replication
175 2018-08-09T08:00:02.923652+01:00 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
176 2018-08-09T08:00:02.923845+01:00 0 [Note] WSREP: Using CRC-32C for message checksums.
177 2018-08-09T08:00:02.923912+01:00 0 [Note] WSREP: gcomm thread scheduling priority set to other:0 
178 2018-08-09T08:00:02.923991+01:00 0 [Warning] WSREP: Fail to access the file (/data/mysql/mysql3308/data//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
179 2018-08-09T08:00:02.923996+01:00 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
180 2018-08-09T08:00:02.924673+01:00 0 [Note] WSREP: GMCast version 0
181 2018-08-09T08:00:02.924779+01:00 0 [Note] WSREP: (74f3db69, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
182 2018-08-09T08:00:02.924779+01:00 0 [Note] WSREP: (74f3db69, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
183 2018-08-09T08:00:02.925300+01:00 0 [Note] WSREP: EVS version 0
184 2018-08-09T08:00:02.925433+01:00 0 [Note] WSREP: gcomm: connecting to group 'pxc_wubx', peer '192.168.1.101:,192.168.1.102:,192.168.1.103:'
185 2018-08-09T08:00:02.928124+01:00 0 [Note] WSREP: (74f3db69, 'tcp://0.0.0.0:4567') connection established to 74f3db69 tcp://192.168.1.103:4567
186 2018-08-09T08:00:02.928158+01:00 0 [Warning] WSREP: (74f3db69, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.103:4567' points to own listening address, blacklisting
187 2018-08-09T08:00:02.928505+01:00 0 [Note] WSREP: (74f3db69, 'tcp://0.0.0.0:4567') connection established to 13eae368 tcp://192.168.1.101:4567
188 2018-08-09T08:00:02.929670+01:00 0 [Note] WSREP: (74f3db69, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
189 2018-08-09T08:00:02.929755+01:00 0 [Note] WSREP: (74f3db69, 'tcp://0.0.0.0:4567') connection established to 23fb8f7a tcp://192.168.1.102:4567
190 2018-08-09T08:00:03.429892+01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
191 2018-08-09T08:00:03.429916+01:00 0 [Note] WSREP: declaring 23fb8f7a at tcp://192.168.1.102:4567 stable
192 2018-08-09T08:00:03.431850+01:00 0 [Note] WSREP: Node 13eae368 state primary
193 2018-08-09T08:00:03.434752+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
194 view (view_id(PRIM,13eae368,11)
195 memb {
196     13eae368,0
197     23fb8f7a,0
198     74f3db69,0
199     }
200 joined {
201     }
202 left {
203     }
204 partitioned {
205     }
206 )
207 2018-08-09T08:00:03.434779+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
208 2018-08-09T08:00:03.927902+01:00 0 [Note] WSREP: gcomm: connected
209 2018-08-09T08:00:03.927980+01:00 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
210 2018-08-09T08:00:03.928042+01:00 0 [Note] WSREP: Waiting for SST/IST to complete.
211 2018-08-09T08:00:03.928251+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3
212 2018-08-09T08:00:03.928261+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
213 2018-08-09T08:00:03.928285+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 753e350b-9b99-11e8-be59-b7e1945f65e8
214 2018-08-09T08:00:03.928290+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 753e350b-9b99-11e8-be59-b7e1945f65e8 from 0 (zlm2)
215 2018-08-09T08:00:03.928294+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 753e350b-9b99-11e8-be59-b7e1945f65e8 from 1 (zlm3)
216 2018-08-09T08:00:03.929335+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 753e350b-9b99-11e8-be59-b7e1945f65e8 from 2 (zlm4)
217 2018-08-09T08:00:03.929361+01:00 0 [Note] WSREP: Quorum results:
218     version    = 4,
219     component  = PRIMARY,
220     conf_id    = 10,
221     members    = 2/3 (primary/total),
222     act_id     = 22,
223     last_appl. = -1,
224     protocols  = 0/8/3 (gcs/repl/appl),
225     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
226 2018-08-09T08:00:03.929366+01:00 0 [Note] WSREP: Flow-control interval: [173, 173]
227 2018-08-09T08:00:03.929369+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
228 2018-08-09T08:00:03.929372+01:00 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 22)
229 2018-08-09T08:00:03.929413+01:00 2 [Note] WSREP: State transfer required: 
230     Group state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
231     Local state: 00000000-0000-0000-0000-000000000000:-1
232 2018-08-09T08:00:03.929419+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
233 2018-08-09T08:00:03.929425+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 11: Primary, number of nodes: 3, my index: 2, protocol version 3
234 2018-08-09T08:00:03.929428+01:00 2 [Note] WSREP: Setting wsrep_ready to true
235 2018-08-09T08:00:03.929430+01:00 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
236 2018-08-09T08:00:03.929432+01:00 2 [Note] WSREP: Setting wsrep_ready to false
237 2018-08-09T08:00:03.929496+01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_rsync --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '5367' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
238     2018-08-09T06:00:04.047845Z WSREP_SST: [INFO] Waiting for data-dir through rsync................ //Begin to transfer data using rsync mode.
239 2018-08-09T08:00:04.319479+01:00 2 [Note] WSREP: Prepared SST/IST request: rsync|192.168.1.103:4444/rsync_sst
240 2018-08-09T08:00:04.319504+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 3) (Increment: 1 -> 3)
241 2018-08-09T08:00:04.319510+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
242 2018-08-09T08:00:04.319527+01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
243 2018-08-09T08:00:04.319546+01:00 0 [Note] WSREP: Service thread queue flushed.
244 2018-08-09T08:00:04.319558+01:00 2 [Note] WSREP: Check if state gap can be serviced using IST
245 2018-08-09T08:00:04.319566+01:00 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: bd5525ab-9a15-11e8-aa0f-4b830c783fc7
246 2018-08-09T08:00:04.319598+01:00 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST
247 2018-08-09T08:00:04.319602+01:00 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (bd5525ab-9a15-11e8-aa0f-4b830c783fc7): 1 (Operation not permitted)
248      at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.
249 2018-08-09T08:00:04.321186+01:00 0 [Note] WSREP: Member 2.0 (zlm4) requested state transfer from '*any*'. Selected 0.0 (zlm2)(SYNCED) as donor.
250 2018-08-09T08:00:04.321208+01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 22)
251 2018-08-09T08:00:04.321275+01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 0
252 2018-08-09T08:00:04.321291+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
253 2018-08-09T08:00:05.939711+01:00 0 [Note] WSREP: (74f3db69, 'tcp://0.0.0.0:4567') turning message relay requesting off
254 2018-08-09T08:00:26.343649+01:00 0 [Note] WSREP: 0.0 (zlm2): State transfer to 2.0 (zlm4) complete.
255 2018-08-09T08:00:26.344408+01:00 0 [Note] WSREP: Member 0.0 (zlm2) synced with group.
256     2018-08-09T06:00:26.399740Z WSREP_SST: [INFO] ..............rsync completed //Finished rsync transfer.
257 2018-08-09T08:00:26.929160+01:00 0 [Note] WSREP: SST complete, seqno: 22
258 2018-08-09T08:00:27.003561+01:00 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
259 2018-08-09T08:00:27.003682+01:00 0 [Note] InnoDB: PUNCH HOLE support available
260 2018-08-09T08:00:27.003696+01:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
261 2018-08-09T08:00:27.003701+01:00 0 [Note] InnoDB: Uses event mutexes
262 2018-08-09T08:00:27.003705+01:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
263 2018-08-09T08:00:27.003710+01:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
264 2018-08-09T08:00:27.003715+01:00 0 [Note] InnoDB: Using Linux native AIO
265 2018-08-09T08:00:27.003734+01:00 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 4 to 1 since innodb_buffer_pool_size is less than 1024 MiB
266 2018-08-09T08:00:27.004212+01:00 0 [Note] InnoDB: Number of pools: 1
267 2018-08-09T08:00:27.004325+01:00 0 [Note] InnoDB: Using CPU crc32 instructions
268 2018-08-09T08:00:27.010477+01:00 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
269 2018-08-09T08:00:27.019767+01:00 0 [Note] InnoDB: Completed initialization of buffer pool
270 2018-08-09T08:00:27.023312+01:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
271 2018-08-09T08:00:27.066815+01:00 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /data/mysql/mysql3308/data/xb_doublewrite
272 2018-08-09T08:00:27.070460+01:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
273 2018-08-09T08:00:27.071136+01:00 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2605210
274 2018-08-09T08:00:27.071168+01:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2605219
275 2018-08-09T08:00:27.071185+01:00 0 [Note] InnoDB: Database was not shutdown normally!
276 2018-08-09T08:00:27.071190+01:00 0 [Note] InnoDB: Starting crash recovery.
277 2018-08-09T08:00:27.306683+01:00 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/mysql3308/data/xb_doublewrite, size 3932160 bytes
278 2018-08-09T08:00:27.558968+01:00 0 [Note] InnoDB: Last MySQL binlog file position 0 194, file name mysql-bin.000022
279 2018-08-09T08:00:27.575777+01:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
280 2018-08-09T08:00:27.575872+01:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
281 2018-08-09T08:00:28.063310+01:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
282 2018-08-09T08:00:28.064352+01:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
283 2018-08-09T08:00:28.064370+01:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
284 2018-08-09T08:00:28.065740+01:00 0 [Note] InnoDB: Waiting for purge to start
285 2018-08-09T08:00:28.116905+01:00 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.22-rel22 started; log sequence number 2605219
286 2018-08-09T08:00:28.117131+01:00 0 [Note] Plugin 'FEDERATED' is disabled.
287 2018-08-09T08:00:28.123701+01:00 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3308/data/ib_buffer_pool
288 2018-08-09T08:00:28.123749+01:00 0 [ERROR] InnoDB: Cannot open '/data/mysql/mysql3308/data/ib_buffer_pool' for reading: No such file or directory
289 2018-08-09T08:00:28.126137+01:00 0 [Note] Recovering after a crash using /data/mysql/mysql3308/logs/mysql-bin
290 2018-08-09T08:00:28.126167+01:00 0 [Note] WSREP: Before binlog recovery (wsrep position bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22)
291 2018-08-09T08:00:28.128885+01:00 0 [Note] Starting crash recovery...
292 2018-08-09T08:00:28.128936+01:00 0 [Note] Crash recovery finished.
293 2018-08-09T08:00:28.128955+01:00 0 [Note] WSREP: After binlog recovery (wsrep position bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22)
294 2018-08-09T08:00:28.135097+01:00 0 [Note] Salting uuid generator variables, current_pid: 5367, server_start_time: 1533794402, bytes_sent: 0, 
295 2018-08-09T08:00:28.135163+01:00 0 [Note] Generated uuid: '83fac5b7-9b99-11e8-9f3a-080027de0e0e', server_start_time: 1510676201539890521, bytes_sent: 51827952
296 2018-08-09T08:00:28.135180+01:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 83fac5b7-9b99-11e8-9f3a-080027de0e0e.
297 2018-08-09T08:00:28.649143+01:00 0 [Note] Auto generated SSL certificates are placed in data directory.
298 2018-08-09T08:00:28.651737+01:00 0 [Warning] CA certificate ca.pem is self signed.
299 2018-08-09T08:00:28.812659+01:00 0 [Note] Auto generated RSA key files are placed in data directory.
300 2018-08-09T08:00:28.812836+01:00 0 [Note] Server hostname (bind-address): '*'; port: 3308
301 2018-08-09T08:00:28.812866+01:00 0 [Note] IPv6 is available.
302 2018-08-09T08:00:28.816928+01:00 0 [Note]   - '::' resolves to '::';
303 2018-08-09T08:00:28.821691+01:00 0 [Note] Server socket created on IP: '::'.
304 2018-08-09T08:00:28.846925+01:00 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
305 2018-08-09T08:00:28.846986+01:00 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
306 2018-08-09T08:00:28.847001+01:00 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
307 2018-08-09T08:00:28.847028+01:00 0 [Warning] 'user' entry 'sst@localhost' ignored in --skip-name-resolve mode.
308 2018-08-09T08:00:28.847089+01:00 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
309 2018-08-09T08:00:28.847097+01:00 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
310 2018-08-09T08:00:28.847124+01:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
311 2018-08-09T08:00:28.858902+01:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
312 2018-08-09T08:00:28.858929+01:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
313 2018-08-09T08:00:28.873861+01:00 0 [Note] Event Scheduler: Loaded 0 events
314 2018-08-09T08:00:28.874109+01:00 0 [Note] WSREP: Signalling provider to continue on SST completion.
315 2018-08-09T08:00:28.874133+01:00 0 [Note] WSREP: Initialized wsrep sidno 2
316 2018-08-09T08:00:28.874156+01:00 0 [Note] WSREP: SST received: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
317 2018-08-09T08:00:28.874266+01:00 0 [Note] mysqld: ready for connections.
318 Version: '5.7.22-22-29.26-log'  socket: '/tmp/mysql3308.sock'  port: 3308  Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
319 2018-08-09T08:00:28.875367+01:00 0 [Note] WSREP: 2.0 (zlm4): State transfer from 0.0 (zlm2) complete.
320 2018-08-09T08:00:28.875409+01:00 0 [Note] WSREP: SST leaving flow control
321 2018-08-09T08:00:28.875418+01:00 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 22)
322 2018-08-09T08:00:28.876587+01:00 0 [Note] WSREP: Member 2.0 (zlm4) synced with group.
323 2018-08-09T08:00:28.876618+01:00 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 22)
324 2018-08-09T08:00:28.876639+01:00 2 [Note] WSREP: Synchronized with group, ready for connections
325 2018-08-09T08:00:28.876645+01:00 2 [Note] WSREP: Setting wsrep_ready to true
326 2018-08-09T08:00:28.876649+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
327 
328 //It's okay now.No error is found this time.

 

  

Implement a
master-slave structure between node zlm3 and zlm4.

  1 //Create a replication user on node zlm3.
  2 (zlm@192.168.1.102 3308)[zlm]>grant all privileges on *.* to repl@'192.168.1.%' identified by 'repl4slave';
  3 ERROR 1045 (28000): Access denied for user 'zlm'@'192.168.1.%' (using password: YES)
  4 (zlm@192.168.1.102 3308)[zlm]>\q
  5 Bye
  6 
  7 [root@zlm3 10:11:28 /data/mysql/mysql3308/data]
  8 #mysql -uroot -pPassw0rd -hlocalhost -P3308
  9 mysql: [Warning] Using a password on the command line interface can be insecure.
 10 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (2)
 11 
 12 [root@zlm3 10:11:48 /data/mysql/mysql3308/data]
 13 #mysql -uroot -pPassw0rd -hlocalhost -P3308 -S /tmp/mysql3308.sock
 14 mysql: [Warning] Using a password on the command line interface can be insecure.
 15 Welcome to the MySQL monitor.  Commands end with ; or \g.
 16 Your MySQL connection id is 8
 17 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
 18 
 19 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 20 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 21 
 22 Oracle is a registered trademark of Oracle Corporation and/or its
 23 affiliates. Other names may be trademarks of their respective
 24 owners.
 25 
 26 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 27 
 28 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to repl@'192.168.1.%' identified by 'repl4slave';
 29 Query OK, 0 rows affected, 1 warning (0.00 sec)
 30 
 31 (root@localhost mysql3308.sock)[(none)]>select user,host from mysql.user;
 32 +---------------+-------------+
 33 | user          | host        |
 34 +---------------+-------------+
 35 | repl          | 192.168.1.% |
 36 | zlm           | 192.168.1.% |
 37 | mysql.session | localhost   |
 38 | mysql.sys     | localhost   |
 39 | root          | localhost   |
 40 | sst           | localhost   |
 41 +---------------+-------------+
 42 6 rows in set (0.01 sec)
 43 
 44 //Execute "change master to ... " on node zlm4.
 45 [root@zlm4 10:14:37 /data/mysql/mysql3308]
 46 #mysql -uzlm -pzlmzlm -h192.168.1.103 -P3308
 47 mysql: [Warning] Using a password on the command line interface can be insecure.
 48 Welcome to the MySQL monitor.  Commands end with ; or \g.
 49 Your MySQL connection id is 2
 50 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
 51 
 52 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 53 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 54 
 55 Oracle is a registered trademark of Oracle Corporation and/or its
 56 affiliates. Other names may be trademarks of their respective
 57 owners.
 58 
 59 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 60 
 61 (zlm@192.168.1.103 3308)[(none)]>change master to master_host='192.168.1.102',master_port=3308,master_user='repl',master_password='repl4slave',master_auto_position=1;
 62 Query OK, 0 rows affected, 1 warning (0.02 sec)
 63 
 64 (zlm@192.168.1.103 3308)[(none)]>start slave;
 65 Query OK, 0 rows affected (0.02 sec)
 66 
 67 (zlm@192.168.1.103 3308)[(none)]>show slave status\G
 68 *************************** 1. row ***************************
 69                Slave_IO_State: Waiting for master to send event
 70                   Master_Host: 192.168.1.102
 71                   Master_User: repl
 72                   Master_Port: 3308
 73                 Connect_Retry: 60
 74               Master_Log_File: mysql-bin.000023
 75           Read_Master_Log_Pos: 1397
 76                Relay_Log_File: zlm4-relay-bin.000002
 77                 Relay_Log_Pos: 367
 78         Relay_Master_Log_File: mysql-bin.000023
 79              Slave_IO_Running: Yes
 80             Slave_SQL_Running: No
 81               Replicate_Do_DB: 
 82           Replicate_Ignore_DB: 
 83            Replicate_Do_Table: 
 84        Replicate_Ignore_Table: 
 85       Replicate_Wild_Do_Table: 
 86   Replicate_Wild_Ignore_Table: 
 87                    Last_Errno: 1007
 88                    Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '42aada54-65ea-ee17-55f0-b47cf387c038:20' at master log mysql-bin.000023, end_log_pos 350. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
 89                  Skip_Counter: 0
 90           Exec_Master_Log_Pos: 194
 91               Relay_Log_Space: 1776
 92               Until_Condition: None
 93                Until_Log_File: 
 94                 Until_Log_Pos: 0
 95            Master_SSL_Allowed: No
 96            Master_SSL_CA_File: 
 97            Master_SSL_CA_Path: 
 98               Master_SSL_Cert: 
 99             Master_SSL_Cipher: 
100                Master_SSL_Key: 
101         Seconds_Behind_Master: NULL
102 Master_SSL_Verify_Server_Cert: No
103                 Last_IO_Errno: 0
104                 Last_IO_Error: 
105                Last_SQL_Errno: 1007
106                Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '42aada54-65ea-ee17-55f0-b47cf387c038:20' at master log mysql-bin.000023, end_log_pos 350. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
107   Replicate_Ignore_Server_Ids: 
108              Master_Server_Id: 1023308
109                   Master_UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e
110              Master_Info_File: /data/mysql/mysql3308/data/master.info
111                     SQL_Delay: 0
112           SQL_Remaining_Delay: NULL
113       Slave_SQL_Running_State: 
114            Master_Retry_Count: 86400
115                   Master_Bind: 
116       Last_IO_Error_Timestamp: 
117      Last_SQL_Error_Timestamp: 180809 10:18:58
118                Master_SSL_Crl: 
119            Master_SSL_Crlpath: 
120            Retrieved_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:20-24
121             Executed_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:1-19
122                 Auto_Position: 1
123          Replicate_Rewrite_DB: 
124                  Channel_Name: 
125            Master_TLS_Version: 
126 1 row in set (0.00 sec)
127 
128 //The error log shows below.
129 2018-08-09T10:15:45.368412+01:00 2 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=zlm4-relay-bin' to avoid this problem.
130 2018-08-09T10:15:45.382500+01:00 2 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.1.102', master_port= 3308, master_log_file='', master_log_pos= 4, master_bind=''.
131 2018-08-09T10:18:58.164370+01:00 3 [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.
132 2018-08-09T10:18:58.173589+01:00 3 [Note] Slave I/O thread for channel '': connected to master 'repl@192.168.1.102:3308',replication started in log 'FIRST' at position 4
133 2018-08-09T10:18:58.180721+01:00 4 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
134 2018-08-09T10:18:58.180761+01:00 4 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'FIRST' at position 0, relay log './zlm4-relay-bin.000001' position: 4
135 2018-08-09T10:18:58.202347+01:00 5 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction '42aada54-65ea-ee17-55f0-b47cf387c038:20' at master log mysql-bin.000023, end_log_pos 350; Error 'Can't create database 'zlm'; database exists' on query. Default database: 'zlm'. Query: 'create database zlm', Error_code: 1007
136 2018-08-09T10:18:58.202651+01:00 4 [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.000023' position 194
137 
138 //Check the "replication_applier_status_by_worker" table for detail.
139 (zlm@192.168.1.103 3308)[(none)]>select * from performance_schema.replication_applier_status_by_worker\G
140 *************************** 1. row ***************************
141          CHANNEL_NAME: 
142             WORKER_ID: 1
143             THREAD_ID: NULL
144         SERVICE_STATE: OFF
145 LAST_SEEN_TRANSACTION: 42aada54-65ea-ee17-55f0-b47cf387c038:20
146     LAST_ERROR_NUMBER: 1007
147    LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '42aada54-65ea-ee17-55f0-b47cf387c038:20' at master log mysql-bin.000023, end_log_pos 350; Error 'Can't create database 'zlm'; database exists' on query. Default database: 'zlm'. Query: 'create database zlm'
148  LAST_ERROR_TIMESTAMP: 2018-08-09 16:18:58
149 *************************** 2. row ***************************
150          CHANNEL_NAME: 
151             WORKER_ID: 2
152             THREAD_ID: NULL
153         SERVICE_STATE: OFF
154 LAST_SEEN_TRANSACTION: 
155     LAST_ERROR_NUMBER: 0
156    LAST_ERROR_MESSAGE: 
157  LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
158 *************************** 3. row ***************************
159          CHANNEL_NAME: 
160             WORKER_ID: 3
161             THREAD_ID: NULL
162         SERVICE_STATE: OFF
163 LAST_SEEN_TRANSACTION: 
164     LAST_ERROR_NUMBER: 0
165    LAST_ERROR_MESSAGE: 
166  LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
167 *************************** 4. row ***************************
168          CHANNEL_NAME: 
169             WORKER_ID: 4
170             THREAD_ID: NULL
171         SERVICE_STATE: OFF
172 LAST_SEEN_TRANSACTION: 
173     LAST_ERROR_NUMBER: 0
174    LAST_ERROR_MESSAGE: 
175  LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
176 4 rows in set (0.00 sec)
177 
178 //Check the target database and table.
179 (zlm@192.168.1.103 3308)[(none)]>show databases;
180 +--------------------+
181 | Database           |
182 +--------------------+
183 | information_schema |
184 | mysql              |
185 | performance_schema |
186 | sys                |
187 | zlm                |
188 +--------------------+
189 5 rows in set (0.00 sec)
190 
191 (zlm@192.168.1.103 3308)[(none)]>use zlm
192 Reading table information for completion of table and column names
193 You can turn off this feature to get a quicker startup with -A
194 
195 Database changed
196 (zlm@192.168.1.103 3308)[zlm]>show tables;
197 +---------------+
198 | Tables_in_zlm |
199 +---------------+
200 | t1            |
201 +---------------+
202 1 row in set (0.00 sec)
203 
204 (zlm@192.168.1.103 3308)[zlm]>select * from t1;
205 +----+------------+
206 | id | name       |
207 +----+------------+
208 |  1 | MySQL      |
209 |  2 | Oracle     |
210 |  3 | PostgreSQL |
211 +----+------------+
212 3 rows in set (0.01 sec)
213 
214 //The changes on node zlm3 has been replicated to zlm4.
215 
216 [root@zlm4 10:35:51 /data/mysql/mysql3308/logs]
217 #ls -l
218 total 16
219 -rw-r----- 1 mysql mysql 194 Aug  9 08:00 mysql-bin.000024
220 -rw-r----- 1 mysql mysql 217 Aug  9 09:02 mysql-bin.000025
221 -rw-r----- 1 mysql mysql 194 Aug  9 10:08 mysql-bin.000026
222 -rw-r----- 1 mysql mysql 132 Aug  9 10:08 mysql-bin.index
223 
224 //No mysql-bin.000023 was found.Check the relay-log file.
225 [root@zlm4 10:38:16 /data/mysql/mysql3308/data]
226 #ls -l|grep relay
227 -rw-r----- 1 mysql mysql        58 Aug  9 10:18 relay-log.info
228 -rw-r----- 1 mysql mysql        84 Aug  9 10:18 worker-relay-log.info.1
229 -rw-r----- 1 mysql mysql        84 Aug  9 10:18 worker-relay-log.info.2
230 -rw-r----- 1 mysql mysql        84 Aug  9 10:18 worker-relay-log.info.3
231 -rw-r----- 1 mysql mysql        84 Aug  9 10:18 worker-relay-log.info.4
232 -rw-r----- 1 mysql mysql       206 Aug  9 10:18 zlm4-relay-bin.000001
233 -rw-r----- 1 mysql mysql      1570 Aug  9 10:18 zlm4-relay-bin.000002
234 -rw-r----- 1 mysql mysql        48 Aug  9 10:18 zlm4-relay-bin.index
235 
236 [root@zlm4 10:38:30 /data/mysql/mysql3308/data]
237 #mysqlbinlog -v --base64-output=decode-rows zlm4-relay-bin.000002 > ~/02.log
238 
239 [root@zlm4 10:38:46 /data/mysql/mysql3308/data]
240 #cd
241 
242 [root@zlm4 10:38:49 ~]
243 #cat 02.log 
244 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
245 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
246 DELIMITER /*!*/;
247 # at 4
248 #180809 10:18:58 server id 1033308  end_log_pos 123 CRC32 0x4367bb3e     Start: binlog v 4, server v 5.7.22-22-29.26-log created 180809 10:18:58
249 # This Format_description_event appears in a relay log and was generated by the slave thread.
250 # at 123
251 #180809 10:18:58 server id 1033308  end_log_pos 154 CRC32 0xf3605911     Previous-GTIDs
252 # [empty]
253 # at 154
254 #700101  1:00:00 server id 1023308  end_log_pos 0 CRC32 0xb5bcfbb7     Rotate to mysql-bin.000023  pos: 4
255 # at 201
256 #180809  4:09:22 server id 1023308  end_log_pos 123 CRC32 0x6f7e8565     Start: binlog v 4, server v 5.7.22-22-29.26-log created 180809  4:09:22 at startup
257 ROLLBACK/*!*/;
258 # at 320
259 #180809 10:18:58 server id 0  end_log_pos 367 CRC32 0xa757553d     Rotate to mysql-bin.000023  pos: 194
260 # at 367
261 #180809  9:07:52 server id 1023308  end_log_pos 259 CRC32 0x531076d3     GTID    last_committed=0    sequence_number=1    rbr_only=no
262 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:20'/*!*/;
263 # at 432
264 #180809  9:07:52 server id 1023308  end_log_pos 350 CRC32 0x9acb4e3f     Query    thread_id=5    exec_time=0    error_code=0
265 SET TIMESTAMP=1533798472/*!*/;
266 SET @@session.pseudo_thread_id=5/*!*/;
267 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
268 SET @@session.sql_mode=1436549152/*!*/;
269 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
270 /*!\C utf8 *//*!*/;
271 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
272 SET @@session.lc_time_names=0/*!*/;
273 SET @@session.collation_database=DEFAULT/*!*/;
274 create database zlm
275 /*!*/;
276 # at 523
277 #180809  9:08:36 server id 1023308  end_log_pos 415 CRC32 0xde316a34     GTID    last_committed=1    sequence_number=2    rbr_only=no
278 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:21'/*!*/;
279 # at 588
280 #180809  9:08:36 server id 1023308  end_log_pos 557 CRC32 0xb29bd4ab     Query    thread_id=5    exec_time=0    error_code=0
281 use `zlm`/*!*/;
282 SET TIMESTAMP=1533798516/*!*/;
283 create table t1(
284 id int,
285 name char(10)
286 ) engine=innodb charset=utf8mb4
287 /*!*/;
288 # at 730
289 #180809  9:13:42 server id 1023308  end_log_pos 622 CRC32 0x2a5f6414     GTID    last_committed=2    sequence_number=3    rbr_only=no
290 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:22'/*!*/;
291 # at 795
292 #180809  9:13:42 server id 1023308  end_log_pos 728 CRC32 0xa803e3aa     Query    thread_id=5    exec_time=0    error_code=0
293 SET TIMESTAMP=1533798822/*!*/;
294 alter table t1 add primary key(id)
295 /*!*/;
296 # at 901
297 #180809  9:13:46 server id 1023308  end_log_pos 793 CRC32 0xfd677245     GTID    last_committed=3    sequence_number=4    rbr_only=yes
298 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
299 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:23'/*!*/;
300 # at 966
301 #180809  9:13:46 server id 1023308  end_log_pos 869 CRC32 0x7b568d5a     Query    thread_id=5    exec_time=0    error_code=0
302 SET TIMESTAMP=1533798826/*!*/;
303 SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
304 BEGIN
305 /*!*/;
306 # at 1042
307 # at 1128
308 #180809  9:13:46 server id 1023308  end_log_pos 1002 CRC32 0x8b4e5e6b     Table_map: `zlm`.`t1` mapped to number 110
309 # at 1175
310 #180809  9:13:46 server id 1023308  end_log_pos 1076 CRC32 0x51750bc0     Write_rows: table id 110 flags: STMT_END_F
311 ### INSERT INTO `zlm`.`t1`
312 ### SET
313 ###   @1=1
314 ###   @2='MySQL'
315 ### INSERT INTO `zlm`.`t1`
316 ### SET
317 ###   @1=2
318 ###   @2='Oracle'
319 ### INSERT INTO `zlm`.`t1`
320 ### SET
321 ###   @1=3
322 ###   @2='PostgreSQL'
323 # at 1249
324 #180809  9:13:46 server id 1023308  end_log_pos 1107 CRC32 0x6033f0ee     Xid = 26
325 COMMIT/*!*/;
326 # at 1280
327 #180809 10:12:07 server id 1023308  end_log_pos 1172 CRC32 0x66f4de8c     GTID    last_committed=4    sequence_number=5    rbr_only=no
328 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:24'/*!*/;
329 # at 1345
330 #180809 10:12:07 server id 1023308  end_log_pos 1397 CRC32 0x86665c03     Query    thread_id=8    exec_time=0    error_code=0
331 SET TIMESTAMP=1533802327/*!*/;
332 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
333 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E'
334 /*!*/;
335 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
336 DELIMITER ;
337 # End of log file
338 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
339 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
340 
341 //Clean the value of variable "gtid_purged".
342 (zlm@192.168.1.103 3308)[(none)]>reset master;
343 Query OK, 0 rows affected (0.03 sec)
344 
345 (zlm@192.168.1.103 3308)[(none)]>set @@global.gtid_purged='42aada54-65ea-ee17-55f0-b47cf387c038:1-24';
346 Query OK, 0 rows affected (0.01 sec)
347 
348 (zlm@192.168.1.103 3308)[(none)]>start slave sql_thread;
349 Query OK, 0 rows affected (0.00 sec)
350 
351 (zlm@192.168.1.103 3308)[(none)]>show slave status\G
352 *************************** 1. row ***************************
353                Slave_IO_State: Waiting for master to send event
354                   Master_Host: 192.168.1.102
355                   Master_User: repl
356                   Master_Port: 3308
357                 Connect_Retry: 60
358               Master_Log_File: mysql-bin.000023
359           Read_Master_Log_Pos: 1397
360                Relay_Log_File: zlm4-relay-bin.000002
361                 Relay_Log_Pos: 1570
362         Relay_Master_Log_File: mysql-bin.000023
363              Slave_IO_Running: Yes
364             Slave_SQL_Running: Yes
365               Replicate_Do_DB: 
366           Replicate_Ignore_DB: 
367            Replicate_Do_Table: 
368        Replicate_Ignore_Table: 
369       Replicate_Wild_Do_Table: 
370   Replicate_Wild_Ignore_Table: 
371                    Last_Errno: 0
372                    Last_Error: 
373                  Skip_Counter: 0
374           Exec_Master_Log_Pos: 1397
375               Relay_Log_Space: 1776
376               Until_Condition: None
377                Until_Log_File: 
378                 Until_Log_Pos: 0
379            Master_SSL_Allowed: No
380            Master_SSL_CA_File: 
381            Master_SSL_CA_Path: 
382               Master_SSL_Cert: 
383             Master_SSL_Cipher: 
384                Master_SSL_Key: 
385         Seconds_Behind_Master: 0
386 Master_SSL_Verify_Server_Cert: No
387                 Last_IO_Errno: 0
388                 Last_IO_Error: 
389                Last_SQL_Errno: 0
390                Last_SQL_Error: 
391   Replicate_Ignore_Server_Ids: 
392              Master_Server_Id: 1023308
393                   Master_UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e
394              Master_Info_File: /data/mysql/mysql3308/data/master.info
395                     SQL_Delay: 0
396           SQL_Remaining_Delay: NULL
397       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
398            Master_Retry_Count: 86400
399                   Master_Bind: 
400       Last_IO_Error_Timestamp: 
401      Last_SQL_Error_Timestamp: 
402                Master_SSL_Crl: 
403            Master_SSL_Crlpath: 
404            Retrieved_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:20-24
405             Executed_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:1-24
406                 Auto_Position: 1
407          Replicate_Rewrite_DB: 
408                  Channel_Name: 
409            Master_TLS_Version: 
410 1 row in set (0.00 sec)

 

Insert a
new record in table “t1” on node zlm3.

 1 (root@localhost mysql3308.sock)[(none)]>use zlm
 2 Reading table information for completion of table and column names
 3 You can turn off this feature to get a quicker startup with -A
 4 
 5 Database changed
 6 (root@localhost mysql3308.sock)[zlm]>insert into t1 values(4,'Redis');
 7 Query OK, 1 row affected (0.01 sec)
 8 
 9 (root@localhost mysql3308.sock)[zlm]>select * from t1;
10 +----+------------+
11 | id | name       |
12 +----+------------+
13 |  1 | MySQL      |
14 |  2 | Oracle     |
15 |  3 | PostgreSQL |
16 |  4 | Redis      |
17 +----+------------+
18 4 rows in set (0.00 sec)

 

**Stop slave and check the value of
 “Relay_Master_Log_File**“,”**Exec_Master_Log_Pos**” and
**Retrieved_Gtid_Set**“.**

 

 1 (zlm@192.168.1.103 3308)[(none)]>stop slave;
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (zlm@192.168.1.103 3308)[(none)]>show slave status\G
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: 192.168.1.102
 8                   Master_User: repl
 9                   Master_Port: 3308
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000023
12           Read_Master_Log_Pos: 1718
13                Relay_Log_File: zlm4-relay-bin.000003
14                 Relay_Log_Pos: 775
15         Relay_Master_Log_File: mysql-bin.000023 //This binlog file is which one we need to analyze later on master node zlm3.
16              Slave_IO_Running: No
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 1718 //This position is where we need to set the Xid.
28               Relay_Log_Space: 2397
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1023308
46                   Master_UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e
47              Master_Info_File: /data/mysql/mysql3308/data/master.info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:20-25
58             Executed_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:1-25
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

 

Analyze the
binlog file we get above(here is mysql-bin.000023).

  1 [root@zlm3 10:54:51 /data/mysql/mysql3308/logs]
  2 #mysqlbinlog -v --base64-output=decode-rows mysql-bin.000023 > ~/23.log
  3 
  4 [root@zlm3 10:55:27 /data/mysql/mysql3308/logs]
  5 #cat ~/23.log
  6 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  7 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  8 DELIMITER /*!*/;
  9 # at 4
 10 #180809  4:09:22 server id 1023308  end_log_pos 123 CRC32 0x6f7e8565     Start: binlog v 4, server v 5.7.22-22-29.26-log created 180809  4:09:22 at startup
 11 # Warning: this binlog is either in use or was not closed properly.
 12 ROLLBACK/*!*/;
 13 # at 123
 14 #180809  4:09:22 server id 1023308  end_log_pos 194 CRC32 0x43a7c3b7     Previous-GTIDs
 15 # 42aada54-65ea-ee17-55f0-b47cf387c038:1-19
 16 # at 194
 17 #180809  9:07:52 server id 1023308  end_log_pos 259 CRC32 0x531076d3     GTID    last_committed=0    sequence_number=1    rbr_only=no
 18 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:20'/*!*/;
 19 # at 259
 20 #180809  9:07:52 server id 1023308  end_log_pos 350 CRC32 0x9acb4e3f     Query    thread_id=5    exec_time=0    error_code=0
 21 SET TIMESTAMP=1533798472/*!*/;
 22 SET @@session.pseudo_thread_id=5/*!*/;
 23 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 24 SET @@session.sql_mode=1436549152/*!*/;
 25 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 26 /*!\C utf8 *//*!*/;
 27 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
 28 SET @@session.lc_time_names=0/*!*/;
 29 SET @@session.collation_database=DEFAULT/*!*/;
 30 create database zlm
 31 /*!*/;
 32 # at 350
 33 #180809  9:08:36 server id 1023308  end_log_pos 415 CRC32 0xde316a34     GTID    last_committed=1    sequence_number=2    rbr_only=no
 34 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:21'/*!*/;
 35 # at 415
 36 #180809  9:08:36 server id 1023308  end_log_pos 557 CRC32 0xb29bd4ab     Query    thread_id=5    exec_time=0    error_code=0
 37 use `zlm`/*!*/;
 38 SET TIMESTAMP=1533798516/*!*/;
 39 create table t1(
 40 id int,
 41 name char(10)
 42 ) engine=innodb charset=utf8mb4
 43 /*!*/;
 44 # at 557
 45 #180809  9:13:42 server id 1023308  end_log_pos 622 CRC32 0x2a5f6414     GTID    last_committed=2    sequence_number=3    rbr_only=no
 46 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:22'/*!*/;
 47 # at 622
 48 #180809  9:13:42 server id 1023308  end_log_pos 728 CRC32 0xa803e3aa     Query    thread_id=5    exec_time=0    error_code=0
 49 SET TIMESTAMP=1533798822/*!*/;
 50 alter table t1 add primary key(id)
 51 /*!*/;
 52 # at 728
 53 #180809  9:13:46 server id 1023308  end_log_pos 793 CRC32 0xfd677245     GTID    last_committed=3    sequence_number=4    rbr_only=yes
 54 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 55 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:23'/*!*/;
 56 # at 793
 57 #180809  9:13:46 server id 1023308  end_log_pos 869 CRC32 0x7b568d5a     Query    thread_id=5    exec_time=0    error_code=0
 58 SET TIMESTAMP=1533798826/*!*/;
 59 SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
 60 BEGIN
 61 /*!*/;
 62 # at 869
 63 # at 955
 64 #180809  9:13:46 server id 1023308  end_log_pos 1002 CRC32 0x8b4e5e6b     Table_map: `zlm`.`t1` mapped to number 110
 65 # at 1002
 66 #180809  9:13:46 server id 1023308  end_log_pos 1076 CRC32 0x51750bc0     Write_rows: table id 110 flags: STMT_END_F
 67 ### INSERT INTO `zlm`.`t1`
 68 ### SET
 69 ###   @1=1
 70 ###   @2='MySQL'
 71 ### INSERT INTO `zlm`.`t1`
 72 ### SET
 73 ###   @1=2
 74 ###   @2='Oracle'
 75 ### INSERT INTO `zlm`.`t1`
 76 ### SET
 77 ###   @1=3
 78 ###   @2='PostgreSQL'
 79 # at 1076
 80 #180809  9:13:46 server id 1023308  end_log_pos 1107 CRC32 0x6033f0ee     Xid = 26
 81 COMMIT/*!*/;
 82 # at 1107
 83 #180809 10:12:07 server id 1023308  end_log_pos 1172 CRC32 0x66f4de8c     GTID    last_committed=4    sequence_number=5    rbr_only=no
 84 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:24'/*!*/;
 85 # at 1172
 86 #180809 10:12:07 server id 1023308  end_log_pos 1397 CRC32 0x86665c03     Query    thread_id=8    exec_time=0    error_code=0
 87 SET TIMESTAMP=1533802327/*!*/;
 88 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 89 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E'
 90 /*!*/;
 91 # at 1397
 92 #180809 10:50:26 server id 1023308  end_log_pos 1462 CRC32 0x9e027e44     GTID    last_committed=5    sequence_number=6    rbr_only=yes
 93 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 94 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:25'/*!*/;
 95 # at 1462
 96 #180809 10:50:26 server id 1023308  end_log_pos 1538 CRC32 0xe5d003cf     Query    thread_id=8    exec_time=0    error_code=0
 97 SET TIMESTAMP=1533804626/*!*/;
 98 SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
 99 BEGIN
100 /*!*/;
101 # at 1538
102 # at 1594
103 #180809 10:50:26 server id 1023308  end_log_pos 1641 CRC32 0x81684c91     Table_map: `zlm`.`t1` mapped to number 110
104 # at 1641
105 #180809 10:50:26 server id 1023308  end_log_pos 1687 CRC32 0x19b42ee8     Write_rows: table id 110 flags: STMT_END_F
106 ### INSERT INTO `zlm`.`t1`
107 ### SET
108 ###   @1=4
109 ###   @2='Redis'
110 # at 1687
111 #180809 10:50:26 server id 1023308  end_log_pos 1718 CRC32 0x3429fa99     Xid = 28
112 COMMIT/*!*/;
113 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
114 DELIMITER ;
115 # End of log file
116 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
117 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
118 
119 [root@zlm3 11:00:48 /data/mysql/mysql3308/logs]
120 #ls -l
121 total 12
122 -rw-r----- 1 mysql mysql  194 Aug  9 04:09 mysql-bin.000022
123 -rw-r----- 1 mysql mysql 1718 Aug  9 10:50 mysql-bin.000023 //The 1718 bytes is right the position we need to decide which Xid we should set.
124 -rw-r----- 1 mysql mysql   88 Aug  9 04:09 mysql-bin.index
125 
126 //As there're no other transactions found in mysql-bin.000023.The last "Xid = 28" is what we need to set on node zlm4.

 

Modify the
grastate.dat file on node zlm4(if not exist,touch a new file of
it).

 1 [root@zlm4 11:07:38 /data/mysql/mysql3308/data]
 2 #vim grastate.dat
 3 
 4 [root@zlm4 11:08:01 /data/mysql/mysql3308/data]
 5 #cat grastate.dat 
 6 #cat grastate.dat
 7 # GALERA saved state
 8 version: 2.1
 9 uuid:    bd5525ab-9a15-11e8-aa0f-4b830c783fc7
10 seqno:   28
11 safe_to_bootstrap: 0
12 
13 [root@zlm4 11:08:42 /data/mysql/mysql3308/data]
14 #ls -l|grep grastate.dat 
15 -rw-r--r-- 1 root  root        131 Aug  9 11:08 grastate.dat
16 
17 [root@zlm4 11:08:03 /data/mysql/mysql3308/data]
18 #chown mysql.mysql grastate.dat 
19 
20 [root@zlm4 11:08:11 /data/mysql/mysql3308/data]
21 #ls -l|grep grastate.dat 
22 -rw-r--r-- 1 mysql mysql       131 Aug  9 11:08 grastate.dat

 

Modify the
my3308.cnf file to restore the parameter of PXC.

 1 [root@zlm4 11:10:28 /data/mysql/mysql3308]
 2 #vim my3308.cnf 
 3 
 4 [root@zlm4 11:11:03 /data/mysql/mysql3308]
 5 #cat my3308.cnf |grep wsrep
 6 loose-wsrep_cluster_name=pxc_wubx 
 7 loose-wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.1.103
 8 loose-wsrep_node_address=192.168.1.103
 9 loose-wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
10 #loose-wsrep_sst_method=xtrabackup-v2
11 loose-wsrep_sst_method=rsync
12 loose-wsrep_sst_auth=sst:zlmzlm
13 #loose-wsrep_debug=on
14 #loose-wsrep_provider_options="debug=on"

 

**Startup
the mysqld on node zlm4.**

  1 [root@zlm4 11:17:09 /data/mysql/mysql3308]
  2 #!ps
  3 ps aux|grep mysqld
  4 mysql     6514 12.0 19.1 1256144 194664 pts/3  Dl   11:17   0:00 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
  5 root      6539  0.0  0.0 112640   960 pts/3    R+   11:17   0:00 grep --color=auto mysqld
  6 
  7 //Check the error log for detail.
  8 [root@zlm4 11:17:01 /data/mysql/mysql3308/data]
  9 #tail -f error.log 
 10 
 11 2018-08-09T11:17:09.518383+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 12 2018-08-09T11:17:09.519740+01:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
 13 2018-08-09T11:17:09.519805+01:00 0 [Note] mysqld (mysqld 5.7.22-22-29.26-log) starting as process 6514 ...
 14 2018-08-09T11:17:09.522792+01:00 0 [Note] WSREP: Setting wsrep_ready to false
 15 2018-08-09T11:17:09.522812+01:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
 16 2018-08-09T11:17:09.522818+01:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
 17 2018-08-09T11:17:09.528196+01:00 0 [Note] WSREP: wsrep_load(): Galera 3.26(r) by Codership Oy <info@codership.com> loaded successfully.
 18 2018-08-09T11:17:09.528328+01:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
 19 2018-08-09T11:17:09.528860+01:00 0 [Note] WSREP: Found saved state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:28, safe_to_bootstrap: 0
 20 2018-08-09T11:17:09.534206+01:00 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/mysql3308/data/; base_host = 192.168.1.103; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /data/mysql/mysql3308/data/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/mysql3308/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = 1; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 8; socket.checksum = 2; socket.recv_buf_size = 212992; 
 21 2018-08-09T11:17:09.547103+01:00 0 [Note] WSREP: GCache history reset: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:28
 22 2018-08-09T11:17:09.551477+01:00 0 [Note] WSREP: Assign initial position for certification: 28, protocol version: -1
 23 2018-08-09T11:17:09.551517+01:00 0 [Note] WSREP: Preparing to initiate SST/IST
 24 2018-08-09T11:17:09.551524+01:00 0 [Note] WSREP: Starting replication
 25 2018-08-09T11:17:09.551542+01:00 0 [Note] WSREP: Setting initial position to bd5525ab-9a15-11e8-aa0f-4b830c783fc7:28
 26 2018-08-09T11:17:09.551666+01:00 0 [Note] WSREP: Using CRC-32C for message checksums.
 27 2018-08-09T11:17:09.551752+01:00 0 [Note] WSREP: gcomm thread scheduling priority set to other:0 
 28 2018-08-09T11:17:09.551861+01:00 0 [Warning] WSREP: Fail to access the file (/data/mysql/mysql3308/data//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
 29 2018-08-09T11:17:09.551870+01:00 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
 30 2018-08-09T11:17:09.552488+01:00 0 [Note] WSREP: GMCast version 0
 31 2018-08-09T11:17:09.553245+01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
 32 2018-08-09T11:17:09.553297+01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
 33 2018-08-09T11:17:09.553883+01:00 0 [Note] WSREP: EVS version 0
 34 2018-08-09T11:17:09.554031+01:00 0 [Note] WSREP: gcomm: connecting to group 'pxc_wubx', peer '192.168.1.101:,192.168.1.102:,192.168.1.103:'
 35 2018-08-09T11:17:09.555884+01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') connection established to fe2c0efc tcp://192.168.1.103:4567
 36 2018-08-09T11:17:09.555917+01:00 0 [Warning] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.103:4567' points to own listening address, blacklisting
 37 2018-08-09T11:17:09.557320+01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') connection established to 13eae368 tcp://192.168.1.101:4567
 38 2018-08-09T11:17:09.557398+01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 39 2018-08-09T11:17:09.558019+01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') connection established to 23fb8f7a tcp://192.168.1.102:4567
 40 2018-08-09T11:17:09.789760+01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
 41 2018-08-09T11:17:09.789795+01:00 0 [Note] WSREP: declaring 23fb8f7a at tcp://192.168.1.102:4567 stable
 42 2018-08-09T11:17:10.791694+01:00 0 [Note] WSREP: Node 13eae368 state primary
 43 2018-08-09T11:17:10.793677+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 44 view (view_id(PRIM,13eae368,15)
 45 memb {
 46     13eae368,0
 47     23fb8f7a,0
 48     fe2c0efc,0
 49     }
 50 joined {
 51     }
 52 left {
 53     }
 54 partitioned {
 55     }
 56 )
 57 2018-08-09T11:17:10.793720+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 58 2018-08-09T11:17:11.055114+01:00 0 [Note] WSREP: gcomm: connected
 59 2018-08-09T11:17:11.055201+01:00 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
 60 2018-08-09T11:17:11.055278+01:00 0 [Note] WSREP: Waiting for SST/IST to complete.
 61 2018-08-09T11:17:11.055571+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3
 62 2018-08-09T11:17:11.055600+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
 63 2018-08-09T11:17:11.055637+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: fee39480-9bb4-11e8-a6f3-7b3d75fc5b99
 64 2018-08-09T11:17:11.055645+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: fee39480-9bb4-11e8-a6f3-7b3d75fc5b99 from 0 (zlm2)
 65 2018-08-09T11:17:11.055653+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: fee39480-9bb4-11e8-a6f3-7b3d75fc5b99 from 1 (zlm3)
 66 2018-08-09T11:17:11.061999+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: fee39480-9bb4-11e8-a6f3-7b3d75fc5b99 from 2 (zlm4)
 67 2018-08-09T11:17:11.062036+01:00 0 [Note] WSREP: Quorum results:
 68     version    = 4,
 69     component  = PRIMARY,
 70     conf_id    = 14,
 71     members    = 3/3 (primary/total),
 72     act_id     = 28,
 73     last_appl. = -1,
 74     protocols  = 0/8/3 (gcs/repl/appl),
 75     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 76 2018-08-09T11:17:11.062046+01:00 0 [Note] WSREP: Flow-control interval: [173, 173]
 77 2018-08-09T11:17:11.062050+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
 78 2018-08-09T11:17:11.062055+01:00 0 [Note] WSREP: Restored state OPEN -> JOINED (28)
 79 2018-08-09T11:17:11.062282+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
 80 2018-08-09T11:17:11.062308+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:28, view# 15: Primary, number of nodes: 3, my index: 2, protocol version 3
 81 2018-08-09T11:17:11.062314+01:00 2 [Note] WSREP: Setting wsrep_ready to true
 82 2018-08-09T11:17:11.062329+01:00 0 [Note] WSREP: SST complete, seqno: 28
 83 2018-08-09T11:17:11.063803+01:00 0 [Note] WSREP: Member 2.0 (zlm4) synced with group.
 84 2018-08-09T11:17:11.063831+01:00 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 28)
 85 2018-08-09T11:17:11.066318+01:00 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
 86 2018-08-09T11:17:11.066413+01:00 0 [Note] InnoDB: PUNCH HOLE support available
 87 2018-08-09T11:17:11.066426+01:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 88 2018-08-09T11:17:11.066439+01:00 0 [Note] InnoDB: Uses event mutexes
 89 2018-08-09T11:17:11.066444+01:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
 90 2018-08-09T11:17:11.066448+01:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
 91 2018-08-09T11:17:11.066453+01:00 0 [Note] InnoDB: Using Linux native AIO
 92 2018-08-09T11:17:11.066471+01:00 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 4 to 1 since innodb_buffer_pool_size is less than 1024 MiB
 93 2018-08-09T11:17:11.067081+01:00 0 [Note] InnoDB: Number of pools: 1
 94 2018-08-09T11:17:11.067235+01:00 0 [Note] InnoDB: Using CPU crc32 instructions
 95 2018-08-09T11:17:11.068926+01:00 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
 96 2018-08-09T11:17:11.071673+01:00 0 [Note] InnoDB: Completed initialization of buffer pool
 97 2018-08-09T11:17:11.074414+01:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 98 2018-08-09T11:17:11.093895+01:00 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /data/mysql/mysql3308/data/xb_doublewrite
 99 2018-08-09T11:17:11.095767+01:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
100 2018-08-09T11:17:11.229162+01:00 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/mysql3308/data/xb_doublewrite, size 3932160 bytes
101 2018-08-09T11:17:11.369285+01:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
102 2018-08-09T11:17:11.369369+01:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
103 2018-08-09T11:17:11.773899+01:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
104 2018-08-09T11:17:11.774714+01:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
105 2018-08-09T11:17:11.774734+01:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
106 2018-08-09T11:17:11.775107+01:00 0 [Note] InnoDB: Waiting for purge to start
107 2018-08-09T11:17:11.845618+01:00 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.22-rel22 started; log sequence number 2641145
108 2018-08-09T11:17:11.845884+01:00 0 [Note] Plugin 'FEDERATED' is disabled.
109 2018-08-09T11:17:11.848626+01:00 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3308/data/ib_buffer_pool
110 2018-08-09T11:17:11.882535+01:00 0 [Note] InnoDB: Buffer pool(s) load completed at 180809 11:17:11
111 2018-08-09T11:17:11.907836+01:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
112 2018-08-09T11:17:11.907856+01:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
113 2018-08-09T11:17:11.908353+01:00 0 [Warning] CA certificate ca.pem is self signed.
114 2018-08-09T11:17:11.908405+01:00 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
115 2018-08-09T11:17:11.908482+01:00 0 [Note] Server hostname (bind-address): '*'; port: 3308
116 2018-08-09T11:17:11.908505+01:00 0 [Note] IPv6 is available.
117 2018-08-09T11:17:11.908513+01:00 0 [Note]   - '::' resolves to '::';
118 2018-08-09T11:17:11.908527+01:00 0 [Note] Server socket created on IP: '::'.
119 2018-08-09T11:17:11.910446+01:00 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
120 2018-08-09T11:17:11.910481+01:00 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
121 2018-08-09T11:17:11.910489+01:00 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
122 2018-08-09T11:17:11.910495+01:00 0 [Warning] 'user' entry 'sst@localhost' ignored in --skip-name-resolve mode.
123 2018-08-09T11:17:11.910512+01:00 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
124 2018-08-09T11:17:11.910516+01:00 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
125 2018-08-09T11:17:11.910522+01:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
126 2018-08-09T11:17:11.911126+01:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
127 2018-08-09T11:17:11.911142+01:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
128 2018-08-09T11:17:11.913579+01:00 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=zlm4-relay-bin' to avoid this problem.
129 2018-08-09T11:17:11.925857+01:00 0 [Note] Event Scheduler: Loaded 0 events
130 2018-08-09T11:17:11.926371+01:00 0 [Note] mysqld: ready for connections.
131 Version: '5.7.22-22-29.26-log'  socket: '/tmp/mysql3308.sock'  port: 3308  Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
132 2018-08-09T11:17:11.930200+01:00 2 [Note] WSREP: Initialized wsrep sidno 2
133 2018-08-09T11:17:11.930223+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 3) (Increment: 1 -> 3)
134 2018-08-09T11:17:11.930233+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
135 2018-08-09T11:17:11.930250+01:00 2 [Note] WSREP: Assign initial position for certification: 28, protocol version: 3
136 2018-08-09T11:17:11.930317+01:00 0 [Note] WSREP: Service thread queue flushed.
137 2018-08-09T11:17:11.930499+01:00 2 [Note] WSREP: Synchronized with group, ready for connections
138 2018-08-09T11:17:11.930505+01:00 2 [Note] WSREP: Setting wsrep_ready to true
139 2018-08-09T11:17:11.930507+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140 2018-08-09T11:17:13.056460+01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') turning message relay requesting off

 

**Check
the status of PXC and the data of table t1.**

 1 (zlm@192.168.1.103 3308)[(none)]>show global status like '%wsrep%';
 2 +----------------------------------+----------------------------------------------------------+
 3 | Variable_name                    | Value                                                    |
 4 +----------------------------------+----------------------------------------------------------+
 5 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
 6 | wsrep_protocol_version           | 8                                                        |
 7 | wsrep_last_applied               | 28                                                       |
 8 | wsrep_last_committed             | 28                                                       |
 9 | wsrep_replicated                 | 0                                                        |
10 | wsrep_replicated_bytes           | 0                                                        |
11 | wsrep_repl_keys                  | 0                                                        |
12 | wsrep_repl_keys_bytes            | 0                                                        |
13 | wsrep_repl_data_bytes            | 0                                                        |
14 | wsrep_repl_other_bytes           | 0                                                        |
15 | wsrep_received                   | 2                                                        |
16 | wsrep_received_bytes             | 279                                                      |
17 | wsrep_local_commits              | 0                                                        |
18 | wsrep_local_cert_failures        | 0                                                        |
19 | wsrep_local_replays              | 0                                                        |
20 | wsrep_local_send_queue           | 0                                                        |
21 | wsrep_local_send_queue_max       | 1                                                        |
22 | wsrep_local_send_queue_min       | 0                                                        |
23 | wsrep_local_send_queue_avg       | 0.000000                                                 |
24 | wsrep_local_recv_queue           | 0                                                        |
25 | wsrep_local_recv_queue_max       | 1                                                        |
26 | wsrep_local_recv_queue_min       | 0                                                        |
27 | wsrep_local_recv_queue_avg       | 0.000000                                                 |
28 | wsrep_local_cached_downto        | 0                                                        |
29 | wsrep_flow_control_paused_ns     | 0                                                        |
30 | wsrep_flow_control_paused        | 0.000000                                                 |
31 | wsrep_flow_control_sent          | 0                                                        |
32 | wsrep_flow_control_recv          | 0                                                        |
33 | wsrep_flow_control_interval      | [ 173, 173 ]                                             |
34 | wsrep_flow_control_interval_low  | 173                                                      |
35 | wsrep_flow_control_interval_high | 173                                                      |
36 | wsrep_flow_control_status        | OFF                                                      |
37 | wsrep_cert_deps_distance         | 0.000000                                                 |
38 | wsrep_apply_oooe                 | 0.000000                                                 |
39 | wsrep_apply_oool                 | 0.000000                                                 |
40 | wsrep_apply_window               | 0.000000                                                 |
41 | wsrep_commit_oooe                | 0.000000                                                 |
42 | wsrep_commit_oool                | 0.000000                                                 |
43 | wsrep_commit_window              | 0.000000                                                 |
44 | wsrep_local_state                | 4                                                        |
45 | wsrep_local_state_comment        | Synced                                                   |
46 | wsrep_cert_index_size            | 0                                                        |
47 | wsrep_cert_bucket_count          | 22                                                       |
48 | wsrep_gcache_pool_size           | 1320                                                     |
49 | wsrep_causal_reads               | 0                                                        |
50 | wsrep_cert_interval              | 0.000000                                                 |
51 | wsrep_ist_receive_status         |                                                          |
52 | wsrep_ist_receive_seqno_start    | 0                                                        |
53 | wsrep_ist_receive_seqno_current  | 0                                                        |
54 | wsrep_ist_receive_seqno_end      | 0                                                        |
55 | wsrep_incoming_addresses         | 192.168.1.101:3308,192.168.1.102:3308,192.168.1.103:3308 |
56 | wsrep_desync_count               | 0                                                        |
57 | wsrep_evs_delayed                |                                                          |
58 | wsrep_evs_evict_list             |                                                          |
59 | wsrep_evs_repl_latency           | 0/0/0/0/0                                                |
60 | wsrep_evs_state                  | OPERATIONAL                                              |
61 | wsrep_gcomm_uuid                 | fe2c0efc-9bb4-11e8-82b0-6b01a1a0030d                     |
62 | wsrep_cluster_conf_id            | 15                                                       |
63 | wsrep_cluster_size               | 3                                                        | //The size of PXC turned out to be three again.
64 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
65 | wsrep_cluster_status             | Primary                                                  |
66 | wsrep_connected                  | ON                                                       |
67 | wsrep_local_bf_aborts            | 0                                                        |
68 | wsrep_local_index                | 2                                                        |
69 | wsrep_provider_name              | Galera                                                   |
70 | wsrep_provider_vendor            | Codership Oy <info@codership.com>                        |
71 | wsrep_provider_version           | 3.26(r)                                                  |
72 | wsrep_ready                      | ON                                                       |
73 +----------------------------------+----------------------------------------------------------+
74 68 rows in set (0.00 sec)
75 
76 (zlm@192.168.1.103 3308)[(none)]>select * from zlm.t1;
77 +----+------------+
78 | id | name       |
79 +----+------------+
80 |  1 | MySQL      |
81 |  2 | Oracle     |
82 |  3 | PostgreSQL |
83 |  4 | Redis      |
84 +----+------------+
85 4 rows in set (0.01 sec)
86 
87 //The fourth record of "Redis" has been shown correctly on node zlm4.

 

**Reset
slave replication information on node zlm4.(optional)**

 1 (zlm@192.168.1.103 3308)[(none)]>show slave status\G
 2 *************************** 1. row ***************************
 3                Slave_IO_State: 
 4                   Master_Host: 192.168.1.102
 5                   Master_User: repl
 6                   Master_Port: 3308
 7                 Connect_Retry: 60
 8               Master_Log_File: mysql-bin.000023
 9           Read_Master_Log_Pos: 1718
10                Relay_Log_File: zlm4-relay-bin.000003
11                 Relay_Log_Pos: 775
12         Relay_Master_Log_File: mysql-bin.000023
13              Slave_IO_Running: No
14             Slave_SQL_Running: No
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 1718
25               Relay_Log_Space: 2831
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: NULL
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 0
38                 Last_IO_Error: 
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 0
43                   Master_UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e
44              Master_Info_File: /data/mysql/mysql3308/data/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: 
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:20-25
55             Executed_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:1-25
56                 Auto_Position: 1
57          Replicate_Rewrite_DB: 
58                  Channel_Name: 
59            Master_TLS_Version: 
60 1 row in set (0.00 sec)
61 
62 (zlm@192.168.1.103 3308)[(none)]>reset slave all;
63 Query OK, 0 rows affected (0.03 sec)
64 
65 (zlm@192.168.1.103 3308)[(none)]>show slave status\G
66 Empty set (0.00 sec)
67 
68 //This step is merely used to make the environment clean and tidy.It's not necessary.

 

相关文章