MySQL主从复制和读写分离

目录

一：MySQL主从复制原理

1：MySQL支持的复制类型

(1) 基于语句的复制(SBR)

(2) 基于行的复制(RBR)

(3) 混合模式复制(MBR)

2：复制的工作过程

(1) 主服务器操作

(2) 从服务器操作

(3) 详细流程

二：MySQL读写分离原理

1：基于程序代码内部实现

2：基于中间代理层实现

（1）MySQL-Proxy.

（2）Amoeba.

（3）MyCAT.

三：案例环境

1. 服务器规划

2. 网络要求

四：主从复制案例实施

1：配置master主服务器

（1）修改MySQL配置文件(my.cnf)

（2）重启MySQL服务

（3）创建复制专用用户

（4）查看主服务器状态

2：配置slave从服务器

（1）修改MySQL配置文件(my.cnf)

（2）重启MySQL服务

（3）登录MySQL，配置同步

（4）启动同步

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

3：验证主从复制效果

（1）在主服务器创建测试数据库

（2）在从服务器验证

五：读写分离案例实施

1：安装Mycat2

2：安装并配置Mycat软件

3：配置Mycat读写分离

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

（2）启动Mycat2

（3）Mycat2配置读写分离

第一步：mycat增加数据源

第二步：创建mycat集群

4：验证Mycat读写分离

一：MySQL主从复制原理

1：MySQL支持的复制类型

(1) 基于语句的复制(SBR)

• 主服务器记录执行的SQL语句

• 从服务器重放这些SQL语句

• 优点：日志量小

• 缺点：某些函数(如UUID(), NOW())可能导致不一致

(2) 基于行的复制(RBR)

• 主服务器记录行级别的变更

• 从服务器直接应用这些行变更

• 优点：数据一致性高

• 缺点：日志量大

(3) 混合模式复制(MBR)

• 默认使用基于语句的复制

• 在特定情况下自动切换到基于行的复制

• 结合了两者的优点

2：复制的工作过程

(1) 主服务器操作

(2) 从服务器操作

(3) 详细流程

二：MySQL读写分离原理

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

1：基于程序代码内部实现

• 原理：在应用程序代码中直接实现读写分离的逻辑，通过判断SQL语句的类型（读或写），将请求路由到对应的主库（写）或从库（读）。

• 优点：

  • 实现灵活，可以根据业务需求定制路由逻辑。

  • 无需额外的中间件，减少了架构的复杂性。

• 缺点：

  • 对代码侵入性强，需要在业务逻辑中嵌入数据库路由代码。

  • 维护成本高，修改路由逻辑可能需要重新部署应用。

  • 难以实现动态扩展或负载均衡。

2：基于中间代理层实现

（1）MySQL-Proxy.

• 原理：MySQL官方提供的轻量级代理，通过Lua脚本实现路由逻辑。

• 优点：

  • 官方支持，与MySQL兼容性好。

  • 灵活性强，可以通过脚本定制路由规则。

• 缺点：

  • 性能较差，不适合高并发场景。

  • 项目已停止维护，不推荐生产环境使用。

（2）Amoeba.

• 原理：开源代理中间件，支持读写分离和分库分表。

• 优点：

  • 配置简单，支持负载均衡和高可用。

  • 对应用透明，无需修改代码。

• 缺点：

  • 社区活跃度低，后续维护不足。

  • 性能和处理能力有限。

（3）MyCAT.

• 原理：基于阿里开源的Cobar改进而来的分布式数据库中间件，支持读写分离、分库分表等。

• 优点：

  • 功能强大，支持复杂的分布式场景。

  • 社区活跃，文档丰富。

• 缺点：

  • 配置和管理较复杂。

  • 对性能有一定影响，需合理调优。

三：案例环境

1. 服务器规划

• 操作系统: openEuler

• MySQL版本: 5.7 或 8.0 (建议使用相同版本)

2. 网络要求

• 确保主从服务器之间网络互通

• 开放MySQL默认端口3306

• 关闭防火墙或设置相应规则

四：主从复制案例实施

1：配置master主服务器

（1）修改MySQL配置文件(my.cnf)

[client]
socket=/usr/local/mysql/data/mysql.sock
[mysqld]
server-id=1
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
lower_case_table_names=1
max_allowed_packet=16M

[mysqld_safe]
log-error=/usr/local/mysql/data/error.log
pid-file=/var/run/mariadb/mariadb.pid

（2）重启MySQL服务

systemctl restart mysqld

（3）创建复制专用用户

