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

我爱普洱茶 发表于 2024-9-11 19:52:48

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

一、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 主从复制工作过程https://img-blog.csdnimg.cn/direct/e47111ff0a5d4377838f3ee25e149acb.png

两个日记（主：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是否同一版本https://img-blog.csdnimg.cn/direct/50cd3a4f2c704d08b29b455c20db9ef7.png

###关闭和禁止防火墙开机自启功能和关闭安全模块
systemctl disable --now firewalld.service
setenforce 0
vim /etc/selinux/config
SELINUX=disabled
https://img-blog.csdnimg.cn/direct/6bb7a07989db4d5aa59746a74b264059.png
（2）时间同步

我的四台服务器都为nat模式（但实在只要服务端联网即可），都要开chrony服务用于时间同步，MHA服务端配置chrony与阿里云时间服务器同步，其他三台数据库服务器server指向服务端ip
# rpm -q ntp
ntp-4.2.6p5-29.el7.centos.2.x86_64
# rpm -qa chrony
chrony-3.4-1.el7.x86_64
# systemctl start chronyd #开启chronyd服务
# vim /etc/chrony.conf    #配置chrony
# systemctl restart chronyd #配置完重启服务
# 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
# vim /etc/selinux/config
解释原本官方源，添加本地提供时钟源的IP，
利用timedatectl查看三台服务器时间是否一致
https://img-blog.csdnimg.cn/direct/2eef565cc15a4661993dd8c5c74ab5d9.png
https://img-blog.csdnimg.cn/direct/426b8461bbb6490faa2d338f849f8185.png
以上方法是可以让数据库服务器不联外网，也可实现时间同步。
第二种方法就是三台机全部直接server ntp.aliyun.com iburst 全部都和阿里云时间同步。
（3）服务器修改主机名

hostnamectl set-hostname 主机名
（3）主从服务器启动mysql

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

# vim /etc/hosts
https://img-blog.csdnimg.cn/direct/3b13a625d05f4bdfa99730f3568a097e.png
（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 https://img-blog.csdnimg.cn/direct/09a4f6d3a28b49bfb79af544940feda5.png
配置完重启服务
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;#用于显示主服务器的状态信息，返回主服务器的二进制日志文件名和位置 https://img-blog.csdnimg.cn/direct/012fa314c8364b888ddd7b5e0d75ebfb.png从服务器（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; #设置为只读
https://img-blog.csdnimg.cn/direct/7800c95dece44b47886de4cb520ec1de.png
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 https://img-blog.csdnimg.cn/direct/4566418978f643668508a0465252cd4f.png
# 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
https://img-blog.csdnimg.cn/direct/8f043c6d5835442ea166216c7db9d739.png

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

mysql-u root -pabc123;
show status like '%Rpl_semi%'; #在主库查询半同步状态 https://img-blog.csdnimg.cn/direct/27e137dedcd6474abd5fd015847be795.png
参考说明：
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.初始化利用

# systemctl disable --now firewalld.service
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
# setenforce 0
# vim /etc/selinux/config
# https://img-blog.csdnimg.cn/direct/33a3879b9bf543bfbca6b4b8b7d339dc.png
# mount /dev/sr0 /mnt
mount: /dev/sr0 写保护，将以只读方式挂载
# df
文件系统                1K-块已用可用已用% 挂载点
devtmpfs                914412    0 914412 0% /dev
tmpfs                   931496    0 931496 0% /dev/shm
tmpfs                   931496 10448 921048 2% /run
tmpfs                   931496    0 931496 0% /sys/fs/cgroup
/dev/mapper/centos-root 38770180 4576740 34193440 12% /
/dev/sda1             1038336191104 847232 19% /boot
tmpfs                   186300    12 186288 1% /run/user/42
tmpfs                   186300    0 186300 0% /run/user/0
/dev/sr0             4635056 4635056    0100% /mnt
# 2.安装 Java 情况

rpm -qa | grep jdk
yum remove java*
#因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。 https://img-blog.csdnimg.cn/direct/49f2525d28b647b19b01601c11a5a5d6.png
# cd /opt/
# ls    #传入amoeba包
amoeba-mysql-binary-2.2.0.tar.gzjdk-6u14-linux-x64.binrh

chmod +x jdk-6u14-linux-x64.bin

./jdk-6u14-linux-x64.bin
//按yes，按enter

mv jdk1.6.0_14/ /usr/local
ls

mkdir /usr/local/amoeba

tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
cd /usr/local https://img-blog.csdnimg.cn/direct/baeb21a9c6264e45865aa59c2ffcf90d.png
vim /etc/profile.d/jdk1.6.sh
export JAVA_HOME=/usr/local/jdk1.6.0_14
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$AMOEBA_HOME/bin
#添加配置
source /etc/profile
java -version
cd /usr/local/amoeba/bin
./amoeba#如显示amoeba start|stop说明安装成功 https://img-blog.csdnimg.cn/direct/c5624e4c2075475d8f6fe70a754f1c41.png
https://img-blog.csdnimg.cn/direct/fbbddb3872484c2f9d023edf9a4fd399.png
cd /usr/local/amoeba/conf/
cp amoeba.xml{,.bak}#修改前先进行备份
vim amoeba.xml#修改amoeba配置文件

<property name="user">lin</property> ##30行修改用户名

