欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL高可用之PXC安装部署

程序员文章站 2022-04-28 18:43:06
Preface Today,I'm gonna implement a PXC,Let's see the procedure. Framework Hostname IP Port OS Version MySQL Version Xtrabackup version zlm2 192.168.1 ......
 
Preface
 
    Today,I'm gonna implement a PXC,Let's see the procedure.
 
Framework
 
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)
 
Procedure
 
    The official website of PXC:
    
 
    Choose the Linux-Generic software.There're three version relevant with openssl(100,101,102),I choose the ssl102 version.
 
Install Percona-XtraDB-Cluster software on three nodes.
 1 [root@zlm2 07:56:03 ~]
 2 #mkdir -p /opt/mysql
 3 
 4 [root@zlm2 07:56:51 ~]
 5 #ls -l|grep Percona
 6 -rwxr-xr-x   1 root root 252395830 Aug  7 07:54 Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar.gz
 7 
 8 [root@zlm2 07:57:01 ~]
 9 #gzip -d Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar.gz 
10 
11 [root@zlm2 07:57:59 ~]
12 #ls -l|grep Percona
13 -rwxr-xr-x   1 root root 1035939840 Aug  7 07:54 Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar
14 
15 [root@zlm2 07:59:36 ~]
16 #tar xf Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar -C /opt/mysql
17 
18 [root@zlm2 08:02:29 ~]
19 #cd /usr/local
20 
21 [root@zlm2 08:02:22 /usr/local]
22 #ln -s /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/ mysql
23 
24 [root@zlm2 08:02:28 /usr/local]
25 #ls -l
26 total 4
27 drwxr-xr-x. 2 root   root    105 May  7 10:44 bin
28 drwxr-xr-x. 2 root   root      6 Jun 10  2014 etc
29 drwxr-xr-x. 2 root   root      6 Jun 10  2014 games
30 drwxr-xr-x. 2 root   root      6 Jun 10  2014 include
31 drwxr-xr-x. 2 root   root      6 Jun 10  2014 lib
32 drwxr-xr-x. 3 root   root     18 May  7 09:37 lib64
33 drwxr-xr-x. 2 root   root      6 Jun 10  2014 libexec
34 lrwxrwxrwx  1 root   root     36 Apr 28 14:30 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64/
35 drwxr-xr-x  9 root   root   4096 Aug  7 10:00 mysql-5.7.21-linux-glibc2.12-x86_64
36 drwxrwxr-x  5 root   root     94 May  7 09:28 mysql_performance_monitor-agent
37 drwxr-xr-x. 2 root   root     18 Apr 28 16:06 sbin
38 drwxr-xr-x. 6 root   root     58 May  7 09:37 share
39 drwxr-xr-x. 2 root   root      6 Jun 10  2014 src
40 drwxr-xr-x  8 zabbix zabbix   70 Apr 28 16:45 zabbix
41 
42 [root@zlm2 08:02:32 /usr/local]
43 #rm -f mysql
44 
45 [root@zlm2 08:02:37 /usr/local]
46 #ln -s /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/ mysql
47 
48 [root@zlm2 08:03:04 /usr/local]
49 #ls -l
50 total 0
51 drwxr-xr-x. 2 root root  21 Jul  4 03:21 bin
52 drwxr-xr-x. 2 root root   6 Jun 10  2014 etc
53 drwxr-xr-x. 2 root root   6 Jun 10  2014 games
54 drwxr-xr-x. 2 root root   6 Jun 10  2014 include
55 drwxr-xr-x. 2 root root   6 Jun 10  2014 lib
56 drwxr-xr-x. 2 root root   6 Jun 10  2014 lib64
57 drwxr-xr-x. 2 root root   6 Jun 10  2014 libexec
58 lrwxrwxrwx  1 root root  75 Aug  7 08:23 mysql -> /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/
59 drwxr-xr-x  9 root root 120 Aug  7 08:22 mysql-5.7.21-linux-glibc2.12-x86_64
60 drwxr-xr-x. 2 root root   6 Jun 10  2014 sbin
61 drwxr-xr-x. 7 root root  71 Jul  4 03:21 share
62 drwxr-xr-x. 2 root root   6 Jun 10  2014 src
63 
64 [root@zlm2 08:32:56 /usr/local]
65 #ldd mysql/bin/mysqld
66     linux-vdso.so.1 =>  (0x00007fff96f3f000)
67     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1119136000)
68     libaio.so.1 => /lib64/libaio.so.1 (0x00007f1118f34000)
69     libnuma.so.1 => /lib64/libnuma.so.1 (0x00007f1118d27000)
70     libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f1118af0000)
71     libssl.so.10 => /lib64/libssl.so.10 (0x00007f1118883000)
72     libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f111849b000)
73     libdl.so.2 => /lib64/libdl.so.2 (0x00007f1118297000)
74     libz.so.1 => /lib64/libz.so.1 (0x00007f1118081000)
75     librt.so.1 => /lib64/librt.so.1 (0x00007f1117e78000)
76     libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f1117b71000)
77     libm.so.6 => /lib64/libm.so.6 (0x00007f111786f000)
78     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f1117658000)
79     libc.so.6 => /lib64/libc.so.6 (0x00007f1117297000)
80     /lib64/ld-linux-x86-64.so.2 (0x00007f1119359000)
81     libfreebl3.so => /lib64/libfreebl3.so (0x00007f1117094000)
82     libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f1116e47000)
83     libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f1116b64000)
84     libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f1116960000)
85     libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f111672d000)
86     libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f111651e000)
87     libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f1116319000)
88     libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f11160ff000)
89     libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f1115eda000)
90     libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f1115c78000)
91     liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f1115a53000)
92     
93 ... //Omitted the procedure of installation on the other nodes.

 

