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) |
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.
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 |
