mysql读写分离

###
#在企业生产环境中，mysql架构采用主从模式，业务系统连接数据库；
1）业务系统连接mysql，master主库；
2）mysql从库是跟master主库进行数据同步；
3）如果master主机宕机，业务系统怎么操作？
4）可以修改业务系统的数据库配置ip为mysql slave从库；
5）不管主从如何切换，业务系统读、写请求只会发给一台数据库。
###

###

#mysql读写分离架构，建议在mysql主从（主主）基础上；

1）首先保障mysql架构是主从关系，主主关系；

2）业务系统SQL写请求-将请求转发给mysql master数据库；

3）业务系统SQL读请求-将请求转发给mysql-slave数据库；

#mysql读写分离的原理：

其实就是让mysql master数据库处理增、删、修改、更新的操作（create、insert into、update、delete），然后让slave数据库处理select操作，mysql读写分离前提是基于mysql主从复制，这样可以保证在master上修改数据，slave同步之后，web应用可以读取到slave端的数据。

实现mysql读写分离可以基于第三方插件，也可以通过开发修改代码实现，具体实现的读写分离的常见方式有如下四种：

        mysql-proxy读写分离；

        amoeba读写分离；

        mycat读写分离；

        基于程序读写分离（效率很高，实施难度大，开发需要代码）

#mysql读写分离架构图

Amoeba是以MySQL为底层数据存储，并对WEB、APP应用提供MySQL协议接口的proxy。它集中地响应WEB应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行，基于此可以实现负载均衡、读写分离、高可用性等需求。

Amoeba相当于一个SQL请求的路由器，目的是为负载均衡、读写分离、高可用性提供机制，而不是完全实现它们。用户需要结合使用MySQL的 Replication等机制来实现副本同步等功能。

Mysql-Proxy是MySQL官方提供的mysql中间件服务，支持无数客户端连接，同时后端可连接若干台Mysql-Server服务器，MYSQL-Proxy自身基于MySQL协议，连接MYSQL-Proxy的客户端无需修改任何设置， 跟正常连接MYSQL Server没有区别，无需修改程序代码。

MySQL Proxy是App应用（客户端）与MYSQL Server之间的一个连接代理，MySQL Proxy负责将APP应用的SQL请求根据转发规则，转发至相应的后端数据库，基于lua脚本，可以实现复杂的连接控制和过滤，从而实现数据读写分离和负载均衡的需求。

Mysql-Proxy允许用户指定Lua脚本对SQL请求进行拦截，对请求进行分析与修改，还允许用户指定Lua脚本对服务器的返回结果进行修改，加入一些结果集或者去除一些结果集，对SQL的请求通常为读请求、写请求，基于Lua脚本，可以实现将SQL读请求转发至后端Slave服务器，将SQL写请求转发至后端Master服务器。

#mycat安装配置

#mycat安装环境准备，至少需要三台机器，其中；i两台配置主从（主主）：

		IP              机器名      角色           
192.168.254.201     yang-1     主主
192.168.254.204     yang-4     主主
192.168.254.205     yang-5     mycat mysql客户端

#jdk1.8.0_131下载地址

https://www.oracle.com/java/technologies/javase/javase8-archive-downloads.html

#205的mycat机器

#上传软件包到mycat机器上

#上传jdk,mycat软件包

jdk-8u131-linux-x64.tar.gz
mycat2-1.21-release-jar-with-dependencies.jar
mycat2-install-template-1.21.zip

#解压文件

[root@yang-5 ~]# cd /tmp/
[root@yang-5 tmp]# tar xzf jdk-8u131-linux-x64.tar.gz
[root@yang-5 tmp]#

#创建java目录，把解压出来的文件挪过来

[root@yang-5 tmp]# mkdir -p /usr/java/
[root@yang-5 tmp]# mv jdk1.8.0_131/ /usr/java/
[root@yang-5 tmp]#

#使用ls看到这个状态说明没有问题

