MySQL主从复制与读写分离

一.案例分析

        在实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，一般来说都是通过主从复制(Master-Slave)来同步数据，再通过读写分离来提升数据库并发负载能力的方案来进行部署与实施。

2案例前置知识点

2.1 MySQL 主从复制原理

      MySQL 的主从复制和 MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

(1)MySQL 支持的复制类型
  基于语句的复制。在主服务器上执行的 SQL语句，在从服务器上执行同样的语句，MySQL 默认采用基于语句的复制，效率比较高。

2.2 MySQL 读写分离原理

      简单来说，读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性查询，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库。

(1)基于程序代码内部实现

      在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支;缺点是需要开发人员来实现，运维人员无从下手。

(2)基于中间代理层实现

       MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行 SQL 判断，虽然是 MySQL 官方产品，但是 MySQL 官方并不建议将 MySQL-Proxy 用到生产环境。

       Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java 语言进行开发，阿里巴巴将其用于生产环境。它不支持事务和存储过程。经过上述简单的比较，通过程序代码实现 MySQL 读写分离自然是一个不错的选择，但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java 应用，如果在程序代码中实现读写分离对代码改动就较大。所以，像这种应用一般会考虑使用代理层来实现。本章后续案例通过Amoeba 实现。

      MyCAT 是一款开源的分布式关系型数据库中间件，主要用于解决大规模数据存储和高效查询的需求。它支持分布式 SQL, 查询，兼容 MySQL通信协议，能够通过数据分片提高数据查询处理能力。MyCAT的前端用户可以将其视为一个数据库代理，使用 MySQL, 客户端工具和命令行访问，而后端则可以通过 MySQL, 原生协议与多个 MySQL服务器通信，或者使用JDBC协议与大多数主流数据库服务器通信。

3案例环境

3.1本案例环境

主机	操作系统	IP 地址	应用
Master	openEuler 24.03	192.168.10.101	Mysql-server
Slave1	openEuler 24.03	192.168.10.102	Mysql-server
Slave2	openEuler 24.03	192.168.10.103	Mysql-server
Mycat	openEuler 24.03	192.168.10.104	Mycat2
客户端	openEuler 24.03	192.168.10.105	mysqI

二案例实施

1 搭建 MySQL 主从复制

1.1配置防火墙和 SELinux

1.2安装 MySQL 数据库

在 Master、Slave1、Slave2 服务器上安装 MySQL 数据库。本案例采用二进制安装

(1)基础环境准备

如果采用 0penEuler minimal 安装的系统，在使用前需要安装一些基础软件包工具。

安装 MySQL 依赖的软件包

创建运行 MySQL 程序的用户

和防火墙关闭 SELinux

(2).二进制安装

       二进制安装的版本采用跟上面编译安装的版本一样 MySQL 8.0.36。首先需要下载该软 件包或者提前上传，然后再解压进行配置。

[root@master] # chown-R mysql:mysql /usr/local/mysql/data

[root@master] ~# cd /usr/local/mysql/bin

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

(3).设定配置文件

将 MySQL 的可执行文件写入环境变量中。

(4).配置 systemctl 方式启动

       将 MySQL 添加成为系统服务,通过使用 systemctl 来管理。在/usr/local/mysql/support-files目录下找到mysql.server文件，将其复制到/etc/rc.d/init.d 目录下，改名为 mysqld 并赋予可执行权限。

编辑生成 mysqld.service 服务，通过 systemctl 方式来管理

1.3配置Master 主服务器

(1)在/etc/my.cnf 中修改或者增加下面内容

log-bin=/usr/local/mysql/data/mysq1-bin #启用二进制日志(Binary Log)并指定其存储路径
binlog_format =MIXED #定义二进制日志的记录格式为混合模式

server-id=1 #为mysq1实例分配一个唯一的服务器标识符

2)重启 MySQL 服务。

(3)登录 MySQL 程序，给从服务器授权

mysql>CREATE USER 'myslave’@’%’IDENTIFIED BY '123456’;

mysql>GRANT REPLICATION SLAVE ON *.* To 'myslave’@’%’;

mysql>ALTER USER ’myslave’@’%’IDENTIFIED WITH mysql native password BY ’123456’;

mysq1>FLUSH PRIVILEGES;
mysql>show master status;

1.4配置Slave 从服务器

(1)在/etc/my.cnf 中修改或者增加下面内容，这里要注意 server-id 不能相同。

server-id = 2        #增加，唯一的服务器标识符，集群内不能冲突

(2)重启 MySQL 服务。

(3)登录 MySQL，配置同步。

按主服务器结果更改下面命令中 master log file和 master log pos 参数。

[root@localhost]# mysql -u root -p

(4).启动同步。

(5)查看 Slave 状态，确保以下两个值为 YES

