Mycat中间件实现Percona Cluster集群读写分离

来源：这里教程网时间：2026-03-01 14:39:55 作者：

Mycat实现读写分离架构图：

Mycat主从自动切换架构图：

Mycat下载地址：http://dl.mycat.io/1.6.7.3/20190828135747/Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz Percona-XtraDB-Cluster下载地址：https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/LATEST/ Percona-XtraBackup下载地址： https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/ yum仓库地址：https://www.percona.com/downloads/percona-release/redhat/ 1.下载，安装Percona-XtraDB-Cluster（三台都需要装） [root@k8s01 ~]# rpm -ivh https://www.percona.com/redir/downloads/percona-release/redhat/1.0-13/percona-release-1.0-13.noarch.rpm [root@k8s01 ~]# yum repolist [root@k8s01 ~]# rpm -ivh percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm [root@k8s01 ~]# yum install Percona-XtraDB-Cluster-57 -y 2.修改配置，启动服务（k8s01节点） [root@k8s01 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf log-bin=k8s01-bin log-bin-index=k8s01-bin-index [root@k8s01 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.54.129,192.168.54.130,192.168.54.128 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=192.168.54.128 --三个节点值不一样 wsrep_cluster_name=mycluster wsrep_node_name=k8s01 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" [root@k8s01 ~]# mysql_install_db --defaults-file=/etc/percona-xtradb-cluster.cnf --datadir=/var/lib/mysql --初使化数据 [root@k8s01 ~]# systemctl start mysql@bootstrap.service --第一次启动，不需要同步数据 [root@k8s01 ~]# ps -ef | grep mysql root 60672 54285 0 14:56 pts/1 00:00:00 tailf /var/log/mysqld.log root 66031 1 0 15:03 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr --wsrep-new-cluster mysql 66568 66031 6 15:03 ? 00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/galera3/libgalera_smm.so --wsrep-new-cluster --log-error= /var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1root 66763 33815 0 15:04 pts/0 00:00:00 grep --color=auto mysql

[root@k8s01 ~]# cat ~/.mysql_secret # Password set for user 'root@localhost' at 2019-11-15 14:40:31 ya;-t*taM8T< --mysql登陆密码 [root@k8s01 ~]# mysql -u root -p'ya;-t*taM8T<' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.7.27-30-57-log

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

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

mysql> set password=password('System135'); Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

mysql> exit Bye

[root@k8s01 ~]# mysql -u root -pSystem135 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

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

mysql> grant all privileges on *.* to sstuser@'%' identified by 's3cretPass'; --创建percona xtradb cluster用户 Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

mysql> exit Bye [root@k8s01 ~]#

