環境介紹：

pxc三個節點:

192.168.1.6 test06

192.168.1.7 test07

192.168.1.8 test08

作業系統版本:CentOS release 6.8 (Final)

在安裝pxc之前需要安裝如下基礎軟體包:

# yum install -y socat perl-IO-Socket-SSL.noarch perl-DBD-MySQL.x86_64 perl-Time-HiRes openssl openssl-devel

關閉防火牆:

# service iptables stop

yum方式安裝:

[root@test06 /]# yum install

Loaded plugins: fastestmirror

Setting up Install Process

percona-release-0.1-4.noarch.rpm | 6.4 kB 00:00

Examining /var/tmp/yum-root-Km06K3/percona-release-0.1-4.noarch.rpm: percona-release-0.1-4.noarch

Marking /var/tmp/yum-root-Km06K3/percona-release-0.1-4.noarch.rpm to be installed

Loading mirror speeds from cached hostfile

* base: mirrors.huaweicloud.com

* epel: mirror01.idc.hinet.net

* extras: mirrors.huaweicloud.com

* updates: mirrors.huaweicloud.com

Resolving Dependencies

--> Running transaction check

---> Package percona-release.noarch 0:0.1-4 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================

Package Arch Version Repository Size

==============================================================================================================================================

Installing:

percona-release noarch 0.1-4 /percona-release-0.1-4.noarch 5.8 k

Transaction Summary

==============================================================================================================================================

Install 1 Package(s)

Total size: 5.8 k

Installed size: 5.8 k

Is this ok [y/N]: y

Downloading Packages:

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : percona-release-0.1-4.noarch 1/1

Verifying : percona-release-0.1-4.noarch 1/1

Installed:

percona-release.noarch 0:0.1-4

Complete!

[root@test data]# yum list | grep Percona-XtraDB-Cluster-57

Percona-XtraDB-Cluster-57.x86_64 5.7.23-31.31.2.el6 percona-release-x86_64

Percona-XtraDB-Cluster-57-debuginfo.x86_64 5.7.23-31.31.2.el6 percona-release-x86_64

[root@test data]# yum install Percona-XtraDB-Cluster-57

Loaded plugins: fastestmirror

Setting up Install Process

Loading mirror speeds from cached hostfile

* base: mirrors.huaweicloud.com

* epel: mirror01.idc.hinet.net

* extras: mirrors.huaweicloud.com

* updates: mirrors.huaweicloud.com

Resolving Dependencies

--> Running transaction check

---> Package Percona-XtraDB-Cluster-57.x86_64 0:5.7.23-31.31.2.el6 will be installed

--> Processing Dependency: Percona-XtraDB-Cluster-server-57 = 5.7.23-31.31.2.el6 for package: Percona-XtraDB-Cluster-57-5.7.23-31.31.2.el6.x86_64

--> Processing Dependency: Percona-XtraDB-Cluster-client-57 = 5.7.23-31.31.2.el6 for package: Percona-XtraDB-Cluster-57-5.7.23-31.31.2.el6.x86_64

--> Running transaction check

---> Package Percona-XtraDB-Cluster-client-57.x86_64 0:5.7.23-31.31.2.el6 will be installed

---> Package Percona-XtraDB-Cluster-server-57.x86_64 0:5.7.23-31.31.2.el6 will be installed

--> Processing Dependency: Percona-XtraDB-Cluster-shared-57 = 5.7.23-31.31.2.el6 for package: Percona-XtraDB-Cluster-server-57-5.7.23-31.31.2.el6.x86_64

--> Processing Dependency: lsof for package: Percona-XtraDB-Cluster-server-57-5.7.23-31.31.2.el6.x86_64

--> Processing Dependency: qpress for package: Percona-XtraDB-Cluster-server-57-5.7.23-31.31.2.el6.x86_64

--> Running transaction check

---> Package Percona-XtraDB-Cluster-shared-57.x86_64 0:5.7.23-31.31.2.el6 will be installed

---> Package lsof.x86_64 0:4.82-5.el6 will be installed

---> Package qpress.x86_64 0:11-1.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================

Package Arch Version Repository Size

==============================================================================================================================================

Installing:

Percona-XtraDB-Cluster-57 x86_64 5.7.23-31.31.2.el6 percona-release-x86_64 27 k

Installing for dependencies:

Percona-XtraDB-Cluster-client-57 x86_64 5.7.23-31.31.2.el6 percona-release-x86_64 8.6 M

Percona-XtraDB-Cluster-server-57 x86_64 5.7.23-31.31.2.el6 percona-release-x86_64 53 M

Percona-XtraDB-Cluster-shared-57 x86_64 5.7.23-31.31.2.el6 percona-release-x86_64 880 k

lsof x86_64 4.82-5.el6 base 324 k

qpress x86_64 11-1.el6 percona-release-x86_64 30 k

Transaction Summary

==============================================================================================================================================

Install 6 Package(s)

Total download size: 63 M

Installed size: 64 M

Is this ok [y/N]: y

Downloading Packages:

(1/6): Percona-XtraDB-Cluster-57-5.7.23-31.31.2.el6.x86_64.rpm | 27 kB 00:00

(2/6): Percona-XtraDB-Cluster-client-57-5.7.23-31.31.2.el6.x86_64.rpm | 8.6 MB 03:26

(3/6): Percona-XtraDB-Cluster-server-57-5.7.23-31.31.2.el6.x86_64.rpm | 53 MB 12:57

(4/6): Percona-XtraDB-Cluster-shared-57-5.7.23-31.31.2.el6.x86_64.rpm | 880 kB 00:13

(5/6): lsof-4.82-5.el6.x86_64.rpm | 324 kB 00:00

(6/6): qpress-11-1.el6.x86_64.rpm | 30 kB 00:00

----------------------------------------------------------------------------------------------------------------------------------------------

Total 64 kB/s | 63 MB 16:43

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : Percona-XtraDB-Cluster-client-57-5.7.23-31.31.2.el6.x86_64 1/6

Installing : Percona-XtraDB-Cluster-shared-57-5.7.23-31.31.2.el6.x86_64 2/6

Installing : lsof-4.82-5.el6.x86_64 3/6

Installing : qpress-11-1.el6.x86_64 4/6

Giving mysqld 5 seconds to exit nicely

Installing : Percona-XtraDB-Cluster-server-57-5.7.23-31.31.2.el6.x86_64 5/6

-------------

