四、MySQL读写分离之MyCAT

一、读写分离概述

1、什么是读写分离：

读写分离：就是将读写操作分发到不同的服务器，读操作分发到对应的服务器（slave），写操作分发到对应的服务器（master）

① M-S (主从) 架构下，读写必须分离，如果不分离，业务可能出现问题；

② M-M (双主) 架构下，可以随意读写操作，特定的操作交由特定的服务器操作，工作效率更高。

2、读写分离目的：

将读写业务分配到不同的服务器上，让服务器做特定的操作，使工作效率提高，同时降低主服务器的压力。

3、读写分离实现方式：

(1) 业务代码读写分离：

需要在业务代码中，判断数据操作是读还是写。

写操作 → mysql01（主数据库）：

● 增加：

mysql> insert into 数据表 values ('字段值1','字段值2',...);

● 删除：

mysql> delete from 数据表 where 条件; #删除满足条件的记录
mysql> delete from 数据表 where 字段 in (字段值1,字段值2,...); #删除多个字段
mysql> delete from 数据表; #删除表中所有记录

● 更新：

mysql> update 数据表 set 字段1=字段值1,字段2=字段值2,... where条件;

读操作 → mysql02（从数据库）：

● 查询：

mysql>select */字段列表 from 数据表 where条件 group by分组 having 子句 order by排序 limit子句;

#where 条件：where 关键字用于过滤结果的条件，只有满足这个条件的记录才会被检索出来。
#group by分组：将检索出来的结果进行分组。
#having 子句：用于过滤分组后的结果集。
#ORDER BY：用于对结果集进行排序，ASC 升序或 DESC 降序。
#LIMIT 子句：用于限制返回的记录数。

(2) 中间件代理读写分离：

在业务代码中，数据库的操作，不直接连接数据库，而是先请求到代理服务器，由代理服务器判断读操作去从数据服务器，写操作去主数据服务器。

二、数据库读写分离操作：

1、MySQL从服务器准备：

(1) 克隆CentOS7母机，生成MySQL02数据库服务器：

配置IP地址与UUID编号

设置主机名称，绑定IP与主机名称到/etc/hosts文件

关闭防火墙与SELinux

关闭NetworkManager

时间同步

(2) 在MySQL02服务器中安装MySQL：

使用rsync传输文件到MySQL02：

不需要进行数据库初始化，只需要保证主从数据库之间的数据一致即可。

