MySQL高可用集群之PXC

September 13, 2018CommentsRead

MySQL高可用集群之PXC环境：

Linux mysql01.t4x.org 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

Linux mysql02.t4x.org 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

Linux mysql02.t4x.org 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

Linux mysql01.t4x.org 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

Linux mysql02.t4x.org 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

一、PXC安装：

二进制安装：文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

ssl100 - for all Debian/Ubuntu versions (libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f2e389a5000));
ssl101 - for CentOS 6 and CentOS 7 (libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007facbe8c4000)); 文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

$ yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes openssl openssl-devel socat libaio-devel lsof rsync#必须安装
$ yum install numactl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat readline-devel lsof rsync openssl openssl-devel gcc gcc-c++ vim wget lrzsz perl-Digest-MD5 -y #openssl openssl-devel
$ yum install scons gcc gcc-c++ openssl opessl-devel check cmake bison boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel socat    #不需要
$ wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.18-29.20/binary/tarball/Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101.tar.gz
$ mv Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101 /opt/
$ ln -s /opt/Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101 /usr/local/mysql
$ mkdir /data/3306/{data,sql_log} -p
$ ls -l /data/3306
$ groupadd -r mysql -g 27
$ useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin
$ chown -R mysql.mysql /data/3306/data/
$ mkdir /usr/local/mysql/tmp
$ chown -R mysql.mysql /usr/local/mysql/tmp/
$ /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data/   #删除
$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/pxc_my.cnf --initialize-insecure --user=mysql --datadir=/data/3306/data/    #删除
$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/3306/data/

$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 
$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --wsrep-new-cluster
> SET PASSWORD = PASSWORD('admin');
> grant usage on *.* to 'pxc-monitor'@'%' identified by 'admin';      
> grant all privileges on *.* to 'sst'@'%' identified by 'admin';

$ yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes openssl openssl-devel socat libaio-devel lsof rsync#必须安装

$ yum install numactl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat readline-devel lsof rsync openssl openssl-devel gcc gcc-c++ vim wget lrzsz perl-Digest-MD5 -y #openssl openssl-devel

$ yum install scons gcc gcc-c++ openssl opessl-devel check cmake bison boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel socat #不需要

$ wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.18-29.20/binary/tarball/Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101.tar.gz

$ mv Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101 /opt/

$ ln -s /opt/Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101 /usr/local/mysql

$ mkdir /data/3306/{data,sql_log} -p

$ ls -l /data/3306

$ groupadd -r mysql -g 27

$ useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin

$ chown -R mysql.mysql /data/3306/data/

$ mkdir /usr/local/mysql/tmp

$ chown -R mysql.mysql /usr/local/mysql/tmp/

$ /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data/ #删除

$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/pxc_my.cnf --initialize-insecure --user=mysql --datadir=/data/3306/data/ #删除

$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/3306/data/

$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --wsrep-new-cluster

> SET PASSWORD = PASSWORD('admin');

> grant usage on *.* to 'pxc-monitor'@'%' identified by 'admin';

> grant all privileges on *.* to 'sst'@'%' identified by 'admin';

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

二、XtraBackup安装：

numactl
$ yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel vim-common
$ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/tarball/percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
$ tar zxf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
$ mv percona-xtrabackup-2.4.8-Linux-x86_64 /opt/
$ ln -s /opt/percona-xtrabackup-2.4.8-Linux-x86_64 /usr/local/xtrabackup
$ ln -s /usr/local/xtrabackup/bin/xtrabackup /usr/sbin/xtrabackup
$ ln -s /usr/local/xtrabackup/bin/xbcloud /usr/sbin/xbcloud
$ ln -s /usr/local/xtrabackup/bin/xbstream /usr/sbin/xbstream
$ ln -s /usr/local/xtrabackup/bin/xbcrypt /usr/sbin/xbcrypt 
$ ln -s /usr/local/xtrabackup/bin/xbcloud_osenv /usr/sbin/xbcloud_osenv
$ ln -s /usr/local/xtrabackup/bin/innobackupex /usr/sbin/innobackupex

numactl

$ yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel vim-common

$ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/tarball/percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz

$ tar zxf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz

$ mv percona-xtrabackup-2.4.8-Linux-x86_64 /opt/

$ ln -s /opt/percona-xtrabackup-2.4.8-Linux-x86_64 /usr/local/xtrabackup

$ ln -s /usr/local/xtrabackup/bin/xtrabackup /usr/sbin/xtrabackup

$ ln -s /usr/local/xtrabackup/bin/xbcloud /usr/sbin/xbcloud

$ ln -s /usr/local/xtrabackup/bin/xbstream /usr/sbin/xbstream

$ ln -s /usr/local/xtrabackup/bin/xbcrypt /usr/sbin/xbcrypt

$ ln -s /usr/local/xtrabackup/bin/xbcloud_osenv /usr/sbin/xbcloud_osenv

$ ln -s /usr/local/xtrabackup/bin/innobackupex /usr/sbin/innobackupex

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

测试数据：

mysql> create database abc;
mysql> use abc
mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
mysql> INSERT INTO abc.example VALUES (1, 'percona1'); 
mysql> SELECT * FROM abc.example;    
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
mysql> CREATE TABLE myisamexample (node_id INT PRIMARY KEY, node_name VARCHAR(30)) engine=myisam;
mysql> INSERT INTO abc.myisamexample VALUES (1, 'myisam');         
ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (abc.myisamexample) that resides in non-transactional storage engine with pxc_strict_mode = ENFORCING or MASTER

mysql> create database abc;

mysql> use abc

mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));

mysql> INSERT INTO abc.example VALUES (1, 'percona1');

mysql> SELECT * FROM abc.example;

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

| node_id | node_name |

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

| 1 | percona1 |

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

mysql> CREATE TABLE myisamexample (node_id INT PRIMARY KEY, node_name VARCHAR(30)) engine=myisam;

mysql> INSERT INTO abc.myisamexample VALUES (1, 'myisam');

ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (abc.myisamexample) that resides in non-transactional storage engine with pxc_strict_mode = ENFORCING or MASTER

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

三、全站备份：

$ innobackupex --databases="abc" /tmp/bk --user=root --password=admin
$ innobackupex /tmp/bk --user=root --password=admin
$ innobackupex --apply-log /tmp/bk/2017-09-19_02-09-07/
$ innobackupex --copy-back /tmp/bk/2017-09-19_02-09-07/
170919 02:14:37 innobackupex: Starting the copy-back operation

Original data directory /data/3306/data is not empty!
[root@mysql01 data]# rm -rf /data/3306/data/*
[root@mysql01 data]# innobackupex --copy-back /tmp/bk/2017-09-19_02-09-07/
[root@mysql01 data]# chown -R mysql.mysql /data/3306/data/

$ innobackupex --databases="abc" /tmp/bk --user=root --password=admin

$ innobackupex /tmp/bk --user=root --password=admin

$ innobackupex --apply-log /tmp/bk/2017-09-19_02-09-07/

$ innobackupex --copy-back /tmp/bk/2017-09-19_02-09-07/

170919 02:14:37 innobackupex: Starting the copy-back operation

Original data directory /data/3306/data is not empty!

[root@mysql01 data]# rm -rf /data/3306/data/*

[root@mysql01 data]# innobackupex --copy-back /tmp/bk/2017-09-19_02-09-07/

[root@mysql01 data]# chown -R mysql.mysql /data/3306/data/

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

完成后查看状态：

mysql> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
mysql> show status like 'wsrep_incoming_addresses'; 
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| wsrep_incoming_addresses | 1.1.1.111:3306,1.1.1.112:3306 |
+--------------------------+-------------------------------+
1 row in set (0.00 sec)