* The suggested mysql options and settings are in /etc/percona-xtradb-cluster.conf.d/mysqld.cnf

* If you want to use mysqld.cnf as default configuration file please make backup of /etc/my.cnf

* Once it is done please execute the following commands:

rm -rf /etc/my.cnf

update-alternatives --install /etc/my.cnf my.cnf "/etc/percona-xtradb-cluster.cnf" 200

-------------

Percona XtraDB Cluster is distributed with several useful UDFs from Percona Toolkit.

Run the following commands to create these functions:

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"

mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"

mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

See for more details

Installing : Percona-XtraDB-Cluster-57-5.7.23-31.31.2.el6.x86_64 6/6

Verifying : Percona-XtraDB-Cluster-client-57-5.7.23-31.31.2.el6.x86_64 1/6

Verifying : qpress-11-1.el6.x86_64 2/6

Verifying : lsof-4.82-5.el6.x86_64 3/6

Verifying : Percona-XtraDB-Cluster-shared-57-5.7.23-31.31.2.el6.x86_64 4/6

Verifying : Percona-XtraDB-Cluster-57-5.7.23-31.31.2.el6.x86_64 5/6

Verifying : Percona-XtraDB-Cluster-server-57-5.7.23-31.31.2.el6.x86_64 6/6

Installed:

Percona-XtraDB-Cluster-57.x86_64 0:5.7.23-31.31.2.el6

Dependency Installed:

Percona-XtraDB-Cluster-client-57.x86_64 0:5.7.23-31.31.2.el6 Percona-XtraDB-Cluster-server-57.x86_64 0:5.7.23-31.31.2.el6

Percona-XtraDB-Cluster-shared-57.x86_64 0:5.7.23-31.31.2.el6 lsof.x86_64 0:4.82-5.el6

qpress.x86_64 0:11-1.el6

Complete!

主節點配置檔案:

# vi /etc/my.cnf

[mysqld]

server-id=216

pid-file=/var/run/mysqld/mysqld.pid

log-error=/var/log/mysqld.log

datadir = /data/mysql/data

log_error=/data/mysql/log/mysqld.err

wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.1.6,192.168.1.7,192.168.1.8

wsrep_node_name=pxc1

wsrep_node_address=192.168.1.6

wsrep_slave_threads=2

binlog_format=ROW

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

wsrep_cluster_name=pxc

wsrep_sst_auth=sst:dsf

wsrep_sst_method=xtrabackup-v2

pxc_strict_mode=ENFORCING

~

啟動主節點pxc:

# /etc/init.d/mysql bootstrap-pxc

Bootstrapping PXC (Percona XtraDB Cluster)Initializing MySQ[ OK ]se:

Starting MySQL (Percona XtraDB Cluster). SUCCESS!

啟動日誌:

2018-10-24T08:48:02.766241Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-10-24T08:48:02.766340Z 0 [Warning] WSREP: Node is running in bootstrap/initialize mode. Disabling pxc_strict_mode checks

2018-10-24T08:48:02.766386Z 0 [ERROR] Can't find error-message file '/usr/local/Percona-XtraDB-Cluster-5.7.23-rel23-31.31.2.Linux.x86_64.ssl101/share/percona-xtradb-cluster/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

2018-10-24T08:48:03.376671Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-10-24T08:48:04.643073Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-10-24T08:48:05.322164Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 85ecb885-d769-11e8-ba96-5254005688c2.

2018-10-24T08:48:05.783587Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2018-10-24T08:48:05.972121Z 0 [Warning] CA certificate ca.pem is self signed.

2018-10-24T08:48:06.114041Z 1 [Note] A temporary password is generated for root@localhost: YvYNbTiIp3<E

2018-10-24T08:48:15.923403Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-10-24T08:48:15.923525Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2018-10-24T08:48:15.923553Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.23-23-31.31) starting as process 619 ...

2018-10-24T08:48:15.927700Z 0 [Note] WSREP: Setting wsrep_ready to false

2018-10-24T08:48:15.927728Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.

2018-10-24T08:48:15.927735Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'

2018-10-24T08:48:15.932003Z 0 [Note] WSREP: wsrep_load(): Galera 3.31(r) by Codership Oy <info@codership.com> loaded successfully.

2018-10-24T08:48:15.932067Z 0 [Note] WSREP: CRC-32C: using "slicing-by-8" algorithm.

2018-10-24T08:48:15.932397Z 0 [Warning] WSREP: Could not open state file for reading: '/data/mysql/data//grastate.dat'

2018-10-24T08:48:15.932423Z 0 [Warning] WSREP: No persistent state found. Bootstraping with default state

2018-10-24T08:48:15.932458Z 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1

2018-10-24T08:48:15.934885Z 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/data/; base_host = 192.168.1.6; 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/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/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 = 9; socket.checksum = 2; socket.recv_buf_size = 212992;

2018-10-24T08:48:15.972184Z 0 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 00000000-0000-0000-0000-000000000000:-1

2018-10-24T08:48:15.973323Z 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1

2018-10-24T08:48:15.973373Z 0 [Note] WSREP: Preparing to initiate SST/IST

2018-10-24T08:48:15.973387Z 0 [Note] WSREP: Starting replication

2018-10-24T08:48:15.973409Z 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1

2018-10-24T08:48:15.973623Z 0 [Note] WSREP: Using CRC-32C for message checksums.

2018-10-24T08:48:15.973770Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0

