三台服务器通过PXC搭建mysql集群

?PXC三台服务器搭建mysql集群

背景介绍：
在我们的生产环境，目前还采用的是单节点的Mater对应多个Slave节点；为了避免Master单点故障，需要尝试其它方案；
类似于通过Keepalived(VIP)或MySQL-Proxy等的方式，应用的比较多，如MySQL MMM；
由于线上采用了Percona Server，因为Manager更推荐采用 Percona 官方的Auto-Failover方式或者XtraDB Cluster的方式，Auto-Failover方式我在上一篇文章中已经讲过了。
因此，本章节主要讲解XtraDB Cluster的功能测试情况。

环境介绍：
servers: demoenv-trial-1 demoenv-trial-2 demoenv-trial-3

1. 安装 Percona Server，在所有服务器上：
$ sudo yum install https://www.360docs.net/doc/8d6488357.html,/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
$ sudo yum install https://www.360docs.net/doc/8d6488357.html,/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ sudo yum install Percona-Server-shared-compat
$ sudo yum install Percona-Server-server-55 Percona-Server-client-55

2. 配置 /etc/https://www.360docs.net/doc/8d6488357.html,f，在所有服务器上：
$ sudo vim /etc/https://www.360docs.net/doc/8d6488357.html,f

01 [mysqld]
02 # basic settings
03 datadir = /opt/mysql/data
04 tmpdir = /opt/mysql/tmp
05 socket = /opt/mysql/run/mysqld.sock
06 port = 3306
07 pid-file = /opt/mysql/run/mysqld.pid
08
09 # innodb settings
10 default-storage-engine = INNODB
11 innodb_file_per_table = 1
12 log-bin = /opt/mysql/binlogs/bin-log-mysqld
13 log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index
14 innodb_data_home_dir = /opt/mysql/data
15 innodb_data_file_path = ibdata1:10M:autoextend
16 innodb_log_group_home_dir = /opt/mysql/data
17 binlog-do-db = testdb
18
19 # server id
20 server-id=1
21
22 # other settings
23 [mysqld_safe]
24 log-error = /opt/mysql/log/mysqld.log
25 pid-file = /opt/mysql/run/mysqld.pid
26 open-files-limit = 8192
27
28 [mysqlhotcopy]
29 interactive-timeout
30
31 [client]
32 port = 3306
33 socket = /opt/mysql/run/mysqld.sock
34 default-character-set = utf8
3. 创建所需目录，在所有服务器上：
$ sudo mkdir -p /opt/mysql/{data,tmp,run,binlogs,log}
$ sudo chown mysql:mysql /opt/mysql/{data,tmp,run,binlogs,log}

4. 初始化数据库，在所有服务器上：
$ sudo -i
# su – mysql
$ mysql_install_db –user=mysql –datadir=/opt/mysql/data/
$ exit
# exit
$ sudo /etc/init.d/mysql start

5. 删除原有软件包，在所有服务器上：
因为XtraDB Cluster的软件包与原有软件包冲突：

1 Error: Percona-XtraDB-Cluster-client conflicts with Percona-Server-client-55-5.5.34-rel32.0.591.rhel6.x86_64
2 Error: Percona-XtraDB-Cluster-server conflicts with Percona-Server-server-55-5.5.34-rel32.0.591.rhel6.x86_64
3 Error: Percona-XtraDB-Cluster-shared conflicts with Percona-Server-shared-55-5.5.34-rel32.0.591.rhel6.x86_64
$ sudo /etc/init.d/mysql stop
$ sudo rpm -qa | grep Percona-Server | grep -v compat | xargs sudo rpm -e –nodeps

6. 配置 /etc/https://www.360docs.net/doc/8d6488357.html,f，添加 XtraDB Cluster 的支持

，在所有服务器上：
注意，每台服务器需要将 wsrep_node_address 设置为本机的IP地址或主机名。
$ sudo vim /etc/https://www.360docs.net/doc/8d6488357.html,f

