MySQL主从复制与读写分离

1.基于语句的复制。在主服务器上执行的SQL语句,在从最务器上执行同样的语句,MySQL默认采用基于语句的复制,效率比较高。
2.基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍。
3.混合类型的复制。默认采用基于语句的复制,一旦发现基于语句无法精确复制时,就会采用基于行的复制。

复制的工作过程

1.在每个事务更新数据完成之前,Master将这些改变记录进二进制日志。写入二进制日志完成后,Master通知存储引擎提交事务。
2.Slave将Master的Binary log 复制到其中继日志(Relay log)。首先,Slave开始一个工作线程一一I/0线程,I/0线程在Master上打开一个普通的连接,然后开始Binlogdump process。Binlog dump process从Master的二进制日志中读取事件,如果已经跟上Master,它会睡眠并等待Master产生新的事件。I/0线程将这些事件写入中继日志。
3.SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新Slave数据,使其与Master中的数据保持一致。只要该线程与I/0线程保持一致,中继日志通常会位于 0S 的缓存中,所以中继日志的开销很小。复制过程有一个很重要的限制,即复制在SIave上是串行化的,也就是说Master上的并行更新操作不能在Slave上并行操作。

MySQL读写分离原理

基本的原理是让主数据库处理事务性查询,而从数据库处理Select 查询。数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库。

目前较为常见的MySQL读写分离分为两种：

（1）基于程序代码内部实现

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

（2）基于中间代理层实现

代理一般位于客户端和服务器之间,代理服务器接到客户户端请求后通过判断
后转发到后端数据库,有两个代表性程序。

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

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

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

案例环境

主机	操作系统	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	mysql

案例需求

本案例要求通过Mycat2实现 MySQL数据库请求的读写分离。

案例实现思路

(1)安装MySQL数据库;
(2)配置MySQL主从复制;
(3)安装并配置Mycat2;
(4)客户端测试读写分离。

案例实施

搭建MySQL主从复制

在所有节点进行时间同步
[root@localhost~]# yum -y install ntpdate
[root@localhost~]# ntpdate ntpl.aliyun.com
25 Mar 22:52:12 ntpdate 2044]: adjust time server 47.96.149.233 offse
t +0.003477 sec

配置防火墙和SELinux

在每台服务器上关闭firewalld或者在防火墙上开放指定的端口和服务
[root@localhost ~]# systemctl stop firewalld
[root@localhost~]# systemctl disable firewalld
[root@localhost ^]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g
etc/selinux/config
[root@localhost~]# setenforce 0

安装MySQL数据库

基础环境准备

如果采用OpenEulerminimal安装的系统,在使用前需要安装一些基础软件包
工具。
[root@master ~]# dnf -y install gcc vim wget net-tools lrzsz tar
安装MySQL依赖的软件包
[root@master ~]# dnf install -y libaio numactl openssl ncurses-ccompat
-libs
创建运行MySQL程序的用户
[root@master ~]# useradd -M -s /sbin/nologin mysql
关闭SELinux和防火墙
[root@master^]# sed -i 's/SELINUX=enforcing/SELINUX=disabled\' /etc/
selinux/config
[root@master~]# setenforce 0
[root@master~]# systemctl disable firewalld
[root@master~]# systemctl stop firewalld

二进制安装

二进制安装的版本采用跟上面编译安装的版本一样MySQL8.00.36。首先需
要下载该软件包或者提前上传,然后再解压进行配置。
[root@master~]# tar xJf mysql-8.0.36-linux-glibc2.28-x86_64. tar. xz
[root@master~]# mv mysql-8.0.36-linux-glibc2.28-x86_64 /usr/local//my
sql
[root@master~]# mkdir /usr/local/mysql/data
[root@master~]# chown -R mysql:mysql /usr/local/mysql/data
[root@master~]# cd /usr/local/mysql/bin~]#./mysqld --initialize --user=mysql --basedir=/usr/lo
[root@master~]cal/mysql --datadir=/usr/local/mysql/data
此次初始化没有采用无密码模式,因此会生成初始随机密码,需要保存,用以后继续登录mysql数据库使用

设定配置文件