Initialized a PXC node with proper pxc relevant parameters below.
 1 [root@zlm2 08:51:49 ~]
 2 #mkdir -p /data/mysql/mysql3308/{data,logs,tmp}
 3 
 4 [root@zlm2 08:52:44 ~]
 5 #chown -R mysql.mysql /data
 6 
 7 [root@zlm2 08:53:27 ~]
 8 #cp /vagrant/my3308.cnf /data/mysql/mysql3308
 9 
10 [root@zlm2 08:54:09 ~]
11 #cd /data/mysql/mysql3308
12 
13 [root@zlm2 08:54:17 /data/mysql/mysql3308]
14 #ls -l
15 total 8
16 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 data
17 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 logs
18 -rwxr-xr-x 1 root  root  4522 Aug  7 08:54 my3308.cnf
19 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 tmp
20 
21 [root@zlm2 08:54:30 /data/mysql/mysql3308]
22 #tail -20 my3308.cnf 
23 ...
24 #pxc
25 innodb_locks_unsafe_for_binlog=1
26 innodb_autoinc_lock_mode=2
27 loose-wsrep_cluster_name=pxc_wubx 
28 loose-wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.103
29 loose-wsrep_node_address=192.168.1.101
30 loose-wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
31 loose-wsrep_sst_method=xtrabackup-v2
32 #loose-wsrep_sst_method=rsync
33 loose-wsrep_sst_auth=sst:zlmzlm
34 #loose-wsrep_debug=on
35 #loose-wsrep_provider_options="debug=on"
36 [root@zlm2 08:54:38 /data/mysql/mysql3308]
37 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --initialize
38 
39 [root@zlm2 09:00:56 /data/mysql/mysql3308]
40 #tail ./data/error.log 
41 2018-08-07T09:00:47.560965+01:00 0 [Warning] CA certificate ca.pem is self signed.
42 2018-08-07T09:00:47.603271+01:00 1 [Note] A temporary password is generated for root@localhost: bY2jmV=k)ltM //This is the password we need when first login.
43 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
44 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
45 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
46 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
47 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
48 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
49 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
50 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
51 
52 [root@zlm2 09:02:45 /data/mysql/mysql3308]
53 #cd data
54 
55 [root@zlm2 09:02:57 /data/mysql/mysql3308/data]
56 #ls -l
57 total 409676
58 -rw-r----- 1 mysql mysql        56 Aug  7 09:00 auto.cnf
59 -rw------- 1 mysql mysql      1680 Aug  7 09:00 ca-key.pem
60 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 ca.pem
61 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 client-cert.pem
62 -rw------- 1 mysql mysql      1680 Aug  7 09:00 client-key.pem
63 -rw-r----- 1 mysql mysql      2354 Aug  7 09:00 error.log
64 -rw-r----- 1 mysql mysql       423 Aug  7 09:00 ib_buffer_pool
65 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ibdata1
66 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile0
67 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile1
68 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile2
69 drwxr-x--- 2 mysql mysql      4096 Aug  7 09:00 mysql
70 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 performance_schema
71 -rw------- 1 mysql mysql      1676 Aug  7 09:00 private_key.pem
72 -rw-r--r-- 1 mysql mysql       452 Aug  7 09:00 public_key.pem
73 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 server-cert.pem
74 -rw------- 1 mysql mysql      1676 Aug  7 09:00 server-key.pem
75 -rw-r----- 1 mysql mysql       227 Aug  7 09:00 slow.log
76 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 sys
77 
78 //Omitted the operations on the other nodes.Notice that there's no need to initialize the other node.

 

