这里写目录标题
前言
在数据规模爆炸增长的今天,数据库系统的扩展性和高可用性已成为企业架构设计的核心命题。传统单节点 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 程序,给从服务器授权
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 集群,查询 test.t1的数据
测试写操作
登录mycat集群执行写入操作
slave1上:
master上:
但在 slave1 和 slave2上查询不到,最终只有在 Master 上才能查看到这条语句内容,说明写操作在 Master 服务器上。由此验证,已经实现了 MySQL 读写分离。目前所有的写操作都全部在Master 主服务器上,用来避免数据的不同步;所有的读操作都分摊给了 Slave 从服务器,用来分担数据库压力