mysql -uroot -ppwd123
create user 'myslave'@'%' identified by '123456';
grant replication slave on *.* to 'myslave'@'%';
alter user 'myslave'@'%' identified with mysql_native_password by '123456';

（4）查看主服务器状态

flush privileges;

show master status;
#记录输出中的File和Position值，例如：
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 745 | test_db | mysql | |
+——————+———-+————–+——————+——————-+

2：配置slave从服务器

在Slave1，Slave2服务器上面分别执行下面步骤：

（1）修改MySQL配置文件(my.cnf)

[client]
socket=/usr/local/mysql/data/mysql.sock
[mysqld]
server-id=2
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
lower_case_table_names=1
max_allowed_packet=16M

[mysqld_safe]
log-error=/usr/local/mysql/data/error.log
pid-file=/var/run/mariadb/mariadb.pid

（2）重启MySQL服务

systemctl restart mysqld

（3）登录MySQL，配置同步

mysql -uroot -ppwd123
change master to master_host='192.168.10.201',master_user='myslave',master_password='123456',master_log_file='mysql-bin.00001',master_log_pos=157;

（4）启动同步

start slave;

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

start slave status\\G;

3：验证主从复制效果

（1）在主服务器创建测试数据库

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test_table (name) VALUES ('测试数据1'), ('测试数据2');

（2）在从服务器验证

USE test_db;
SELECT * FROM test_table;

五：读写分离案例实施

1：安装Mycat2

dnf -y install jdk-8ul71-linux-x64.rpm

2：安装并配置Mycat软件

#解压软件包
unzip mycat2-install-template-1.20.zip -d /usr/local/
ls /usr/local/mycat/

#添加路径
echo 'export PATH=$PATH:/usr/local/mycat/bin' >> /etc/profile
source /etc/profile

#移动目录
cp mycat2-1.21-release-jar-with-dependencies.jar mysql-connector-java-8.0.18.jar /usr/local/mycat/lib

#添加执行权限
chmod -R +x /usr/local/mycat/bin

#验证安装的正确性
mycat -h

3：配置Mycat读写分离

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

mysql -uroot -ppwd123
create user 'myslave'@'%' identified by 'pwd123';
grant all on *.* to 'myslave'@'%';
alter user 'myslave'@'%' identified with mysql_native_password by 'pwd123';
flush privileges;
show master status;

（2）启动Mycat2

vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json
修改："password":"pwd123",
"url":"—-192.168.10.203—–",
"user":"mycat",
mycat start
netstat -anpt | grep 8066
tail -f /usr/local/mycat/logs/wrapper.log
cat /usr/local/mycat/conf/users/root.users.json
dnf -y install mariadb
mysql -uroot -p123456 -p8066 -h192.168.10.203

（3）Mycat2配置读写分离

第一步：mycat增加数据源

mysql -uroot -p123456 -p8066 -h192.168.10.206
#增加主库master：
mysql>/*+ mycat:createDataSource{ "name":"master","url":"jdbc:mysql://192.168.10.101:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"WRITE","user":"mycat","password":"pwd123"} */;
#增加从库slave1和slave2：
#slave1：
mysql>/*+ mycat:createDataSource{ "name":"slave1","url":"jdbc:mysql://192.168.10.102:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */;

#slave2：
mysql>/*+ mycat:createDataSource{ "name":"slave2","url":"jdbc:mysql://192.168.10.103:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */;

#查看数据源信息
/*+ mycat:showDataSources{} */\\G
#如数据源配置有误可使用，重新配置：
/*+ mycat:resetConfig{} */；
ll /usr/local/mycat/conf/datasources

第二步：创建mycat集群

#创建集群
mysql>/*! mycat:createCluster{"name":"cls01","masters":["master"],"replicas":["slave1","slave2"]} */;

#查询创建
cat /usr/local/mycat/conf/clusters/cls01.cluster.json
cat server.json

#启动集群
mycat restart

4：验证Mycat读写分离

#登录mycat集群，创建测试库和测试表
mysql -uroot -p123456 -p8066 -h192.168.10.206
create database test;

#重启
mycat restart

#停止节点主从同步
slave1,slave2:
mysql -uroot -ppwd123
stop slave;

#创建测试数据
master,slave1,slave2:
use test;
insert into test.zang values('1','zhangsan','this_is_master'); #master
insert into test.zang values('2','zhangsan','this_is_slave1'); #slave1
insert into test.zang values('3','zhangsan','this_is_slave2'); #slave2

#测试读操作
mysql -uroot -p123456 -p8066 -h192.168.10.206
select * from test.zang #第一次查询
select * from test.zang #第二次查询