[root@yang-5 tmp]# cd
[root@yang-5 ~]# ls -l /usr/java/jdk1.8.0_131/
总用量 25864
drwxr-xr-x 2 10 143     4096 3月  15 2017 bin
-r--r--r-- 1 10 143     3244 3月  15 2017 COPYRIGHT
drwxr-xr-x 4 10 143      122 3月  15 2017 db
drwxr-xr-x 3 10 143      132 3月  15 2017 include
-rwxr-xr-x 1 10 143  5097105 3月  15 2017 javafx-src.zip
drwxr-xr-x 5 10 143      185 3月  15 2017 jre
drwxr-xr-x 5 10 143      245 3月  15 2017 lib
-r--r--r-- 1 10 143       40 3月  15 2017 LICENSE
drwxr-xr-x 4 10 143       47 3月  15 2017 man
-r--r--r-- 1 10 143      159 3月  15 2017 README.html
-rw-r--r-- 1 10 143      526 3月  15 2017 release
-rw-r--r-- 1 10 143 21115141 3月  15 2017 src.zip
-rwxr-xr-x 1 10 143    63933 3月  15 2017 THIRDPARTYLICENSEREADME-JAVAFX.txt
-r--r--r-- 1 10 143   177094 3月  15 2017 THIRDPARTYLICENSEREADME.txt
[root@yang-5 ~]#

#配置java环境变量，在/etc/profile配置文件中末尾添加

export JAVA_HOME=/usr/java/jdk1.8.0_131

#刷新生效

[root@yang-5 ~]# source /etc/profile
[root@yang-5 ~]#

#运行这条命令查看版本，能打印出版本说明java部署成功

[root@yang-5 ~]# $JAVA_HOME/bin/java -version
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)
[root@yang-5 ~]#

#mycat官网

www.mycat.org.cn

#解压mycat，移动到指定目录

unzip mycat2-install-template-1.21.zip
[root@yang-5 tmp]# mv mycat /usr/local/
[root@yang-5 tmp]# ls -l /usr/local/mycat/
总用量 8
drwxr-xr-x 2 root root 4096 3月   5 2021 bin
drwxr-xr-x 9 root root  275 3月   5 2021 conf
drwxr-xr-x 2 root root 4096 3月   5 2021 lib
drwxr-xr-x 2 root root    6 3月   5 2021 logs
[root@yang-5 tmp]#

#因为mycat是以来jar包的，把jar包移动到/usr/local/mycat/lib/目录下，然后mycat就部署成功了

[root@yang-5 tmp]# mv mycat2-1.目录下21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
[root@yang-5 tmp]# cd
[root@yang-5 ~]# ls -l /usr/local/mycat/lib/mycat2-1.21-release-jar-with-dependencies.jar
-rw-r--r-- 1 root root 151819628 11月  4 22:45 /usr/local/mycat/lib/mycat2-1.21-release-jar-with-dependencies.jar
[root@yang-5 ~]#

#给bin目录执行权限

[root@yang-5 ~]# chmod -R o+x /usr/local/mycat/bin/
[root@yang-5 ~]# ls -l /usr/local/mycat/bin/
总用量 2588
-rw-r--r-x 1 root root  15666 3月   5 2021 mycat
-rw-r--r-x 1 root root   3916 3月   5 2021 mycat.bat
-rw-r--r-x 1 root root 281540 3月   5 2021 wrapper-aix-ppc-32
-rw-r--r-x 1 root root 319397 3月   5 2021 wrapper-aix-ppc-64
-rw-r--r-x 1 root root 253808 3月   5 2021 wrapper-hpux-parisc-64
-rw-r--r-x 1 root root 140198 3月   5 2021 wrapper-linux-ppc-64
-rw-r--r-x 1 root root  99401 3月   5 2021 wrapper-linux-x86-32
-rw-r--r-x 1 root root 111027 3月   5 2021 wrapper-linux-x86-64
-rw-r--r-x 1 root root 114052 3月   5 2021 wrapper-macosx-ppc-32
-rw-r--r-x 1 root root 233604 3月   5 2021 wrapper-macosx-universal-32
-rw-r--r-x 1 root root 253432 3月   5 2021 wrapper-macosx-universal-64
-rw-r--r-x 1 root root 112536 3月   5 2021 wrapper-solaris-sparc-32
-rw-r--r-x 1 root root 148512 3月   5 2021 wrapper-solaris-sparc-64
-rw-r--r-x 1 root root 110992 3月   5 2021 wrapper-solaris-x86-32
-rw-r--r-x 1 root root 204800 3月   5 2021 wrapper-windows-x86-32.exe
-rw-r--r-x 1 root root 220672 3月   5 2021 wrapper-windows-x86-64.exe
[root@yang-5 ~]#