Start mysqld of PXC on zlm2 as the first node in PXC.
  1 [root@zlm2 09:21:46 /data/mysql/mysql3308/data]
  2 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster &
  3 [1] 6251
  4 
  5 [root@zlm2 09:22:57 /data/mysql/mysql3308/data]
  6 #tail ../data/error.log |grep ERROR
  7 2018-08-07T09:22:57.471628+01:00 0 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/local/mysql/lib/libgalera_smm.so: symbol SSL_COMP_free_compression_methods, version libssl.so.10 not defined in file libssl.so.10 with link time reference
  8 2018-08-07T09:22:57.471657+01:00 0 [ERROR] WSREP: wsrep_load() failed to load the provider('/usr/local/mysql/lib/libgalera_smm.so'): Invalid argument (22). Need to abort.
  9 2018-08-07T09:22:57.471661+01:00 0 [ERROR] Aborting
 10 [1]+  Exit 1                  mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
 11 
 12 [root@zlm2 09:23:19 /data/mysql/mysql3308/data]
 13 #cd /usr/local/mysql/lib
 14 
 15 [root@zlm2 09:26:28 /usr/local/mysql/lib]
 16 #ls -l|grep libgalera_smm.so
 17 -rwxr-xr-x 1 root root 35086192 Jun 29 05:44 libgalera_smm.so
 18 
 19 [root@zlm2 09:26:38 /usr/local/mysql/lib]
 20 #rpm -qa|grep openssl
 21 openssl-libs-1.0.1e-42.el7.9.x86_64
 22 openssl-devel-1.0.1e-42.el7.9.x86_64
 23 openssl-1.0.1e-42.el7.9.x86_64
 24 
 25 [root@zlm2 09:32:25 /usr/local/mysql/lib]
 26 #cd /usr/lib64
 27 
 28 [root@zlm2 09:34:03 /usr/lib64]
 29 #ls -l|grep libssl
 30 -rwxr-xr-x.  1 root root  255888 Mar  6  2015 libssl3.so
 31 lrwxrwxrwx   1 root root      16 Jul 16  2015 libssl.so -> libssl.so.1.0.1e
 32 lrwxrwxrwx   1 root root      16 Jul 16  2015 libssl.so.10 -> libssl.so.1.0.1e
 33 -rwxr-xr-x   1 root root  449864 Jun 29  2015 libssl.so.1.0.1e
 34 
 35 [root@zlm2 09:41:02 /usr/lib64]
 36 #yum install openssl
 37 ... //Omitted.
 38 
 39 Updated:
 40   openssl.x86_64 1:1.0.2k-12.el7                                                                                                                     
 41 
 42 Dependency Updated:
 43   openssl-devel.x86_64 1:1.0.2k-12.el7                                      openssl-libs.x86_64 1:1.0.2k-12.el7                                     
 44 
 45 Complete!
 46 
 47 [root@zlm2 09:44:39 /usr/lib64]
 48 #tail /data/mysql/mysql3308/data/error.log |grep ERROR
 49 
 50 [root@zlm2 09:45:15 /usr/lib64]
 51 #ps aux|grep mysqld
 52 mysql     6345  2.9 20.1 1556468 204812 pts/0  Sl   09:44   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
 53 root      6390  0.0  0.0 112640   960 pts/0    R+   09:45   0:00 grep --color=auto mysqld
 54 
 55 [root@zlm2 09:46:00 /usr/lib64]
 56 #cat /data/mysql/mysql3308/data/error.log |grep password
 57 2018-08-07T09:00:47.603271+01:00 1 [Note] A temporary password is generated for root@localhost: bY2jmV=k)ltM
 58 
 59 [root@zlm2 09:47:28 /usr/lib64]
 60 #mysql -uroot -p -S /tmp/mysql3308.sock
 61 Enter password: 
 62 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.101' (111)
 63 
 64 [root@zlm2 09:48:11 /usr/lib64]
 65 #ps aux|grep mysqld
 66 mysql     6345  0.6 20.1 1556468 204812 pts/0  Sl   09:44   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
 67 root      6433  0.0  0.0 112640   960 pts/0    R+   09:48   0:00 grep --color=auto mysqld
 68 
 69 [root@zlm2 09:57:21 /usr/lib64]
 70 #mysql -uroot -p -hlocalhost -S /tmp/mysql3308.sock
 71 Enter password: 
 72 Welcome to the MySQL monitor.  Commands end with ; or \g.
 73 Your MySQL connection id is 4
 74 Server version: 5.7.22-22-29.26-log
 75 
 76 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 77 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 78 
 79 Oracle is a registered trademark of Oracle Corporation and/or its
 80 affiliates. Other names may be trademarks of their respective
 81 owners.
 82 
 83 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 84 
 85 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to zlm@'192.168.1.%';
 86 ERROR 1133 (42000): Can't find any matching row in the user table
 87 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to zlm@'192.168.1.%' identified by 'zlmzlm';
 88 Query OK, 0 rows affected, 1 warning (0.34 sec)
 89 
 90 (root@localhost mysql3308.sock)[(none)]>grant reload,lock tables,replication client on *.* to sst@localhost identified by 'zlmzlm';
 91 Query OK, 0 rows affected, 2 warnings (0.03 sec)
 92 
 93 (root@localhost mysql3308.sock)[(none)]>select user,host from mysql.user;
 94 +---------------+-------------+
 95 | user          | host        |
 96 +---------------+-------------+
 97 | zlm           | 192.168.1.% |
 98 | mysql.session | localhost   |
 99 | mysql.sys     | localhost   |