<property name="password">lin123</property>##32行修改密码
##115行默认服务器连接到master组    #该用户名及密码为客户端连接amoeba的用户名及密码
<property name="defaultPool">master</property>
##117行去掉注释并修改
<property name="writePool">master</property>
<property name="readPool">slaves</property> cd /usr/local/amoeba/conf/
cp amoeba.xml{,.bak}

vim amoeba.xml https://img-blog.csdnimg.cn/direct/5996fe5592654eed93e9ae05a5fd61cf.png
https://img-blog.csdnimg.cn/direct/5bb222894b1b4f20afe832d2fe8d6d55.png
主从服务器创建用户
主服务器：
mysql -uroot -pabc123

create user 'amoeba'@'192.168.18.%' identified by 'amoeba123';

grant all on *.* to 'amoeba'@'192.168.18.%'; https://img-blog.csdnimg.cn/direct/2ca71ff0376e4ac88f87582891d5116f.png
两个从服务器
mysql -uroot -pabc123

grant all on *.* to 'amoeba'@'192.168.18.%' identified by 'amoeba123'; https://img-blog.csdnimg.cn/direct/c7f9d4530cfa44149fd2ca2872c460ee.png
Amoeba服务器
cd /usr/local/amoeba/conf/
cp dbServers.xml{,.bak}
vim dbServers.xml
注释23行
##设置为amoeba用户
<property name="user">amoeba</property>
##删除30行注释，28行注释添加完整，29行密码修改
<property name="password">amoeba123</property>
##44行47行分别设置主服务器名字和地址          ##此处用户名和密码为amoeba与主从服务器连接用
<dbServer name="master"parent="abstractServer">
<property name="ipAddress">192.168.9.150</property>
##51行54行分别设置从服务器名字和地址
<dbServer name="slave1"parent="abstractServer">
<property name="ipAddress">192.168.9.210</property>
##56行下额外添加一个从服务器配置
      <dbServer name="slave2"parent="abstractServer">
               <factoryConfig>
                     
                     <property name="ipAddress">192.168.9.120</property>
            </factoryConfig>
      </dbServer>
##64行设置服务器池名称
<dbServer name="slaves" virtual="true">
##70行定义两个服务器地址
<property name="poolNames">slave1,slave2</property> cd /usr/local/amoeba/conf/

cp dbServers.xml{,.bak}

vim dbServers.xml https://img-blog.csdnimg.cn/direct/f2dc88b056a24b69a91bc51392138110.png
https://img-blog.csdnimg.cn/direct/f1e22954bc954c22b80766e56894a5fc.png
https://img-blog.csdnimg.cn/direct/e9cefc7233e14e0393d5e01e5b2214ef.png
https://img-blog.csdnimg.cn/direct/fa6684715c8f4272a70e90e371542016.png
https://img-blog.csdnimg.cn/direct/965f0875a7b148aa913aa9dd955d6b7b.png
https://img-blog.csdnimg.cn/direct/7e7aa2f7d3fe4ce5bb45da1028d9aa4d.png
https://img-blog.csdnimg.cn/direct/d33c11185de54ad5b03e3bd9b8f53739.png
cd /usr/local/amoeba/bin

./amoeba start & //后台运行amoeba

netstat -lntp | grep 8066 https://img-blog.csdnimg.cn/direct/31489246652042afb38ce88d9b7ee5f8.png
# netstat -lntp | grep 8066
tcp6    0    0 :::8066             :::*                LISTEN    66180/java       测试

利用navicat
https://img-blog.csdnimg.cn/direct/febcf3fae6bb4644a7184a2f1deb2c84.pnghttps://img-blog.csdnimg.cn/direct/3b42735f7fe64ea5af05986d8e3e78cd.pnghttps://img-blog.csdnimg.cn/direct/0ee295dcaa8f49998bd75cacbdeda9bf.pnghttps://img-blog.csdnimg.cn/direct/a147044431f848ae98763093b91b64de.pnghttps://img-blog.csdnimg.cn/direct/339aa8acc2f545cab6215440287a1736.pnghttps://img-blog.csdnimg.cn/direct/5c084d00184644a5b8b9463184daa8fe.png
关闭从库进行验证
测试1
stop slave;#关闭两个从库 https://img-blog.csdnimg.cn/direct/948aef8d90ae4f57b0c216bd11a3b808.png
https://img-blog.csdnimg.cn/direct/b4e82d28f8ad4734b9acc13c75fe21d5.pnghttps://img-blog.csdnimg.cn/direct/cfc1f9704aef4a0597b40a1a6abad9f6.pnghttps://img-blog.csdnimg.cn/direct/520ff29004874d7c869574cd615cd7ed.png
测试2https://img-blog.csdnimg.cn/direct/4eb64cc3a0f8406eada45a088cd97df7.pnghttps://img-blog.csdnimg.cn/direct/a9038d9d0eae42aab561fe702e6e8f85.pnghttps://img-blog.csdnimg.cn/direct/0fb292b282a5440ba3eb435edae4d417.png
当两台从服务器重新开启时https://img-blog.csdnimg.cn/direct/6f89d52c3a7746a99d65ed67cc140da5.png
https://img-blog.csdnimg.cn/direct/d75a2b947c3f4c9895e5eefd398e1277.png

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

页: [1]

IT评测·应用市场-qidao123.com技术社区's Archiver

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