MySQL的配置文件跟上面编译安装的配置文件类似。
[root@master ^]# vim /etc/my.cnf
[client]
socket=/usr/local/mysql/data/mysql.sock
[mysqld]
socket=/usr/local/mysql/data/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
port = 3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=2048
character-set-server=utf8
default-storage-engine=INNODB
max_allowed_packet=16M
将MySQL的可执行文件写入环境变量中。
[root@master~]# echo "export PATH=$PATH:/usr/local/mysql/bin
/etc
/profile
[root@master ~]# . /etc/profile
//使配置在当前Shell中生效

配置systemctl方式启动

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

[root@master ~]# cp /usr/local/mysql/support-files/mysql.server /etc/
rc.d/init.d/mysqld
[root@master ~]# chmod +x /etc/rc.d/init.d/mysqld

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

[root@master~]# vim /lib/systemd/system/mysqld.service
[Unit]
Description=mysqld
After=network.target
[Service]
Type=forking
ExecStart=/etc/rc.d/init.d/mysqld start
ExecReload=/etc/rc. d/init.d/mysqld restart _
ExecStop=/etc/rc.d/init.d/mysqld stop
PrivateTmp=true
[Install]
WantedBy=multi-user.target
[root@master~]# systemctl daemon-reload
[root@master~]# systemctl enable mysqld
[root@master~]# systemctl start mysqld~]# netstat -tunlp grep 3306
[root@master~]# netstat -tunlp |grep 3306
[root@master~]# mysqladmin -u root password 'pwd12:3'/为root用户设
置密码

配置master主服务器

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

log-bin=/usr/local/mysql/data/mysql-bin #启用二进制日志(Binary Log并指定其存储路径
binlog_format=MIXED #定义二进制日志的记录格式为混合模式
server-id=1#为mysql实例分配一个唯一的服务器标识符

（2）重启MySQL服务