3.修改配置，启动服务（k8s02节点） [root@k8s02 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf log-bin=k8s01-bin log-bin-index=k8s01-bin-index [root@k8s02 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.54.128,192.168.54.129,192.168.54.130 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=192.168.54.129 wsrep_cluster_name=mycluster wsrep_node_name=k8s02 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" [root@k8s02 ~]# systemctl start mysql 4.修改配置，启动服务（k8s03节点） [root@k8s03 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf log-bin=k8s01-bin log-bin-index=k8s01-bin-index [root@k8s03 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.54.128,192.168.54.129,192.168.54.130

binlog_format=ROW

default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=192.168.54.130 wsrep_cluster_name=mycluster wsrep_node_name=k8s03 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" [root@k8s03 ~]# systemctl start mysql 5.测试Percona-XtraDB-Cluster集群数据同步 k8s01节点：

[root@k8s01 ~]# mysql -u root -pSystem135 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

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

mysql> create database wuhan charset utf8; Query OK, 1 row affected (0.01 sec)

mysql> use wuhan Database changed mysql> create table t (a int primary key,b int); Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(1,2),(2,3); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t; +---+------+ | a | b | +---+------+ | 1 | 2 | | 2 | 3 | +---+------+ 2 rows in set (0.00 sec)

mysql> exit Bye [root@k8s01 ~]#

k8s02节点：

[root@k8s02 ~]# mysql -u root -pSystem135 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

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

mysql> use wuhan Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> select * from t; +---+------+ | a | b | +---+------+ | 1 | 2 | | 2 | 3 | +---+------+ 2 rows in set (0.01 sec)

mysql> exit Bye [root@k8s02 ~]#

6.下载，安装Mycat中间件 [root@[root@k8s01 soft]# rpm -ivh jdk-8u221-linux-x64.rpm warning: jdk-8u221-linux-x64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY Preparing... ################################# [100%] Updating / installing... 1:jdk1.8-2000:1.8.0_221-fcs ################################# [100%] Unpacking JAR files... tools.jar... plugin.jar... javaws.jar... deploy.jar... rt.jar... jsse.jar... charsets.jar... localedata.jar... [root@k8s01 soft]# tar xvf Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz -C /usr/local/ [root@k8s01 soft]# cd /usr/local/mycat/conf/ [root@k8s01 conf]# vim schema.xml [root@k8s01 conf]# vim server.xml

<user name="root" defaultAccount="true"> <property name="password">System135</property> <property name="schemas">hubei</property>

</user>

[root@k8s01 conf]# ../bin/mycat restart Stopping Mycat-server... Mycat-server was not running. Starting Mycat-server... [root@k8s01 conf]# netstat -antulp | grep 9066 tcp6 0 0 :::9066 :::* LISTEN 100087/java [root@k8s01 conf]# 7.使用Mycat验证读，写数据(1).查看管理端口服务 (2).查看数据，如果想要看负载均衡效果，必须退出终端，再登陆。 (3).k8s01节点写入数据，k8s02和k8s03读数据 8.k8s01节点宕机后，k8s02节点负责写入数据，k8s03读数据 [root@k8s01 conf]# systemctl stop mysql@bootstrap.service [root@k8s01 conf]# netstat -antulp | grep 3306 tcp6 0 0 10.4.132.50:60568 10.4.132.42:3306 ESTABLISHED 100087/java tcp6 0 0 10.4.132.50:51072 10.4.132.66:3306 ESTABLISHED 100087/java [root@k8s01 conf]# ps -ef | grep mysqld root 115559 33815 0 16:58 pts/0 00:00:00 grep --color=auto mysqld

[root@k8s01 conf]#

(1).关闭k8s01节点的mysql，写入一条数据，读取一次。

(2).写入节点是k8s02，读节点是k8s03 错误处理: (1).Mysql没有初使化数据

2019-11-15T06:33:04.384904Z 0 [Note] Server socket created on IP: '::'. 2019-11-15T06:33:04.385584Z 0 [Warning] Failed to open optimizer cost constant tables

2019-11-15T06:33:04.385659Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 2019-11-15T06:33:04.385668Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files. 2019-11-15T06:33:04.385687Z 0 [ERROR] Aborting 解决方法： [root@k8s01 conf]# mysql_install_db --defaults-file=/etc/percona-xtradb-cluster.cnf --datadir=/var/lib/mysql --需要初使化数据 (2).第一个节点第一次启动必须要创建新的集群 2019-11-15T06:41:35.761089Z 0 [Note] WSREP: Current view of cluster as seen by this node view ((empty)) 2019-11-15T06:41:35.761280Z 0 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout): 110 (Connection timed out) at gcomm/src/pc.cpp:connect():159 2019-11-15T06:41:35.761309Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():209: Failed to open backend connection: -110 (Connection timed out) 2019-11-15T06:41:35.761583Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1514: Failed to open channel 'pxc_1' at 'gcomm://192.168.54.128,192.168.54.129,192.168.54.130': -110 (Connection timed out) 2019-11-15T06:41:35.761597Z 0 [ERROR] WSREP: gcs connect failed: Connection timed out 2019-11-15T06:41:35.761600Z 0 [ERROR] WSREP: Provider/Node (gcomm://192.168.54.128,192.168.54.129,192.168.54.130) failed to establish connection with cluster (reason: 7) 2019-11-15T06:41:35.761603Z 0 [ERROR] Aborting 解决方法：

[root@k8s01 ~]# systemctl start mysql@bootstrap.service --必须添加 @bootstrap.service启动服务

(3).没有创建wsrep.cnf用户名 2019-11-15T07:27:59.158666Z 0 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.54.129:4444/xtrabackup_sst//1' --socket '/var/lib/mysql/mysql.sock' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suf fix '' --mysqld-version '5.7.27-30-57' '' --gtid '19a21dce-0776-11ea-8879-92a989219bb1:2'2019-11-15T07:27:59.159302Z 0 [Warning] WSREP: Protocol violation. JOIN message sender 0.0 (k8s01) is not in state transfer (SYNCED). Message ignored. 2019-11-15T07:27:59.159405Z 5 [Note] WSREP: Synchronized with group, ready for connections 解决方法： [root@k8s01 conf]# grep sst /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" [root@k8s01 conf]#

mysql> grant all privileges on *.* to sstuser@'%' identified by 's3cretPass'; --创建用户 Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

mysql> exit Bye [root@k8s01 ~]#