mysql> show global status like 'wsrep%';
+----------------------------------+----------------------------------------------+
| Variable_name                    | Value                                        |
+----------------------------------+----------------------------------------------+
| wsrep_local_state_uuid           | e91545a3-9961-11e7-a297-a6d9abf17c70         |
| wsrep_protocol_version           | 7                                            |
| wsrep_last_committed             | 0                                            |
| wsrep_replicated                 | 0                                            |
| wsrep_replicated_bytes           | 0                                            |
| wsrep_repl_keys                  | 0                                            |
| wsrep_repl_keys_bytes            | 0                                            |
| wsrep_repl_data_bytes            | 0                                            |
| wsrep_repl_other_bytes           | 0                                            |
| wsrep_received                   | 13                                           |
| wsrep_received_bytes             | 1801                                         |
| wsrep_local_commits              | 0                                            |
| wsrep_local_cert_failures        | 0                                            |
| wsrep_local_replays              | 0                                            |
| wsrep_local_send_queue           | 0                                            |
| wsrep_local_send_queue_max       | 1                                            |
| wsrep_local_send_queue_min       | 0                                            |
| wsrep_local_send_queue_avg       | 0.000000                                     |
| wsrep_local_recv_queue           | 0                                            |
| wsrep_local_recv_queue_max       | 2                                            |
| wsrep_local_recv_queue_min       | 0                                            |
| wsrep_local_recv_queue_avg       | 0.076923                                     |
| wsrep_local_cached_downto        | 0                                            |
| wsrep_flow_control_paused_ns     | 0                                            |
| wsrep_flow_control_paused        | 0.000000                                     |
| wsrep_flow_control_sent          | 0                                            |
| wsrep_flow_control_recv          | 0                                            |
| wsrep_flow_control_interval      | [ 173, 173 ]                                 |
| wsrep_flow_control_interval_low  | 173                                          |
| wsrep_flow_control_interval_high | 173                                          |
| wsrep_flow_control_status        | OFF                                          |
| wsrep_cert_deps_distance         | 0.000000                                     |
| wsrep_apply_oooe                 | 0.000000                                     |
| wsrep_apply_oool                 | 0.000000                                     |
| wsrep_apply_window               | 0.000000                                     |
| wsrep_commit_oooe                | 0.000000                                     |
| wsrep_commit_oool                | 0.000000                                     |
| wsrep_commit_window              | 0.000000                                     |
| wsrep_local_state                | 4                                            |
| wsrep_local_state_comment        | Synced                                       |
| wsrep_cert_index_size            | 0                                            |
| wsrep_cert_bucket_count          | 22                                           |
| wsrep_gcache_pool_size           | 1544                                         |
| wsrep_causal_reads               | 0                                            |
| wsrep_cert_interval              | 0.000000                                     |
| wsrep_ist_receive_status         |                                              |
| wsrep_ist_receive_seqno_start    | 0                                            |
| wsrep_ist_receive_seqno_current  | 0                                            |
| wsrep_ist_receive_seqno_end      | 0                                            |
| wsrep_incoming_addresses         | 1.1.1.112:3306,1.1.1.111:3306,1.1.1.113:3306 |
| wsrep_desync_count               | 0                                            |
| wsrep_evs_delayed                |                                              |
| wsrep_evs_evict_list             |                                              |
| wsrep_evs_repl_latency           | 0/0/0/0/0                                    |
| wsrep_evs_state                  | OPERATIONAL                                  |
| wsrep_gcomm_uuid                 | e0f85c9b-995d-11e7-a8cc-0ed149d31c1c         |
| wsrep_cluster_conf_id            | 9                                            |
| wsrep_cluster_size               | 3                                            |
| wsrep_cluster_state_uuid         | e91545a3-9961-11e7-a297-a6d9abf17c70         |
| wsrep_cluster_status             | Primary                                      |
| wsrep_connected                  | ON                                           |
| wsrep_local_bf_aborts            | 0                                            |
| wsrep_local_index                | 1                                            |
| wsrep_provider_name              | Galera                                       |
| wsrep_provider_vendor            | Codership Oy <info@codership.com>            |
| wsrep_provider_version           | 3.20(r)                                      |
| wsrep_ready                      | ON                                           |
+----------------------------------+----------------------------------------------+
67 rows in set (0.00 sec)

mysql> show global status like 'wsrep_cluster_size';

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

| Variable_name | Value |

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

| wsrep_cluster_size | 3 |

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

mysql> show status like 'wsrep_incoming_addresses';

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

| Variable_name | Value |

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

| wsrep_incoming_addresses | 1.1.1.111:3306,1.1.1.112:3306 |

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

1 row in set (0.00 sec)

mysql> show global status like 'wsrep%';

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

| Variable_name | Value |

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

| wsrep_local_state_uuid | e91545a3-9961-11e7-a297-a6d9abf17c70 |

| wsrep_protocol_version | 7 |

| wsrep_last_committed | 0 |

| wsrep_replicated | 0 |

| wsrep_replicated_bytes | 0 |

| wsrep_repl_keys | 0 |

| wsrep_repl_keys_bytes | 0 |

| wsrep_repl_data_bytes | 0 |

| wsrep_repl_other_bytes | 0 |

| wsrep_received | 13 |

| wsrep_received_bytes | 1801 |

| wsrep_local_commits | 0 |

| wsrep_local_cert_failures | 0 |

| wsrep_local_replays | 0 |

| wsrep_local_send_queue | 0 |

| wsrep_local_send_queue_max | 1 |

| wsrep_local_send_queue_min | 0 |

| wsrep_local_send_queue_avg | 0.000000 |

| wsrep_local_recv_queue | 0 |

| wsrep_local_recv_queue_max | 2 |

| wsrep_local_recv_queue_min | 0 |

| wsrep_local_recv_queue_avg | 0.076923 |

| wsrep_local_cached_downto | 0 |

| wsrep_flow_control_paused_ns | 0 |

| wsrep_flow_control_paused | 0.000000 |

| wsrep_flow_control_sent | 0 |

| wsrep_flow_control_recv | 0 |

| wsrep_flow_control_interval | [ 173, 173 ] |

| wsrep_flow_control_interval_low | 173 |

| wsrep_flow_control_interval_high | 173 |

| wsrep_flow_control_status | OFF |

| wsrep_cert_deps_distance | 0.000000 |

| wsrep_apply_oooe | 0.000000 |

| wsrep_apply_oool | 0.000000 |

| wsrep_apply_window | 0.000000 |

| wsrep_commit_oooe | 0.000000 |

| wsrep_commit_oool | 0.000000 |

| wsrep_commit_window | 0.000000 |

| wsrep_local_state | 4 |

| wsrep_local_state_comment | Synced |

| wsrep_cert_index_size | 0 |

| wsrep_cert_bucket_count | 22 |

| wsrep_gcache_pool_size | 1544 |

| wsrep_causal_reads | 0 |

| wsrep_cert_interval | 0.000000 |

| wsrep_ist_receive_status | |

| wsrep_ist_receive_seqno_start | 0 |

| wsrep_ist_receive_seqno_current | 0 |

| wsrep_ist_receive_seqno_end | 0 |

| wsrep_incoming_addresses | 1.1.1.112:3306,1.1.1.111:3306,1.1.1.113:3306 |

| wsrep_desync_count | 0 |

| wsrep_evs_delayed | |

| wsrep_evs_evict_list | |

| wsrep_evs_repl_latency | 0/0/0/0/0 |

| wsrep_evs_state | OPERATIONAL |

| wsrep_gcomm_uuid | e0f85c9b-995d-11e7-a8cc-0ed149d31c1c |

| wsrep_cluster_conf_id | 9 |

| wsrep_cluster_size | 3 |

| wsrep_cluster_state_uuid | e91545a3-9961-11e7-a297-a6d9abf17c70 |

| wsrep_cluster_status | Primary |

| wsrep_connected | ON |

| wsrep_local_bf_aborts | 0 |

| wsrep_local_index | 1 |

| wsrep_provider_name | Galera |

| wsrep_provider_vendor | Codership Oy <info@codership.com> |

| wsrep_provider_version | 3.20(r) |

| wsrep_ready | ON |

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

67 rows in set (0.00 sec)

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

四、错误解决：

Q:

galera/src/replicator_smm.cpp:connect():347: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .

0

galera/src/replicator_smm.cpp:connect():347: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .

A:

$ rm -f /data/3306/data/grastate.dat

0

$ rm -f /data/3306/data/grastate.dat

Q:

/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

0

/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

A:

0

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

Q:
结点3错误日志：

2017-09-20T18:52:48.772011+08:00 WSREP_SST: [INFO] ............Waiting for SST streaming to complete! 2017-09-20T18:53:00.562178+08:00 WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 2017-09-20T18:53:00.563222+08:00 WSREP_SST: [ERROR] xtrabackup_checkpoints missing. xtrabackup/SST failed on DONOR. Check DONOR log 2017-09-20T18:53:00.564150+08:00 WSREP_SST: [ERROR] ****************************************************** 2017-09-20T18:53:00.565241+08:00 WSREP_SST: [ERROR] Cleanup after exit with status:2 2017-09-20T18:53:00.574468+08:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '1.1.1.112' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '2408' --binlog '/data/3306/sql_log/mysql-bin' : 2 (No such file or directory) 2017-09-20T18:53:00.574503+08:00 0 [ERROR] WSREP: Failed to read uuid:seqno from joiner script. 2017-09-20T18:53:00.574514+08:00 0 [ERROR] WSREP: SST script aborted with error 2 (No such file or directory) 2017-09-20T18:53:00.574546+08:00 0 [ERROR] WSREP: SST failed: 2 (No such file or directory) 2017-09-20T18:53:00.574556+08:00 0 [ERROR] Aborting

0
1
2
3
4
5
6
7
8
9

        2017-09-20T18:52:48.772011+08:00 WSREP_SST: [INFO] ............Waiting for SST streaming to complete!
        2017-09-20T18:53:00.562178+08:00 WSREP_SST: [ERROR] ******************* FATAL ERROR **********************
        2017-09-20T18:53:00.563222+08:00 WSREP_SST: [ERROR] xtrabackup_checkpoints missing. xtrabackup/SST failed on DONOR. Check DONOR log
        2017-09-20T18:53:00.564150+08:00 WSREP_SST: [ERROR] ******************************************************
        2017-09-20T18:53:00.565241+08:00 WSREP_SST: [ERROR] Cleanup after exit with status:2
2017-09-20T18:53:00.574468+08:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '1.1.1.112' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '2408' --binlog '/data/3306/sql_log/mysql-bin' : 2 (No such file or directory)
2017-09-20T18:53:00.574503+08:00 0 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2017-09-20T18:53:00.574514+08:00 0 [ERROR] WSREP: SST script aborted with error 2 (No such file or directory)
2017-09-20T18:53:00.574546+08:00 0 [ERROR] WSREP: SST failed: 2 (No such file or directory)
2017-09-20T18:53:00.574556+08:00 0 [ERROR] Aborting

结点1错误日志：[执行XtraBackup备份时]

170920 19:19:31 >> log scanned up to (2567352) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0 InnoDB: Attempted to open a previously opened tablespace. Previous tablespace abc/example at filepath: ./abc/example.ibd uses space ID: 28. Cannot open filepath: ./xxxx/xxxx.ibd which uses the same space ID.

0
1
2
3

170920 19:19:31 >> log scanned up to (2567352)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
InnoDB: Attempted to open a previously opened tablespace. Previous tablespace abc/example at filepath: ./abc/example.ibd uses space ID: 28. Cannot open filepath: ./xxxx/xxxx.ibd which uses the same space ID.

A:

数据库异常，导致备份无法完成，间接导致另外一台服务器异常。

0

数据库异常，导致备份无法完成，间接导致另外一台服务器异常。

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