100 | root          | localhost   |
101 | sst           | localhost   |
102 +---------------+-------------+
103 5 rows in set (0.00 sec)
104 
105 (root@localhost mysql3308.sock)[(none)]>show global status like '%wsrep%';
106 +----------------------------------+--------------------------------------+
107 | Variable_name                    | Value                                |
108 +----------------------------------+--------------------------------------+
109 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7 |
110 | wsrep_protocol_version           | 8                                    |
111 | wsrep_last_applied               | 2                                    |
112 | wsrep_last_committed             | 2                                    |
113 | wsrep_replicated                 | 2                                    |
114 | wsrep_replicated_bytes           | 432                                  |
115 | wsrep_repl_keys                  | 2                                    |
116 | wsrep_repl_keys_bytes            | 64                                   |
117 | wsrep_repl_data_bytes            | 232                                  |
118 | wsrep_repl_other_bytes           | 0                                    |
119 | wsrep_received                   | 2                                    |
120 | wsrep_received_bytes             | 141                                  |
121 | wsrep_local_commits              | 0                                    |
122 | wsrep_local_cert_failures        | 0                                    |
123 | wsrep_local_replays              | 0                                    |
124 | wsrep_local_send_queue           | 0                                    |
125 | wsrep_local_send_queue_max       | 1                                    |
126 | wsrep_local_send_queue_min       | 0                                    |
127 | wsrep_local_send_queue_avg       | 0.000000                             |
128 | wsrep_local_recv_queue           | 0                                    |
129 | wsrep_local_recv_queue_max       | 2                                    |
130 | wsrep_local_recv_queue_min       | 0                                    |
131 | wsrep_local_recv_queue_avg       | 0.500000                             |
132 | wsrep_local_cached_downto        | 1                                    |
133 | wsrep_flow_control_paused_ns     | 0                                    |
134 | wsrep_flow_control_paused        | 0.000000                             |
135 | wsrep_flow_control_sent          | 0                                    |
136 | wsrep_flow_control_recv          | 0                                    |
137 | wsrep_flow_control_interval      | [ 100, 100 ]                         |
138 | wsrep_flow_control_interval_low  | 100                                  |
139 | wsrep_flow_control_interval_high | 100                                  |
140 | wsrep_flow_control_status        | OFF                                  |
141 | wsrep_cert_deps_distance         | 1.000000                             |
142 | wsrep_apply_oooe                 | 0.000000                             |
143 | wsrep_apply_oool                 | 0.000000                             |
144 | wsrep_apply_window               | 1.000000                             |
145 | wsrep_commit_oooe                | 0.000000                             |
146 | wsrep_commit_oool                | 0.000000                             |
147 | wsrep_commit_window              | 1.000000                             |
148 | wsrep_local_state                | 4                                    |
149 | wsrep_local_state_comment        | Synced                               |
150 | wsrep_cert_index_size            | 1                                    |
151 | wsrep_cert_bucket_count          | 22                                   |
152 | wsrep_gcache_pool_size           | 1832                                 |
153 | wsrep_causal_reads               | 0                                    |
154 | wsrep_cert_interval              | 0.000000                             |
155 | wsrep_ist_receive_status         |                                      |
156 | wsrep_ist_receive_seqno_start    | 0                                    |
157 | wsrep_ist_receive_seqno_current  | 0                                    |
158 | wsrep_ist_receive_seqno_end      | 0                                    |
159 | wsrep_incoming_addresses         | 192.168.1.101:3308                   |
160 | wsrep_desync_count               | 0                                    |
161 | wsrep_evs_delayed                |                                      |
162 | wsrep_evs_evict_list             |                                      |
163 | wsrep_evs_repl_latency           | 0/0/0/0/0                            |
164 | wsrep_evs_state                  | OPERATIONAL                          |
165 | wsrep_gcomm_uuid                 | bd54497d-9a15-11e8-b386-2759969fcd01 |
166 | wsrep_cluster_conf_id            | 1                                    |
167 | wsrep_cluster_size               | 1                                    |
168 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7 |
169 | wsrep_cluster_status             | Primary                              |
170 | wsrep_connected                  | ON                                   |
171 | wsrep_local_bf_aborts            | 0                                    |
172 | wsrep_local_index                | 0                                    |
173 | wsrep_provider_name              | Galera                               |
174 | wsrep_provider_vendor            | Codership Oy <info@codership.com>    |
175 | wsrep_provider_version           | 3.26(r)                              |
176 | wsrep_ready                      | ON                                   |
177 +----------------------------------+--------------------------------------+
178 68 rows in set (0.00 sec)
179 
180 //Check the below global status of PXC.
181 | wsrep_local_state                | 4                                    |
182 | wsrep_local_state_comment        | Synced                               |
183 | wsrep_cluster_status             | Primary                              |
184 | wsrep_connected                  | ON                                   |
185 
186 //It means that the PXC has been worked normally on node zlm2.
187 
188 //The partial error log of PXC which shows the success of starting up of it.
189 2018-08-07T09:44:40.631123+01:00 0 [Note] Event Scheduler: Loaded 0 events
190 2018-08-07T09:44:40.631550+01:00 0 [Note] mysqld: ready for connections.
191 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
192 2018-08-07T09:44:40.635469+01:00 2 [Note] WSREP: Initialized wsrep sidno 2
193 2018-08-07T09:44:40.635501+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 1) (Increment: 1 -> 1)
194 2018-08-07T09:44:40.635510+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
195 2018-08-07T09:44:40.635525+01:00 2 [Note] WSREP: Assign initial position for certification: 0, protocol version: 3
196 2018-08-07T09:44:40.635540+01:00 0 [Note] WSREP: Service thread queue flushed.
197 2018-08-07T09:44:40.635577+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:0
198 2018-08-07T09:44:40.637360+01:00 2 [Note] WSREP: Synchronized with group, ready for connections 
199 2018-08-07T09:44:40.637369+01:00 2 [Note] WSREP: Setting wsrep_ready to true
200 2018-08-07T09:44:40.637372+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
201 
202 //Check the files in datadir.
203 [root@zlm2 10:38:25 /data/mysql/mysql3308/data]
204 #ls -l
205 total 556916
206 -rw-r----- 1 mysql mysql        56 Aug  7 09:00 auto.cnf
207 -rw------- 1 mysql mysql      1680 Aug  7 09:00 ca-key.pem
208 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 ca.pem
209 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 client-cert.pem
210 -rw------- 1 mysql mysql      1680 Aug  7 09:00 client-key.pem
211 -rw-r----- 1 mysql mysql     24663 Aug  7 10:30 error.log
212 -rw-r----- 1 mysql mysql 134219048 Aug  7 10:30 galera.cache
213 -rw-r----- 1 mysql mysql       113 Aug  7 10:30 grastate.dat
214 -rw-r----- 1 mysql mysql       170 Aug  7 10:30 gvwstate.dat
215 -rw-r----- 1 mysql mysql       423 Aug  7 09:00 ib_buffer_pool
216 -rw-r----- 1 mysql mysql 104857600 Aug  7 10:16 ibdata1
217 -rw-r----- 1 mysql mysql 104857600 Aug  7 10:16 ib_logfile0
218 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile1
219 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile2
220 -rw-r----- 1 mysql mysql  12582912 Aug  7 09:44 ibtmp1
221 drwxr-x--- 2 mysql mysql      4096 Aug  7 09:00 mysql
222 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 performance_schema
223 -rw------- 1 mysql mysql      1676 Aug  7 09:00 private_key.pem
224 -rw-r--r-- 1 mysql mysql       452 Aug  7 09:00 public_key.pem
225 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 server-cert.pem
226 -rw------- 1 mysql mysql      1676 Aug  7 09:00 server-key.pem
227 -rw-r----- 1 mysql mysql       454 Aug  7 09:44 slow.log
228 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 sys
229 -rw-r----- 1 mysql mysql   3932160 Aug  7 10:16 xb_doublewrite
230 -rw-r----- 1 mysql mysql         5 Aug  7 09:44 zlm2.pid
231 
232 [root@zlm2 10:38:27 /data/mysql/mysql3308/data]
233 #cat grastate.dat
234 # GALERA saved state
235 version: 2.1
236 uuid:    bd5525ab-9a15-11e8-aa0f-4b830c783fc7
237 seqno:   -1
238 safe_to_bootstrap: 1 //This means the node is the first one who starts the cluster.

 