[root@master `]# systemctl restart mysqld

（3）登录MySQL程序，给从服务器授权

[root@master ^]# mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysq1_native_pas
sword BY '123456';
Query OK, O rows affected (0.01 sec)
mysql> CREATE USER ' myslave' @'%'IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@%
Query OK, O rows affected (0.01 sec)
mysql>ALTER USER 'myslave' IDENTIFIED WITH mysql_native_password BY'123456';
#MySQL8.0默认使用caching_sha2_password认证插件,将mysql_native_password替换为旧版认证插件,确保从库能兼容
mysql>FLUSH PRIVILEGES;
mysql>show master status;
其中File列显示日志名,Position列显示偏移量,这两个值在后面配置从服务器的时候需要。Slave应从该点上进行新的更新。

配置Slave从服务器

在Slavel、Slave2服务器上面分别执行下面步骤。

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

[root@localhost ^]# vim /etc/my.cnf
server-id=2//增加,唯一的服务器标识符,集群内不能冲突

（2）重启MySQL服务

[root@localhost ^]# systemctl restart mysqld

（3）登录MySQL，配置同步

按主服务器结果更改下面命令中 master_log_file和master_log_pos 参数
[root@localhost ^]# mysql -uroot -p
mysql>ALTER USER 'root'@localhost' IDENTIFIED WITH mysql_native_pas sword BY'123456';
Query OK, O rows affected (0.01 sec)
mysql> change master to master_host='192.168.10.101', masteer_user=' myslave', master_password='123456',master_log_file='mysq1-bin. 0000001', master_log_pos=157;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

（4）启动同步


mysql>start slave;
Query OK, O rows affected (0.00 sec)

（5）查看slave状态，确保以下两个值为YES

Slave_I0_Running: Yes
Slave SQL_Running: Yes

验证主从复制效果

（1）在主，从服务器上登录MySQL

[root@localhost ^]# mysql -uroot -p
mysql>show databases;
两台数据库执行结果应该相同。

（2）在主服务器上新建数据库 db_test.

mysql>create database db_test;

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

mysql> show databases;
Database
db_test
information schema
mysql
performance_schema
sys
5 rows in set (0.00 sec)

搭建MySQL读写分离

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

安装Mycat2

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

[root@localhost~]# dnf install jdk-8u171-linux-x64.rpm

安装并配置mycat软件

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

[root@localhost ~]# unzip mycat2-install-template-1.20.zipd /usr/lo
cal/
[root@localhost ~]# ls /usr/local/mycat/
bin conf lib logs

为简化输入,可将系统环境变量文件"/etc/profile"最后一行的内容追加Mycat安装目录的可执行文件所在绝对路径,更新后的"/etc/profile"文件最后一行的完整内容为:
[root@localhost~]# echo'export PATH=$PATH:/usr/local/mycat/bin
etc/profile
[root@localhost~]# source /etc/profile

把依赖包mycat2-1.21-release-jar-with-dependencies.jar和mysql-connector-java-8.0.18.jar,原样移动或者复制到目录"/usr/local/mycat/lib"
[root@localhost~]# cp mycat2-1.21-release-jar-with-dependenacies. jar mysql-connector-java-8.0.18. jar /usr/local/mycat/lib

为Mycat命令添加执行权限
[root@localhost~]# chmod -R +x /usr/local/mycat/bin

到目前为止,安装的步骤基本上算是完成了,任意命令行下执行指令"mycat-h",验证安装的正确性
[root@localhost ~]# mycat -h
Usage:/usr/local/mycat/bin/mycat{ console | start | stop | restart | status | dump }

配置Mycat读写分离

（1）创建Mycat2工作所必须的账号

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

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

mysql>create user 'mycat'@'%'identified by 'pwd123';
Query OK, O rows affected (0.04 sec)
A
mysql>grantall on *.* to 'mycat'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'mycat'@' IDENTIFIED WITH mysql_nativepassword BY'pwd123';
Query OK, O rows affected (0.01 sec)

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

（2）启动Mycat2

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

"password":"pwd123

"url":"jdbc:mysql://192.168. 10.101:3306/mysql?serverTimezone=Asia
/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

user":"mycat"

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

[root@localhost conf]# mycat start
Starting mycat2...

[root@localhost_conf]# ss -tnl | grep 8066
查看端口号

[root@localhost~]# tail -f /usr/local/mycat/logs/wrapper.log

用MySQL客户端工具连接Mycat的服务端口TCP 8066、用户名与密码在配置文件"/usr/local/mycat/conf/users/ root.user.json
[root@localhost ~]# cat /usr/local/mycat/conf/users/rootuser. json
{
"dialect":"mysql",
"password": "123456",
"transaction Type":"proxy",
"username":"root“
}

用命令行连接Mycat管理后台的指令为"mysql-uroot-p123456-P8066 -h192.168.10.101",进入用户交互界面,表明Mycat2运行正常,可在此交互界面进行读写分离配置。
[root@localhost~]# mysql -uroot -p123456 -P8066 -h192.168.10.101

（3）Mycat2配置读写分离

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

第一步：Mycat2增加数据源

需要正确输入的数据主要包括:MySQL主从数据库的IP地址、数据库库名(chema)、数据库账号、数据库密码(生产数据库请使用复复杂密码)、实例类型(READ、WRITE或READ WRITE)。下边是添加一个主库源和两个从库源的具体指令：

[root@localhost~]mysql -uroot -p123456 -P8066 -h192.168.10.101

增加主库master：

mysql>/*+ mycat:createDataSource{"name":"master","url": " jdbc:mysql:/
/192.168.10.101:3306/?useSSL=false&characterEncoding=UTF-8&use,JDBCCom
pliantTimezoneShift=true","instanceType": "WRITE", "user": "mycat",
password":"pwd123"} */;
Query OK, O rows affected (0.01 sec)

增加从库slave1和slave2：

mysql>/*+ mycat:createDataSource{"name":"slavel","url":"jdbc:mysql:/
/192.168.10.102:3306/?useSSL=false&characterEncoding=UTF8&use JDBCCom
pliantTimezoneShift=true","instanceType": "READ", "user": "mycat", "password":"pwd123"} */;
Query OK, O rows affected (0.01 sec)

mysql>/*+ mycat:createDataSource {"name":"slave2", "ur1":"jdbc:mysql:/
/192.168.10.103:3306/?useSSL=false&characterEncoding=UTF-8&use,JDBCCom
pliantTimezoneShift=true","instanceType":"READ", "user":"mycat", "passw
ord":"pwd123"} */;
Query OK, 0 rows affected (0.01 sec)

查看数据源信息：

查看数据源信息:
mysql> /*+ mycat:showDataSources{} */\G

如数据源配置有误可使用"/*+ mycat:resetConfig{}*/;"进行重置

正确执行完上面三条SQL语句以后,在目录"/usr/local/mydat/conf/data

sources"下自动生成三个文本文件,文件名以已经执行的SQ语句中"name"的键值做前缀

[root@localhost ^]# 11 /usr/local/mycat/conf/datasources
总计16

第二步：创建Mycat集群

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

mysql>/*! mycat:createCluster{"name":"cls01", "masters": ["master"],"re
plicas":["slavel", "slave2"]} */;
Query OK, O rows affected (0.01 sec)

上述SQL语句执行完以后,将在目录"/usr/local/mycat/conf/clusters
自动生成Mycat集群配置文件"cls0l.cluster.json'

查看并修改集群配置

[root@localhost~]# cat /usr/local/mycat/conf/clusters/cls01. cluster. json

"readBalanceType":"BALANCE_ALL_READ",#请修改,所有显式读请求路由到从节点(事务内除外)

"balance":1, #没有请添加,所有从节点按固定顺序依次接收读请求

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

[root@localhost conf]# cat server. json
"loadBalance":{
"defaultLoadBalance ":"BalanceRoundRobin",
"loadBalances":[]

}

修改配置后需重启mycat

[root@localhost conf]# mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...

验证Mycat读写分离

（1）登录mycat集群，创建测试库和测试表

[root@localhost ^]# mysql -uroot -p123456 -P8066 -h1922. 168.10.101
mysql>create database test;
Query OK, O rows affected (0.47 sec)

注意:
创建完库以后在mycat的schemas 目录下会有一个以该库明明白的文件,要修
改这个文件标注上mycat的集群,然后重启mycat。
[root@localhost schemas]# pwd
/usr/local/mycat/conf/schemas
[root@localhost schemas]# 11
总计8
-rw-r--r-- 1 root 143 3月27日15:09 test. schema. json
[root@localhost schemas]# cat test. schema. json
"targetName":"cls01",
#重启
[root@localhost schemas]# mycat restart
mysql> create table test.zang(id int(10), name varchar(l0), address yarchar (20));
Query OK, O rows affected (0.47 sec)
创建库和表的操作会路由到master执行,并被同步到slave节点

（2）停止slave1和slave2的主从同步

Slavel:
[root@localhost ~]# mysql -uroot -p
mysql> stop slave;
Query OK, 0 rows affected, I warning (0.01 sec)
Slave2:
[root@localhost ~]# mysql -uroot -p
mysql> stop slave;
Query OK, 0 rows affected, I warning (0.01 sec)

（3）在master和slave1和slave2创建测试数据

Master上操作
mysql> use test;
Database changed
mysql> insert into test.zang values('l', 'zhang', this_is_maaster')
Query OK, I row affected (0.01 sec)

Slavel上操作
mysql> use test;
Database changed
mysql>insert into test.zang values('2', 'zhang'
this_is_slaveľ);
Query OK, I row affected (0.01 sec)

Slave2上操作
mysql> use test;
Database changed
mysql>insert into test.zang values('3', 'zhang','this_is_s)lave2');
Query OK, I row affected (0.01 sec)

（4）测试读操作

登录mycat集群,查询test.zang的数据
[root@localhost ^]# mysql -uroot -p123456 -P8066 -h192168.10.101
mysql> select * from test.zang;#第一次查询

mysql> select * from test.zang;#第二次查询

mysql> select * from test.zang;#第三次查询

（5）测试写操作

登录mycat集群执行写入操作
[root@localhost ~]# mysql -uroot -p123456 -P8066 -h192. 168.10.101
MySQL [(none)]>insert into zang values(‘4’,'zhang','write_test');
Query OK, I row affected (0.08 sec)
但在slavel和slave2上查询不到,最终只有在 Master上z才能查看到这条语句内容,说明写操作在Master服务器上。由此验证,已经实现了 MySQL读写分离。目前所有的写操作都全部在Master主服务器上,用来避免数据的不同步;所有的读操作都分摊给了Slave从服务器,用来分担数据库压力。