2018-10-24T08:48:15.973897Z 0 [Warning] WSREP: Fail to access the file (/data/mysql/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

2018-10-24T08:48:15.973920Z 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown

2018-10-24T08:48:15.974649Z 0 [Note] WSREP: GMCast version 0

2018-10-24T08:48:15.974955Z 0 [Note] WSREP: (8c460ee0, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567

2018-10-24T08:48:15.974982Z 0 [Note] WSREP: (8c460ee0, 'tcp://0.0.0.0:4567') multicast: , ttl: 1

2018-10-24T08:48:15.975321Z 0 [Note] WSREP: EVS version 0

2018-10-24T08:48:15.975417Z 0 [Note] WSREP: gcomm: bootstrapping new group 'pxc'

2018-10-24T08:48:15.975478Z 0 [Note] WSREP: start_prim is enabled, turn off pc_recovery

2018-10-24T08:48:15.976320Z 0 [Note] WSREP: Node 8c460ee0 state primary

2018-10-24T08:48:15.976384Z 0 [Note] WSREP: Current view of cluster as seen by this node

view (view_id(PRIM,8c460ee0,1)

memb {

8c460ee0,0

}

joined {

}

left {

}

partitioned {

}

)

2018-10-24T08:48:15.976410Z 0 [Note] WSREP: Save the discovered primary-component to disk

2018-10-24T08:48:15.977468Z 0 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.1.6:4567

2018-10-24T08:48:15.977497Z 0 [Note] WSREP: discarding pending addr proto entry 0x3033460

2018-10-24T08:48:15.977534Z 0 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.1.7:4567

2018-10-24T08:48:15.977554Z 0 [Note] WSREP: discarding pending addr proto entry 0x303bc40

2018-10-24T08:48:15.977567Z 0 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.1.8:4567

2018-10-24T08:48:15.977581Z 0 [Note] WSREP: discarding pending addr proto entry 0x3044430

2018-10-24T08:48:15.977612Z 0 [Note] WSREP: gcomm: connected

2018-10-24T08:48:15.977689Z 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)

2018-10-24T08:48:15.978163Z 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 1

2018-10-24T08:48:15.978371Z 0 [Note] WSREP: Waiting for SST/IST to complete.

2018-10-24T08:48:15.979995Z 0 [Note] WSREP: Starting new group from scratch: 8c46dd3d-d769-11e8-84c6-d6aac3977406

2018-10-24T08:48:15.980513Z 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 8c46fb53-d769-11e8-ae76-f615ec681929

2018-10-24T08:48:15.980543Z 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 8c46fb53-d769-11e8-ae76-f615ec681929

2018-10-24T08:48:15.980550Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 8c46fb53-d769-11e8-ae76-f615ec681929 from 0 (pxc1)

2018-10-24T08:48:15.980558Z 0 [Note] WSREP: Quorum results:

version = 4,

component = PRIMARY,

conf_id = 0,

members = 1/1 (primary/total),

act_id = 0,

last_appl. = -1,

protocols = 0/9/3 (gcs/repl/appl),

group UUID = 8c46dd3d-d769-11e8-84c6-d6aac3977406

2018-10-24T08:48:15.980566Z 0 [Note] WSREP: Flow-control interval: [100, 100]

2018-10-24T08:48:15.980570Z 0 [Note] WSREP: Trying to continue unpaused monitor

2018-10-24T08:48:15.980584Z 0 [Note] WSREP: Restored state OPEN -> JOINED (0)

2018-10-24T08:48:15.980611Z 0 [Note] WSREP: Member 0.0 (pxc1) synced with group.

2018-10-24T08:48:15.980625Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)

2018-10-24T08:48:15.980665Z 2 [Note] WSREP: REPL Protocols: 9 (4, 2)

2018-10-24T08:48:15.980704Z 2 [Note] WSREP: New cluster view: global state: 8c46dd3d-d769-11e8-84c6-d6aac3977406:0, view# 1: Primary, number of nodes: 1, my index: 0, protocol version 3

2018-10-24T08:48:15.980726Z 2 [Note] WSREP: Setting wsrep_ready to true

2018-10-24T08:48:15.980779Z 0 [Note] WSREP: SST complete, seqno: 0

2018-10-24T08:48:15.982216Z 0 [Note] InnoDB: PUNCH HOLE support available

2018-10-24T08:48:15.982247Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-10-24T08:48:15.982272Z 0 [Note] InnoDB: Uses event mutexes

2018-10-24T08:48:15.982278Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

2018-10-24T08:48:15.982283Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-10-24T08:48:15.982287Z 0 [Note] InnoDB: Using Linux native AIO

2018-10-24T08:48:15.982520Z 0 [Note] InnoDB: Number of pools: 1

2018-10-24T08:48:15.982646Z 0 [Note] InnoDB: Not using CPU crc32 instructions

2018-10-24T08:48:15.983985Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2018-10-24T08:48:15.993069Z 0 [Note] InnoDB: Completed initialization of buffer pool

2018-10-24T08:48:15.998464Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2018-10-24T08:48:16.009838Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /data/mysql/data/xb_doublewrite

2018-10-24T08:48:16.013640Z 0 [Note] InnoDB: Highest supported file format is Barracuda.

2018-10-24T08:48:16.033651Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/data/xb_doublewrite, size 3932160 bytes

2018-10-24T08:48:16.042078Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2018-10-24T08:48:16.042156Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2018-10-24T08:48:16.057643Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2018-10-24T08:48:16.058376Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

2018-10-24T08:48:16.058414Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.

2018-10-24T08:48:16.062360Z 0 [Note] InnoDB: Percona XtraDB () 5.7.23-rel23 started; log sequence number 2594622

2018-10-24T08:48:16.064275Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/data/ib_buffer_pool

2018-10-24T08:48:16.064499Z 0 [Note] Plugin 'FEDERATED' is disabled.

2018-10-24T08:48:16.074083Z 0 [Note] InnoDB: Buffer pool(s) load completed at 181024 16:48:16

2018-10-24T08:48:16.077060Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.

2018-10-24T08:48:16.077089Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.

2018-10-24T08:48:16.083947Z 0 [Warning] CA certificate ca.pem is self signed.

2018-10-24T08:48:16.084031Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.

2018-10-24T08:48:16.084142Z 0 [Note] Server hostname (bind-address): '*'; port: 3306

2018-10-24T08:48:16.084196Z 0 [Note] IPv6 is available.

2018-10-24T08:48:16.084219Z 0 [Note] - '::' resolves to '::';

2018-10-24T08:48:16.084242Z 0 [Note] Server socket created on IP: '::'.

2018-10-24T08:48:16.185509Z 0 [Note] Event Scheduler: Loaded 0 events

2018-10-24T08:48:16.187123Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.7.23-23-31.31' socket: '/tmp/mysql.sock' port: 3306 Percona XtraDB Cluster binary (GPL) 5.7.23-31.31, Revision f5578f0, wsrep_31.31

2018-10-24T08:48:16.203249Z 2 [Note] WSREP: Initialized wsrep sidno 2

找出臨時密碼:

# cat mysqld.err | grep temporary

2018-10-24T08:48:06.114041Z 1 [Note] A temporary password is generated for root@localhost: YvYNbTiIp3<E

2018-10-24T08:48:16.042078Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

[root@test data]# mysql -uroot -p'YvYNbTiIp3<E'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.23-23-31.31

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user root@localhost identified by '';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

建立pxc sst賬號:

mysql> create user sst@localhost identified by 'dsf';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to sst@localhost;

Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

第二個節點:

[root@solr07 log]# cat /etc/my.cnf