Q:

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.

0

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.

A:

yum install perl-Digest-MD5

0

yum install perl-Digest-MD5

Q:

which: no rsync in (/usr/sbin:/sbin:/opt/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101/bin:/sbin:/usr/sbin:/bin:/usr/bin:/mnt/workspace/percona-xtradb-cluster-5.7-binary-tarball/label_exp/centos6-64/Percona-XtraDB-Cluster-5.7.21-29.26/390/usr/local/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101/bin) 2018-03-26T16:39:02.777149+08:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_rsync --role 'joiner' --address '1.1.1.143' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '10898' --binlog '/data/3306/sql_log/mysql-bin' Read: ''rsync' not found in PATH'

0
1
2

which: no rsync in (/usr/sbin:/sbin:/opt/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101/bin:/sbin:/usr/sbin:/bin:/usr/bin:/mnt/workspace/percona-xtradb-cluster-5.7-binary-tarball/label_exp/centos6-64/Percona-XtraDB-Cluster-5.7.21-29.26/390/usr/local/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101/bin)
2018-03-26T16:39:02.777149+08:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_rsync --role 'joiner' --address '1.1.1.143' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '10898' --binlog '/data/3306/sql_log/mysql-bin'
Read: ''rsync' not found in PATH'

A:

yum install rsync

0

yum install rsync

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-high-availability-percona-xtradb-cluster/

报错日志：

