Percona XtraDB Cluster 功能测试


参考资料:
http://www.mysqlperformanceblog.com/2013/09/23/percona-xtradb-cluster-setting-simple-cluster/

背景介绍:
在我们的生产环境,目前还采用的是单节点的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 http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
$ sudo yum install http://dl.fedoraproject.org/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/my.cnf,在所有服务器上:
$ sudo vim /etc/my.cnf

[mysqld]
# basic settings
datadir = /opt/mysql/data
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock
port = 3306
pid-file = /opt/mysql/run/mysqld.pid

skip-external-locking
max_allowed_packet = 16M

# innodb settings
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /opt/mysql/binlogs/bin-log-mysqld
log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index
innodb_data_home_dir = /opt/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/data
binlog-do-db = testdb

# server id
server-id=1

# other settings
[mysqld_safe]
log-error = /opt/mysql/log/mysqld.log
pid-file = /opt/mysql/run/mysqld.pid
open-files-limit = 8192

[mysqlhotcopy]
interactive-timeout

[client]
port = 3306
socket = /opt/mysql/run/mysqld.sock
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的软件包与原有软件包冲突:

Error: Percona-XtraDB-Cluster-client conflicts with Percona-Server-client-55-5.5.34-rel32.0.591.rhel6.x86_64
Error: Percona-XtraDB-Cluster-server conflicts with Percona-Server-server-55-5.5.34-rel32.0.591.rhel6.x86_64
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/my.cnf,添加 XtraDB Cluster 的支持,在所有服务器上:
注意,每台服务器需要将 wsrep_node_address 设置为本机的IP地址或主机名。
$ sudo vim /etc/my.cnf

[mysqld]
# basic settings
datadir = /opt/mysql/data
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock
port = 3306
pid-file = /opt/mysql/run/mysqld.pid

# innodb settings
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /opt/mysql/binlogs/bin-log-mysqld
log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index
innodb_data_home_dir = /opt/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/data
binlog-do-db = testdb

# xtradb cluster settings
binlog_format = ROW
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://demoenv-trial-1,demoenv-trial-2,demoenv-trial-3
wsrep_node_address = demoenv-trial-1
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_sst_method = xtrabackup
wsrep_sst_auth = sst:secret
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2

# server id
server-id=1

# other settings
[mysqld_safe]
log-error = /opt/mysql/log/mysqld.log
pid-file = /opt/mysql/run/mysqld.pid
open-files-limit = 8192

[mysqlhotcopy]
interactive-timeout

[client]
port = 3306
socket = /opt/mysql/run/mysqld.sock
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

Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster).. SUCCESS! 

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

mysql> show global status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68 |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 2                                    |
| wsrep_received_bytes       | 146                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| 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_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | demoenv-trial-1:3306                 |
| wsrep_cluster_conf_id      | 1                                    |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_state_uuid   | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 0                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy (info@codership.com)    |
| wsrep_provider_version     | 2.8(r162)                            |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

参数 "wsrep_cluster_size" 为 1,因为目前Cluster中只有一个Node。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

