优化MySQL性能：主从复制与读写分离实践指南
目录
一、知识介绍
1.MySQL主从复制原理
2.MySQL读写分离原理
二、资源清单
三、案例实施
1.修改主机名
2.搭建MySQL主从复制
3.搭建MySQL读写分离
一、知识介绍
1.MySQL主从复制原理
- MySQL支持的复制类型
- 基于语句的复制
- 基于行的复制
- 混合模型复制
- 工作过程
-
主（master）数据库启动bin二进制日志，这样会有一个Dump线程，这个线程是把主（master）数据库的写入操作都会记录到这个bin的二进制文件中。
-
然后从（slave）数据库会启动一个I/O线程（监控主服务器的二进制日志的变化），这个线程主要是把主（master）数据库的bin二进制文件读取到本地，并写入到中继日志（Relay log）文件中。
-
最后从（slave）数据库其他SQL线程，把中继日志（Relay log）文件中的事件再执行一遍，更新从（slave）数据库的数据，保持主从数据一致，重新写入数据库
-
2.MySQL读写分离原理
- 分类
- 基于程序代码内部实现
- 根据select、insert进行路由分配，在目前生产环境应用广泛。
- 优点：性能较好，不需要增加额外的设备作为硬件开支
- 缺点：需要开发人员来实现，运维人员无从下手
- 基于中间代理层实现
- 位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后数据
- 代表性程序：
- MySQL-Proxy：为MySQL开源项目，通过自带的lua脚本进行SQL判断
- Amoeba：该程序由Java语言进行开发，用于生成环境，不支持事务和存储过程
- MyCAT是一款开源的分布式关系型数据库中间件，主要用于解决大规模数据存储和高效查询的需求。它支持分布式SQL查询，兼容MySQL通信协议，能够通过数据分片提高数据查询处理能力。
- 基于程序代码内部实现
- 工作过程
-
读写分离就是只在主服务器上写，只在从服务器上读
-
基本原理是让主数据库处理事务性查询（select，update，insert），而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库。
-
二、资源清单
| 主机 | 操作系统 | IP地址 | 应用 |
| Master | openEuler 24.03 | 192.168.16.142 | Mysql-server |
| Slave1 | openEuler 24.03 | 192.168.16.143 | Mysql-server |
| Slave2 | openEuler 24.03 | 192.168.16.144 | Mysql-server |
| Mycat | openEuler 24.03 | 192.168.16.145 | Mycat2 |
| 客户端 | openEuler 24.03 | 192.168.16.146 | mysql |
三、案例实施
1.修改主机名
hostnamectl set-hostname Master
hostnamectl set-hostname Slave1
hostnamectl set-hostname Slave2
hostnamectl set-hostname mycat2.搭建MySQL主从复制
1.安装MySQL数据库（Master、Slave1、Slave2）
dnf install -y tartar zxf autoinstall-mysql.tar.gzcd autoinstall-mysql
./start.sh
cd
source /etc/profile
mysql -uroot -p'临时密码'
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql -uroot -p1234562.配置Master主服务器（Master上）
-
在/etc/my.cnf中修改或增加以下内容
vi /etc/my.cnf[mysqld] log-bin=master-bin #启用二进制日志并指定其存储路径 binlog_format = MIXED #定义二进制日志的记录格式为混合模式 server-id=1 #为mysql实例分配一个唯一的服务器标识符 -
重启MySQL服务
systemctl restart mysqld ls /usr/local/mysql/data/ -
登录MySQL程序，给从服务器授权
mysql -uroot -p123456#创建用户 CREATE USER 'myslave'@'%' IDENTIFIED BY '123456'; #授权同步给所有用户 GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'%'; #修改密码 ALTER USER 'myslave'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; #刷新配置 FLUSH PRIVILEGES; #查看状态 show master status; +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 1148 | | | | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
3.配置Slave从服务器（Slave1、Slave2）
- 在/etc/my.cnf中修改或增加以下内容（注：server-id不能相同）
- Slave1
vi /etc/my.cnf[mysqld] log-bin=slave1-bin #启用二进制日志并指定其存储路径 binlog_format = MIXED #定义二进制日志的记录格式为混合模式 server-id=2 #为mysql实例分配一个唯一的服务器标识符systemctl restart mysqld ls /usr/local/mysql/data/ - Slave2
vi /etc/my.cnf[mysqld] log-bin=slave2-bin #启用二进制日志并指定其存储路径 binlog_format = MIXED #定义二进制日志的记录格式为混合模式 server-id=3 #为mysql实例分配一个唯一的服务器标识符systemctl restart mysqld ls /usr/local/mysql/data/
- Slave1
- 登录MySQL，配置并启动同步
- Slave1
mysql -uroot -p123456 #连接主节点 change master to master_host='192.168.16.142',master_user='myslave', master_password='123456',master_log_file='master-bin.000001',master_log_pos=1148; #开启同步 start slave; #查看状态 show slave status\G#查看出来显示# Slave_IO_Running: Yes# Slave_SQL_Running: Yes - Slave2
mysql -uroot -p123456 #连接主节点 change master to master_host='192.168.16.142',master_user='myslave', master_password='123456',master_log_file='master-bin.000001',master_log_pos=1148; #开启同步 start slave; #查看状态 show slave status\G#查看出来显示# Slave_IO_Running: Yes# Slave_SQL_Running: Yes
- Slave1
4.验证主从复制效果
-
在主服务器上新建db_test数据库（Master上）
create database db_test; -
在两台从服务器上分别查看数据库（Slave1、Slave2）
show databases;
3.搭建MySQL读写分离
1.安装Mycat2（Mycat上）
dnf -y install jdk-8u171-linux-x64.rpm 2.安装并配置mycat软件（Mycat上）
- 解压软件包到/usr/local/，并重命名为”mycat“
unzip mycat2-install-template-1.20.zip -d /usr/local ls /usr/local/mycat - 把依赖包复制到指定目录
cp mycat2-1.21-release-jar-with-dependencies.jar mysql-connector-java-8.0.18.jar /usr/local/mycat/lib - 为Mycat命令添加执行权限
chmod -R +x /usr/local/mycat/bin - 添加环境变量
echo 'export PATH=$PATH:/usr/local/mycat/bin' >>/etc/profile source /etc/profile - 验证安装正确性
mycat -h
3.配置Mycat读写分离
- 创建Mycat2工作所需要的账号（Master上）
mysql -uroot -p123456create user 'mycat'@'%' identified by 'pwd123'; grant all on *.* to 'mycat'@'%'; ALTER USER 'mycat'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd123'; flush privileges; - 调整Mycat配置文件（Mycat上）
vi /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":"pwd123","type":"JDBC","url":"jdbc:mysql://192.168.16.142:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8","user":"mycat","weight":0 }
- 启动Mycat服务（Master上）
mycat start ss -tnl | grep 8066 cat /usr/local/mycat/conf/users/root.user.json - Mycat配置读写分离
- 验证连接（Slave2上）
mysql -uroot -p123456 -P8066 -h192.168.16.145 - Mycat增加数据源（Slave2上）
- 增加主库master
/*+ mycat:createDataSource{ "name":"master","url":"jdbc:mysql://192.168.16.142:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"WRITE","user":"mycat","password":"pwd123"} */; - 增加从库slave1和slave2
#增加slave1 /*+ mycat:createDataSource{ "name":"slave1","url":"jdbc:mysql://192.168.16.143:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */; #增加slave2 /*+ mycat:createDataSource{ "name":"slave2","url":"jdbc:mysql://192.168.16.144:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */; - 查看数据源信息
/*+ mycat:showDataSources{} */\G#如果配置有误可使用重置：/*+ mycat:resetConfig{} */;
- 增加主库master
- 创建Mycat集群
- 创建Mycat集群的SQL语句（Slave2上）
/*! mycat:createCluster{"name":"cls01","masters":["master"],"replicas":["slave1","slave2"]} */; - 查看并修改集群配置（Mycat上）
vi /usr/local/mycat/conf/clusters/cls01.cluster.json"readBalanceType":"BALANCE_ALL_READ","balance": 1,
- 修改负载均衡给策略为轮询（Mycat上）
vi /usr/local/mycat/conf/server.jsonBalanceRoundRobin
- 重启Mycat服务（Mycat上）
mycat restart
- 创建Mycat集群的SQL语句（Slave2上）
- 验证连接（Slave2上）
4.验证Mycat读写分离
- 登录mycat集群，创建测试库（客户端上）
dnf -y install mariadb mysql -uroot -p123456 -P8066 -h192.168.16.145create database kgc; - 修改配置文件（mycat上）
cd /usr/local/mycat/conf/schemas vi kgc.schema.json #添加 "targetName":"cls01",{"customTables":{},"globalTables":{},"normalProcedures":{},"normalTables":{},"schemaName":"kgc","targetName":"cls01","shardingTables":{},"views":{} }#重启服务 mycat restart ss -tnl | grep 8066 - 登录mycat集群，创建测试表（客户端上）
mysql -uroot -p123456 -P8066 -h192.168.16.145use kgc; create table kcs74(id int); - 在master和slave1、slave2创建测试数据
- master上
mysql -uroot -p123456 use kgc; insert into kcs74 values(1); - slave1上
mysql -uroot -p123456 use kgc; insert into kcs74 values(2); - slave2上
mysql -uroot -p123456use kgc; insert into kcs74 values(3);
- master上
- 测试读操作（客户端上）
mysql -uroot -p123456 -P8066 -h192.168.16.145select * from kgc.kcs74; - 测试写操作（客户端上）
mysql -uroot -p123456 -P8066 -h192.168.16.145 use kgc; insert into kcs74 values(6); - 测试（Master、Slave1、Slave2上）
mysql -uroot -p123456use kgc; select * from kgc.kcs74;