2017-09-14T15:38:44.398172Z 0 [Warning] option 'max_binlog_size': unsigned value 2147483648 adjusted to 1073741824
2017-09-14T23:38:44.398317+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-09-14T23:38:44.398354+08:00 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-09-14T23:38:44.415190+08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-09-14T23:38:44.415245+08:00 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.18-15-29.20-log) starting as process 2207 ...
2017-09-14T23:38:44.530392+08:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2017-09-14T23:38:44.530439+08:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
2017-09-14T23:38:44.599485+08:00 0 [Note] WSREP: wsrep_load(): Galera 3.20(r) by Codership Oy <info@codership.com> loaded successfully.
2017-09-14T23:38:44.599684+08:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
2017-09-14T23:38:44.602535+08:00 0 [Note] WSREP: galera/src/saved_state.cpp:SavedState():120: Found saved state: d205fc58-9947-11e7-905a-2b8124076583:0, safe_to_bootsrap: 1
2017-09-14T23:38:44.635513+08:00 0 [Note] WSREP: galera/src/galera_gcs.hpp:Gcs():89: Passing config to GCS: base_dir = /data/3306/data/; base_host = 1.1.1.113; base_port = 4567; cert.log_conflicts = no; debug = 1; 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/3306/data/; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/3306/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 1G; 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 = 7; socket.checksum = 2; socket.recv_buf_size = 212992; 
2017-09-14T23:38:44.777551+08:00 0 [Note] WSREP: galera/src/certification.cpp:assign_initial_position():913: Assign initial position for certification: 0, protocol version: -1
2017-09-14T23:38:44.777623+08:00 0 [Note] WSREP: Preparing to initiate SST/IST
2017-09-14T23:38:44.777631+08:00 0 [Note] WSREP: Starting replication
2017-09-14T23:38:44.777663+08:00 0 [Note] WSREP: galera/src/replicator_smm.cpp:connect():342: Setting initial position to d205fc58-9947-11e7-905a-2b8124076583:0
2017-09-14T23:38:44.778063+08:00 0 [Note] WSREP: gcomm/src/datagram.cpp:checksum_type():26: Using CRC-32C for message checksums.
2017-09-14T23:38:44.778383+08:00 0 [Note] WSREP: gcs/src/gcs_gcomm.cpp:connect():279: gcomm thread scheduling priority set to other:0 
2017-09-14T23:38:44.778565+08:00 0 [Warning] WSREP: gcomm/src/view.cpp:read_file():389: Fail to access the file (/data/3306/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
2017-09-14T23:38:44.778576+08:00 0 [Note] WSREP: gcomm/src/pc.cpp:PC():256: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
2017-09-14T23:38:44.779239+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:GMCast():98: GMCast version 0
2017-09-14T23:38:44.779584+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:GMCast():191: (cae2ac03, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
2017-09-14T23:38:44.779604+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:GMCast():192: (cae2ac03, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
2017-09-14T23:38:44.780054+08:00 0 [Note] WSREP: gcomm/src/evs_proto.cpp:Proto():199: EVS version 0
2017-09-14T23:38:44.780310+08:00 0 [Note] WSREP: gcs/src/gcs_gcomm.cpp:connect():309: gcomm: connecting to group 'Byrd_PXC', peer '1.1.1.111:,1.1.1.112:,1.1.1.113:'
2017-09-14T23:38:44.784768+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_established():555: (cae2ac03, 'tcp://0.0.0.0:4567') connection established to cae2ac03 tcp://1.1.1.113:4567
2017-09-14T23:38:44.784841+08:00 0 [Warning] WSREP: gcomm/src/gmcast.cpp:handle_established():580: (cae2ac03, 'tcp://0.0.0.0:4567') address 'tcp://1.1.1.113:4567' points to own listening address, blacklisting
2017-09-14T23:38:44.785268+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_established():555: (cae2ac03, 'tcp://0.0.0.0:4567') connection established to e0f85c9b tcp://1.1.1.111:4567
2017-09-14T23:38:44.785363+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:check_liveness():1147: (cae2ac03, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
2017-09-14T23:38:44.785384+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_established():555: (cae2ac03, 'tcp://0.0.0.0:4567') connection established to 857db596 tcp://1.1.1.112:4567
2017-09-14T23:38:44.861669+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_stable_view():1597: declaring 857db596 at tcp://1.1.1.112:4567 stable
2017-09-14T23:38:44.861816+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_stable_view():1597: declaring e0f85c9b at tcp://1.1.1.111:4567 stable
2017-09-14T23:38:44.863719+08:00 0 [Note] WSREP: gcomm/src/pc_proto.cpp:is_prim():796: Node 857db596 state primary
2017-09-14T23:38:44.866475+08:00 0 [Note] WSREP: gcomm/src/pc_proto.cpp:deliver_view():229: Current view of cluster as seen by this node
view (view_id(PRIM,857db596,17)
memb {
        857db596,0
        cae2ac03,0
        e0f85c9b,0
        }
joined {
        }
left {
        }
partitioned {
        }
)
2017-09-14T23:38:44.866526+08:00 0 [Note] WSREP: gcomm/src/pc.cpp:handle_up():30: Save the discovered primary-component to disk
2017-09-14T23:38:45.284527+08:00 0 [Note] WSREP: gcs/src/gcs_gcomm.cpp:connect():319: gcomm: connected
2017-09-14T23:38:45.284964+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_shift_state():596: Shifting CLOSED -> OPEN (TO: 0)
2017-09-14T23:38:45.285518+08:00 0 [Note] WSREP: Waiting for SST/IST to complete.
2017-09-14T23:38:45.286499+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_comp_msg():410: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 3
2017-09-14T23:38:45.286680+08:00 0 [Note] WSREP: gcs/src/gcs_core.cpp:core_handle_comp_msg():771: STATE EXCHANGE: Waiting for state UUID.
2017-09-14T23:38:45.286840+08:00 0 [Note] WSREP: gcs/src/gcs_core.cpp:core_handle_uuid_msg():861: STATE EXCHANGE: sent state msg: cb033ff5-9962-11e7-95e0-4f27451b8c35
2017-09-14T23:38:45.286870+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_state_msg():592: STATE EXCHANGE: got state msg: cb033ff5-9962-11e7-95e0-4f27451b8c35 from 0 (MySQL02)
2017-09-14T23:38:45.286894+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:group_print_state_debug():574: 
        Version      : 4
        Flags        : 0x3
        Protocols    : 0 / 7 / 3
        State        : SYNCED
        Desync count : 0
        Prim state   : SYNCED
        Prim UUID    : 86182a4e-9962-11e7-8935-13a5c5d97653
        Prim  seqno  : 6
        First seqno  : -1
        Last  seqno  : 0
        Prim JOINED  : 2
        State UUID   : cb033ff5-9962-11e7-95e0-4f27451b8c35
        Group UUID   : e91545a3-9961-11e7-a297-a6d9abf17c70
        Name         : 'MySQL02'
        Incoming addr: '1.1.1.112:3306'

2017-09-14T23:38:45.286910+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_state_msg():592: STATE EXCHANGE: got state msg: cb033ff5-9962-11e7-95e0-4f27451b8c35 from 2 (MySQL01)
2017-09-14T23:38:45.286930+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:group_print_state_debug():574: 
        Version      : 4
        Flags        : 0x2
        Protocols    : 0 / 7 / 3
        State        : SYNCED
        Desync count : 0
        Prim state   : SYNCED
        Prim UUID    : 86182a4e-9962-11e7-8935-13a5c5d97653
        Prim  seqno  : 6
        First seqno  : -1
        Last  seqno  : 0
        Prim JOINED  : 2
        State UUID   : cb033ff5-9962-11e7-95e0-4f27451b8c35
        Group UUID   : e91545a3-9961-11e7-a297-a6d9abf17c70
        Name         : 'MySQL01'
        Incoming addr: '1.1.1.111:3306'

2017-09-14T23:38:45.291349+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_state_msg():592: STATE EXCHANGE: got state msg: cb033ff5-9962-11e7-95e0-4f27451b8c35 from 1 (MySQL03)
2017-09-14T23:38:45.291429+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:group_print_state_debug():574: 
        Version      : 4
        Flags        : 00
        Protocols    : 0 / 7 / 3
        State        : NON-PRIMARY
        Desync count : 0
        Prim state   : NON-PRIMARY
        Prim UUID    : 00000000-0000-0000-0000-000000000000
        Prim  seqno  : -1
        First seqno  : -1
        Last  seqno  : 0
        Prim JOINED  : 0
        State UUID   : cb033ff5-9962-11e7-95e0-4f27451b8c35
        Group UUID   : d205fc58-9947-11e7-905a-2b8124076583
        Name         : 'MySQL03'
        Incoming addr: '1.1.1.113:3306'

2017-09-14T23:38:45.291464+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:group_post_state_exchange():369: Quorum results:
        version    = 4,
        component  = PRIMARY,
        conf_id    = 6,
        members    = 2/3 (primary/total),
        act_id     = 0,
        last_appl. = -1,
        protocols  = 0/7/3 (gcs/repl/appl),
        group UUID = e91545a3-9961-11e7-a297-a6d9abf17c70
2017-09-14T23:38:45.291591+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:_set_fc_limits():803: Flow-control interval: [173, 173]
2017-09-14T23:38:45.291616+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_shift_state():596: Shifting OPEN -> PRIMARY (TO: 0)
2017-09-14T23:38:45.292690+08:00 2 [Note] WSREP: galera/src/replicator_smm.cpp:process_conf_change():1536: State transfer required: 
        Group state: e91545a3-9961-11e7-a297-a6d9abf17c70:0
        Local state: d205fc58-9947-11e7-905a-2b8124076583:0
2017-09-14T23:38:45.292820+08:00 2 [Note] WSREP: New cluster view: global state: e91545a3-9961-11e7-a297-a6d9abf17c70:0, view# 7: Primary, number of nodes: 3, my index: 1, protocol version 3
2017-09-14T23:38:45.292884+08:00 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
2017-09-14T23:38:45.293584+08:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '1.1.1.113' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '2207' --binlog '/data/3306/sql_log/mysql-bin' )
which: no xtrabackup in (/usr/sbin:/sbin:/opt/Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
        2017-09-14T23:38:45.461973+08:00 WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
        2017-09-14T23:38:45.464474+08:00 WSREP_SST: [ERROR] xtrabackup not in path: /usr/sbin:/sbin:/opt/Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
        2017-09-14T23:38:45.465838+08:00 WSREP_SST: [ERROR] ****************************************************** 
2017-09-14T23:38:45.466092+08:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '1.1.1.113' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '2207' --binlog '/data/3306/sql_log/mysql-bin' 
        Read: '(null)'
2017-09-14T23:38:45.466242+08:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '1.1.1.113' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '2207' --binlog '/data/3306/sql_log/mysql-bin' : 2 (No such file or directory)
2017-09-14T23:38:45.466310+08:00 2 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
2017-09-14T23:38:45.466322+08:00 2 [ERROR] Aborting

2017-09-14T23:38:45.466325+08:00 2 [Note] Giving 2 client threads a chance to die gracefully
2017-09-14T23:38:47.467157+08:00 2 [Note] WSREP: gcs/src/gcs_sm.cpp:gcs_sm_close():76: Closing send monitor...
2017-09-14T23:38:47.467228+08:00 2 [Note] WSREP: gcs/src/gcs_sm.cpp:gcs_sm_close():106: Closed send monitor.
2017-09-14T23:38:47.467257+08:00 2 [Note] WSREP: gcs/src/gcs_gcomm.cpp:close():331: gcomm: terminating thread
2017-09-14T23:38:47.467276+08:00 2 [Note] WSREP: gcs/src/gcs_gcomm.cpp:close():334: gcomm: joining thread
2017-09-14T23:38:47.467308+08:00 1 [Note] WSREP: rollbacker thread exiting
2017-09-14T23:38:47.467929+08:00 2 [Note] WSREP: gcs/src/gcs_gcomm.cpp:close():338: gcomm: closing backend
2017-09-14T23:38:47.468993+08:00 2 [Note] WSREP: gcomm/src/pc_proto.cpp:deliver_view():229: Current view of cluster as seen by this node
view (view_id(NON_PRIM,857db596,17)
memb {
        cae2ac03,0
        }
joined {
        }
left {
        }
partitioned {
        857db596,0
        e0f85c9b,0
        }
)
2017-09-14T23:38:47.469148+08:00 2 [Note] WSREP: gcomm/src/pc_proto.cpp:deliver_view():229: Current view of cluster as seen by this node
view ((empty))
2017-09-14T23:38:47.469467+08:00 2 [Note] WSREP: gcs/src/gcs_gcomm.cpp:close():350: gcomm: closed
2017-09-14T23:38:47.469569+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_comp_msg():410: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
2017-09-14T23:38:47.469584+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:_set_fc_limits():803: Flow-control interval: [100, 100]
2017-09-14T23:38:47.469589+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_handle_act_conf():923: Received NON-PRIMARY.
2017-09-14T23:38:47.469592+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_shift_state():596: Shifting PRIMARY -> OPEN (TO: 0)
2017-09-14T23:38:47.469599+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_comp_msg():430: Received self-leave message.
2017-09-14T23:38:47.469603+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:_set_fc_limits():803: Flow-control interval: [0, 0]
2017-09-14T23:38:47.469606+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_handle_act_conf():919: Received SELF-LEAVE. Closing connection.
2017-09-14T23:38:47.469608+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_shift_state():596: Shifting OPEN -> CLOSED (TO: 0)
2017-09-14T23:38:47.469613+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_recv_thread():1410: RECV thread exiting 0: Success
2017-09-14T23:38:47.469773+08:00 2 [Note] WSREP: gcs/src/gcs.cpp:_close():1197: recv_thread() joined.
2017-09-14T23:38:47.469788+08:00 2 [Note] WSREP: gcs/src/gcs.cpp:_close():1203: Closing replication queue.
2017-09-14T23:38:47.469792+08:00 2 [Note] WSREP: gcs/src/gcs.cpp:_close():1226: Closing slave action queue.
2017-09-14T23:38:47.469844+08:00 0 [Note] WSREP: Waiting for active wsrep applier to exit
2017-09-14T23:38:47.469849+08:00 0 [Note] WSREP: Service disconnected.
2017-09-14T23:38:47.469851+08:00 0 [Note] WSREP: Waiting to close threads......
2017-09-14T23:38:52.470562+08:00 0 [Note] WSREP: Some threads may fail to exit.
2017-09-14T23:38:52.470764+08:00 0 [Note] Binlog end
2017-09-14T23:38:52.471074+08:00 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

2017-09-14T15:38:44.398172Z 0 [Warning] option 'max_binlog_size': unsigned value 2147483648 adjusted to 1073741824

2017-09-14T23:38:44.398317+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-09-14T23:38:44.398354+08:00 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2017-09-14T23:38:44.415190+08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2017-09-14T23:38:44.415245+08:00 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.18-15-29.20-log) starting as process 2207 ...

2017-09-14T23:38:44.530392+08:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.

2017-09-14T23:38:44.530439+08:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'

2017-09-14T23:38:44.599485+08:00 0 [Note] WSREP: wsrep_load(): Galera 3.20(r) by Codership Oy <info@codership.com> loaded successfully.

2017-09-14T23:38:44.599684+08:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.

2017-09-14T23:38:44.602535+08:00 0 [Note] WSREP: galera/src/saved_state.cpp:SavedState():120: Found saved state: d205fc58-9947-11e7-905a-2b8124076583:0, safe_to_bootsrap: 1

2017-09-14T23:38:44.635513+08:00 0 [Note] WSREP: galera/src/galera_gcs.hpp:Gcs():89: Passing config to GCS: base_dir = /data/3306/data/; base_host = 1.1.1.113; base_port = 4567; cert.log_conflicts = no; debug = 1; 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/3306/data/; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/3306/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 1G; 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 = 7; socket.checksum = 2; socket.recv_buf_size = 212992;

2017-09-14T23:38:44.777551+08:00 0 [Note] WSREP: galera/src/certification.cpp:assign_initial_position():913: Assign initial position for certification: 0, protocol version: -1

2017-09-14T23:38:44.777623+08:00 0 [Note] WSREP: Preparing to initiate SST/IST

2017-09-14T23:38:44.777631+08:00 0 [Note] WSREP: Starting replication

2017-09-14T23:38:44.777663+08:00 0 [Note] WSREP: galera/src/replicator_smm.cpp:connect():342: Setting initial position to d205fc58-9947-11e7-905a-2b8124076583:0

2017-09-14T23:38:44.778063+08:00 0 [Note] WSREP: gcomm/src/datagram.cpp:checksum_type():26: Using CRC-32C for message checksums.

2017-09-14T23:38:44.778383+08:00 0 [Note] WSREP: gcs/src/gcs_gcomm.cpp:connect():279: gcomm thread scheduling priority set to other:0

2017-09-14T23:38:44.778565+08:00 0 [Warning] WSREP: gcomm/src/view.cpp:read_file():389: Fail to access the file (/data/3306/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

2017-09-14T23:38:44.778576+08:00 0 [Note] WSREP: gcomm/src/pc.cpp:PC():256: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown

2017-09-14T23:38:44.779239+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:GMCast():98: GMCast version 0

2017-09-14T23:38:44.779584+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:GMCast():191: (cae2ac03, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567

2017-09-14T23:38:44.779604+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:GMCast():192: (cae2ac03, 'tcp://0.0.0.0:4567') multicast: , ttl: 1

2017-09-14T23:38:44.780054+08:00 0 [Note] WSREP: gcomm/src/evs_proto.cpp:Proto():199: EVS version 0

2017-09-14T23:38:44.780310+08:00 0 [Note] WSREP: gcs/src/gcs_gcomm.cpp:connect():309: gcomm: connecting to group 'Byrd_PXC', peer '1.1.1.111:,1.1.1.112:,1.1.1.113:'

2017-09-14T23:38:44.784768+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_established():555: (cae2ac03, 'tcp://0.0.0.0:4567') connection established to cae2ac03 tcp://1.1.1.113:4567

2017-09-14T23:38:44.784841+08:00 0 [Warning] WSREP: gcomm/src/gmcast.cpp:handle_established():580: (cae2ac03, 'tcp://0.0.0.0:4567') address 'tcp://1.1.1.113:4567' points to own listening address, blacklisting

2017-09-14T23:38:44.785268+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_established():555: (cae2ac03, 'tcp://0.0.0.0:4567') connection established to e0f85c9b tcp://1.1.1.111:4567

2017-09-14T23:38:44.785363+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:check_liveness():1147: (cae2ac03, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:

2017-09-14T23:38:44.785384+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_established():555: (cae2ac03, 'tcp://0.0.0.0:4567') connection established to 857db596 tcp://1.1.1.112:4567

2017-09-14T23:38:44.861669+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_stable_view():1597: declaring 857db596 at tcp://1.1.1.112:4567 stable

2017-09-14T23:38:44.861816+08:00 0 [Note] WSREP: gcomm/src/gmcast.cpp:handle_stable_view():1597: declaring e0f85c9b at tcp://1.1.1.111:4567 stable

2017-09-14T23:38:44.863719+08:00 0 [Note] WSREP: gcomm/src/pc_proto.cpp:is_prim():796: Node 857db596 state primary

2017-09-14T23:38:44.866475+08:00 0 [Note] WSREP: gcomm/src/pc_proto.cpp:deliver_view():229: Current view of cluster as seen by this node

view (view_id(PRIM,857db596,17)

memb {

857db596,0

cae2ac03,0

e0f85c9b,0

}

joined {

}

left {

}

partitioned {

}

)

2017-09-14T23:38:44.866526+08:00 0 [Note] WSREP: gcomm/src/pc.cpp:handle_up():30: Save the discovered primary-component to disk

2017-09-14T23:38:45.284527+08:00 0 [Note] WSREP: gcs/src/gcs_gcomm.cpp:connect():319: gcomm: connected

2017-09-14T23:38:45.284964+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_shift_state():596: Shifting CLOSED -> OPEN (TO: 0)

2017-09-14T23:38:45.285518+08:00 0 [Note] WSREP: Waiting for SST/IST to complete.

2017-09-14T23:38:45.286499+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_comp_msg():410: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 3

2017-09-14T23:38:45.286680+08:00 0 [Note] WSREP: gcs/src/gcs_core.cpp:core_handle_comp_msg():771: STATE EXCHANGE: Waiting for state UUID.

2017-09-14T23:38:45.286840+08:00 0 [Note] WSREP: gcs/src/gcs_core.cpp:core_handle_uuid_msg():861: STATE EXCHANGE: sent state msg: cb033ff5-9962-11e7-95e0-4f27451b8c35

2017-09-14T23:38:45.286870+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_state_msg():592: STATE EXCHANGE: got state msg: cb033ff5-9962-11e7-95e0-4f27451b8c35 from 0 (MySQL02)

2017-09-14T23:38:45.286894+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:group_print_state_debug():574:

Version : 4

Flags : 0x3

Protocols : 0 / 7 / 3

State : SYNCED

Desync count : 0

Prim state : SYNCED

Prim UUID : 86182a4e-9962-11e7-8935-13a5c5d97653

Prim seqno : 6

First seqno : -1

Last seqno : 0

Prim JOINED : 2

State UUID : cb033ff5-9962-11e7-95e0-4f27451b8c35

Group UUID : e91545a3-9961-11e7-a297-a6d9abf17c70

Name : 'MySQL02'

Incoming addr: '1.1.1.112:3306'

2017-09-14T23:38:45.286910+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_state_msg():592: STATE EXCHANGE: got state msg: cb033ff5-9962-11e7-95e0-4f27451b8c35 from 2 (MySQL01)

2017-09-14T23:38:45.286930+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:group_print_state_debug():574:

Version : 4

Flags : 0x2

Protocols : 0 / 7 / 3

State : SYNCED

Desync count : 0

Prim state : SYNCED

Prim UUID : 86182a4e-9962-11e7-8935-13a5c5d97653

Prim seqno : 6

First seqno : -1

Last seqno : 0

Prim JOINED : 2

State UUID : cb033ff5-9962-11e7-95e0-4f27451b8c35

Group UUID : e91545a3-9961-11e7-a297-a6d9abf17c70

Name : 'MySQL01'

Incoming addr: '1.1.1.111:3306'

2017-09-14T23:38:45.291349+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_state_msg():592: STATE EXCHANGE: got state msg: cb033ff5-9962-11e7-95e0-4f27451b8c35 from 1 (MySQL03)

2017-09-14T23:38:45.291429+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:group_print_state_debug():574:

Version : 4

Flags : 00

Protocols : 0 / 7 / 3

State : NON-PRIMARY

Desync count : 0

Prim state : NON-PRIMARY

Prim UUID : 00000000-0000-0000-0000-000000000000

Prim seqno : -1

First seqno : -1

Last seqno : 0

Prim JOINED : 0

State UUID : cb033ff5-9962-11e7-95e0-4f27451b8c35

Group UUID : d205fc58-9947-11e7-905a-2b8124076583

Name : 'MySQL03'

Incoming addr: '1.1.1.113:3306'

2017-09-14T23:38:45.291464+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:group_post_state_exchange():369: Quorum results:

version = 4,

component = PRIMARY,

conf_id = 6,

members = 2/3 (primary/total),

act_id = 0,

last_appl. = -1,

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

group UUID = e91545a3-9961-11e7-a297-a6d9abf17c70

2017-09-14T23:38:45.291591+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:_set_fc_limits():803: Flow-control interval: [173, 173]

2017-09-14T23:38:45.291616+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_shift_state():596: Shifting OPEN -> PRIMARY (TO: 0)

2017-09-14T23:38:45.292690+08:00 2 [Note] WSREP: galera/src/replicator_smm.cpp:process_conf_change():1536: State transfer required:

Group state: e91545a3-9961-11e7-a297-a6d9abf17c70:0

Local state: d205fc58-9947-11e7-905a-2b8124076583:0

2017-09-14T23:38:45.292820+08:00 2 [Note] WSREP: New cluster view: global state: e91545a3-9961-11e7-a297-a6d9abf17c70:0, view# 7: Primary, number of nodes: 3, my index: 1, protocol version 3

2017-09-14T23:38:45.292884+08:00 2 [Warning] WSREP: Gap in state sequence. Need state transfer.

2017-09-14T23:38:45.293584+08:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '1.1.1.113' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '2207' --binlog '/data/3306/sql_log/mysql-bin' )

which: no xtrabackup in (/usr/sbin:/sbin:/opt/Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)

2017-09-14T23:38:45.461973+08:00 WSREP_SST: [ERROR] ******************* FATAL ERROR **********************

2017-09-14T23:38:45.464474+08:00 WSREP_SST: [ERROR] xtrabackup not in path: /usr/sbin:/sbin:/opt/Percona-XtraDB-Cluster-5.7.18-rel15-29.20.1.Linux.x86_64.ssl101/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

2017-09-14T23:38:45.465838+08:00 WSREP_SST: [ERROR] ******************************************************

2017-09-14T23:38:45.466092+08:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '1.1.1.113' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '2207' --binlog '/data/3306/sql_log/mysql-bin'

Read: '(null)'

2017-09-14T23:38:45.466242+08:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '1.1.1.113' --datadir '/data/3306/data/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '2207' --binlog '/data/3306/sql_log/mysql-bin' : 2 (No such file or directory)

2017-09-14T23:38:45.466310+08:00 2 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.

2017-09-14T23:38:45.466322+08:00 2 [ERROR] Aborting

2017-09-14T23:38:45.466325+08:00 2 [Note] Giving 2 client threads a chance to die gracefully

2017-09-14T23:38:47.467157+08:00 2 [Note] WSREP: gcs/src/gcs_sm.cpp:gcs_sm_close():76: Closing send monitor...

2017-09-14T23:38:47.467228+08:00 2 [Note] WSREP: gcs/src/gcs_sm.cpp:gcs_sm_close():106: Closed send monitor.

2017-09-14T23:38:47.467257+08:00 2 [Note] WSREP: gcs/src/gcs_gcomm.cpp:close():331: gcomm: terminating thread

2017-09-14T23:38:47.467276+08:00 2 [Note] WSREP: gcs/src/gcs_gcomm.cpp:close():334: gcomm: joining thread

2017-09-14T23:38:47.467308+08:00 1 [Note] WSREP: rollbacker thread exiting

2017-09-14T23:38:47.467929+08:00 2 [Note] WSREP: gcs/src/gcs_gcomm.cpp:close():338: gcomm: closing backend

2017-09-14T23:38:47.468993+08:00 2 [Note] WSREP: gcomm/src/pc_proto.cpp:deliver_view():229: Current view of cluster as seen by this node

view (view_id(NON_PRIM,857db596,17)

memb {

cae2ac03,0

}

joined {

}

left {

}

partitioned {

857db596,0

e0f85c9b,0

}

)

2017-09-14T23:38:47.469148+08:00 2 [Note] WSREP: gcomm/src/pc_proto.cpp:deliver_view():229: Current view of cluster as seen by this node

view ((empty))

2017-09-14T23:38:47.469467+08:00 2 [Note] WSREP: gcs/src/gcs_gcomm.cpp:close():350: gcomm: closed

2017-09-14T23:38:47.469569+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_comp_msg():410: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1

2017-09-14T23:38:47.469584+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:_set_fc_limits():803: Flow-control interval: [100, 100]

2017-09-14T23:38:47.469589+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_handle_act_conf():923: Received NON-PRIMARY.

2017-09-14T23:38:47.469592+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_shift_state():596: Shifting PRIMARY -> OPEN (TO: 0)

2017-09-14T23:38:47.469599+08:00 0 [Note] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_comp_msg():430: Received self-leave message.

2017-09-14T23:38:47.469603+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:_set_fc_limits():803: Flow-control interval: [0, 0]

2017-09-14T23:38:47.469606+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_handle_act_conf():919: Received SELF-LEAVE. Closing connection.

2017-09-14T23:38:47.469608+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_shift_state():596: Shifting OPEN -> CLOSED (TO: 0)

2017-09-14T23:38:47.469613+08:00 0 [Note] WSREP: gcs/src/gcs.cpp:gcs_recv_thread():1410: RECV thread exiting 0: Success

2017-09-14T23:38:47.469773+08:00 2 [Note] WSREP: gcs/src/gcs.cpp:_close():1197: recv_thread() joined.

2017-09-14T23:38:47.469788+08:00 2 [Note] WSREP: gcs/src/gcs.cpp:_close():1203: Closing replication queue.

2017-09-14T23:38:47.469792+08:00 2 [Note] WSREP: gcs/src/gcs.cpp:_close():1226: Closing slave action queue.

2017-09-14T23:38:47.469844+08:00 0 [Note] WSREP: Waiting for active wsrep applier to exit

2017-09-14T23:38:47.469849+08:00 0 [Note] WSREP: Service disconnected.

2017-09-14T23:38:47.469851+08:00 0 [Note] WSREP: Waiting to close threads......

2017-09-14T23:38:52.470562+08:00 0 [Note] WSREP: Some threads may fail to exit.

2017-09-14T23:38:52.470764+08:00 0 [Note] Binlog end

2017-09-14T23:38:52.471074+08:00 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

五、配置文件：

[client]
port    = 3306
socket  = /usr/local/mysql/tmp/mysql.sock
default-character-set = utf8

[mysqld]
character-set-server = utf8
user    = mysql
port    = 3306
socket  = /usr/local/mysql/tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit    = 10240
back_log = 600
max_connections = 3000
max_connect_errors = 6
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 300
query_cache_size = 128M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
query_cache_type = 1
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 256M
max_heap_table_size = 256M
long_query_time = 2
log-error = /usr/local/mysql/tmp/error.log
pid-file = /usr/local/mysql/tmp/mysql.pid
log-bin = /data/3306/sql_log/mysql-bin
relay_log = /data/3306/sql_log/relay-bin
relay-log-info-file = /data/3306/sql_log/relay-log.info
binlog_cache_size = 512M
max_binlog_cache_size = 1024M
max_binlog_size = 2048M
#binlog_cache_size = 4M
#max_binlog_cache_size = 8M
#max_binlog_size = 512M
expire_logs_days = 7
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_repair_threads = 1
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062,1007
#replicate-ignore-db = mysql
server-id = 111
gtid_mode = on
enforce_gtid_consistency = on
#explicit_defaults_for_timestamp = true
#ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#sql_mode=NO_ENGINE_SUBSTITUTION
#sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#log-slave-updates = 1
binlog_format = row
binlog_row_image = minimal
log_timestamps = SYSTEM
skip_slave_start = 1
log-slave-updates = true
enforce-gtid-consistency = true
#rpl_semi_sync_master_enabled = ON
#rpl_semi_sync_master_timeout = 3000
#rpl_semi_sync_master_wait_point = AFTER_SYNC


#pxc
#引擎
default_storage_engine=Innodb
#库文件位置
wsrep_provider = /usr/local/mysql/lib/libgalera_smm.so
#集群中所有节点的ip
wsrep_cluster_address = gcomm://1.1.1.111,1.1.1.112,1.1.1.113,1.1.1.114
#本节点的名字
wsrep_node_name = MySQL01
#本节点的ip
wsrep_node_address = 1.1.1.111
#集群名字
wsrep_cluster_name = Byrd_PXC
#sst模式需要的用户名和密码
wsrep_sst_auth = sst:admin
#采用什么方式复制数据。还支持mysqldump，rsync
#wsrep_sst_method = rsync
wsrep_sst_method = xtrabackup-v2
#开启的复制线程数，建议cpu核数*2 ，解决apply_cb跟不上问题
wsrep_slave_threads = 2
#pxc严厉模式，还有DISABLED、PERMISSIVE、MASTER可选
pxc_strict_mode = ENFORCING
#自增锁的优化
innodb_autoinc_lock_mode = 2
#打开调试模式
wsrep_provider_options = "debug=1;gcache.size=1G"

[client]

port = 3306

socket = /usr/local/mysql/tmp/mysql.sock

default-character-set = utf8

[mysqld]

character-set-server = utf8

user = mysql

port = 3306

socket = /usr/local/mysql/tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/3306/data

open_files_limit = 10240

back_log = 600

max_connections = 3000

max_connect_errors = 6

external-locking = FALSE

max_allowed_packet = 32M

sort_buffer_size = 2M

join_buffer_size = 2M

thread_cache_size = 300

query_cache_size = 128M

query_cache_limit = 4M

query_cache_min_res_unit = 2k

query_cache_type = 1

thread_stack = 192K

transaction_isolation = READ-COMMITTED

tmp_table_size = 256M

max_heap_table_size = 256M

long_query_time = 2

log-error = /usr/local/mysql/tmp/error.log

pid-file = /usr/local/mysql/tmp/mysql.pid

log-bin = /data/3306/sql_log/mysql-bin

relay_log = /data/3306/sql_log/relay-bin

relay-log-info-file = /data/3306/sql_log/relay-log.info

binlog_cache_size = 512M

max_binlog_cache_size = 1024M

max_binlog_size = 2048M

#binlog_cache_size = 4M

#max_binlog_cache_size = 8M

#max_binlog_size = 512M

expire_logs_days = 7

key_buffer_size = 32M

read_buffer_size = 1M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_repair_threads = 1

lower_case_table_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062,1007

#replicate-ignore-db = mysql

server-id = 111

gtid_mode = on

enforce_gtid_consistency = on

#explicit_defaults_for_timestamp = true

#ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#sql_mode=NO_ENGINE_SUBSTITUTION

#sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#log-slave-updates = 1

binlog_format = row

binlog_row_image = minimal

log_timestamps = SYSTEM

skip_slave_start = 1

log-slave-updates = true

enforce-gtid-consistency = true

#rpl_semi_sync_master_enabled = ON

#rpl_semi_sync_master_timeout = 3000

#rpl_semi_sync_master_wait_point = AFTER_SYNC

#pxc

#引擎

default_storage_engine=Innodb

#库文件位置

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

#集群中所有节点的ip

wsrep_cluster_address = gcomm://1.1.1.111,1.1.1.112,1.1.1.113,1.1.1.114

#本节点的名字

wsrep_node_name = MySQL01

#本节点的ip

wsrep_node_address = 1.1.1.111

#集群名字

wsrep_cluster_name = Byrd_PXC

#sst模式需要的用户名和密码

wsrep_sst_auth = sst:admin

#采用什么方式复制数据。还支持mysqldump，rsync

#wsrep_sst_method = rsync

wsrep_sst_method = xtrabackup-v2

#开启的复制线程数，建议cpu核数*2 ，解决apply_cb跟不上问题

wsrep_slave_threads = 2

#pxc严厉模式，还有DISABLED、PERMISSIVE、MASTER可选

pxc_strict_mode = ENFORCING

#自增锁的优化

innodb_autoinc_lock_mode = 2

#打开调试模式

wsrep_provider_options = "debug=1;gcache.size=1G"

数据库备份脚本：

#!/bin/bash
# Author:Byrd
# Version:0.1
# Site:note.t4x.org
# Contact:root#t4x.org
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
export PATH
Back_DIR="/byrd/back/mysql"
RERY_DIR="$2"
MySQL_CNF="/etc/my.cnf"
MySQL_USER="root"
MySQL_PWD="admin"
MySQL_SOCK="/usr/local/mysql/tmp/mysql.sock"
[ ! -d ${Back_DIR} ] && mkdir ${Back_DIR} -p
if [ $# -lt "1" ];then
    echo "arg error!" 
fi
case $1 in
    Backup)
    if [ `ls -l ${Back_DIR} | grep $(date +%F) | wc -l` -eq 0 ]; then
        touch /tmp/MySQL_Innodb_backupex.txt
        innobackupex --defaults-file=${MySQL_CNF} ${Back_DIR} --user=${MySQL_USER} --password=${MySQL_PWD} --socket=${MySQL_SOCK}
        if [ `echo $?` -ne 0 ]; then
            echo "Backup Error" > /tmp/MySQL_Innodb_backupex.txt
        else
            echo "Backup Successed"
        fi
    else
        if [ `cat /tmp/MySQL_Innodb_backupex.txt | wc -l` -eq 0 ]; then
            echo "今天已经备份过"
        else
            echo "备份失败,重新备份"
            innobackupex --defaults-file=${MySQL_CNF} ${Back_DIR} --user=${MySQL_USER} --password=${MySQL_PWD} --socket=${MySQL_SOCK}
        fi
    fi
    ;;
    Recovery)
    if [ `netstat -tunlp | grep mysql | wc -l` -eq 0 ]; then
        innobackupex --defaults-file=${MySQL_CNF} --user=${MySQL_USER} --password=${MySQL_PWD} --socket=${MySQL_SOCK} --apply-log ${RERY_DIR}
        innobackupex --defaults-file=${MySQL_CNF} --user=${MySQL_USER} --password=${MySQL_PWD} --socket=${MySQL_SOCK} --copy-back ${RERY_DIR}
        if [ `echo $?` -ne 0 ]; then
            echo "Recovery Error" >> /tmp/MySQL_Innodb_Recovery.txt
        else
            echo "Recovery Successed"
        fi
    else
        echo "检查当前MySQL运行中,请关闭MySQL后进行恢复"
    fi
    ;;
    *)
        echo "Usage:$0(Backup|Recovery)"
        ;;
esac

#!/bin/bash

# Author:Byrd

# Version:0.1

# Site:note.t4x.org

# Contact:root#t4x.org

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

export PATH

Back_DIR="/byrd/back/mysql"

RERY_DIR="$2"

MySQL_CNF="/etc/my.cnf"

MySQL_USER="root"

MySQL_PWD="admin"

MySQL_SOCK="/usr/local/mysql/tmp/mysql.sock"

[ ! -d ${Back_DIR} ] && mkdir ${Back_DIR} -p

if [ $# -lt "1" ];then

echo "arg error!"

case $1 in

Backup)

if [ `ls -l ${Back_DIR} | grep $(date +%F) | wc -l` -eq 0 ]; then

touch /tmp/MySQL_Innodb_backupex.txt

innobackupex --defaults-file=${MySQL_CNF} ${Back_DIR} --user=${MySQL_USER} --password=${MySQL_PWD} --socket=${MySQL_SOCK}

if [ `echo $?` -ne 0 ]; then

echo "Backup Error" > /tmp/MySQL_Innodb_backupex.txt

else

echo "Backup Successed"

else

if [ `cat /tmp/MySQL_Innodb_backupex.txt | wc -l` -eq 0 ]; then

echo "今天已经备份过"

else

echo "备份失败,重新备份"

innobackupex --defaults-file=${MySQL_CNF} ${Back_DIR} --user=${MySQL_USER} --password=${MySQL_PWD} --socket=${MySQL_SOCK}

;;

Recovery)

if [ `netstat -tunlp | grep mysql | wc -l` -eq 0 ]; then

innobackupex --defaults-file=${MySQL_CNF} --user=${MySQL_USER} --password=${MySQL_PWD} --socket=${MySQL_SOCK} --apply-log ${RERY_DIR}

innobackupex --defaults-file=${MySQL_CNF} --user=${MySQL_USER} --password=${MySQL_PWD} --socket=${MySQL_SOCK} --copy-back ${RERY_DIR}

if [ `echo $?` -ne 0 ]; then

echo "Recovery Error" >> /tmp/MySQL_Innodb_Recovery.txt

else

echo "Recovery Successed"

else

echo "检查当前MySQL运行中,请关闭MySQL后进行恢复"

;;

echo "Usage:$0(Backup|Recovery)"

;;

esac

六、压力测试：

DB1:1.1.1.141 PXC01
DB2:1.1.1.142 PXC02
DB3:1.1.1.143 PXC03
DB4:1.1.1.144 MySQL5.7.18
Nginx:1.1.1.146  upstream负载均衡
压测服务器：1.1.1.145
测试服务器为：1核心1G

DB1:1.1.1.141 PXC01

DB2:1.1.1.142 PXC02

DB3:1.1.1.143 PXC03

DB4:1.1.1.144 MySQL5.7.18

Nginx:1.1.1.146 upstream负载均衡

压测服务器：1.1.1.145

测试服务器为：1核心1G

独立服务器测试：Server version: 5.7.18-log Source distribution

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.144 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=60 --time=120 --report-interval=10 run [ 10s ] thds: 60 tps: 185.46 qps: 3788.26 (r/w/o: 2669.15/742.05/377.06) lat (ms,95%): 458.96 err/s: 0.30 reconn/s: 0.00 [ 20s ] thds: 60 tps: 197.91 qps: 3998.50 (r/w/o: 2787.01/815.46/396.03) lat (ms,95%): 450.77 err/s: 0.10 reconn/s: 0.00 [ 30s ] thds: 60 tps: 213.69 qps: 4250.17 (r/w/o: 2982.51/840.07/427.59) lat (ms,95%): 404.61 err/s: 0.20 reconn/s: 0.00 [ 40s ] thds: 60 tps: 211.50 qps: 4261.37 (r/w/o: 2978.55/859.41/423.41) lat (ms,95%): 390.30 err/s: 0.40 reconn/s: 0.00 [ 50s ] thds: 60 tps: 212.39 qps: 4230.78 (r/w/o: 2970.74/834.96/425.08) lat (ms,95%): 427.07 err/s: 0.30 reconn/s: 0.00 [ 60s ] thds: 60 tps: 212.81 qps: 4273.21 (r/w/o: 2985.84/861.74/425.62) lat (ms,95%): 390.30 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 60 tps: 200.60 qps: 3982.72 (r/w/o: 2795.71/785.70/401.30) lat (ms,95%): 475.79 err/s: 0.10 reconn/s: 0.00 [ 80s ] thds: 60 tps: 204.80 qps: 4098.03 (r/w/o: 2861.52/826.71/409.80) lat (ms,95%): 427.07 err/s: 0.20 reconn/s: 0.00 [ 90s ] thds: 60 tps: 173.00 qps: 3472.43 (r/w/o: 2441.02/685.41/346.00) lat (ms,95%): 669.89 err/s: 0.10 reconn/s: 0.00 [ 100s ] thds: 60 tps: 203.60 qps: 4089.55 (r/w/o: 2852.96/829.19/407.39) lat (ms,95%): 419.45 err/s: 0.10 reconn/s: 0.00 [ 110s ] thds: 60 tps: 206.90 qps: 4117.60 (r/w/o: 2889.00/814.70/413.90) lat (ms,95%): 484.44 err/s: 0.10 reconn/s: 0.00 [ 120s ] thds: 60 tps: 205.20 qps: 4037.74 (r/w/o: 2811.93/820.71/405.10) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 340522 write: 97238 other: 48627 total: 486387 transactions: 24304 (202.32 per sec.) queries: 486387 (4049.05 per sec.) ignored errors: 19 (0.16 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.1220s total number of events: 24304 Latency (ms): min: 38.38 avg: 296.10 max: 1478.37 95th percentile: 442.73 sum: 7196417.78 Threads fairness: events (avg/stddev): 405.0667/4.53 execution time (avg/stddev): 119.9403/0.14

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.144 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=60 --time=120 --report-interval=10 run

[ 10s ] thds: 60 tps: 185.46 qps: 3788.26 (r/w/o: 2669.15/742.05/377.06) lat (ms,95%): 458.96 err/s: 0.30 reconn/s: 0.00
[ 20s ] thds: 60 tps: 197.91 qps: 3998.50 (r/w/o: 2787.01/815.46/396.03) lat (ms,95%): 450.77 err/s: 0.10 reconn/s: 0.00
[ 30s ] thds: 60 tps: 213.69 qps: 4250.17 (r/w/o: 2982.51/840.07/427.59) lat (ms,95%): 404.61 err/s: 0.20 reconn/s: 0.00
[ 40s ] thds: 60 tps: 211.50 qps: 4261.37 (r/w/o: 2978.55/859.41/423.41) lat (ms,95%): 390.30 err/s: 0.40 reconn/s: 0.00
[ 50s ] thds: 60 tps: 212.39 qps: 4230.78 (r/w/o: 2970.74/834.96/425.08) lat (ms,95%): 427.07 err/s: 0.30 reconn/s: 0.00
[ 60s ] thds: 60 tps: 212.81 qps: 4273.21 (r/w/o: 2985.84/861.74/425.62) lat (ms,95%): 390.30 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 60 tps: 200.60 qps: 3982.72 (r/w/o: 2795.71/785.70/401.30) lat (ms,95%): 475.79 err/s: 0.10 reconn/s: 0.00
[ 80s ] thds: 60 tps: 204.80 qps: 4098.03 (r/w/o: 2861.52/826.71/409.80) lat (ms,95%): 427.07 err/s: 0.20 reconn/s: 0.00
[ 90s ] thds: 60 tps: 173.00 qps: 3472.43 (r/w/o: 2441.02/685.41/346.00) lat (ms,95%): 669.89 err/s: 0.10 reconn/s: 0.00
[ 100s ] thds: 60 tps: 203.60 qps: 4089.55 (r/w/o: 2852.96/829.19/407.39) lat (ms,95%): 419.45 err/s: 0.10 reconn/s: 0.00
[ 110s ] thds: 60 tps: 206.90 qps: 4117.60 (r/w/o: 2889.00/814.70/413.90) lat (ms,95%): 484.44 err/s: 0.10 reconn/s: 0.00
[ 120s ] thds: 60 tps: 205.20 qps: 4037.74 (r/w/o: 2811.93/820.71/405.10) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            340522
        write:                           97238
        other:                           48627
        total:                           486387
    transactions:                        24304  (202.32 per sec.)
    queries:                             486387 (4049.05 per sec.)
    ignored errors:                      19     (0.16 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1220s
    total number of events:              24304

Latency (ms):
         min:                                   38.38
         avg:                                  296.10
         max:                                 1478.37
         95th percentile:                      442.73
         sum:                              7196417.78

Threads fairness:
    events (avg/stddev):           405.0667/4.53
    execution time (avg/stddev):   119.9403/0.14

独立服务器测试：Server version: 5.7.21-20-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.21-29.26, Revision 1702aea, wsrep_29.26

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.141 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare $ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.143 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=60 --time=120 --report-interval=10 run [ 10s ] thds: 60 tps: 144.29 qps: 2965.40 (r/w/o: 2083.78/528.73/352.89) lat (ms,95%): 893.56 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 60 tps: 178.00 qps: 3559.79 (r/w/o: 2496.99/633.80/429.00) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 60 tps: 180.01 qps: 3590.42 (r/w/o: 2510.29/648.42/431.71) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 60 tps: 158.90 qps: 3187.00 (r/w/o: 2236.30/568.80/381.90) lat (ms,95%): 773.68 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 60 tps: 177.60 qps: 3572.49 (r/w/o: 2493.36/648.12/431.01) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 60 tps: 169.90 qps: 3329.71 (r/w/o: 2325.51/596.50/407.70) lat (ms,95%): 612.21 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 60 tps: 177.10 qps: 3572.00 (r/w/o: 2508.30/635.60/428.10) lat (ms,95%): 458.96 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 60 tps: 159.00 qps: 3182.10 (r/w/o: 2225.70/570.40/386.00) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 60 tps: 162.40 qps: 3258.30 (r/w/o: 2283.30/587.30/387.70) lat (ms,95%): 719.92 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 60 tps: 153.00 qps: 3068.80 (r/w/o: 2154.10/545.30/369.40) lat (ms,95%): 759.88 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 60 tps: 166.80 qps: 3325.67 (r/w/o: 2321.58/603.59/400.50) lat (ms,95%): 669.89 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 60 tps: 160.39 qps: 3198.80 (r/w/o: 2235.73/573.12/389.94) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 279034 write: 71553 other: 48033 total: 398620 transactions: 19931 (165.77 per sec.) queries: 398620 (3315.38 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.2316s total number of events: 19931 Latency (ms): min: 59.40 avg: 361.40 max: 1513.07 95th percentile: 646.19 sum: 7203000.19 Threads fairness: events (avg/stddev): 332.1833/3.72 execution time (avg/stddev): 120.0500/0.15

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.141 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare
$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.143 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=60 --time=120 --report-interval=10 run

[ 10s ] thds: 60 tps: 144.29 qps: 2965.40 (r/w/o: 2083.78/528.73/352.89) lat (ms,95%): 893.56 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 60 tps: 178.00 qps: 3559.79 (r/w/o: 2496.99/633.80/429.00) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 60 tps: 180.01 qps: 3590.42 (r/w/o: 2510.29/648.42/431.71) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 60 tps: 158.90 qps: 3187.00 (r/w/o: 2236.30/568.80/381.90) lat (ms,95%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 60 tps: 177.60 qps: 3572.49 (r/w/o: 2493.36/648.12/431.01) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 60 tps: 169.90 qps: 3329.71 (r/w/o: 2325.51/596.50/407.70) lat (ms,95%): 612.21 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 60 tps: 177.10 qps: 3572.00 (r/w/o: 2508.30/635.60/428.10) lat (ms,95%): 458.96 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 60 tps: 159.00 qps: 3182.10 (r/w/o: 2225.70/570.40/386.00) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 60 tps: 162.40 qps: 3258.30 (r/w/o: 2283.30/587.30/387.70) lat (ms,95%): 719.92 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 60 tps: 153.00 qps: 3068.80 (r/w/o: 2154.10/545.30/369.40) lat (ms,95%): 759.88 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 60 tps: 166.80 qps: 3325.67 (r/w/o: 2321.58/603.59/400.50) lat (ms,95%): 669.89 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 60 tps: 160.39 qps: 3198.80 (r/w/o: 2235.73/573.12/389.94) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            279034
        write:                           71553
        other:                           48033
        total:                           398620
    transactions:                        19931  (165.77 per sec.)
    queries:                             398620 (3315.38 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.2316s
    total number of events:              19931

Latency (ms):
         min:                                   59.40
         avg:                                  361.40
         max:                                 1513.07
         95th percentile:                      646.19
         sum:                              7203000.19

Threads fairness:
    events (avg/stddev):           332.1833/3.72
    execution time (avg/stddev):   120.0500/0.15

PXC架构：

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.146 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=60 --time=120 --report-interval=10 run [ 10s ] thds: 60 tps: 169.70 qps: 3564.26 (r/w/o: 2505.61/635.82/422.83) lat (ms,95%): 877.61 err/s: 3.55 reconn/s: 0.00 [ 20s ] thds: 60 tps: 201.29 qps: 4087.23 (r/w/o: 2860.78/737.87/488.58) lat (ms,95%): 707.07 err/s: 3.40 reconn/s: 0.00 [ 30s ] thds: 60 tps: 221.31 qps: 4505.45 (r/w/o: 3158.88/810.35/536.23) lat (ms,95%): 580.02 err/s: 4.30 reconn/s: 0.00 [ 40s ] thds: 60 tps: 167.60 qps: 3427.72 (r/w/o: 2401.41/615.30/411.00) lat (ms,95%): 977.74 err/s: 4.10 reconn/s: 0.00 [ 50s ] thds: 60 tps: 198.90 qps: 4055.56 (r/w/o: 2842.27/727.19/486.10) lat (ms,95%): 746.32 err/s: 4.90 reconn/s: 0.00 [ 60s ] thds: 60 tps: 194.02 qps: 4016.19 (r/w/o: 2813.97/732.21/470.01) lat (ms,95%): 694.45 err/s: 5.50 reconn/s: 0.00 [ 70s ] thds: 60 tps: 193.28 qps: 3937.27 (r/w/o: 2764.17/702.40/470.70) lat (ms,95%): 846.57 err/s: 4.40 reconn/s: 0.00 [ 80s ] thds: 60 tps: 186.40 qps: 3790.72 (r/w/o: 2655.65/683.09/451.99) lat (ms,95%): 909.80 err/s: 3.50 reconn/s: 0.00 [ 90s ] thds: 60 tps: 160.70 qps: 3265.68 (r/w/o: 2289.29/587.60/388.80) lat (ms,95%): 1050.76 err/s: 3.00 reconn/s: 0.00 [ 100s ] thds: 60 tps: 191.70 qps: 3899.15 (r/w/o: 2730.73/701.01/467.41) lat (ms,95%): 612.21 err/s: 3.30 reconn/s: 0.00 [ 110s ] thds: 60 tps: 185.20 qps: 3779.11 (r/w/o: 2645.91/681.40/451.80) lat (ms,95%): 746.32 err/s: 3.90 reconn/s: 0.00 [ 120s ] thds: 54 tps: 167.39 qps: 3317.89 (r/w/o: 2319.82/599.28/398.79) lat (ms,95%): 816.63 err/s: 3.10 reconn/s: 0.00 SQL statistics: queries performed: read: 320250 write: 82243 other: 54509 total: 457002 transactions: 22405 (186.44 per sec.) queries: 457002 (3802.89 per sec.) ignored errors: 470 (3.91 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.1705s total number of events: 22405 Latency (ms): min: 42.19 avg: 321.23 max: 2203.86 95th percentile: 787.74 sum: 7197058.38 Threads fairness: events (avg/stddev): 373.4167/26.16 execution time (avg/stddev): 119.9510/0.16

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.146 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=60 --time=120 --report-interval=10 run

[ 10s ] thds: 60 tps: 169.70 qps: 3564.26 (r/w/o: 2505.61/635.82/422.83) lat (ms,95%): 877.61 err/s: 3.55 reconn/s: 0.00
[ 20s ] thds: 60 tps: 201.29 qps: 4087.23 (r/w/o: 2860.78/737.87/488.58) lat (ms,95%): 707.07 err/s: 3.40 reconn/s: 0.00
[ 30s ] thds: 60 tps: 221.31 qps: 4505.45 (r/w/o: 3158.88/810.35/536.23) lat (ms,95%): 580.02 err/s: 4.30 reconn/s: 0.00
[ 40s ] thds: 60 tps: 167.60 qps: 3427.72 (r/w/o: 2401.41/615.30/411.00) lat (ms,95%): 977.74 err/s: 4.10 reconn/s: 0.00
[ 50s ] thds: 60 tps: 198.90 qps: 4055.56 (r/w/o: 2842.27/727.19/486.10) lat (ms,95%): 746.32 err/s: 4.90 reconn/s: 0.00
[ 60s ] thds: 60 tps: 194.02 qps: 4016.19 (r/w/o: 2813.97/732.21/470.01) lat (ms,95%): 694.45 err/s: 5.50 reconn/s: 0.00
[ 70s ] thds: 60 tps: 193.28 qps: 3937.27 (r/w/o: 2764.17/702.40/470.70) lat (ms,95%): 846.57 err/s: 4.40 reconn/s: 0.00
[ 80s ] thds: 60 tps: 186.40 qps: 3790.72 (r/w/o: 2655.65/683.09/451.99) lat (ms,95%): 909.80 err/s: 3.50 reconn/s: 0.00
[ 90s ] thds: 60 tps: 160.70 qps: 3265.68 (r/w/o: 2289.29/587.60/388.80) lat (ms,95%): 1050.76 err/s: 3.00 reconn/s: 0.00
[ 100s ] thds: 60 tps: 191.70 qps: 3899.15 (r/w/o: 2730.73/701.01/467.41) lat (ms,95%): 612.21 err/s: 3.30 reconn/s: 0.00
[ 110s ] thds: 60 tps: 185.20 qps: 3779.11 (r/w/o: 2645.91/681.40/451.80) lat (ms,95%): 746.32 err/s: 3.90 reconn/s: 0.00
[ 120s ] thds: 54 tps: 167.39 qps: 3317.89 (r/w/o: 2319.82/599.28/398.79) lat (ms,95%): 816.63 err/s: 3.10 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            320250
        write:                           82243
        other:                           54509
        total:                           457002
    transactions:                        22405  (186.44 per sec.)
    queries:                             457002 (3802.89 per sec.)
    ignored errors:                      470    (3.91 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1705s
    total number of events:              22405

Latency (ms):
         min:                                   42.19
         avg:                                  321.23
         max:                                 2203.86
         95th percentile:                      787.74
         sum:                              7197058.38

Threads fairness:
    events (avg/stddev):           373.4167/26.16
    execution time (avg/stddev):   119.9510/0.16