[mysqld]

pid-file=/var/run/mysqld/mysqld.pid

log-error=/var/log/mysqld.log

server-id=217

datadir = /data/mysql/data

log_error=/data/mysql/log/mysqld.err

#wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so

wsrep_provider=/usr/lib64/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.1.6,192.168.1.7,192.168.1.8

wsrep_node_name=pxc2

wsrep_node_address=192.168.1.7

wsrep_slave_threads=2

binlog_format=ROW

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

wsrep_cluster_name=pxc

wsrep_sst_auth=sst:dsf

wsrep_sst_method=xtrabackup-v2

#wsrep_sst_method=mysqldump

pxc_strict_mode=ENFORCING

啟動:

# /etc/init.d/mysql start

ERROR! MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists

Initializing MySQL database: [ OK ]

Starting MySQL (Percona XtraDB Cluster)..State transfer in progress, setting sleep higher

... SUCCESS!

啟動日誌:

2018-10-24T09:21:27.904734Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-10-24T09:21:27.904806Z 0 [Warning] WSREP: Node is running in bootstrap/initialize mode. Disabling pxc_strict_mode checks

2018-10-24T09:21:28.132131Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-10-24T09:21:28.167258Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-10-24T09:21:28.225899Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2fbf9399-d76e-11e8-816f-52540001d3d4.

2018-10-24T09:21:28.227125Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2018-10-24T09:21:28.713696Z 0 [Warning] CA certificate ca.pem is self signed.

2018-10-24T09:21:28.761550Z 1 [Note] A temporary password is generated for root@localhost: soynlamI&8FY

2018-10-24T09:21:41.960201Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-10-24T09:21:41.964613Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-23-57) starting as process 21455 ...

2018-10-24T09:21:41.966960Z 0 [Note] WSREP: Setting wsrep_ready to false

2018-10-24T09:21:41.966985Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.

2018-10-24T09:21:41.966999Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/libgalera_smm.so'

2018-10-24T09:21:41.971037Z 0 [Note] WSREP: wsrep_load(): Galera 3.31(rf216443) by Codership Oy <info@codership.com> loaded successfully.

2018-10-24T09:21:41.971113Z 0 [Note] WSREP: CRC-32C: using "slicing-by-8" algorithm.

2018-10-24T09:21:41.971414Z 0 [Warning] WSREP: Could not open state file for reading: '/data/mysql/data//grastate.dat'

2018-10-24T09:21:41.971438Z 0 [Warning] WSREP: No persistent state found. Bootstraping with default state

2018-10-24T09:21:41.971472Z 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1

2018-10-24T09:21:41.974016Z 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/data/; base_host = 192.168.1.7; 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/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/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 = 9; socket.checksum = 2; socket.recv_buf_size = 212992;

2018-10-24T09:21:42.015236Z 0 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 00000000-0000-0000-0000-000000000000:-1

2018-10-24T09:21:42.016305Z 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1

2018-10-24T09:21:42.016351Z 0 [Note] WSREP: Preparing to initiate SST/IST

2018-10-24T09:21:42.016367Z 0 [Note] WSREP: Starting replication

2018-10-24T09:21:42.016400Z 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1

2018-10-24T09:21:42.016628Z 0 [Note] WSREP: Using CRC-32C for message checksums.

2018-10-24T09:21:42.016768Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0

2018-10-24T09:21:42.016894Z 0 [Warning] WSREP: Fail to access the file (/data/mysql/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

2018-10-24T09:21:42.016915Z 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown

2018-10-24T09:21:42.017551Z 0 [Note] WSREP: GMCast version 0

2018-10-24T09:21:42.017762Z 0 [Note] WSREP: (37f7ecae, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567

2018-10-24T09:21:42.017783Z 0 [Note] WSREP: (37f7ecae, 'tcp://0.0.0.0:4567') multicast: , ttl: 1

2018-10-24T09:21:42.018098Z 0 [Note] WSREP: EVS version 0

2018-10-24T09:21:42.018270Z 0 [Note] WSREP: gcomm: connecting to group 'pxc', peer '192.168.1.6:,192.168.1.7:,192.168.1.8:'

2018-10-24T09:21:42.019829Z 0 [Note] WSREP: (37f7ecae, 'tcp://0.0.0.0:4567') connection established to 37f7ecae tcp://192.168.1.7:4567

2018-10-24T09:21:42.019867Z 0 [Warning] WSREP: (37f7ecae, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.7:4567' points to own listening address, blacklisting

2018-10-24T09:21:42.020458Z 0 [Note] WSREP: (37f7ecae, 'tcp://0.0.0.0:4567') connection established to e9a1e0cb tcp://192.168.1.6:4567

2018-10-24T09:21:42.020517Z 0 [Note] WSREP: (37f7ecae, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:

2018-10-24T09:21:42.520282Z 0 [Note] WSREP: declaring e9a1e0cb at tcp://192.168.1.6:4567 stable

2018-10-24T09:21:42.521151Z 0 [Note] WSREP: Node e9a1e0cb state primary

2018-10-24T09:21:42.521698Z 0 [Note] WSREP: Current view of cluster as seen by this node

view (view_id(PRIM,37f7ecae,2)

memb {

37f7ecae,0

e9a1e0cb,0

}

joined {

}

left {

}

partitioned {

}

)

2018-10-24T09:21:42.521745Z 0 [Note] WSREP: Save the discovered primary-component to disk

2018-10-24T09:21:42.712361Z 0 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.1.8:4567

2018-10-24T09:21:43.019199Z 0 [Note] WSREP: gcomm: connected

2018-10-24T09:21:43.019485Z 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)

2018-10-24T09:21:43.019588Z 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2

2018-10-24T09:21:43.019885Z 0 [Note] WSREP: Waiting for SST/IST to complete.

2018-10-24T09:21:43.020289Z 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 3890ec7c-d76e-11e8-ae62-57d35f50d7d7

2018-10-24T09:21:43.020881Z 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 3890ec7c-d76e-11e8-ae62-57d35f50d7d7

2018-10-24T09:21:43.021558Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 3890ec7c-d76e-11e8-ae62-57d35f50d7d7 from 0 (pxc2)

2018-10-24T09:21:43.022228Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 3890ec7c-d76e-11e8-ae62-57d35f50d7d7 from 1 (pxc1)

2018-10-24T09:21:43.022260Z 0 [Note] WSREP: Quorum results:

version = 4,

component = PRIMARY,

conf_id = 1,

members = 1/2 (primary/total),

act_id = 5,

last_appl. = -1,

protocols = 0/9/3 (gcs/repl/appl),

group UUID = 8c46dd3d-d769-11e8-84c6-d6aac3977406

2018-10-24T09:21:43.022284Z 0 [Note] WSREP: Flow-control interval: [141, 141]

2018-10-24T09:21:43.022295Z 0 [Note] WSREP: Trying to continue unpaused monitor

2018-10-24T09:21:43.022305Z 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 5)

2018-10-24T09:21:43.022408Z 2 [Note] WSREP: State transfer required:

Group state: 8c46dd3d-d769-11e8-84c6-d6aac3977406:5

Local state: 00000000-0000-0000-0000-000000000000:-1

2018-10-24T09:21:43.022453Z 2 [Note] WSREP: REPL Protocols: 9 (4, 2)

2018-10-24T09:21:43.022466Z 2 [Note] WSREP: New cluster view: global state: 8c46dd3d-d769-11e8-84c6-d6aac3977406:5, view# 2: Primary, number of nodes: 2, my index: 0, protocol version 3

2018-10-24T09:21:43.022496Z 2 [Note] WSREP: Setting wsrep_ready to true

2018-10-24T09:21:43.022509Z 2 [Warning] WSREP: Gap in state sequence. Need state transfer.

2018-10-24T09:21:43.022521Z 2 [Note] WSREP: Setting wsrep_ready to false

2018-10-24T09:21:43.022682Z 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.7' --datadir '/data/mysql/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '21455' --mysqld-version '5.7.23-23-57' '' )

2018-10-24T09:21:43.139992Z WSREP_SST: [WARNING] wsrep_node_address or wsrep_sst_receive_address not set. Consider setting them if SST fails.

2018-10-24T09:21:43.600512Z 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.1.7:4444/xtrabackup_sst//1

2018-10-24T09:21:43.600557Z 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 1) (Increment: 1 -> 2)