#启动mycat，然后查看日志，是否存在问题

[root@yang-5 ~]# /usr/local/mycat/bin/mycat
Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@yang-5 ~]# /usr/local/mycat/bin/mycat start
Starting mycat2...
[root@yang-5 ~]#
[root@yang-5 ~]#/usr/local/mycat/bin/mycat status
mycat2 is running (30635).
[root@yang-5 ~]#

#tail -fn20 /usr/local/mycat/logs/wrapper.log 查看日志如果显示找不到JVM，那么配置环境变量即可

#配置环境变量，末行加入

vim /etc/profile
export PATH=$PATH:/usr/java/jdk1.8.0_131/bin
#刷新生效
source /etc/profile

#tail -fn20 /usr/local/mycat/logs/wrapper.log 查看日志，显示拒绝连接，因为这时候mycat不知道需要做读写分离机器，需要去配置

#200的主机器

#在mysql主从节点均创建一个用户并授权(可以使用root)，给mycal程序使用

mysql> create user 'mycat'@'192.168.254.205' identified by '123456';
Query OK, 0 rows affected (0.07 sec)

mysql> grant all privileges on *.* to 'mycat'@'192.168.254.205';
Query OK, 0 rows affected (0.01 sec)

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

#创建原始数据库

create database mycat;

#进入mysql库

use mysql;

#查看user表字段

desc user;

#查看刚刚创建的mysql权限

mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------------+------------------------------------------------------------------------+
| user             | host            | authentication_string                                                  |
+------------------+-----------------+------------------------------------------------------------------------+
| root             | %               | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              |
| tongbu           | %               | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
| mycat            | 192.168.254.205 | $A$005$:sp+{v&"Ct=tte[ VaTnTxlrzoDh5tSuEZl4lijCz8asRArmnPdz5.OiqE9 |
| mysql.infoschema | localhost       | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost       | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost       | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              |
+------------------+-----------------+------------------------------------------------------------------------+
7 rows in set (0.00 sec)
mysql>

#204的从机器，同样查看在主机器上创建的用户和授权有没有同步过来

mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------------+------------------------------------------------------------------------+
| user             | host            | authentication_string                                                  |
+------------------+-----------------+------------------------------------------------------------------------+
| root             | %               | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              |
| tongbu           | %               | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
| mycat            | 192.168.254.205 | $A$005$W=        (W
#v(
#eAzXi5s2Sbjlkr2QpItwmWVb9oDfttM0rkk78JhU9tlO1D |
| mysql.infoschema | localhost       | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost       | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost       | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              |
+------------------+-----------------+------------------------------------------------------------------------+
7 rows in set (0.01 sec)
mysql>

#205的mycat机器

#配置mycat原始数据库中的数据源，这个库不配置的话，无法启动mycat

vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
"password":"123456",
        "type":"JDBC",
192.168.254.200:3306/mycat?useUnicode=true&serverTimeznotallow=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"mycat",
        "weight":0
}

#然后添加物理数据的数据源，如果存在多从，那么就配置多个slave即可

#进入DataSource目录;

cd /usr/local/mycat/conf/datasources/

#拷贝模板文件，生成master和slave配置文件;

\cp prototypeDs.datasource.json master.datasource.json
\cp prototypeDs.datasource.json slave-01.datasource.json
#修改master.datasource.json代码;
cat>master.datasource.json<<EOF
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"master",
        "password":"123456",
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.254.200:3306/discuz?useUnicode=true&serverTimeznotallow=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}
EOF
#修改slave-01.datasource.json代码;
cat>slave-01.datasource.json<<EOF
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"slave-01",
        "password":"123456",
        "type":"JDBC",
        "url":"jdbc:mysql://192.168.254.204:3306/discuz?useUnicode=true&serverTimeznotallow=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}
EOF

#给数据库配置集群信息

#prototype.cluster.json #是原始的集群json文件，它的作用是给mycat定义请求转到后端之后是属于什么类型的，这个配置文件不要修改，重新复制一份进行操作

[root@yang-5 datasources]# cd /usr/local/mycat/conf/clusters/
[root@yang-5 clusters]# ls
prototype.cluster.json
[root@yang-5 clusters]#