mysql> create database testdb;
mysql> use testdb;
Database changed
mysql> CREATE TABLE `hostgroup` (
    ->   `hostgroup_id` tinyint(4) NOT NULL AUTO_INCREMENT,
    ->   `hostgroup_name` char(20) DEFAULT NULL,
    ->   `hostgroup_next` tinyint(4) NOT NULL,
    ->   `colo_name` char(4) NOT NULL,
    ->   PRIMARY KEY (`hostgroup_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

Starting MySQL (Percona XtraDB Cluster)....SST in progress, setting sleep higher
.. SUCCESS! 

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+

mysql> use testdb;
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| hostgroup        |
+------------------+

mysql> use mysql;
mysql> select User,Password,Host,Grant_priv from mysql.user;
+------+-------------------------------------------+-----------------+------------+
| User | Password                                  | Host            | Grant_priv |
+------+-------------------------------------------+-----------------+------------+
| root |                                           | localhost       | Y          |
| root |                                           | demoenv-trial-1 | Y          |
| root |                                           | 127.0.0.1       | Y          |
| root |                                           | ::1             | Y          |
|      |                                           | localhost       | N          |
|      |                                           | demoenv-trial-1 | N          |
| sst  | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | localhost       | N          |
+------+-------------------------------------------+-----------------+------------+

可以发现 "binlog-do-db = testdb" 并未生效,新的节点同步了第一个节点的所有数据;
同理,server-id也就没有什么用了;

查看Cluster的状态:

mysql> show global status like 'wsrep%';
+----------------------------+-------------------------------------------+
| Variable_name              | Value                                     |
+----------------------------+-------------------------------------------+
| wsrep_local_state_uuid     | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68      |
| wsrep_protocol_version     | 4                                         |
| wsrep_last_committed       | 3                                         |
| wsrep_replicated           | 3                                         |
| wsrep_replicated_bytes     | 831                                       |
| wsrep_received             | 6                                         |
| wsrep_received_bytes       | 435                                       |
| wsrep_local_commits        | 0                                         |
| wsrep_local_cert_failures  | 0                                         |
| wsrep_local_bf_aborts      | 0                                         |
| wsrep_local_replays        | 0                                         |
| wsrep_local_send_queue     | 0                                         |
| wsrep_local_send_queue_avg | 0.000000                                  |
| wsrep_local_recv_queue     | 0                                         |
| wsrep_local_recv_queue_avg | 0.000000                                  |
| wsrep_flow_control_paused  | 0.000000                                  |
| wsrep_flow_control_sent    | 0                                         |
| wsrep_flow_control_recv    | 0                                         |
| 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_causal_reads         | 0                                         |
| wsrep_incoming_addresses   | demoenv-trial-2:3306,demoenv-trial-1:3306 |
| wsrep_cluster_conf_id      | 2                                         |
| wsrep_cluster_size         | 2                                         |
| wsrep_cluster_state_uuid   | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68      |
| wsrep_cluster_status       | Primary                                   |
| wsrep_connected            | ON                                        |
| wsrep_local_index          | 1                                         |
| wsrep_provider_name        | Galera                                    |
| wsrep_provider_vendor      | Codership Oy (info@codership.com)         |
| wsrep_provider_version     | 2.8(r162)                                 |
| wsrep_ready                | ON                                        |
+----------------------------+-------------------------------------------+

参数 "wsrep_cluster_size" 变为了 2。

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

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

mysql> drop user ''@localhost;
mysql> drop user ''@'demoenv-trial-1';
mysql> drop user root@'demoenv-trial-1';
mysql> drop user root@'::1';
mysql> select User,Password,Host,Grant_priv from mysql.user;
+------+-------------------------------------------+-----------------+------------+
| User | Password                                  | Host            | Grant_priv |
+------+-------------------------------------------+-----------------+------------+
| root |                                           | localhost       | Y          |
| root |                                           | 127.0.0.1       | Y          |
| sst  | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | localhost       | N          |
+------+-------------------------------------------+-----------------+------------+

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

mysql> select User,Password,Host,Grant_priv from mysql.user;
+------+-------------------------------------------+-----------------+------------+
| User | Password                                  | Host            | Grant_priv |
+------+-------------------------------------------+-----------------+------------+
| root |                                           | localhost       | Y          |
| root |                                           | 127.0.0.1       | Y          |
| sst  | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | localhost       | N          |
+------+-------------------------------------------+-----------------+------------+

数据自动同步了,在 XtraDB Cluster 中,没有主从的概念;
它是一个Multi-Master的同步方案,支持在任意Node上写入数据,其它Node自动同步;

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

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

mysql> show global status like 'wsrep%';
+----------------------------+-------------------------------------------+
| Variable_name              | Value                                     |
+----------------------------+-------------------------------------------+
| wsrep_local_state_uuid     | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68      |
| wsrep_protocol_version     | 4                                         |
| wsrep_last_committed       | 7                                         |
| wsrep_replicated           | 2                                         |
| wsrep_replicated_bytes     | 328                                       |
| wsrep_received             | 10                                        |
| wsrep_received_bytes       | 1121                                      |
| wsrep_local_commits        | 0                                         |
| wsrep_local_cert_failures  | 0                                         |
| wsrep_local_bf_aborts      | 0                                         |
| wsrep_local_replays        | 0                                         |
| wsrep_local_send_queue     | 0                                         |
| wsrep_local_send_queue_avg | 0.000000                                  |
| wsrep_local_recv_queue     | 0                                         |
| wsrep_local_recv_queue_avg | 0.000000                                  |
| wsrep_flow_control_paused  | 0.000000                                  |
| wsrep_flow_control_sent    | 0                                         |
| wsrep_flow_control_recv    | 0                                         |
| wsrep_cert_deps_distance   | 0.000000                                  |
| wsrep_apply_oooe           | 0.000000                                  |
| wsrep_apply_oool           | 0.000000                                  |
| wsrep_apply_window         | 1.000000                                  |
| wsrep_commit_oooe          | 0.000000                                  |
| wsrep_commit_oool          | 0.000000                                  |
| wsrep_commit_window        | 1.000000                                  |
| wsrep_local_state          | 4                                         |
| wsrep_local_state_comment  | Synced                                    |
| wsrep_cert_index_size      | 0                                         |
| wsrep_causal_reads         | 0                                         |
| wsrep_incoming_addresses   | demoenv-trial-3:3306,demoenv-trial-2:3306 |
| wsrep_cluster_conf_id      | 4                                         |
| wsrep_cluster_size         | 2                                         |
| wsrep_cluster_state_uuid   | 5bff0df7-4e1d-11e3-82a1-2288fc08ae68      |
| wsrep_cluster_status       | Primary                                   |
| wsrep_connected            | ON                                        |
| wsrep_local_index          | 1                                         |
| wsrep_provider_name        | Galera                                    |
| wsrep_provider_vendor      | Codership Oy (info@codership.com)         |
| wsrep_provider_version     | 2.8(r162)                                 |
| wsrep_ready                | ON                                        |
+----------------------------+-------------------------------------------+

参数 "wsrep_cluster_size" 变为了 2,并且 "demoenv-trial-1" 被移出了 Cluster 列表 "wsrep_incoming_addresses"。

测试写入数据:

mysql> use testdb;
mysql> INSERT INTO hostgroup (hostgroup_name,hostgroup_next,colo_name) VALUES ("adse","2","awse");

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

mysql> use testdb;
mysql> select * from hostgroup where hostgroup_name = "adse";
+--------------+----------------+----------------+-----------+
| hostgroup_id | hostgroup_name | hostgroup_next | colo_name |
+--------------+----------------+----------------+-----------+
|            2 | adse           |              2 | awse      |
+--------------+----------------+----------------+-----------+

可以看到 "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

Starting MySQL (Percona XtraDB Cluster).......SST in progress, setting sleep higher
.. SUCCESS! 

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

mysql> use testdb;
mysql> select User,Password,Host,Grant_priv from mysql.user;
+------+-------------------------------------------+-----------+------------+
| User | Password                                  | Host      | Grant_priv |
+------+-------------------------------------------+-----------+------------+
| root |                                           | localhost | Y          |
| root |                                           | 127.0.0.1 | Y          |
| sst  | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | localhost | N          |
+------+-------------------------------------------+-----------+------------+

mysql> GRANT USAGE ON *.* TO 'clustercheck'@'localhost' IDENTIFIED BY 'password';

[dong.guo@demoenv-trial-1 ~]$ /usr/bin/clustercheck clustercheck password 0;

HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
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方式好。

, ,

  1. #1 by Evan on 2013/12/24 - 16:14

    请问是同步复制的吗?性能对比MySQL Cluster如何?

  2. #2 by colen.me on 2014/05/14 - 12:54

    chcon -R -t mysqld_db_t /opt/mysql

    权限不对,你们都懂的。

(will not be published)
*