Start mysqld of PXC of the remaining nodes.
  1 //Let's start the PXC on node zlm3.
  2 [root@zlm3 10:00:12 /data/mysql/mysql3308/data]
  3 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
  4 [1] 4181
  5 
  6 [root@zlm3 10:30:43 /data/mysql/mysql3308/data]
  7 #ps aux|grep mysqld
  8 mysql     4181 20.0 16.9 756016 172580 pts/0   Sl   10:30   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
  9 root      4596  0.0  0.0 112640   960 pts/0    R+   10:30   0:00 grep --color=auto mysqld
 10 
 11 [root@zlm3 10:31:10 /data/mysql/mysql3308/data]
 12 #tail -1000 error.log 
 13 2018-08-07T09:54:08.825870+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 14 2018-08-07T09:54:08.825960+01:00 0 [Warning] WSREP: Node is running in bootstrap/initialize mode. Disabling pxc_strict_mode checks
 15 2018-08-07T09:54:08.836945+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
 16  100
 17  100
 18  100
 19  100
 20 2018-08-07T09:54:27.784997+01:00 0 [Warning] InnoDB: New log files created, LSN=45790
 21 2018-08-07T09:54:28.565405+01:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 22 2018-08-07T09:54:29.043051+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: 1ca6f9ae-9a17-11e8-9a7c-080027de0e0e.
 23 2018-08-07T09:54:29.054082+01:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 24 2018-08-07T09:54:29.471412+01:00 0 [Warning] CA certificate ca.pem is self signed.
 25 2018-08-07T09:54:29.536187+01:00 1 [Note] A temporary password is generated for root@localhost: ,M3=why-ok&d
 26 2018-08-07T09:55:01.556746+01:00 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
 27 2018-08-07T09:55:01.556802+01:00 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
 28 2018-08-07T09:55:01.556812+01:00 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
 29 2018-08-07T09:55:01.556830+01:00 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
 30 2018-08-07T09:55:01.556837+01:00 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
 31 2018-08-07T09:55:01.556847+01:00 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
 32 2018-08-07T09:55:01.556920+01:00 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
 33 2018-08-07T09:55:01.556931+01:00 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
 34 
 35 //The starting mysqld operation begins here.
 36 2018-08-07T10:30:43.794296+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 37 2018-08-07T10:30:43.801975+01:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
 38 2018-08-07T10:30:43.802040+01:00 0 [Note] mysqld (mysqld 5.7.22-22-29.26-log) starting as process 4181 ...
 39 2018-08-07T10:30:43.805420+01:00 0 [Note] WSREP: Setting wsrep_ready to false //Not ready.
 40 2018-08-07T10:30:43.805476+01:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
 41 2018-08-07T10:30:43.805486+01:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
 42 2018-08-07T10:30:43.829026+01:00 0 [Note] WSREP: wsrep_load(): Galera 3.26(r) by Codership Oy <info@codership.com> loaded successfully.
 43 2018-08-07T10:30:43.829070+01:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
 44 2018-08-07T10:30:43.829385+01:00 0 [Warning] WSREP: Could not open state file for reading: '/data/mysql/mysql3308/data//grastate.dat'
 45 2018-08-07T10:30:43.829395+01:00 0 [Warning] WSREP: No persistent state found. Bootstraping with default state
 46 2018-08-07T10:30:43.829432+01:00 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1
 47 2018-08-07T10:30:43.837823+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; 
 48 2018-08-07T10:30:43.861834+01:00 0 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 00000000-0000-0000-0000-000000000000:-1
 49 2018-08-07T10:30:43.866886+01:00 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
 50 2018-08-07T10:30:43.866919+01:00 0 [Note] WSREP: Preparing to initiate SST/IST //Begin to SST or IST.
 51 2018-08-07T10:30:43.866922+01:00 0 [Note] WSREP: Starting replication
 52 2018-08-07T10:30:43.866936+01:00 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
 53 2018-08-07T10:30:43.867102+01:00 0 [Note] WSREP: Using CRC-32C for message checksums.
 54 2018-08-07T10:30:43.867166+01:00 0 [Note] WSREP: gcomm thread scheduling priority set to other:0 
 55 2018-08-07T10:30:43.869814+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
 56 2018-08-07T10:30:43.869842+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
 57 2018-08-07T10:30:43.870450+01:00 0 [Note] WSREP: GMCast version 0
 58 2018-08-07T10:30:43.870721+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
 59 2018-08-07T10:30:43.870729+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
 60 2018-08-07T10:30:43.871017+01:00 0 [Note] WSREP: EVS version 0
 61 2018-08-07T10:30:43.871191+01:00 0 [Note] WSREP: gcomm: connecting to group 'pxc_wubx', peer '192.168.1.101:,192.168.1.102:,192.168.103:'
 62 2018-08-07T10:30:43.873170+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection established to 2cf33746 tcp://192.168.1.102:4567
 63 2018-08-07T10:30:43.873199+01:00 0 [Warning] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.102:4567' points to own listening address, blacklisting
 64 2018-08-07T10:30:43.873754+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection established to bd54497d tcp://192.168.1.101:4567
 65 2018-08-07T10:30:43.873806+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 66 2018-08-07T10:30:44.373051+01:00 0 [Note] WSREP: declaring bd54497d at tcp://192.168.1.101:4567 stable
 67 2018-08-07T10:30:44.373698+01:00 0 [Note] WSREP: Node bd54497d state primary
 68 2018-08-07T10:30:44.374205+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 69 view (view_id(PRIM,2cf33746,2)
 70 memb {
 71     2cf33746,0 //This is node zlm2,first one started the cluster.
 72     bd54497d,0 //This is node zlm3.
 73     }
 74 joined {
 75     }
 76 left {
 77     }
 78 partitioned {
 79     }
 80 )
 81 2018-08-07T10:30:44.374227+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 82 2018-08-07T10:30:44.374354+01:00 0 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.0.103:4567
 83 2018-08-07T10:30:44.374361+01:00 0 [Note] WSREP: discarding pending addr proto entry 0x378db50
 84 2018-08-07T10:30:44.872036+01:00 0 [Note] WSREP: gcomm: connected
 85 2018-08-07T10:30:44.872107+01:00 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
 86 2018-08-07T10:30:44.872173+01:00 0 [Note] WSREP: Waiting for SST/IST to complete.
 87 2018-08-07T10:30:44.872327+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2
 88 2018-08-07T10:30:44.872840+01:00 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5
 89 2018-08-07T10:30:44.873577+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5
 90 2018-08-07T10:30:44.874140+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5 from 0 (zlm3)
 91 2018-08-07T10:30:44.874158+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5 from 1 (zlm2)
 92 2018-08-07T10:30:44.874169+01:00 0 [Note] WSREP: Quorum results:
 93     version    = 4,
 94     component  = PRIMARY,
 95     conf_id    = 1,
 96     members    = 1/2 (primary/total),
 97     act_id     = 18,
 98     last_appl. = -1,
 99     protocols  = 0/8/3 (gcs/repl/appl),