#拷贝配置文件编辑配置，源文件也不用修改，只需要加上master有哪些机器，从库有哪些机器

[root@yang-5 clusters]# cp prototype.cluster.json master-slave.cluster.json
[root@yang-5 clusters]#
cat>master-slave.cluster.json<<EOF
{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetry":3,
                "minSwitchTimeInterval":300,
                "slaveThreshold":0
        },
        "masters":[
                "master"
        ],
        "replicas":[
                "slave-01"
        ],
        "maxCon":5000,
        "name":"master-slave",
        "readBalanceType":"BALANCE_ALL",
        "switchType":"NOT_SWITCH"
}
EOF

#然后配置物理库（schema）和mycat中数据源，数据源集群的关系

cd /usr/local/mycat/conf/schemas/
cat>discuz.schema.json<<EOF
{
    "schemaName": "discuz",
    "targetName": "master-slave",
    "normalTables": {}
}
EOF

#最后修改mycat用户登录的信息，指定用户名密码

[root@yang-5 schemas]# cd /usr/local/mycat/conf/users/
[root@yang-5 users]# ls
root.user.json
[root@yang-5 users]# vim root.user.json
[root@yang-5 users]#
{
        "dialect":"mysql",
        "ip":null,
        "password":"123456",
        "transactionType":"xa",
        "username":"root"
}

#启动服务

/usr/local/mycat/bin/mycat start

#查看日志，是否存在报错，这么查看日志，看的不全面，可以把这个日志清空，重新启动服务使用less或者more查看

tail -fn20 /usr/local/mycat/logs/wrapper.log

#这时候并没有启动成功

查看日志发现找不到200主这个机器上的discuz库，因为前面并没有在200主机器上创建这个物理数据库

#200主机器

#创建discuz库，同时查看204从机器上是否同步创建成功

mysql> create database discuz;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| discuz             |
| information_schema |
| mycat              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

#授权

mysql> update mysql.user set host='%' where user="root";

#允许使用8.0版本之前的规则定义密码

mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

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

#205mycat机器

#再次启动mycat服务，查看日志是否还存在报错

/usr/local/mycat/bin/mycat start
Starting mycat2...
[root@yang-5 users]# less /usr/local/mycat/logs/wrapper.log

#查看进程，启动成功

ps aux |grep mycat

#查看监听端口

#8066端口是应用程序来连接的端口

#9066是mycat本身管理端口

[root@yang-5 users]# netstat -tnlp |grep -aiE java
tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      33815/java
tcp6       0      0 127.0.0.1:9066          :::*                    LISTEN      33815/java
tcp6       0      0 :::34731                :::*                    LISTEN      33815/java
tcp6       0      0 :::45741                :::*                    LISTEN      33815/java
tcp6       0      0 :::1984                 :::*                    LISTEN      33815/java
tcp6       0      0 :::8066                 :::*                    LISTEN      33815/java
[root@yang-5 users]#

#测试一台机器当作客户端去连接mycat

[root@localhost ~]# mysql -h 192.168.254.205 -uroot -p'123456' -P8066
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 0
Server version: 5.7.33-mycat-2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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`         |
+--------------------+
| discuz             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.15 sec)
mysql>

#200主机器创建

#在discuz库中创建表，插入数据，测试读写分离功能是否正常

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| discuz             |
| information_schema |
| mycat              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use discuz
Database changed
mysql> create table t1 (id varchar(30),name char(20),job char(20));
Query OK, 0 rows affected (0.02 sec)

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

mysql> insert into t1 values (006,'lisi','IT');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+----------+------+
| id   | name     | job  |
+------+----------+------+
| 5    | zhangsan | IT   |
| 6    | lisi     | IT   |
+------+----------+------+
2 rows in set (0.00 sec)
mysql>

#205的机器重启mycat

/usr/local/mycat/bin/mycat restart

#客户端去查看mycat机器，能够查到数据表示读写分离功能正常

[root@localhost ~]# mysql -uroot -p'123456' -h 192.168.254.205 -P 8066 -e "select * from discuz.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+----------+------+
| id   | name     | job  |
+------+----------+------+
| 5    | zhangsan | IT   |
| 6    | lisi     | IT   |
+------+----------+------+
[root@localhost ~]#