澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

在PXC中重新添加掉线节点,MySQL高可用之PXC安装部

 

 

 

Preface

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.

    Today,I'm gonna implement a PXC,Let's see the procedure.

 

 

 

Procedure

Framework

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

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.

    The official website of PXC:

 

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

    Choose the Linux-Generic software.There're three version relevant with openssl(100,101,102),I choose the ssl102 version.

 

 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.

 

Install Percona-XtraDB-Cluster software on three nodes.

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

 

 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.

 

**Initialized a PXC node with proper pxc relevant parameters below.**

[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@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.

 

 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.

 

**Start mysqld of PXC on zlm2 as the first node in PXC.**

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

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:在PXC中重新添加掉线节点,MySQL高可用之PXC安装部