1.5验证主从复制效果

(1)在主、从服务器上登录 MySQL。

[root@localhost]#mysql -u root -p
mysql>show databases;

(2)在主服务器上新建数据库 db test。

(3)在两台从服务器上分别查看数据库，显示数据库相同，则主从复制成功。

2.搭建MySQL读写分离

       MyCAT是目前最流行的分布式数据库中间插件，是一个开源的分布式数据库系统，是一个实现了 MySQL协议的服务器。前端用户可以把它看作一个数据库代理，用 MySQL客户端工具和命令行访问，其后端可以用 MySQL 原生协议与多个MySQL 服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。

      MyCAT 发展到目前，已经不是一个单纯的 MySQL代理了，它的后端可以支持MySQL、SQL Server、0racle、DB2、PostgreSQL 等主流数据库，也支持 MongoDB这种新型 NoSQL方式的存储。未来，它还会支持更多类型的存储。

2.1安装 Mycat2

Mycat 需要依赖于JAVA，因此需要在读写分离代理所在的系统预先安装 JAVA环境

2.2安装并配置mycat 软件

unzip 解开后 mycat2安装包，将其移动到目录“/usr/local”，并重命名为“mycat ”.具体的命令如下:

       为简化输入，可将系统环境变量文件“/etc/profile”最后一行的内容追加 Mycat 安装目录的可执行文件所在绝对路径，更新后的“/etc/profile”文件最后一行的完整内容为:

        把依赖包 mycat2-1.21-release-jar-with-dependencies.jar 和 mysql-connector-java-8.0.18.jar,原样移动或者复制到目录"/usr/local/mycat/lib”

为 Mycat 命令添加执行权限

到目前为止，安装的步骤基本上算是完成了，任意命令行下执行指令“myca验证安装的正确性

2.3配置 Mycat 读写分离

(1)创建 Mycat2 工作所必须的账号

       启动 Mycat2服务，需要有真实的数据库服务器支撑才能运行，因此，需要在 MySQL 服务器(其它被 Mycat2 支持的数据库也如此)创建账号并给账号授权然后在 Mycat2所在的宿主系统用 MySQL,客户端用创建好的账号远程进行连接，验证账号的有效性和正确性。

      在前边的章节，我们已经做好了 MySQL数据库间的主从同步，因此创建 Mycat2 所需账号的操作只需也只能在主数据库上进行，具体的指令如下:

(2)启动 Mycat2

        与 Mycat1.x版本相比，Mycat2 的配置基本不需要手动去修改配置文件，而是可以在 Mycat2 启动之后，登录 Mycat 管理后台，用 SQL 指令或者客户端工具进行配置。在启动 Mycat2 之前，需要对原型库的数据源做相应的修改，修改的项主要是主数据库的连接信息，一个完整的修改过的原型数据源文件“/usr/local/mycat/conf/datasources/prototypeDs.datasource.json”的内容如下:被修改过的内容，以红色字显示

      因为已经对系统变量做了设置，所以在任意路径执行“mycatstart”就可以启动 Mycat2。在 Mycat2 的安装目录“/usr/local/mycat”下，存在目录“1o，打开此目录中的日志文件“wrapper.log”，可了解Mycat2 服务的运行状gS况

[root@localhost conf]# mycat start

[root@localhost conf]#ss -tnl | grep 8066

        用 MySQL 客户端工具连接 Mycat 的服务端口 TCP 8066、用户名与密码在配置文件“/usr/local/mycat/conf/users/root.user.json

       用命令行连接 Mycat 管理后台的指令为“mysql -uroot -p123456 -P8066h192.168.10.101 ”，进入用户交互界面，表明 Mycat2 运行正常，可在此交互界面进行读写分离配置。

（3)Mycat2配置读写分离

       两种配置 MySQL读写分离的方法，一种是直接在 Mycat 的配置目录“/usr/1ocal/mycat/conf”的子目录编辑相关的文本文件(Mycat1.x版本只用这种方法)另一种登录到 Mycat 交互界面，用特殊语法的 SQL 命令进行配置。本教程采用第一种方法，直接在 Mycat 的交互界面输入命令。

增加主库 master:

增加从库slavel和 slave2:

查看数据源信息:

        正确执行完上面三条 SQL语句以后，在目录“/usr/local/mycat/conf/datasources”下自动生成三个文本文件，文件名以已经执行的 SQL 语句中“name9的键值做前缀

第二步:创建 Mycat 集群

      在本案例中，集群成员包括一个主库与两个从库。根据业务场景，也可以创建多个集群，充分、有效的利用系统资源。创建 Mycat 集群的 SQL 语句如下:

查看并修改集群配置

修改负载均衡的默认策略为轮询

修改配置后需重启 mycat