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.

 

网站地图xml地图