2018-10-24T09:21:43.600569Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-10-24T09:21:43.600600Z 2 [Note] WSREP: Assign initial position for certification: 5, protocol version: 4

2018-10-24T09:21:43.600682Z 0 [Note] WSREP: Service thread queue flushed.

2018-10-24T09:21:43.600747Z 2 [Note] WSREP: Check if state gap can be serviced using IST

2018-10-24T09:21:43.600779Z 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: 8c46dd3d-d769-11e8-84c6-d6aac3977406

2018-10-24T09:21:43.600834Z 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST

2018-10-24T09:21:43.600859Z 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (8c46dd3d-d769-11e8-84c6-d6aac3977406): 1 (Operation not permitted)

at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.

2018-10-24T09:21:43.601680Z 0 [Note] WSREP: Member 0.0 (pxc2) requested state transfer from '*any*'. Selected 1.0 (pxc1)(SYNCED) as donor.

2018-10-24T09:21:43.601703Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 5)

2018-10-24T09:21:43.601744Z 2 [Note] WSREP: Requesting state transfer: success, donor: 1

2018-10-24T09:21:43.601769Z 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 8c46dd3d-d769-11e8-84c6-d6aac3977406:5

2018-10-24T09:21:43.977125Z WSREP_SST: [INFO] Proceeding with SST.........

2018-10-24T09:21:44.020194Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!

2018-10-24T09:21:45.519726Z 0 [Note] WSREP: (37f7ecae, 'tcp://0.0.0.0:4567') turning message relay requesting off

2018-10-24T09:22:00.495213Z 0 [Note] WSREP: 1.0 (pxc1): State transfer to 0.0 (pxc2) complete.

2018-10-24T09:22:00.495796Z 0 [Note] WSREP: Member 1.0 (pxc1) synced with group.

2018-10-24T09:22:01.309046Z WSREP_SST: [INFO] Preparing the backup at /data/mysql/data//.sst

2018-10-24T09:22:04.853587Z WSREP_SST: [INFO] Moving the backup to /data/mysql/data/

2018-10-24T09:22:04.970909Z WSREP_SST: [INFO] Galera co-ords from recovery: 8c46dd3d-d769-11e8-84c6-d6aac3977406:5

2018-10-24T09:22:04.984305Z 0 [Note] WSREP: SST complete, seqno: 5

2018-10-24T09:22:04.985890Z 0 [Note] InnoDB: PUNCH HOLE support available

2018-10-24T09:22:04.985922Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-10-24T09:22:04.985929Z 0 [Note] InnoDB: Uses event mutexes

2018-10-24T09:22:04.985945Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

2018-10-24T09:22:04.985950Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-10-24T09:22:04.985955Z 0 [Note] InnoDB: Using Linux native AIO

2018-10-24T09:22:04.986233Z 0 [Note] InnoDB: Number of pools: 1

2018-10-24T09:22:04.986376Z 0 [Note] InnoDB: Not using CPU crc32 instructions

2018-10-24T09:22:04.988080Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2018-10-24T09:22:04.992104Z 0 [Note] InnoDB: Completed initialization of buffer pool

2018-10-24T09:22:04.994670Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2018-10-24T09:22:05.006381Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /data/mysql/data/xb_doublewrite

2018-10-24T09:22:05.007329Z 0 [Note] InnoDB: Highest supported file format is Barracuda.

2018-10-24T09:22:05.022205Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/data/xb_doublewrite, size 3932160 bytes

2018-10-24T09:22:05.033680Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2018-10-24T09:22:05.033707Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2018-10-24T09:22:05.033744Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2018-10-24T09:22:05.049824Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2018-10-24T09:22:05.050835Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

2018-10-24T09:22:05.050865Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.

2018-10-24T09:22:05.051845Z 0 [Note] InnoDB: Percona XtraDB () 5.7.23-23 started; log sequence number 2596392

2018-10-24T09:22:05.052441Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/data/ib_buffer_pool

2018-10-24T09:22:05.052998Z 0 [Note] Plugin 'FEDERATED' is disabled.

2018-10-24T09:22:05.062214Z 0 [Note] InnoDB: Buffer pool(s) load completed at 181024 17:22:05

2018-10-24T09:22:05.065451Z 0 [Note] Salting uuid generator variables, current_pid: 21455, server_start_time: 1540372901, bytes_sent: 0,

2018-10-24T09:22:05.065530Z 0 [Note] Generated uuid: '45b4dc30-d76e-11e8-8a3b-52540001d3d4', server_start_time: 6039045626867518836, bytes_sent: 49147200