01 [mysqld]
02 # basic settings
03 datadir = /opt/mysql/data
04 tmpdir = /opt/mysql/tmp
05 socket = /opt/mysql/run/mysqld.sock
06 port = 3306
07 pid-file = /opt/mysql/run/mysqld.pid
08
09 # innodb settings
10 default-storage-engine = INNODB
11 innodb_file_per_table = 1
12 log-bin = /opt/mysql/binlogs/bin-log-mysqld
13 log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index
14 innodb_data_home_dir = /opt/mysql/data
15 innodb_data_file_path = ibdata1:10M:autoextend
16 innodb_log_group_home_dir = /opt/mysql/data
17 binlog-do-db = testdb
18
19 # xtradb cluster settings
20 binlog_format = ROW
21 wsrep_cluster_name = mycluster
22 wsrep_cluster_address = gcomm://demoenv-trial-1,demoenv-trial-2,demoenv-trial-3
23 wsrep_node_address = demoenv-trial-1
24 wsrep_provider = /usr/lib64/libgalera_smm.so
25 wsrep_sst_method = xtrabackup
26 wsrep_sst_auth = sst:secret
27 innodb_locks_unsafe_for_binlog = 1
28 innodb_autoinc_lock_mode = 2
29
30 # server id
31 server-id=1
32
33 # other settings
34 [mysqld_safe]
35 log-error = /opt/mysql/log/mysqld.log
36 pid-file = /opt/mysql/run/mysqld.pid
37 open-files-limit = 8192
38
39 [mysqlhotcopy]
40 interactive-timeout
41
42 [client]
43 port = 3306
44 socket = /opt/mysql/run/mysqld.sock
45 default-character-set = utf8
7. 安装 XtraDB Cluster，在所有服务器上：
$ sudo yum install Percona-Server-shared-compat
$ sudo yum install Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client

8. 初始化第一个Node:
[dong.guo@demoenv-trial-1 ~]$ sudo service mysql bootstrap-pxc

1 Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster).. SUCCESS!
[dong.guo@demoenv-trial-1 ~]$ mysql -uroot

01 mysql> show global status like 'wsrep%';
02 +----------------------------+--------------------------------------+
03 | Variable_name | Value |
04 +----------------------------+--------------------------------------+
05 | wsrep_local_state_uuid | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68 |
06 | wsrep_protocol_version | 4 |
07 | wsrep_last_committed | 0 |
08 | wsrep_replicated | 0 |
09 | wsrep_replicated_bytes | 0 |
10 | wsrep_received | 2 |
11 | wsrep_received_bytes | 146 |
12 | wsrep_local_commits | 0 |
13 | wsrep_local_cert_failures | 0 |
14 | wsrep_local_bf_aborts | 0 |
15 | wsrep_local_replays | 0 |
16 | wsrep_local_send_queue | 0

