溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離

發(fā)布時(shí)間：2020-08-13 12:40:07 來(lái)源：ITPUB博客 閱讀：353 作者：z597011036 欄目：MySQL數(shù)據(jù)庫(kù)

Mycat實(shí)現(xiàn)讀寫(xiě)分離架構(gòu)圖：

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離

Mycat主從自動(dòng)切換架構(gòu)圖：

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離

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倉(cāng)庫(kù)地址：https://www.percona.com/downloads/percona-release/redhat/ 

1.下載，安裝Percona-XtraDB-Cluster（三臺(tái)都需要裝）
[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.修改配置，啟動(dòng)服務(wù)（k8s01節(jié)點(diǎn)）

[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      --三個(gè)節(jié)點(diǎn)值不一樣

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   --初使化數(shù)據(jù)

[root@k8s01 ~]# systemctl start mysql@bootstrap.service         --第一次啟動(dòng)，不需要同步數(shù)據(jù)
[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';      --創(chuàng)建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.修改配置，啟動(dòng)服務(wù)（k8s02節(jié)點(diǎn)）

[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.修改配置，啟動(dòng)服務(wù)（k8s03節(jié)點(diǎn)）

[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.測(cè)試Percona-XtraDB-Cluster集群數(shù)據(jù)同步

k8s01節(jié)點(diǎn)：

[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節(jié)點(diǎn)：

[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

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離 [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驗(yàn)證讀，寫(xiě)數(shù)據(jù)

(1).查看管理端口服務(wù)

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離

(2).查看數(shù)據(jù)，如果想要看負(fù)載均衡效果，必須退出終端，再登陸。

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離

(3).k8s01節(jié)點(diǎn)寫(xiě)入數(shù)據(jù)，k8s02和k8s03讀數(shù)據(jù)

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離

8.k8s01節(jié)點(diǎn)宕機(jī)后，k8s02節(jié)點(diǎn)負(fù)責(zé)寫(xiě)入數(shù)據(jù)，k8s03讀數(shù)據(jù)

[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).關(guān)閉k8s01節(jié)點(diǎn)的mysql，寫(xiě)入一條數(shù)據(jù)，讀取一次。

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離

(2).寫(xiě)入節(jié)點(diǎn)是k8s02，讀節(jié)點(diǎn)是k8s03

Mycat中間件實(shí)現(xiàn)Percona Cluster集群讀寫(xiě)分離

錯(cuò)誤處理:

(1).Mysql沒(méi)有初使化數(shù)據(jù)

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    --需要初使化數(shù)據(jù)

(2).第一個(gè)節(jié)點(diǎn)第一次啟動(dòng)必須要?jiǎng)?chuàng)建新的集群

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啟動(dòng)服務(wù) 

(3).沒(méi)有創(chuàng)建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';     --創(chuàng)建用戶
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 ~]#

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手