【MySQL】数据库——主从复制和读写分离

我爱普洱茶 · 2024-9-11 19:52:48

一、MySQL读写分离

1.概念：

读写分离，基本的原理是让主数据库处理事务性增、改、删利用（insert、update、delete），而从数据库处理SELECT查询利用。数据库复制被用来把事务性利用导致的变更同步到集群中的从数据库。
2.为什么要读写分离呢？

因为数据库的“写”（写10000条数据可能要3分钟）利用是比较耗时的。
但是数据库的“读”（读10000条数据可能只要5秒钟）。
所以读写分离，解决的是：数据库的写入，影响了查询的效率。
3.什么时候要读写分离？

数据库不肯定要读写分离，如果步调利用数据库较多时，而更新少，查询多的情况下会考虑利用。利用数据库主从同步，再通过读写分离可以分担数据库压力，进步性能。
4.主从复制与读写分离

在实际的生产情况中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性照旧高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提拔数据库的并发负载本领。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。
二、MySQL主从复制原理

2.1 MySQL复制范例

MySQL的主从复制范例是基于二进制日记范例来实现的。

STATEMENT：基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。但是高并发情况下，会出现SQL语句执行顺序紊乱的错误。
ROW：基于行的复制。把改变的内容复制已往，而不是把下令在从服务器上执行一遍。
MIXED：混淆范例的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

2.2 主从复制工作过程

两个日记（主：binlog二进制日记，从：relay-log中继日记）
四个线程（主：dump线程和ack collector线程，从：IO线程和SQL线程）

主库(master)如果发生数据更新，会将写入利用记载到二进制日记(bin log)里
从库(slave)探测到主库的二进制日记发生了更新，就会开启IO线程向主库哀求二进制日记变乱
主库会为每个从库IO线程的哀求开启DUMP线程，并发送二进制日记变乱给从库
从库吸收到二进制日记变乱后会保存到本身的中继日记(relay log)中
附：在半同步模式下从库会返回确认信息给主库，主库会用ack网络线程吸收从库反馈的确认信息（5.7版本开始支持）
从库还会开启SQL线程读取中继日记里的变乱，并在本地重放（将二进制日记变乱解析成sql语句逐一执行），从而实现主库和从库的数据一致

注：

中继日记通常会位于 OS 缓存中，所以中继日记的开销很小。
复制过程有一个很紧张的限制，即复制在 Slave上是串行化的，也就是说 Master上的并行更新利用不能在 Slave上并行利用。

2.3 MySQL 读写分离原理

读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性利用，而从数据库处理select查询。数据库复制被用来主数据库上事务性利用导致的变更，同步到集群中的从数据库。
2.4 读写分离的两种实现方式

（1）基于步调代码内部实现

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产情况应用最广泛的。
优点是性能较好，因为在步调代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。
但是并不是所有的应用都得当在步调代码中实现读写分离，像一些大型复杂的Java应用，如果在步调代码中实现读写分离对代码改动就较大。
（2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端哀求后通过判断后转发到后端数据库，有以下代表性步调。
（1）MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断。
（2）Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部利用Atlas运行的mysql业务，天天承载的读写哀求数达几十亿条。支持事物以及存储过程。
（3）Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该步调由Java语言进行开发，阿里巴巴将其用于生产情况。但是它不支持事务和存储过程。
（4）Mycat。是一款流行的基于Java语言编写的数据库中间件，是一个实现了MySql协议的服务器，其核心功能是分库分表。共同数据库的主从模式还可以实现读写分离。
三、实验：实现MySQL主从复制

首先装mysqlLNMP网站架构分布式搭建部署-CSDN博客

1 搭建MySQL主从复制步骤

开启三台假造机：
主：192.168.10.16 centos7
从1：192.168.10.18 centos7
从2：192.168.10.19 centos7
服务端: 192.168.10.20 centos7 为主从服务器提供时钟源
初始化利用：

