MySQL主从复制与读写分离

最新推荐文章于 2025-06-11 10:38:01 发布

天空之城夢主

最新推荐文章于 2025-06-11 10:38:01 发布

阅读量712

点赞数 26

文章标签： mysql android adb

本文链接：https://blog.csdn.net/tkzcmz/article/details/148477410

版权

前言

在数据规模爆炸增长的今天，数据库系统的扩展性和高可用性已成为企业架构设计的核心命题。传统单节点 MySQL架构在面对高并发读写、数据容灾及性能瓶颈时显得力不从心，而 MySQL 主从复制通过实时数据同步机制，为主库分担读压力并提供灾备支持;MyCat中间件则基于主从架构实现透明化读写分离，进一步释放系统资源、提升吞吐量。本课程将围绕这两大核心技术展开，通过原理讲解、配置实战与案例剖析，帮助学员系统掌握数据库高可用架构设计思路，实现从基础配置到性能调优的全链路技能提升。

MySQL主从复制原理

主从复制是数据库管理中的一种技术，它允许将一个数据库服务器(主服务器)上的数据复制到一个或多个数据库服务器(从服务器)。在MySQL中，这通常是通过异步复制来实现的，意味着从服务器不需要实时连接到主服务器来更新数据。从服务器可以复制主服务器中的所有数据库、特定数据库或特定表。

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

SQL支持的复制类型

①：基于语句的复制。在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句，MySQL 默认采用基于语句的复制，效率比较高

②：基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍

③：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制

复制的工作过程

MySQL 复制的工作过程如图

在这里插入图片描述

①：在每个事务更新数据完成之前，Master 将这些改变记录进二进制日志。写入二进制日志完成后，Master 通知存储引擎提交事务

②：Slave 将 Master 的 Binary log 复制到其中继日志(Relaylog)。首先，Slave 开始一个工作线程–1/0 线程,1/0 线程在 Master 上打开一个普通的连接，然后开始 Binlog dump process。Binlog dump process 从 Master 的二进制日志中读取事件，如果已经跟上 Master，它会睡眠并等待 Master 产生新的事件。1/0 线程将这些事件写入中继日志

③：SQL slave thread(SQL 从线程)处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新 Slave 数据，使其与 Master 中的数据保持一致。只要该线程与_1/0 线程保持一致，中继日志通常会位于0S 的缓存中，所以中继日志的开销很小。复制过程有一个很重要的限制，即复制在 S1ave 上是串行化的，也就是说 Maser 上的并行更新操作不能在 Slave 上并行操作

MySQL读写分离原理

在这里插入图片描述

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

基于程序代码内部实现

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

基于中间代理层实现

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

①：MySQL-Proxy。MySQL-Proxy 为MySQL 开源项目，通过其自带的 lua 脚本进行 SQL 判断，虽然是 MySQL 官方产品，但是 MySQL 官方并不建议将 MySQL-Proxy 用到生产环境。
②：Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java 语言进行开发，阿里巴巴将其用于生产环境。它不支持事务和存储过程。经过上述简单的比较，通过程序代码实现 MySQL 读写分离自然是一个不错的选择，但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java 应用，如果在程序代码中实现读写分离对代码改动就较大。所以，像这种应用一般会考虑使用代理层来实现。本章后续案例通过Amoeba 实现。
③：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

案例实现思路

安装 MySQL 数据库;
配置 MySQL 主从复制;
安装并配置 Mycat2;
客户端测试读写分离

案例实施

搭建 MySQL 主从复制

[root@bogon ~]# yum -y install ntpdate
OS         8.1 kB/s | 2.1 kB     00:00    
everything 9.0 kB/s | 2.2 kB     00:00    
EPOL       3.5 kB/s | 2.3 kB     00:00

[root@bogon ~]# ntpdate ntpl.aliyun.com
22 Apr 17:41:18 ntpdate[11984]: no servers can be used, exiting

配置防火墙和 SELinux

在每台服务器上关闭 firewalld 或者在防火墙上开放指定的端口和服务

