Mycat中间件实现Percona Cluster集群读写分离 - MySQL数据库

博主:xiaoweixiaowei 2023-01-18 条评论

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 Copyright (c) 2009-2019 Percona LLC and/or its affiliates

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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 Copyright (c) 2009-2019 Percona LLC and/or its affiliates

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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 Copyright (c) 2009-2019 Percona LLC and/or its affiliates

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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 Copyright (c) 2009-2019 Percona LLC and/or its affiliates

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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 ~]#

The End

发布于:2023-01-18,除非注明,否则均为 主机评测原创文章,转载请注明出处。