2018-10-24T09:22:05.065565Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 45b4dc30-d76e-11e8-8a3b-52540001d3d4.

2018-10-24T09:22:05.067396Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.

2018-10-24T09:22:05.067427Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.

2018-10-24T09:22:05.074014Z 0 [Warning] CA certificate ca.pem is self signed.

2018-10-24T09:22:05.074098Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.

2018-10-24T09:22:05.074249Z 0 [Note] Server hostname (bind-address): '*'; port: 3306

2018-10-24T09:22:05.074305Z 0 [Note] IPv6 is available.

2018-10-24T09:22:05.074328Z 0 [Note] - '::' resolves to '::';

2018-10-24T09:22:05.074360Z 0 [Note] Server socket created on IP: '::'.

2018-10-24T09:22:05.083820Z 0 [Note] Event Scheduler: Loaded 0 events

2018-10-24T09:22:05.084002Z 0 [Note] WSREP: Signalling provider to continue on SST completion.

2018-10-24T09:22:05.084035Z 0 [Note] WSREP: Initialized wsrep sidno 2

2018-10-24T09:22:05.084075Z 0 [Note] WSREP: SST received: 8c46dd3d-d769-11e8-84c6-d6aac3977406:5

2018-10-24T09:22:05.084379Z 0 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.7.23-23-57' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona XtraDB Cluster (GPL), Release rel23, Revision f5578f0, WSREP version 31.31, wsrep_31.31

2018-10-24T09:22:05.086648Z 0 [Note] WSREP: 0.0 (pxc2): State transfer from 1.0 (pxc1) complete.

2018-10-24T09:22:05.086680Z 0 [Note] WSREP: SST leaving flow control

2018-10-24T09:22:05.086686Z 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 5)

2018-10-24T09:22:05.087098Z 0 [Note] WSREP: Member 0.0 (pxc2) synced with group.

2018-10-24T09:22:05.087140Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 5)

2018-10-24T09:22:05.087188Z 2 [Note] WSREP: Synchronized with group, ready for connections

2018-10-24T09:22:05.087209Z 2 [Note] WSREP: Setting wsrep_ready to true

2018-10-24T09:22:05.087223Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

[root@solr07 log]#

第三個節點配置檔案:

# vi /etc/my.cnf

[mysqld]

pid-file=/var/run/mysqld/mysqld.pid

log-error=/var/log/mysqld.log

server-id=218

#basedir = /usr/local/mysql

datadir = /data/mysql/data

log_error=/data/mysql/log/mysqld.err

#wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so

wsrep_provider=/usr/lib64/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.1.6,192.168.1.7,192.168.1.8

wsrep_node_name=pxc3

wsrep_node_address=192.168.1.8

wsrep_slave_threads=2

binlog_format=ROW

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

wsrep_cluster_name=pxc

wsrep_sst_auth=sst:dsf

wsrep_sst_method=xtrabackup-v2

pxc_strict_mode=ENFORCING

# /etc/init.d/mysql start

Initializing MySQL database: [ OK ]

Starting MySQL (Percona XtraDB Cluster)..State transfer in progress, setting sleep higher

... SUCCESS!

啟動日誌檔案內容:

2018-10-25T00:37:23.339703Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-10-25T00:37:23.339800Z 0 [Warning] WSREP: Node is running in bootstrap/initialize mode. Disabling pxc_strict_mode checks

2018-10-25T00:37:28.239438Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-10-25T00:37:28.454670Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-10-25T00:37:28.517289Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 26a2823b-d7ee-11e8-b25c-52540012a8a2.

2018-10-25T00:37:28.518535Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2018-10-25T00:37:28.638866Z 0 [Warning] CA certificate ca.pem is self signed.

2018-10-25T00:37:28.873076Z 1 [Note] A temporary password is generated for root@localhost: QA-vwoqsB7V+

2018-10-25T00:37:37.582900Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-10-25T00:37:37.584720Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-23-57) starting as process 19275 ...

2018-10-25T00:37:37.587168Z 0 [Note] WSREP: Setting wsrep_ready to false

2018-10-25T00:37:37.587197Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.

2018-10-25T00:37:37.587212Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/libgalera_smm.so'

2018-10-25T00:37:37.591528Z 0 [Note] WSREP: wsrep_load(): Galera 3.31(rf216443) by Codership Oy <info@codership.com> loaded successfully.

2018-10-25T00:37:37.591602Z 0 [Note] WSREP: CRC-32C: using "slicing-by-8" algorithm.

2018-10-25T00:37:37.591929Z 0 [Warning] WSREP: Could not open state file for reading: '/data/mysql/data//grastate.dat'

2018-10-25T00:37:37.591955Z 0 [Warning] WSREP: No persistent state found. Bootstraping with default state

2018-10-25T00:37:37.591995Z 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1

2018-10-25T00:37:37.602759Z 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/data/; base_host = 192.168.1.8; 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/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/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 = 9; socket.checksum = 2; socket.recv_buf_size = 212992;

2018-10-25T00:37:37.639698Z 0 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 00000000-0000-0000-0000-000000000000:-1

2018-10-25T00:37:37.640861Z 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1

2018-10-25T00:37:37.640921Z 0 [Note] WSREP: Preparing to initiate SST/IST

2018-10-25T00:37:37.640931Z 0 [Note] WSREP: Starting replication

2018-10-25T00:37:37.640955Z 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1

2018-10-25T00:37:37.641204Z 0 [Note] WSREP: Using CRC-32C for message checksums.

2018-10-25T00:37:37.641388Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0