[root@bogon ~]# systemctl stop firewalld
[root@bogon ~]# systemctl disable firewall
[root@bogon ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config 
[root@bogon ~]# setenforce 0

安装 MySQL数据库

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

基础环境准备

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

[root@bogon ~]# dnf -y install gcc vim net-tools lrzsz tar

安装 MySQL 依赖的软件包

[root@bogon ~]# dnf install -y libaio numactl openssl ncurses-compat-libs

创建运行 MySQL 程序的用户

[root@bogon ~]# useradd -M -s /sbin/nologin mysql

二进制安装

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

[root@bogon ~]# tar xf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz 
[root@bogon ~]# mv mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz /usr/local/mysql

[root@bogon ~]# mkdir /usr/local/mysql/data
[root@bogon ~]# chown -R mysql:mysql /usr/local/mysql/
[root@bogon ~]# cd /usr/local/mysql/bin
[root@bogon bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/

设定配置文件

[root@bogon ~]# 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@bogon ~]# echo "export PATH=$PATH:/usr/local/mysql/bin">> /etc/profile
[root@bogon ~]# . /etc/profile

配置 systemctl 方式启动

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

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

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

[root@bogon ~]# 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@bogon ~]# systemctl daemon-reload
[root@bogon ~]# systemctl enable mysqld
[root@bogon ~]# systemctl start mysqld
[root@bogon ~]# netstat -tunlp |grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      11562/mysqld

用户设置密码

[root@bogon ~]# mysqladmin -u root password 'pwd123

配置Master主服务器

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

[root@bogon ~]# vim /etc/my.cnf
[client]
socket=/usr/local/mysql/data/mysql.sock

[mysqld]
log-bin=/usr/local/mysql/data/mysql-bin
binlog-format = MIXED
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
max_allowed_packet=16M

重启 MySQL 服务

[root@bogon ~]# systemctl restart mysqld

mysql> create user 'mysqlave'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'mysqlave'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'mysqlave'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

#MySQL 8.0 默认使用 caching sha2 password 认证插件,将mysql native password 替换为旧版认证插件，确保从库能兼容

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1151 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

其中 File 列显示日志名，Position 列显示偏移量，这两个值在后面配置从服务器的时候需要。Slave 应从该点上进行新的更新

配置Slave 从服务器

[root@bogon ~]# vim /etc/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
max_allowed_packet=16M

重启 MySQL 服务

[root@bogon ~]# systemctl restart mysqld

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='192.168.10.101',master_user='mysqlave', master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1151;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

启动同步

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.10.101
                  Master_User: mysqlave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1151
               Relay_Log_File: bogon-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

验证主从复制效果

在主、从服务器上登录 MySQL

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

在主服务器上新建数据库 db_test

mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_test            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

在主服务器上新建数据库aaa,创建表t1，并插入数据

在两台从服务器上分别查看数据库、表、以及数据信息；显示同，则主从复制成功
请添加图片描述

搭建MySQL读写分离

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

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

不过，无论是哪种存储方式，在最终用户看，MyCAT里都是一个传统的数据库表，支持标准的 SQL语句进行数据的操作，这样一来，对前端业务系统来说，可以大幅降低开发难度，提升开发速度。

安装Mycat2

Mycat 需要依赖于JAVA，因此需要在读写分离代理所在的系统预先安装 JAVA环境
请添加图片描述

安装并配置mycat软件

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

[root@localhost ~]#ls /usr/local/mycat/
bin conf lib logs

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

[root@localhost ~]#ln -s /usr/local/mycat/bin/* /usr/local/bin

把依赖包 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-dependencies.jar mysql-connector-java.8.0.18.iar /usr/local/mycat/lib

为 Mycat 命令添加执行权限

请添加图片描述

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

[root@localhost ~]# mycat -h
Usage:/usr/local/bin/mycat { console | startstop | restart | status | dump }

配置Mycat读写分离

创建mycat2的账号

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

启动mycat2

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

请添加图片描述

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

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

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

Mycat2配置读写分离

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

Mycat增加数据源

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

查看数据源信息：

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

请添加图片描述

创建Mtcat集群

请添加图片描述

上述 SQL语句执行完以后，将在目录“/usr/local/mycat/conf/clusters自动生成 Mycat 集群配置文件“cls01.cluster.json
请添加图片描述

查看并修改集群配置

请添加图片描述

修改配置后重启mycat

请添加图片描述

验证Mycat读写分离
登录mycat集群，创建测试库和测试表

请添加图片描述

查看主(master)和从(slave1)同步

请添加图片描述

停止slave1和slave2的主从同步

slave1：

请添加图片描述

slave2：

请添加图片描述

在slave1、slave2创建测试数据

slave1：
请添加图片描述

slave2：
请添加图片描述

测试读操作

请添加图片描述

测试写操作

登录mycat集群执行写入操作

slave1上：

请添加图片描述

master上：

请添加图片描述

但在 slave1 和 slave2上查询不到,最终只有在 Master 上才能查看到这条语句内容，说明写操作在 Master 服务器上。由此验证，已经实现了 MySQL 读写分离。目前所有的写操作都全部在Master 主服务器上，用来避免数据的不同步;所有的读操作都分摊给了 Slave 从服务器，用来分担数据库压力