（1）开启同步，看看四台MySQL是否同一版本

###关闭和禁止防火墙开机自启功能和关闭安全模块
systemctl disable --now firewalld.service
setenforce 0
vim /etc/selinux/config
SELINUX=disabled

复制代码

（2）时间同步

我的四台服务器都为nat模式（但实在只要服务端联网即可），都要开chrony服务用于时间同步，MHA服务端配置chrony与阿里云时间服务器同步，其他三台数据库服务器server指向服务端ip

[root@localhost ~]# rpm -q ntp
ntp-4.2.6p5-29.el7.centos.2.x86_64
[root@localhost ~]# rpm -qa chrony
chrony-3.4-1.el7.x86_64
[root@localhost ~]# systemctl start chronyd #开启chronyd服务
[root@localhost ~]# vim /etc/chrony.conf #配置chrony
[root@localhost ~]# systemctl restart chronyd #配置完重启服务
[root@localhost ~]# timedatectl #查看是否时间同步
Local time: 三 2024-06-26 20:18:35 CST
Universal time: 三 2024-06-26 12:18:35 UTC
RTC time: 三 2024-06-26 12:18:34
Time zone: Asia/Shanghai (CST, +0800)
NTP enabled: yes #yes
NTP synchronized: yes #yes
RTC in local TZ: no
DST active: n/a
[root@localhost ~]# vim /etc/selinux/config

复制代码

解释原本官方源，添加本地提供时钟源的IP，
利用timedatectl查看三台服务器时间是否一致

以上方法是可以让数据库服务器不联外网，也可实现时间同步。
第二种方法就是三台机全部直接server ntp.aliyun.com iburst 全部都和阿里云时间同步。
（3）服务器修改主机名

hostnamectl set-hostname 主机名

复制代码

（3）主从服务器启动mysql

systemctl start mysqld

复制代码

（4）设置参数实现通过主机名管理这些主机（四台服务器同时修改，利用一致）

[root@mysql1 ~]# vim /etc/hosts

复制代码

（5）配置主从服务器（三台同样利用，id要改）

修改/etc/my.cnf文件，开启二进制日记和中继日记

log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-log-bin
relay-log-index=relay-log-bin.index
log-slave-updates = 1

复制代码

配置完重启服务

systemctl restart mysqld

复制代码

（6）主从服务器创建mysql和mysqlbinlog的软链接（三台同样利用）

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqladmin /usr/sbin/

复制代码

（7）主从服务器创建并授权数据库主从复制和MHA用户

主服务器（192.168.10.16）

mysql -u root -pabc123
create user 'myslave'@'192.168.10.%' identified by 'myslave123'; #创建用户
grant replication slave on *.* to 'myslave'@'192.168.10.%'; #给权限
create user 'manger'@'192.168.10.%' identified by 'manger123'; #MHA用户
grant all on *.* to 'manger'@'192.168.10.%'; #如果单纯做主从复制可忽略这两行
flush privileges; #刷新
show master status; #用于显示主服务器的状态信息，返回主服务器的二进制日志文件名和位置

复制代码

从服务器（192.168.10.18）（192.168.10.19）

mysql -u root -pabc123
create user 'myslave'@'192.168.10.%' identified by 'myslave123';
grant replication slave on *.* to 'myslave'@'192.168.10.%';
create user 'manger'@'192.168.10.%' identified by 'manger123';
grant all on *.* to 'manger'@'192.168.10.%';
flush privileges;
change master to master_host='192.168.10.16', master_port=3306, master_user='myslave', master_password='myslave123', master_log_file='mysql-bin.000001', master_log_pos=1910; ##file和pos值为主服务器查询的值
start slave;
show slave status\G #检查状态
set global read_only=1; #设置为只读

复制代码

8）验证：

主服务器历程创建库、表，并插入数据