tar -zxf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql
useradd -r -s /sbin/nologin mysql
chown -R mysql.mysql /usr/local/mysql
cd /usr/local/mysql
rm -rf data/*  #移除测试数据库

(3) 将MySQL01的/usr/local/mysql/data/下的所有文件传输MySQL02中：

rsync -av /usr/local/mysql/data/* root@10.1.1.14:/usr/local/mysql/data/

(4) 在MySQL02服务器中，删除/usr/local/mysql/data/auto.cnf文件：

auto.cnf这个文件保存了每一个数据库的唯一编号，删除避免主从数据库配置出错。

rm /usr/local/mysql/data/auto.cnf

(5) 环境配置：

cp support-files/mysql.server /etc/init.d/mysql
service mysql start
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

2、MySQL主从配置：

(1) MySQL主从同步原理：

① 从库的IO线程连接到主库的主进程，并请求复制数据。主库验证从库身份后，将复制数据交给主库IO线程处理。

② 主库IO线程读取主库上的binlog，并将binlog事件发送到从库。从库的IO线程接收到这些事件，并将它们写入到从库的relay-log中。

③ 从库的SQL线程读取relay-log中的binlog事件，并将它们应用到从库的数据中。同时，从库会更新master.info文件，记录复制的位置信息。

④ 重复上述步骤，从而实现持续的主从同步。

(2) 配置MySQL01与MySQL02的my.cnf文件：

MySQL01：

vim /usr/local/mysql/my.cnf

[mysqld]

basedir=/usr/local/mysql #MySQL安装的基础目录
datadir=/usr/local/mysql/data #数据库文件存储位置
port=3306
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/data/mysql.err #错误日志文件的位置
character_set_server=utf8mb4 #MySQL服务器使用的字符集，utf8mb4可存储四字节的字符。
server_id = 10 #主从复制中标识服务器的身份，必须唯一。
log-bin=/usr/local/mysql/data/binlog
#二进制日志 (binlog) 的存储位置，二进制日志记录了所有更改数据的语句。

MySQL02：

vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/data/mysql.err
character_set_server=utf8mb4
server_id=20
relay-log=/usr/local/mysql/data/relaylog
#中继日志（relay logs）的路径。中继日志是从服务器在复制过程中用来暂存主服务器的二进制日志（binlog）事件的。

(3) 在MASTER（MySQL01）中创建一个账号slave，用于实现数据同步：

mysql> grant replication slave on *.* to 'slave'@'10.1.1.%' identified by '123';

#授予slave用户将主服务器上的二进制日志文件复制到从服务器上的权限

mysql> flush privileges;

(4) 开启主从同步：

MySQL01：show master status\G

标识了二进制日志文件以及从哪个点开始读取日志中的事件。

MySQL02：

mysql> CHANGE MASTER TO
       MASTER_HOST='10.1.1.12',  #主数据库IP
       MASTER_USER='slave',    #连接到主库的用户名
       MASTER_PASSWORD='123',  #连接到主库的密码
       MASTER_PORT=3306,
       MASTER_LOG_FILE='binlog.000001',  #从哪个二进制日志文件开始复制
       MASTER_LOG_POS=405,   #指定二进制日志文件中的开始位置
       MASTER_CONNECT_RETRY=10;   #连接失败，从服务器重试连接的秒数间隔。

mysql>start slave;
mysql> show slave status\G

3、使用MyCat中间件实现读写分离：

(1) 准备一台MyCat服务器：

基于CentOS7母机进行克隆生成MyCAT服务器，调整CPU与内存（建议2核心2GB）

更改IP地址与UUID编号

更改主机名称

绑定IP与主机名称到/etc/hosts文件

关闭防火墙与SELinux

关闭NetworkManager

时间同步

(2) JDK软件的安装与配置：

① 为什么需要JDK：

MyCAT是基于Java语言开发的程序，其操作系统中必须拥有Java的运行环境，否则MyCAT无法运行。

② JDK与JRE的区别：

JDK：包含了JRE和Java开发工具，主要用于开发Java应用程序。

JRE：只包含了运行Java程序所必需的文件，用于运行已经编译好的Java应用程序。

③ 安装JDK：

tar xvf jdk-8u192-linux-x64.tar.gz
mkdir /usr/local/java
mv jdk1.8.0_192 /usr/local/java/

配置环境变量：mycat在环境变量中找java环境。

export PATH=$PATH:/usr/local/java/jdk1.8.0_192/bin >> /etc/profile
source /etc/profile

查看java版本：

(3) MyCAT软件的安装：

tar xvf Mycat-server-1.6.5-release-20180122220033- linux.tar.gz -C /usr/local

bin：mycat二进制文件目录； conf：配置文件目录； logs：日志目录。

查看mysql是否启动：/usr/local/mycat/bin/mycat console

此时终端占用中，开启另一个终端查看端口：

8066：MyCAT客户端 (连接web服务器) ； 9066：MyCAT管理端 (连接MySQL数据库)

4、MyCAT核心配置：

MyCAT核心配置文件有两个：

server.xml：对外提供的用户等的设置

schema.xml：配置后端数据库服务器相关信息

(1) server.xml文件：
默认情况下，MyCAT中server.xml已经配置完毕，无需修改。

vim /usr/local/mycat/conf/server.xml

(2) schema.xml文件：

schema.xml文件面向的是后端真实的数据库 (MySQL01与MySQL02)，需要进行配置。

vim /usr/local/mycat/conf/schema.xml

① 删除配置文件中多余的部分（建议先备份，避免删错）：

删除所有注释；

删除schema标签中的所有table：

:6,22d

dataNode节点只保留一个dn1；

writeHost节点只保留一个。

schema.xml文件的大致结构：

② 在标签中添加dataNode节点：

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

TESTDB是虚拟数据库，用于连接web服务器和MyCAT。dataNode用于连接真实的MySQL数据库。

③ 在dataNode节点中，指定真实的数据库：

<dataNode name="dn1" dataHost="localhost1" database="ds_shop"/>

dataHost：数据库集群

④ 设置writeHost与readHost (读写服务器信息)：

<writeHost host="hostM1" url="10.1.1.12:3306" user="dsshop" password="123">

<readHost host="hostS2" url="10.1.1.14:3306" user="dsshop" password="123" />

</writeHost>

dsshop：数据库中创建的用于远程连接的账号。

⑤ 开启读写分离：

balance属性：

balance="0"：关闭读写分离，所有读写操作都发送到 writeHost 上；

balance="1"：开启读写分离，所有读操作发送到 readHost 上。

⑥ 启动MyCAT：

/usr/local/mycat/bin/mycat start

5、MyCAT客户端与管理端：

(1) MycCAT组成：

MyCAT软件一共有两端：8066客户端；9066管理端

Web01/Web02 通过 MyCAT 8066客户端去访问 MySQL01/MySQL02

MyCAT（8066客户端）→ 9066管理端 → MySQL01/MySQL02

(2) 查看代理客户端 8066 (对接 Web)：

① 在MyCAT服务器中安装MySQL客户端软件mysql：

yum install mysql -y
rm -rf /etc/my.cnf

② 登录MyCAT中的虚拟数据库：

使用IP地址+端口号链接MyCAT 8066客户端

mysql -h 10.1.1.15 -P 8066 -uroot -p
Enter password:123456

虽然数据库名是TESTDB，但数据库中的表仍是ds_，说明TESTDB只是用于连接真实数据库的一个虚拟数据库。

(3) 管理监控端 9066 (管理后端MySQL)：

① 使用IP地址+9066端口号链接管理端：

mysql -h 10.1.1.15 -P 9066 -uroot -p
Enter password:123456

② 使用show @@help命令查看MyCAT管理端提供的所有操作指令：

③ 使用show @@heartbeat命令查看后端MySQL数据库的连接心跳信息：

当停止了MySQL02后：

6、DSShop商城系统更改：

web服务器与MyCAT服务器关联，修改database.php配置文件。

web01、web02与MyCAT建立联系所需要配置的内容：

IP地址：10.1.1.15

端口：8066

数据库名称：TESTDB

用户名：root

密码：123456

vim /home/www/application/database.php

登录商城后出现这种错误，其原因在于DSShop商城系统中，没有开启SQL语句的预读功能。

vim /home/www/application/database.php

'params' => [
    \PDO::ATTR_EMULATE_PREPARES => true
]