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

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

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

 

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

 

 

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

 

 

 

Restart PXC again on zlm2.

 

**Restore backup on node zlm4.**

It still show the ERROR output when node zlm3 startup.

**Start mysqld of PXC of the remaining nodes.**

 

 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

 

 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!

    The error above was due to the parameter of "innodb_皇冠投注app,log_group_home_皇冠体育手机版,dir",after I omitted it,there was no error message any more.

 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.

 

    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.

    The official website of PXC:

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

 

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

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

 

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

 

Framework

Procedure

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

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

 

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

 

Procedure

 

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

 

 

 

 

 

 

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

    

 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

 

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

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

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

 

 

 

 

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

 

Evidence on node zlm2.

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

Preface

 

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

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

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