mysql数据库主从复制和读写分离

最新推荐文章于 2024-09-09 08:37:04 发布

你好，我是阿西鞭

最新推荐文章于 2024-09-09 08:37:04 发布

阅读量130

点赞数

分类专栏： Web服务

本文链接：https://blog.csdn.net/weixin_56665913/article/details/119064095

版权

Web服务专栏收录该内容

23 篇文章 0 订阅

订阅专栏

一、主从复制概述：

在企业应用中，成熟的业务通常数据量都比较大。单台MySQL在安全性、高可用性（冗余备份）和高并发方面都无法满足实际的需求配置多台主从数据库服务器以实现读写分离。

二、主从复制原理：

1、MySQL的复制类型：
①基于语句的复制(STATEMENT,MySQL默认类型)
②基于行的复制(ROW)
③混合类型的复制(MIXED)
2、主从复制默认使用的机制：
①全同步②半同步③异步(默认)
3、主从复制原理：
原理核心：2个日志文件(二进制和中继)、3个线程
3个线程：
①master上的dump线程
②slave上的IO线程和SQL线程
目的：slave上的中继日志会趋近实时同步master上的二进制日志

过程：dump线程会监控二进制日志的更新，如果有更新则会通知slave上的IO线程——》master上申请同步二进制日志的更新内容
dump线程会把同步的sql日志内容给slave服务器，slave的IO线程就会写入自己的中继日志
slave sql线程把日志中的更新语句同步执行到数据库内部，以达到和master数据库趋近一致

master	slave
二进制日志	中继日志
线程dump用于监听二进制日志binlog同时通知slave的IO线程	线程IO负责向master请求更新日志内容，并将更新的内容谈不到中继日志relaylog
–	–
	线程sql将本地的中继日志relaylog中的更新（对本地的relaylog做监听），内容同步到自己的数据库内

三、主从复制实验

准备5台虚拟机
mysql-master 192.168.153.200
mysql-slave1 192.168.153.215
mysql-slave2 192.168.153.220
客户端client（测试） 192.168.153.225
Ameoba 192.168.153.100

1、先配置好mysql5.7基础配置

2、主从服务器时间同步

主服务器：
[root@master ~]# yum install -y ntp
[root@master ~]# yum install -y ntpdate
[root@master ~]# systemctl start ntpd    #开启ntpd
[root@master ~]# ntpdate ntp.aliyun.com  #与阿里云同步
关闭防火墙等等
systemctl stop firewalld.service
systemctl disable firewalld.service
setenforce 0
[root@master ~]# crontab -e
*/10 * * * * /usr/sbin/ntpdate ntp.aliyun.com
[root@localhost mysql]# vim /etc/resolv.conf
nameserver 114.114.114.114

从服务器：
[root@master ~]# yum install -y ntp
[root@master ~]# yum install -y ntpdate
[root@master ~]# systemctl start ntpd    #开启ntpd
[root@master ~]# ntpdate ntp.aliyun.com  #与阿里云同步
关闭防火墙等等
systemctl stop firewalld.service
systemctl disable firewalld.service
setenforce 0
[root@master ~]# crontab -e
*/10 * * * * /usr/sbin/ntpdate ntp.aliyun.com
[root@localhost mysql]# vim /etc/resolv.conf
nameserver 114.114.114.114

3、配置主从同步

主服务器：
[root@master mysql]# vim /etc/my.cnf
在[mysqld]模块下添加
 20 log_bin=master-bin #开启二进制日志文件（之后生成的日志名为master-bin）
 21 log_slave_updates=true    #开启从服务器日志同步
 22 server_id=11            #主服务器id为11（不可重复）
保存退出,重启服务
[root@master mysql]# systemctl stop mysqld
[root@master mysql]# systemctl start mysqld

两个从服务器配置：
[root@localhost ~]# vim /etc/my.cnf
log_bin=master-bin          #开启二进制日志文件
server_id = 22/23           #设置server1id为22,server2id为23(不可重复)    
relay-log=relay-log-bin      #从服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index #定义relay-log的位置和名称（index索引）
保存退出，重启服务
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld

4、配置规则

主服务器：
[root@master mysql]# mysql -uroot -p123
mysql> grant replication slave on *.* to 'myslave'@'192.168.153.%' identified by '123';  #给从服务器提权，允许使用slave的身份复制master的所有数据库的所有表，并指定密码为123
mysql> flush privileges;   #刷新权限表
mysql> show master status;  查看master数据库状态（下图看见日志文件定位为1504，从服务器需要定位到此处进行复制）
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      1504 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