2018-10-25T00:37:37.641549Z 0 [Warning] WSREP: Fail to access the file (/data/mysql/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

2018-10-25T00:37:37.641572Z 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown

2018-10-25T00:37:37.642236Z 0 [Note] WSREP: GMCast version 0

2018-10-25T00:37:37.642482Z 0 [Note] WSREP: (2c12c4d0, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567

2018-10-25T00:37:37.642512Z 0 [Note] WSREP: (2c12c4d0, 'tcp://0.0.0.0:4567') multicast: , ttl: 1

2018-10-25T00:37:37.642869Z 0 [Note] WSREP: EVS version 0

2018-10-25T00:37:37.642993Z 0 [Note] WSREP: gcomm: connecting to group 'pxc', peer '192.168.1.6:,192.168.1.7:,192.168.1.8:'

2018-10-25T00:37:37.644651Z 0 [Note] WSREP: (2c12c4d0, 'tcp://0.0.0.0:4567') connection established to 2c12c4d0 tcp://192.168.1.8:4567

2018-10-25T00:37:37.644690Z 0 [Warning] WSREP: (2c12c4d0, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.8:4567' points to own listening address, blacklisting

2018-10-25T00:37:37.645337Z 0 [Note] WSREP: (2c12c4d0, 'tcp://0.0.0.0:4567') connection established to e9a1e0cb tcp://192.168.1.6:4567

2018-10-25T00:37:37.645409Z 0 [Note] WSREP: (2c12c4d0, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:

2018-10-25T00:37:37.645491Z 0 [Note] WSREP: (2c12c4d0, 'tcp://0.0.0.0:4567') connection established to 37f7ecae tcp://192.168.1.7:4567

2018-10-25T00:37:38.087173Z 0 [Note] WSREP: declaring 37f7ecae at tcp://192.168.1.7:4567 stable

2018-10-25T00:37:38.087221Z 0 [Note] WSREP: declaring e9a1e0cb at tcp://192.168.1.6:4567 stable

2018-10-25T00:37:38.087766Z 0 [Note] WSREP: Node 37f7ecae state primary

2018-10-25T00:37:38.088418Z 0 [Note] WSREP: Current view of cluster as seen by this node

view (view_id(PRIM,2c12c4d0,3)

memb {

2c12c4d0,0

37f7ecae,0

e9a1e0cb,0

}

joined {

}

left {

}

partitioned {

}

)

2018-10-25T00:37:38.088467Z 0 [Note] WSREP: Save the discovered primary-component to disk

2018-10-25T00:37:38.143936Z 0 [Note] WSREP: gcomm: connected

2018-10-25T00:37:38.144112Z 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)

2018-10-25T00:37:38.144223Z 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 3

2018-10-25T00:37:38.144485Z 0 [Note] WSREP: Waiting for SST/IST to complete.

2018-10-25T00:37:38.144937Z 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 2c5f795f-d7ee-11e8-8779-ef01318078bb

2018-10-25T00:37:38.145593Z 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 2c5f795f-d7ee-11e8-8779-ef01318078bb

2018-10-25T00:37:38.146131Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2c5f795f-d7ee-11e8-8779-ef01318078bb from 0 (pxc3)

2018-10-25T00:37:38.146183Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2c5f795f-d7ee-11e8-8779-ef01318078bb from 1 (pxc2)

2018-10-25T00:37:38.146191Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2c5f795f-d7ee-11e8-8779-ef01318078bb from 2 (pxc1)

2018-10-25T00:37:38.146209Z 0 [Note] WSREP: Quorum results:

version = 4,

component = PRIMARY,

conf_id = 2,

members = 2/3 (primary/total),

act_id = 5,

last_appl. = -1,

protocols = 0/9/3 (gcs/repl/appl),

group UUID = 8c46dd3d-d769-11e8-84c6-d6aac3977406

2018-10-25T00:37:38.146230Z 0 [Note] WSREP: Flow-control interval: [173, 173]

2018-10-25T00:37:38.146235Z 0 [Note] WSREP: Trying to continue unpaused monitor

2018-10-25T00:37:38.146239Z 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 5)

2018-10-25T00:37:38.146330Z 2 [Note] WSREP: State transfer required:

Group state: 8c46dd3d-d769-11e8-84c6-d6aac3977406:5

Local state: 00000000-0000-0000-0000-000000000000:-1

2018-10-25T00:37:38.146378Z 2 [Note] WSREP: REPL Protocols: 9 (4, 2)

2018-10-25T00:37:38.146390Z 2 [Note] WSREP: New cluster view: global state: 8c46dd3d-d769-11e8-84c6-d6aac3977406:5, view# 3: Primary, number of nodes: 3, my index: 0, protocol version 3

2018-10-25T00:37:38.146413Z 2 [Note] WSREP: Setting wsrep_ready to true

2018-10-25T00:37:38.146417Z 2 [Warning] WSREP: Gap in state sequence. Need state transfer.

2018-10-25T00:37:38.146421Z 2 [Note] WSREP: Setting wsrep_ready to false

2018-10-25T00:37:38.146607Z 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.8' --datadir '/data/mysql/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '19275' --mysqld-version '5.7.23-23-57' '' )

2018-10-25T00:37:38.276053Z WSREP_SST: [WARNING] wsrep_node_address or wsrep_sst_receive_address not set. Consider setting them if SST fails.

2018-10-25T00:37:38.717842Z 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.1.8:4444/xtrabackup_sst//1

2018-10-25T00:37:38.717898Z 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 1) (Increment: 1 -> 3)

2018-10-25T00:37:38.717913Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-10-25T00:37:38.717956Z 2 [Note] WSREP: Assign initial position for certification: 5, protocol version: 4

2018-10-25T00:37:38.718094Z 0 [Note] WSREP: Service thread queue flushed.

2018-10-25T00:37:38.718185Z 2 [Note] WSREP: Check if state gap can be serviced using IST

2018-10-25T00:37:38.718221Z 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: 8c46dd3d-d769-11e8-84c6-d6aac3977406

2018-10-25T00:37:38.718290Z 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST

2018-10-25T00:37:38.718309Z 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (8c46dd3d-d769-11e8-84c6-d6aac3977406): 1 (Operation not permitted)

at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.

2018-10-25T00:37:38.719260Z 0 [Note] WSREP: Member 0.0 (pxc3) requested state transfer from '*any*'. Selected 1.0 (pxc2)(SYNCED) as donor.

2018-10-25T00:37:38.719292Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 5)

2018-10-25T00:37:38.719342Z 2 [Note] WSREP: Requesting state transfer: success, donor: 1

2018-10-25T00:37:38.719379Z 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 8c46dd3d-d769-11e8-84c6-d6aac3977406:5

2018-10-25T00:37:39.065413Z WSREP_SST: [INFO] Proceeding with SST.........

2018-10-25T00:37:39.107901Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!

2018-10-25T00:37:41.144363Z 0 [Note] WSREP: (2c12c4d0, 'tcp://0.0.0.0:4567') turning message relay requesting off

2018-10-25T00:37:57.416507Z 0 [Note] WSREP: 1.0 (pxc2): State transfer to 0.0 (pxc3) complete.

2018-10-25T00:37:57.417182Z 0 [Note] WSREP: Member 1.0 (pxc2) synced with group.

2018-10-25T00:37:57.792678Z WSREP_SST: [INFO] Preparing the backup at /data/mysql/data//.sst