———————————————————————主服务器——————————————————
mysql> create database cywl; //创建库
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cywl |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use cywl;
Database changed
mysql> create table xy101 (id int, name varchar(20)); //创建表
Query OK, 0 rows affected (0.00 sec)
mysql> insert into xy101 values (1, 'yuan'); //插入数据
Query OK, 1 row affected (0.01 sec)
mysql> insert into xy101 values (2, 'zhi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from xy101;
+------+------+
| id | name |
+------+------+
| 1 | yuan |
| 2 | zhi |
+------+------+
2 rows in set (0.00 sec)
mysql>

复制代码

——————————————————从服务器查看验证——————————————————
mysql> select * from cywl.xy101;
+------+------+
| id | name |
+------+------+
| 1 | yuan |
| 2 | zhi |
+------+------+
2 rows in set (0.00 sec)
mysql>

复制代码

实验：搭建半同步模式

半同步复制：主库在执行完客户端提交的事务后，只要等候一个从库返回响应给主库，才会响应给客户端
此实验基于以上主从复制实验结果为基础
三台假造机：一主（1）190.168.10.16，两从（2,3）192.168.10.18,192.168.10.19

主服务器：

（1）添加半同步模式参数，并重启mysql服务；

vim /etc/my.cnf
###添加半同步模式的参数
plugin-load=rpl_semi_sync_master=semisync_master.so #加载mysql半同步复制的插件
rpl_semi_sync_master_enabled=ON #或者设置为"1"，即开启半同步复制功能
rpl-semi-sync-master-timeout=1000 #超时时间为1000ms，即1s
systemctl restart mysqld

复制代码

vim /etc/my.cnf
————————————————————————————————————————————————————————————————
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=ON
rpl-semi-sync-master-timeout=1000
————————————————————————————————————————————————————————————————
systemctl restart mysqld

复制代码

[root@l1 ~]# systemctl restart mysqld.service

复制代码

从服务器（两个服务器配置一致）：

从服务器二号机

vim /etc/my.cnf
————————————插入————————————————————————————————
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=ON
————————————————————————————————————————————————
systemctl restart mysqld

复制代码

从服务器三号机和二号机利用一样
主服务器查看：

mysql -u root -pabc123;
show status like '%Rpl_semi%'; #在主库查询半同步状态

复制代码

参考说明：

[/code] [code]Rpl_semi_sync_master_clients #半同步复制客户端的个数
Rpl_semi_sync_master_net_avg_wait_time #平均等待时间（默认毫秒）
Rpl_semi_sync_master_net_wait_time #总共等待时间
Rpl_semi_sync_master_net_waits #等待次数
Rpl_semi_sync_master_no_times #关闭半同步复制的次数
Rpl_semi_sync_master_no_tx #表示没有成功接收slave提交的次数
Rpl_semi_sync_master_status #表示当前是异步模式还是半同步模式，on为半同步
Rpl_semi_sync_master_timefunc_failures #调用时间函数失败的次数
Rpl_semi_sync_master_tx_avg_wait_time #事物的平均传输时间
Rpl_semi_sync_master_tx_wait_time #事物的总共传输时间
Rpl_semi_sync_master_tx_waits #事物等待次数
Rpl_semi_sync_master_wait_pos_backtraverse #可以理解为"后来的先到了，而先来的还没有到的次数"
Rpl_semi_sync_master_wait_sessions #当前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_yes_tx #成功接受到slave事物回复的次数

复制代码

实验：搭建MySQL读写分离：

centos7-2（192.168.9.210）与centos7-5（192.168.9.120）
二号机（192.168.10.18），三号机（192.168.10.19）做从服务器；
centos7-8（192.168.9.150）
一号机（192.168.10.16）做主服务器；
centos7-7（192.168.9.140）
四号机（192.168.10.20）Amoeba服务器
该“案例实行：搭建MySQL读写分离”，是在“案例实行：搭建MySQL主从复制”的基础上进行的，因此此处省略MySQL主从复制的步骤
Amoeba服务器：

1.初始化利用