两台从服务器：
mysql> change master to master_host='192.168.153.200',master_user='myslave',master_passwo
rd='123',master_log_file='master-bin.000001',master_log_pos=1504;  #添加指向主服务器

mysql> start slave;   #开启从服务器同步
mysql> show slave status\G  #查看slave状态，图片如下

在这里插入图片描述

5、测试

在主服务器上创建一个数据库和表，测试是否同步
在这里插入图片描述

四、小结

MySQL主从复制与读写分离是密切相关的，上面介绍了MysQL的主从复制(同步)，下面会介绍MySQL读写分离。
二进制日志中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其他额外信息，但是它不记录SELECT、SHOW等不修改数据的SQL语句。二进制日志主要用于数据库恢复和主从复制及审计操作。

如果同步失败可使用以下方法尝试解决
先stop slave;
①slave数据库中: SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;   #忽略当前错误执行下一步同步 
START SLAVE;
②slave数据库中: CHANGE MASTER TO MASTER LOG_FILE='mysql-bin.000001' ,MASTER_LOG_POS=0;

五、读写分离原理

1、只在主服务器.上写，只在从服务器上读
2、主数据库处理事务性查询，从数据库处理SELECT查询
3、数据库复制用于将事务性查询的变更同步到集群中的从数据库
4、读写分离方案
1)、基于程序代码内部实现
2)、基于中间代理层实现(mysql-proxy，Amoeba)

1、读写分离的优势

因为数据库的“写”(写10000条数据可能要3分钟)操作是比较耗时的。
但是数据库的“读”(读10000条数据可能只要5秒钟)。
所以读写分离，解决的是，数据库的写入，影响了查询的效率

2、读写分离适合哪些场景使用

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询(select)多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

3、读写分离的实现方式

①基于程序代码内部实现
在代码中根据select、 insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支;缺点是需要开发人员来实现，运维人员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离，像一-些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

②基于中间代理层实现
代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。
(1) MySQL-Proxy。 MySQL-Proxy 为MySQL开源项目，通过其自带的lua脚本进行SQL判断。
(2)Atlas是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy0.8.2版本的基础_上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
(3) Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。

Amoeba:是一个以MySql为底层数据存储，并对应提供MySQL协议接口的proxy(代理)，外号变形虫读取请求发送给从服务器是，Q采用轮询调度算法
amoeba使用的java语言编写，配置文件为xml
amoeba主要负责对外的一个代理IP
访问这个IP时，发送的请求为“写"请求，则会转给主服务器
当发送的请求为“读"时，会通过调度转发给从服务器，使用轮询算法，轮流分配给两台从服务器amoeba可以视为调度器，如果主服务器挂掉，则会有MHA解决这个问题

六、搭建读写分离

1、安装环境