|
17 | wsrep_local_send_queue_avg | 0.000000 |
18 | wsrep_local_recv_queue | 0 |
19 | wsrep_local_recv_queue_avg | 0.000000 |
20 | wsrep_flow_control_paused | 0.000000 |
21 | wsrep_flow_control_sent | 0 |
22 | wsrep_flow_control_recv | 0 |
23 | wsrep_cert_deps_distance | 0.000000 |
24 | wsrep_apply_oooe | 0.000000 |
25 | wsrep_apply_oool | 0.000000 |
26 | wsrep_apply_window | 0.000000 |
27 | wsrep_commit_oooe | 0.000000 |
28 | wsrep_commit_oool | 0.000000 |
29 | wsrep_commit_window | 0.000000 |
30 | wsrep_local_state | 4 |
31 | wsrep_local_state_comment | Synced |
32 | wsrep_cert_index_size | 0 |
33 | wsrep_causal_reads | 0 |
34 | wsrep_incoming_addresses | demoenv-trial-1:3306 |
35 | wsrep_cluster_conf_id | 1 |
36 | wsrep_cluster_size | 1 |
37 | wsrep_cluster_state_uuid | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68 |
38 | wsrep_cluster_status | Primary |
39 | wsrep_connected | ON |
40 | wsrep_local_index | 0 |
41 | wsrep_provider_name | Galera |
42 | wsrep_provider_vendor | Codership Oy (info@https://www.360docs.net/doc/8d6488357.html,) |
43 | wsrep_provider_version | 2.8(r162) |
44 | wsrep_ready | ON |
45 +----------------------------+--------------------------------------+
参数 “wsrep_cluster_size” 为 1，因为目前Cluster中只有一个Node。

01 mysql> show databases;
02 +--------------------+
03 | Database |
04 +--------------------+
05 | information_schema |
06 | mysql |
07 | performance_schema |
08 | test |
09 +--------------------+
10
11 mysql> create database testdb;
12 mysql> use testdb;
13 Database changed
14 mysql> CREATE TABLE `hostgroup` (
15 -> `hostgroup_id` tinyint(4) NOT NULL AUTO_INCREMENT,
16 -> `hostgroup_name` char(20) DEFAULT NULL,
17 -> `hostgroup_next` tinyint(4) NOT NULL,
18 -> `colo_name` char(4) NOT NULL,
19 -> PRIMARY KEY (`hostgroup_id`)
20 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
21
22 mysql> GRANT RELOAD, LOCK TABLES,

REPLICATION CLIENT ON *.* TO 'sst'@'localhost'IDENTIFIED BY 'secret';
9. 添加新的 Node 到Cluster中：
[dong.guo@demoenv-trial-2 etc]$ sudo service mysql start

1 Starting MySQL (Percona XtraDB Cluster)....SST in progress, setting sleep higher
2 .. SUCCESS!
[dong.guo@demoenv-trial-2 etc]$ mysql -uroot

01 mysql> show databases;
02 +--------------------+
03 | Database |
04 +--------------------+
05 | information_schema |
06 | mysql |
07 | performance_schema |
08 | test |
09 | testdb |
10 +--------------------+
11
12 mysql> use testdb;
13 mysql> show tables;
14 +------------------+
15 | Tables_in_testdb |
16 +------------------+
17 | hostgroup |
18 +------------------+
19
20 mysql> use mysql;
21 mysql> select User,Password,Host,Grant_priv from https://www.360docs.net/doc/8d6488357.html,er;
22 +------+-------------------------------------------+-----------------+------------+
23 | User | Password | Host | Grant_priv |
24 +------+-------------------------------------------+-----------------+------------+
25 | root | | localhost | Y |
26 | root | | demoenv-trial-1 | Y |
27 | root | | 127.0.0.1 | Y |
28 | root | | ::1 | Y |
29 | | | localhost | N |
30 | | | demoenv-trial-1 | N |
31 | sst | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | localhost | N |
32 +------+-------------------------------------------+-----------------+------------+
可以发现 “binlog-do-db = testdb” 并未生效，新的节点同步了第一个节点的所有数据；
同理，server-id也就没有什么用了；

查看Cluster的状态：

01 mysql> show global status like 'wsrep%';
02 +----------------------------+-------------------------------------------+
03 | Variable_name | Value |
04 +----------------------------+-------------------------------------------+
05 | wsrep_local_state_uuid | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68 |
06 | wsrep_protocol_version | 4 |
07 | wsrep_last_committed | 3 |
08 | wsrep_replicated | 3 |
09 | wsrep_replicated_bytes | 831 |
10 | wsrep_received | 6 |
11 | wsrep_received_bytes | 435 |
12 | wsrep_local_commits | 0 |
13 | wsrep_local_cert_failures | 0

|
14 | wsrep_local_bf_aborts | 0 |
15 | wsrep_local_replays | 0 |
16 | wsrep_local_send_queue | 0 |
17 | wsrep_local_send_queue_avg | 0.000000 |
18 | wsrep_local_recv_queue | 0 |
19 | wsrep_local_recv_queue_avg | 0.000000 |
20 | wsrep_flow_control_paused | 0.000000 |
21 | wsrep_flow_control_sent | 0 |
22 | wsrep_flow_control_recv | 0 |
23 | wsrep_cert_deps_distance | 0.000000 |
24 | wsrep_apply_oooe | 0.000000 |
25 | wsrep_apply_oool | 0.000000 |
26 | wsrep_apply_window | 0.000000 |
27 | wsrep_commit_oooe | 0.000000 |
28 | wsrep_commit_oool | 0.000000 |
29 | wsrep_commit_window | 0.000000 |
30 | wsrep_local_state | 4 |
31 | wsrep_local_state_comment | Synced |
32 | wsrep_cert_index_size | 0 |
33 | wsrep_causal_reads | 0 |
34 | wsrep_incoming_addresses | demoenv-trial-2:3306,demoenv-trial-1:3306 |
35 | wsrep_cluster_conf_id | 2 |
36 | wsrep_cluster_size | 2 |
37 | wsrep_cluster_state_uuid | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68 |
38 | wsrep_cluster_status | Primary |
39 | wsrep_connected | ON |
40 | wsrep_local_index | 1 |
41 | wsrep_provider_name | Galera |
42 | wsrep_provider_vendor | Codership Oy (info@https://www.360docs.net/doc/8d6488357.html,) |
43 | wsrep_provider_version | 2.8(r162) |
44 | wsrep_ready | ON |
45 +----------------------------+-------------------------------------------+
参数 “wsrep_cluster_size” 变为了 2。

下面，就可以按照同样的步骤，添加更多的节点到Cluster中了，在这个测试环境中，我们一共有3个Node。

10. 测试Cluster功能：
[dong.guo@demoenv-trial-2 ~]$ mysql -uroot

01 mysql> drop user ''@localhost;
02 mysql> drop user ''@'demoenv-trial-1';
03 mysql> drop user root@'demoenv-trial-1';
04 mysql> drop user root@'::1';
05 mysql> select User,Password,Host,Grant_priv

from https://www.360docs.net/doc/8d6488357.html,er;
06 +------+-------------------------------------------+-----------------+------------+
07 | User | Password | Host | Grant_priv |
08 +------+-------------------------------------------+-----------------+------------+
09 | root | | localhost | Y |
10 | root | | 127.0.0.1 | Y |
11 | sst | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | localhost | N |
12 +------+-------------------------------------------+-----------------+------------+
[dong.guo@demoenv-trial-1 ~]$ mysql -uroot

1 mysql> select User,Password,Host,Grant_priv from https://www.360docs.net/doc/8d6488357.html,er;
2 +------+-------------------------------------------+-----------------+------------+
3 | User | Password | Host | Grant_priv |
4 +------+-------------------------------------------+-----------------+------------+
5 | root | | localhost | Y |
6 | root | | 127.0.0.1 | Y |
7 | sst | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | localhost | N |
8 +------+-------------------------------------------+-----------------+------------+
数据自动同步了，在 XtraDB Cluster 中，没有主从的概念；
它是一个Multi-Master的同步方案，支持在任意Node上写入数据，其它Node自动同步；

测试如果一个Node失效的情况：
[dong.guo@demoenv-trial-1 ~]$ sudo killall -9 mysqld

[dong.guo@demoenv-trial-2 ~]$ mysql -uroot

01 mysql> show global status like 'wsrep%';
02 +----------------------------+-------------------------------------------+
03 | Variable_name | Value |
04 +----------------------------+-------------------------------------------+
05 | wsrep_local_state_uuid | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68 |
06 | wsrep_protocol_version | 4 |
07 | wsrep_last_committed | 7 |
08 | wsrep_replicated | 2 |
09 | wsrep_replicated_bytes | 328 |
10 | wsrep_received | 10 |
11 | wsrep_received_bytes | 1121 |
12 | wsrep_local_commits | 0 |
13 | wsrep_local_cert_failures | 0 |
14 | wsrep_local_bf_aborts | 0 |
15 | wsrep_local_replays | 0 |
16 | wsrep_local_send_queue | 0 |
17 | wsrep_local_send_queue_avg | 0.000000 |
18 | wsrep

_local_recv_queue | 0 |
19 | wsrep_local_recv_queue_avg | 0.000000 |
20 | wsrep_flow_control_paused | 0.000000 |
21 | wsrep_flow_control_sent | 0 |
22 | wsrep_flow_control_recv | 0 |
23 | wsrep_cert_deps_distance | 0.000000 |
24 | wsrep_apply_oooe | 0.000000 |
25 | wsrep_apply_oool | 0.000000 |
26 | wsrep_apply_window | 1.000000 |
27 | wsrep_commit_oooe | 0.000000 |
28 | wsrep_commit_oool | 0.000000 |
29 | wsrep_commit_window | 1.000000 |
30 | wsrep_local_state | 4 |
31 | wsrep_local_state_comment | Synced |
32 | wsrep_cert_index_size | 0 |
33 | wsrep_causal_reads | 0 |
34 | wsrep_incoming_addresses | demoenv-trial-3:3306,demoenv-trial-2:3306 |
35 | wsrep_cluster_conf_id | 4 |
36 | wsrep_cluster_size | 2 |
37 | wsrep_cluster_state_uuid | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68 |
38 | wsrep_cluster_status | Primary |
39 | wsrep_connected | ON |
40 | wsrep_local_index | 1 |
41 | wsrep_provider_name | Galera |
42 | wsrep_provider_vendor | Codership Oy (info@https://www.360docs.net/doc/8d6488357.html,) |
43 | wsrep_provider_version | 2.8(r162) |
44 | wsrep_ready | ON |
45 +----------------------------+-------------------------------------------+
参数 “wsrep_cluster_size” 变为了 2，并且 “demoenv-trial-1″ 被移出了 Cluster 列表 “wsrep_incoming_addresses”。

测试写入数据：

1 mysql> use testdb;
2 mysql> INSERT INTO hostgroup (hostgroup_name,hostgroup_next,colo_name) VALUES ("adse","2","awse");
[dong.guo@demoenv-trial-3 ~]$ mysql -uroot

1 mysql> use testdb;
2 mysql> select * from hostgroup where hostgroup_name = "adse";
3 +--------------+----------------+----------------+-----------+
4 | hostgroup_id | hostgroup_name | hostgroup_next | colo_name |
5 +--------------+----------------+----------------+-----------+
6 | 2 | adse | 2 | awse |
7 +--------------+----------------+----------------+-----------+
可以看到 “demoenv-trial-1″ 已经恢

复。

[dong.guo@demoenv-trial-1 ~]$ sudo rm -f /opt/mysql/run/mysqld.pid
[dong.guo@demoenv-trial-1 ~]$ sudo rm -f /var/lock/subsys/mysql
[dong.guo@demoenv-trial-1 ~]$ sudo /etc/init.d/mysql start

1 Starting MySQL (Percona XtraDB Cluster).......SST in progress, setting sleep higher
2 .. SUCCESS!
[dong.guo@demoenv-trial-1 ~]$ mysql -uroot

01 mysql> use testdb;
02 mysql> select User,Password,Host,Grant_priv from https://www.360docs.net/doc/8d6488357.html,er;
03 +------+-------------------------------------------+-----------+------------+
04 | User | Password | Host | Grant_priv |
05 +------+-------------------------------------------+-----------+------------+
06 | root | | localhost | Y |
07 | root | | 127.0.0.1 | Y |
08 | sst | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | localhost | N |
09 +------+-------------------------------------------+-----------+------------+
10
11 mysql> GRANT USAGE ON *.* TO 'clustercheck'@'localhost' IDENTIFIED BY 'password';
[dong.guo@demoenv-trial-1 ~]$ /usr/bin/clustercheck clustercheck password 0;

1 HTTP/1.1 200 OK
2 Content-Type: text/plain
3 Connection: close
4 Content-Length: 40
5 Percona XtraDB Cluster Node is synced.
“demoenv-trial-1″ 的同步状态正常。

11. 以下是对 “Percona XtraDB Cluster” 功能测试的总结：
它和 Percona-Server-server-55 是兼容的，可以共用数据文件，但是软件包是冲突的，必须删除原有的软件包之后再安装新的软件包；
它没有 master 和 slave 的概念，而是 multi-master 方式的同步，支持在任意节点上写入数据，同时它能确保写入时不产生一致性冲突问题；
它没有通过binlog方式来同步，而是xtrabackup的方式，因为每个节点都是彼此的镜像，像参数 “binlog-do-db” 这类的就没有什么用了；
它在高可用方面明显要比master-slave方式好。