100     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
101 2018-08-07T10:30:44.874173+01:00 0 [Note] WSREP: Flow-control interval: [141, 141] //The scope of fc interval.
102 2018-08-07T10:30:44.874176+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
103 2018-08-07T10:30:44.874179+01:00 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 18)
104 2018-08-07T10:30:44.874224+01:00 2 [Note] WSREP: State transfer required: 
105     Group state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18
106     Local state: 00000000-0000-0000-0000-000000000000:-1
107 2018-08-07T10:30:44.874231+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
108 2018-08-07T10:30:44.874237+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18, view# 2: Primary, number of nodes: 2, my index: 0, protocol version 3
109 2018-08-07T10:30:44.874241+01:00 2 [Note] WSREP: Setting wsrep_ready to true
110 2018-08-07T10:30:44.874243+01:00 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
111 2018-08-07T10:30:44.874245+01:00 2 [Note] WSREP: Setting wsrep_ready to false
112 2018-08-07T10:30:44.874315+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 '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
113 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)
114     2018-08-07T08:30:45.523175Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
115     2018-08-07T08:30:45.524742Z 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
116     2018-08-07T08:30:45.525848Z WSREP_SST: [ERROR] ****************************************************** 
117 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: 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 '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
118     Read: '(null)'
119 2018-08-07T10:30:45.526151+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 '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
120 2018-08-07T10:30:45.526201+01:00 2 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
121 2018-08-07T10:30:45.526208+01:00 2 [ERROR] Aborting
122 
123 2018-08-07T10:30:45.526211+01:00 2 [Note] Giving 2 client threads a chance to die gracefully
124 2018-08-07T10:30:47.373430+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting off
125 2018-08-07T10:30:47.526391+01:00 2 [Note] WSREP: Closing send monitor...
126 2018-08-07T10:30:47.526431+01:00 2 [Note] WSREP: Closed send monitor.
127 2018-08-07T10:30:47.526455+01:00 2 [Note] WSREP: gcomm: terminating thread
128 2018-08-07T10:30:47.526465+01:00 2 [Note] WSREP: gcomm: joining thread
129 2018-08-07T10:30:47.526479+01:00 1 [Note] WSREP: rollbacker thread exiting
130 2018-08-07T10:30:47.526675+01:00 2 [Note] WSREP: gcomm: closing backend
131 2018-08-07T10:30:50.874663+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection to peer bd54497d with addr tcp://192.168.1.101:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
132 2018-08-07T10:30:50.874839+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.101:4567 
133 2018-08-07T10:30:51.877925+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') reconnecting to bd54497d (tcp://192.168.1.101:4567), attempt 0
134 2018-08-07T10:30:53.026867+01:00 2 [Note] WSREP: declaring node with index 1 suspected, timeout PT5S (evs.suspect_timeout)
135 2018-08-07T10:30:53.027032+01:00 2 [Note] WSREP: evs::proto(2cf33746, LEAVING, view_id(REG,2cf33746,2)) suspecting node: bd54497d
136 2018-08-07T10:30:53.027042+01:00 2 [Note] WSREP: evs::proto(2cf33746, LEAVING, view_id(REG,2cf33746,2)) suspected node without join message, declaring inactive
137 2018-08-07T10:30:53.027063+01:00 2 [Note] WSREP: Current view of cluster as seen by this node
138 view (view_id(NON_PRIM,2cf33746,2)
139 memb {
140     2cf33746,0 //Only remains the first node zlm2.
141     }
142 joined {
143     }
144 left {
145     }
146 partitioned {
147     bd54497d,0 //Node zlm3 became a suspected node and be moved here.
148     }
149 )
150 2018-08-07T10:30:53.027092+01:00 2 [Note] WSREP: Current view of cluster as seen by this node
151 view ((empty))
152 2018-08-07T10:30:53.027370+01:00 2 [Note] WSREP: gcomm: closed
153 2018-08-07T10:30:53.027421+01:00 0 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
154 2018-08-07T10:30:53.027435+01:00 0 [Note] WSREP: Flow-control interval: [100, 100]
155 2018-08-07T10:30:53.027438+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
156 2018-08-07T10:30:53.027443+01:00 0 [Note] WSREP: Received NON-PRIMARY.
157 2018-08-07T10:30:53.027447+01:00 0 [Note] WSREP: Shifting PRIMARY -> OPEN (TO: 18)
158 2018-08-07T10:30:53.027458+01:00 0 [Note] WSREP: Received self-leave message.
159 2018-08-07T10:30:53.027463+01:00 0 [Note] WSREP: Flow-control interval: [0, 0]
160 2018-08-07T10:30:53.027466+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
161 2018-08-07T10:30:53.027469+01:00 0 [Note] WSREP: Received SELF-LEAVE. Closing connection.
162 2018-08-07T10:30:53.027472+01:00 0 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 18)
163 2018-08-07T10:30:53.027477+01:00 0 [Note] WSREP: RECV thread exiting 0: Success
164 2018-08-07T10:30:53.027719+01:00 2 [Note] WSREP: recv_thread() joined.
165 2018-08-07T10:30:53.027732+01:00 2 [Note] WSREP: Closing replication queue.
166 2018-08-07T10:30:53.027738+01:00 2 [Note] WSREP: Closing slave action queue.
167 2018-08-07T10:30:53.027796+01:00 0 [Note] WSREP: Waiting for active wsrep applier to exit
168 2018-08-07T10:30:53.027804+01:00 0 [Note] WSREP: Service disconnected.
169 2018-08-07T10:30:53.027807+01:00 0 [Note] WSREP: Waiting to close threads......
170 2018-08-07T10:30:58.028048+01:00 0 [Note] WSREP: Some threads may fail to exit.
171 2018-08-07T10:30:58.028088+01:00 0 [Note] Binlog end
172 2018-08-07T10:30:58.028341+01:00 0 [Note] mysqld: Shutdown complete
173 
174 [root@zlm3 10:35:04 /data/mysql/mysql3308/data]
175 #ps aux|grep mysqld
176 root      4672  0.0  0.0 112640   960 pts/0    R+   10:35   0:00 grep --color=auto mysqld
177 
178 //Let's see the detail of ERROR message.
179 [root@zlm3 10:35:06 /data/mysql/mysql3308/data]
180 #cat error.log |grep ERROR
181     2018-08-07T08:30:45.523175Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
182     2018-08-07T08:30:45.524742Z 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
183     2018-08-07T08:30:45.525848Z WSREP_SST: [ERROR] ****************************************************** 
184 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: 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 '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
185 2018-08-07T10:30:45.526151+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 '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
186 2018-08-07T10:30:45.526201+01:00 2 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
187 2018-08-07T10:30:45.526208+01:00 2 [ERROR] Aborting
188 
189 //It says that we are absent of "socat" package.
190 
191 [root@zlm3 10:43:15 /data/mysql/mysql3308/data]
192 #yum install socat
193 ... //Omitted.
194 
195 Warning: RPMDB altered outside of yum.
196   Installing : socat-1.7.3.2-2.el7.x86_64                                                                                                        1/1 
197   Verifying  : socat-1.7.3.2-2.el7.x86_64                                                                                                        1/1 
198 
199 Installed:
200   socat.x86_64 0:1.7.3.2-2.el7                                                                                                                       
201 
202 Complete!
203 
204 //Start the PXC on zlm2 again.
205 [root@zlm3 10:35:58 /data/mysql/mysql3308/data]
206 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
207 [1] 4725
208 
209 [root@zlm3 10:53:02 /data/mysql/mysql3308/data]
210 #tail -f error.log 
211 2018-08-07T10:53:15.116919+01:00 0 [Note] WSREP: Member 0.0 (zlm3) requested state transfer from '*any*'. Selected 1.0 (zlm2)(SYNCED) as donor.
212 2018-08-07T10:53:15.117004+01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 18)
213 2018-08-07T10:53:15.117035+01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 1
214 2018-08-07T10:53:15.117051+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18
215 2018-08-07T10:53:15.696885+01:00 0 [Warning] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) failed: -2 (No such file or directory)
216 2018-08-07T10:53:15.696918+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
217 2018-08-07T10:53:15.696940+01:00 0 [Note] WSREP: gcomm: terminating thread
218 2018-08-07T10:53:15.696949+01:00 0 [Note] WSREP: gcomm: joining thread
219 2018-08-07T10:53:15.697076+01:00 0 [Note] WSREP: gcomm: closing backend
220 2018-08-07T10:53:17.026257+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') turning message relay requesting off
221 2018-08-07T10:53:19.026327+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') connection to peer bd54497d with addr tcp://192.168.1.101:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
222 2018-08-07T10:53:19.026394+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.101:4567 
223 2018-08-07T10:53:20.086590+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') reconnecting to bd54497d (tcp://192.168.1.101:4567), attempt 0
224 2018-08-07T10:53:20.786605+01:00 0 [Note] WSREP: declaring node with index 1 suspected, timeout PT5S (evs.suspect_timeout)
225 2018-08-07T10:53:20.786778+01:00 0 [Note] WSREP: evs::proto(5167e9c7, LEAVING, view_id(REG,5167e9c7,4)) suspecting node: bd54497d
226 2018-08-07T10:53:20.786826+01:00 0 [Note] WSREP: evs::proto(5167e9c7, LEAVING, view_id(REG,5167e9c7,4)) suspected node without join message, declaring inactive
227 2018-08-07T10:53:20.786941+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
228 view (view_id(NON_PRIM,5167e9c7,4)
229 memb {
230     5167e9c7,0
231     }
232 joined {
233     }
234 left {
235     }
236 partitioned {
237     bd54497d,0
238     }
239 )
240 2018-08-07T10:53:20.787061+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
241 view ((empty))
242 2018-08-07T10:53:20.787324+01:00 0 [Note] WSREP: gcomm: closed
243 2018-08-07T10:53:20.787362+01:00 0 [Note] WSREP: mysqld: Terminated.
244 ^C
245 
246 [root@zlm3 10:53:48 /data/mysql/mysql3308/data]
247 #cat error.log |grep ERROR
248 2018-08-07T10:53:15.696918+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
249     
250 [root@zlm3 11:13:14 /data/mysql/mysql3308/data]
251 #ps aux|grep mysqld
252 root      6785  0.0  0.0 112640   960 pts/0    R+   11:20   0:00 grep --color=auto mysqld
253 [1]+  Aborted                 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
254 
255 [root@zlm3 11:20:16 /data/mysql/mysql3308/data]
256 #cat error.log |grep ERROR
257 2018-08-07T11:13:15.619315+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
258     2018-08-07T09:13:45.246252Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
259     2018-08-07T09:13:45.247507Z WSREP_SST: [ERROR] Error while getting data from donor node:  exit codes: 255 0
260     2018-08-07T09:13:45.254423Z WSREP_SST: [ERROR] ****************************************************** 
261     2018-08-07T09:13:45.258735Z WSREP_SST: [ERROR] Cleanup after exit with status:32
262     2018-08-07T09:14:10.137993Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
263     2018-08-07T09:14:10.139033Z WSREP_SST: [ERROR] Possible timeout in receving first data from donor in gtid/keyring stage
264     2018-08-07T09:14:10.140912Z WSREP_SST: [ERROR] ****************************************************** 
265     2018-08-07T09:14:10.141951Z WSREP_SST: [ERROR] Cleanup after exit with status:32
266     
267 //There's still one error which cause the mysqld process cannot be started.

 

