MySQL高可用集群之PXC

  • A+
所属分类：Database

环境：

0
1
2
3
4
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

一、PXC安装：

二进制安装：

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

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$ 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';  

二、XtraBackup安装：

0
1
2
3
4
5
6
7
8
9
10
11
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

测试数据：

0
1
2
3
4
5
6
7
8
9
10
11
12
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

三、全站备份：

0
1
2
3
4
5
6
7
8
9
$ 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/

完成后查看状态：

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
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)

四、错误解决：

Q:

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:

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

Q:

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
 

Q:
结点3错误日志：

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备份时]

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
数据库异常，导致备份无法完成，间接导致另外一台服务器异常。

Q:

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:

0
yum install perl-Digest-MD5

Q:

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:

0
yum install rsync

报错日志：

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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

五、配置文件：

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
[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"

数据库备份脚本：

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
40
41
42
43
44
45
46
47
48
49
50
51
52
#!/bin/bash
# Author:Byrd
# Version:0.1
# Site:www.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

六、压力测试：

0
1
2
3
4
5
6
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

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

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架构：

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

参考文档：
1：https://www.percona.com/doc/percona-server/5.7/installation.html