2018-10-25T00:38:01.216124Z WSREP_SST: [INFO] Moving the backup to /data/mysql/data/

2018-10-25T00:38:01.334368Z WSREP_SST: [INFO] Galera co-ords from recovery: 8c46dd3d-d769-11e8-84c6-d6aac3977406:5

2018-10-25T00:38:01.349989Z 0 [Note] WSREP: SST complete, seqno: 5

2018-10-25T00:38:01.351642Z 0 [Note] InnoDB: PUNCH HOLE support available

2018-10-25T00:38:01.351673Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-10-25T00:38:01.351679Z 0 [Note] InnoDB: Uses event mutexes

2018-10-25T00:38:01.351684Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

2018-10-25T00:38:01.351689Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-10-25T00:38:01.351693Z 0 [Note] InnoDB: Using Linux native AIO

2018-10-25T00:38:01.351959Z 0 [Note] InnoDB: Number of pools: 1

2018-10-25T00:38:01.352117Z 0 [Note] InnoDB: Not using CPU crc32 instructions

2018-10-25T00:38:01.353692Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2018-10-25T00:38:01.357575Z 0 [Note] InnoDB: Completed initialization of buffer pool

2018-10-25T00:38:01.359321Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2018-10-25T00:38:01.371008Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /data/mysql/data/xb_doublewrite

2018-10-25T00:38:01.372004Z 0 [Note] InnoDB: Highest supported file format is Barracuda.

2018-10-25T00:38:01.435065Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/data/xb_doublewrite, size 3932160 bytes

2018-10-25T00:38:01.443390Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2018-10-25T00:38:01.443417Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2018-10-25T00:38:01.443468Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2018-10-25T00:38:01.460205Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2018-10-25T00:38:01.461083Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

2018-10-25T00:38:01.461110Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.

2018-10-25T00:38:01.465650Z 0 [Note] InnoDB: Percona XtraDB () 5.7.23-23 started; log sequence number 2596904

2018-10-25T00:38:01.466028Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/data/ib_buffer_pool

2018-10-25T00:38:01.474954Z 0 [Note] InnoDB: Buffer pool(s) load completed at 181025 8:38:01

2018-10-25T00:38:01.475105Z 0 [Note] Plugin 'FEDERATED' is disabled.

2018-10-25T00:38:01.482292Z 0 [Note] Salting uuid generator variables, current_pid: 19275, server_start_time: 1540427857, bytes_sent: 0,

2018-10-25T00:38:01.482368Z 0 [Note] Generated uuid: '3a4894e6-d7ee-11e8-8d4d-52540012a8a2', server_start_time: 5425430177638341532, bytes_sent: 47033232

2018-10-25T00:38:01.482410Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3a4894e6-d7ee-11e8-8d4d-52540012a8a2.

2018-10-25T00:38:01.484106Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.

2018-10-25T00:38:01.484140Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.

2018-10-25T00:38:01.490882Z 0 [Warning] CA certificate ca.pem is self signed.

2018-10-25T00:38:01.490975Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.

2018-10-25T00:38:01.491111Z 0 [Note] Server hostname (bind-address): '*'; port: 3306

2018-10-25T00:38:01.491163Z 0 [Note] IPv6 is available.

2018-10-25T00:38:01.491187Z 0 [Note] - '::' resolves to '::';

2018-10-25T00:38:01.491215Z 0 [Note] Server socket created on IP: '::'.

2018-10-25T00:38:01.500423Z 0 [Note] Event Scheduler: Loaded 0 events

2018-10-25T00:38:01.500596Z 0 [Note] WSREP: Signalling provider to continue on SST completion.

2018-10-25T00:38:01.500641Z 0 [Note] WSREP: Initialized wsrep sidno 2

2018-10-25T00:38:01.500675Z 0 [Note] WSREP: SST received: 8c46dd3d-d769-11e8-84c6-d6aac3977406:5

2018-10-25T00:38:01.501000Z 0 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.7.23-23-57' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona XtraDB Cluster (GPL), Release rel23, Revision f5578f0, WSREP version 31.31, wsrep_31.31

2018-10-25T00:38:01.503540Z 0 [Note] WSREP: 0.0 (pxc3): State transfer from 1.0 (pxc2) complete.

2018-10-25T00:38:01.503558Z 0 [Note] WSREP: SST leaving flow control

2018-10-25T00:38:01.503563Z 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 5)

2018-10-25T00:38:01.504189Z 0 [Note] WSREP: Member 0.0 (pxc3) synced with group.

2018-10-25T00:38:01.504209Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 5)

2018-10-25T00:38:01.504236Z 2 [Note] WSREP: Synchronized with group, ready for connections

2018-10-25T00:38:01.504258Z 2 [Note] WSREP: Setting wsrep_ready to true

2018-10-25T00:38:01.504272Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

主節點批次寫入資料測試:

[root@test06 /]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 5.7.23-23-31.31 Percona XtraDB Cluster binary (GPL) 5.7.23-31.31, Revision f5578f0, wsrep_31.31

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> create database test;

Query OK, 1 row affected (0.03 sec)

mysql> delimiter //

mysql> create procedure test_load(count int unsigned)

-> begin

-> declare s int unsigned default 1;

-> declare c char(80) default repeat('a',80);

-> while s <= count do

-> insert into test select null,c;

-> commit;

-> set s = s + 1;

-> end while;

-> end;

-> //

Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;

mysql> call test_load(10000);

Query OK, 0 rows affected (1 min 20.58 sec)

登陸兩個從節點檢視同步資料:

[root@test07 ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.7.23-23-57 Percona XtraDB Cluster (GPL), Release rel23, Revision f5578f0, WSREP version 31.31, wsrep_31.31

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select count(*) from test;

+----------+

| count(*) |

+----------+

| 10000 |

+----------+

1 row in set (0.00 sec)

[root@test08 ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.7.23-23-57 Percona XtraDB Cluster (GPL), Release rel23, Revision f5578f0, WSREP version 31.31, wsrep_31.31

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select count(*) from test;

+----------+

| count(*) |

+----------+

| 10000 |

+----------+

1 row in set (0.00 sec)

備註:pxc在任何一個節點寫入資料都會自動同步到其他節點。

另外，pxc不支援沒有主鍵的表，否則插入資料會報錯，例如:

mysql> create table a(id int);

Query OK, 0 rows affected (0.07 sec)

mysql> insert into a values(1);

ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (dsf.a) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

PXC(Percona-XtraDB-Cluster)5.7版本叢集安裝和使用初探

相關文章