Start mysqld of PXC on node zlm4.

 1 [root@zlm4 11:27:18 /data/mysql/mysql3308/data]
 2 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf initialize
 3 
 4 [root@zlm4 11:27:37 /data/mysql/mysql3308/data]
 5 #ls -l
 6 total 131092
 7 -rw-r----- 1 mysql mysql     13275 Aug  7 11:27 error.log
 8 -rw-r----- 1 mysql mysql 134219048 Aug  7 11:27 galera.cache
 9 -rw-r----- 1 mysql mysql         0 Aug  7 11:27 grastate.dat
10 
11 [root@zlm4 11:27:48 /data/mysql/mysql3308/data]
12 #cat error.log |grep ERROR
13     2018-08-07T09:27:24.770212Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
14     2018-08-07T09:27:24.771275Z WSREP_SST: [ERROR] xtrabackup not in path: /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
15     2018-08-07T09:27:24.771951Z WSREP_SST: [ERROR] ****************************************************** 
16 2018-08-07T11:27:24.772254+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: 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 '4363' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
17 2018-08-07T11:27:24.772279+01:00 0 [ERROR] WSREP: Process completed with error: 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 '4363' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
18 2018-08-07T11:27:24.772317+01:00 1 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
19 2018-08-07T11:27:24.772322+01:00 1 [ERROR] Aborting
20 
21 [root@zlm4 11:28:39 /data/mysql/mysql3308/data]
22 #innobackupex --help
23 -bash: innobackupex: command not found
24 
25 //It's due to absence of Xtrabackup tools on node zlm4.