1、安装java环境
将jdk-6u14-linux-x64，bin和amoeba-mysql-binary-2.2.0.tar.gz.0上传到opt目录下
[root@localhost opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@localhost opt]# cd /usr/local/
2、给一个执行权限
[root@localhost local]# chmod +x jdk-6u14-linux-x64.bin
[root@amobema local]# ./jdk-6u14-linux-x64.bin
3、重命名
[root@amobema local]# mv jdk1.6.0_14/ /usr/local/jdk1.6
4、添加环境变量
[root@amobema local]# vim /etc/profile
在最后加入如下内容
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
5、加载
[root@amobema local]# source /etc/profile
6、查看版本
[root@amobema local]# java -version
7、安装Amoeaba
[root@amobema local]# mkdir /usr/local/amoeba
[root@amobema ~]# cd /opt
[root@amobema opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz  httpd-2.4.48  jdk-6u14-linux-x64.bin  jdk-6u14-linux-x64.zip  jdk-6u14-linux-x64.zip.0  rh
[root@amobema opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amobema opt]# /usr/local/amoeba/bin/amoeba
amoeba start|stop      #出现这个代表安装成功

2、三台mysql服务器开放权限给amoeba访问

mysql> grant all on *.* to 'test'@'192.168.153.%' identified by '123';
阿米巴访问数据库身份是test，指定给他一个密码123， 
mysql> flush privileges;    刷新权限

3、修改amoeba配置文件

[root@amobema ~]# cd /usr/local/amoeba/conf
[root@amobema conf]# ls
access_list.conf  amoeba.dtd  amoeba.xml  dbserver.dtd  dbServers.xml  function.dtd  functionMap.xml  log4j.dtd  log4j.xml  rule.dtd  ruleFunctionMap.xml  rule.xml
[root@amobema conf]# vim amoeba.xml   修改主配置文件
以下的配置的是第三个账户（客户端找amoeba的用户身份）

30行修改帐户名(这里是客户端访问amoeba服务器使使用的账号)
 <property name="user">amoeba</property>
32行修改账户密码（这里是数据库访问amoeba服务器时使用账号时用的密码）
<property name="password">123</property>
115行修改默认池
<property name="defaultPool">master</property>
117-120的注释符号取消
<!-- -->
118行-119行修改读池和写池
 <property name="writePool">master</property>
 <property name="readPool">slaves</property>
保存退出

进入amoeba数据配置文件修改
[root@amobema conf]# cp dbServers.xml dbServers.xml.bak
[root@amobema conf]# vim dbServers.xml
23行修改为mysql
 <property name="schema">mysql</property>
26行，阿米巴访问三台mysql数据库的账户和密码（账户为test）
27-29先取消注释符号，再修改访问数据库的密码
<!--  mysql password -->
<property name="password">123</property>
45行修改数据库的主服务器名
<dbServer name="master"  parent="abstractServer">
48行，修改数据库主服务ip
<property name="ipAddress">192.168.153.200</property>
52行修改从服务器名
<dbServer name="slave1"  parent="abstractServer">
55行修改从服务器ip
<property name="ipAddress">192.168.153.215</property>

52-57行复制粘贴到下面，同上修改第二个从服务器名和ip
 <dbServer name="slave2"  parent="abstractServer">
 <property name="ipAddress">192.168.153.220</property>
66行修改多个服务器池的名称
<dbServer name="slaves" virtual="true">
72行添加两个从服务器名
<property name="poolNames">slave1,slave2</property>
保存退出

4、开启

[root@amobema conf]# /usr/local/amoeba/bin/amoeba start
此时再开一个终端，查看是否启动
打开主文件amoeba.xml在11行看见端口号是8066
[root@amobema conf]# netstat -antp| grep java
[root@amobema conf]# netstat -antp| grep 8066

在这里插入图片描述

5、测试

打开另一台客户端
下载mariadb客户端
[root@localhost xkl]# yum -y install mariadb
远程登陆amoeba服务器
[root@localhost xkl]# mysql -uamoeba -p123 -h 192.168.153.100 -P 8066

测试一：

阿米巴服务器是否关联后端mysql客户端进入数据库创建表
在这里插入图片描述

测试二：

测试mysql读写分离
把2台从服务器关闭主从同步，测试amoeba读写分离，在从服务器上stop slave；即可
在这里插入图片描述

测试三

读写分离架构，对于读的任务如何操作的：
在两台从服务器上面插入不同数据，如何用客户端查询
在这里插入图片描述

七、小结

1、主从同步原理
通过amoeba代理服务器，实现只在主服务器上写，只在从服务.上读;
主数据库处理事务性查询，从数据库处理select查询;
数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库
2、如何查看主从同步状态是否成功
在从服务器内输入命令show slave status\G，查看主从信息进行查看，里面有IO线程的状态信息，还有master服务器的IP地址、端口、事务开始号,
当slave_ io_ running 和slave_ sq1_ running 都显示为yes时，表示主从同步状态成功
3、如果I/0和SQL不是yes排查方法
首先排除网络问题，使用ping命令查看从服务是否能与主服务器通信
再者查看防火墙和核心防护是否关闭
查看从服务器内的slave是否开启
两个从服务器的server-id是否相同导致只能连上一台
master_ log_ file 和master_ log_ pos的值要是否与Master查询的一致
4、show slave status能看到哪些重要信息
IO线程的状态信息
master服务器的IP地址、端口、事务开始位置
最近一次的报错信息和报错位置等
5、导致主从复制慢(延迟) 的原因
1)主服务器的负载过大，被多个睡眠或者僵线程占用，导致系统负载过大
2)从库硬件比主库差，导致复制延迟
3)主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。
4)SQL语句过多
4)网络延迟