Mycat中間件實現Mysql主從讀寫分離

發布時間：2020-08-18 03:56:41 來源：ITPUB博客閱讀：359 作者：z597011036 欄目：MySQL數據庫

環境規劃：

IP地址	主機名	角色	備注
10.4.132.50	k8s01	mycat,master
10.4.132.42	k8s02	slave
10.4.132.66	k8s03	slave

Mycat下載地址：http://dl.mycat.io/1.6.7.3/20190828135747/Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz

Mysql下載地址： http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

Mycal管理集群端口：[root@k8s01 conf]# mysql -h 127.0.0.1 -u root -p123456 -P 9066

Mycat數據端口：[root@k8s01 conf]# mysql -h 127.0.0.1 -u root -p123456 -P 8066

1.下載安裝mysql(1臺master節點和2臺slave節點)

[root@k8s01 soft]# wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

[root@k8s01 soft]# tar xvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

[root@k8s01 soft]# cd /usr/local/

[root@k8s01 local]# mv mysql-5.7.27-linux-glibc2.12-x86_64/ mysql-5.7.27

[root@k8s01 local]# chown -R root:root mysql-5.7.27/

[root@k8s01 local]# cd mysql-5.7.27/

[root@k8s01 mysql-5.7.27]# mkdir data

[root@k8s01 mysql-5.7.27]# useradd -r -M -s /bin/nologin mysql

[root@k8s01 mysql-5.7.27]# chown -R mysql:mysql data/

[root@k8s01 mysql-5.7.27]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql-5.7.27 --datadir=/usr/local/mysql-5.7.27/data

2019-11-02T04:24:41.908404Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-11-02T04:24:46.687678Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-11-02T04:24:47.428823Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-11-02T04:24:47.487404Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b42cef88-fd28-11e9-a5cc-000c29ee86d5.

2019-11-02T04:24:47.488204Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-11-02T04:24:47.612739Z 1 [Note] A temporary password is generated for root@localhost: 3m;5yQ_7T#jc --登陸密碼

[root@k8s01 mysql-5.7.27]# cp -a support-files/mysql.server /etc/init.d/mysqld

[root@k8s01 mysql-5.7.27]# chkconfig --add mysqld

[root@k8s01 mysql-5.7.27]# chkconfig mysqld on

[root@k8s01 mysql-5.7.27]# vim /etc/init.d/mysqld

basedir=/usr/local/mysql-5.7.27

datadir=/usr/local/mysql-5.7.27/data

[root@k8s01 mysql-5.7.27]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql-5.7.27
datadir=/usr/local/mysql-5.7.27/data
socket=/tmp/mysql.sock
symbolic-links=0
server_id=10
binlog_format=ROW
max_binlog_size=2G
sync_binlog=1
binlog_cache_size=64M
log_bin=bin-log
log_bin_index=bin-index

[mysqld_safe]

log-error=/usr/local/mysql-5.7.27/data/mariadb.log

pid-file=/usr/local/mysql-5.7.27/data/mariadb.pid

[root@k8s01 mysql-5.7.27]# /etc/init.d/mysqld restart

ERROR! MySQL server PID file could not be found!

Starting MySQL.Logging to '/usr/local/mysql-5.7.27/data/mariadb.log'.

... SUCCESS!

[root@k8s01 mysql-5.7.27]# vim /etc/profile

export PATH=$PATH:/usr/local/mysql-5.7.27/bin

[root@k8s01 mysql-5.7.27]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.27

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respectiveowners.

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.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@k8s01 mysql-5.7.27]# 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 6

Server version: 5.7.27 MySQL Community Server (GPL)

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql>

2.master節點和slave節點做主從

master節點：

[root@k8s01 mysql-5.7.27]# 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 39
Server version: 5.7.27-log MySQL Community Server (GPL)

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>

3.兩個slave節點（兩個slave節點都要連接到master節點）

[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 2
Server version: 5.7.27 MySQL Community Server (GPL)

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> change master to master_host='10.4.132.50',master_user='repl',master_password='123456',master_port=3306,master_log_file='bin-log.000002',master_log_pos=3093;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.4.132.50
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000002
          Read_Master_Log_Pos: 3093
               Relay_Log_File: k8s02-relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: bin-log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

mysql>

4.驗證主從數據是否同步

master節點：

mysql> system hostname
k8s01

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

mysql> use wuhan
Database changed
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql>

slave1節點：

mysql> system hostname
k8s02

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 t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql>

slave2節點：

mysql> system hostname
k8s03
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 t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql>

5.下載安裝配置Mycat（master節點）

[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中間件實現Mysql主從讀寫分離

配置講解：

schema name="wuhan" --需要做讀取寫分離的庫

checkSQLschema="true" --執行sql時是否去掉schema名

sqlMaxLimit="100" --如果sql語句沒有加limit限制，此時默認值是100

<table name="t1" --指定讀寫分離的表

dataNode="dn1" --數據節點

<dataNode name="dn1" --對應上面的數據節點（任意起）

dataHost="10.4.132.50" --數據主機名（任意起）

database="wuhan" --庫名

<dataHost name="10.4.132.50" --對應以上值

balance="0" --不開啟讀寫分離機制，所有操作都在master上。1 所有讀操作都在slave節點上。 2 所有讀操作都隨機在master和slave節點上。 3 所有讀操作都發送到slave節點，master節點只負責寫。

<heartbeat>select user()</heartbeat> --心跳探測

<writeHost host="W_k8s01" url="10.4.132.50:3306" user="repl" password="123456"> --后端主機(用戶名和密碼是mycat服務器可以登陸后面mysql的權限)

<schema name="hubei" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> --不寫table name關鍵字，表示全庫所有表做讀寫分離。

[root@k8s01 conf]# vim server.xml

<user name="root" defaultAccount="true">
<property name="password">123456</property> --mycat登陸帳號（密碼可以隨意指定）
<property name="schemas">wuhan</property> --讀寫分離的庫名

</user>

[root@k8s01 logs]# ../bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@k8s01 logs]#

6.查看各節點的狀態

Mycat中間件實現Mysql主從讀寫分離

7.測試mycat實現的讀寫分離（登陸一次查詢后要退出，再登陸查詢）

[root@k8s01 conf]# mysql -h 127.0.0.1 -u root -p123456 -P 8066 -A
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 2
Server version: 5.6.29-mycat-1.6.7.3-release-20190828215749 MyCat Server (OpenCloudDB)

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
Database changed
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 rows in set (0.08 sec)

mysql> exit
Bye
[root@k8s01 conf]# mysql -h 127.0.0.1 -u root -p123456 -P 8066 -A
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 3
Server version: 5.6.29-mycat-1.6.7.3-release-20190828215749 MyCat Server (OpenCloudDB)

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
Database changed
mysql> select * from t1;
+------+
| a |
+------+
| 1 |
+------+
1 rows in set (0.00 sec)

mysql>

日志查看結果：

Mycat中間件實現Mysql主從讀寫分離

可重復登陸查詢，寫入數據結果（必須退出會話再登陸查詢）：

Mycat中間件實現Mysql主從讀寫分離

向AI問一下細節

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

Mycat中間件實現Mysql主從讀寫分離

猜你喜歡

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

Mycat中間件實現Mysql主從讀寫分離

猜你喜歡

最新資訊

相關推薦

相關標簽