具有口碑的产品设计网站,seochinazcom,广西网站建设-好发信息网,asp做的药店网站模板文章目录 MySQL---主从复制和读写分离主从复制mysql主从复制的作用mysql主从复制的分类mysql主从复制原理mysql主从复制的配置步骤mysql主从复制的同步模式在什么情况下半同步复制会将为异步复制#xff1f;mysql主从复制不一致问题如何解决#xff1f;mysql主从复制延迟问题… 文章目录 MySQL---主从复制和读写分离主从复制mysql主从复制的作用mysql主从复制的分类mysql主从复制原理mysql主从复制的配置步骤mysql主从复制的同步模式在什么情况下半同步复制会将为异步复制mysql主从复制不一致问题如何解决mysql主从复制延迟问题原因如何判断发生了主从复制延迟导致主从复制延迟有哪些因素 延迟问题的解决环境准备具体流程 读写分离两种读写分离的原理读写分离的作用环境准备具体流程 MySQL—主从复制和读写分离
主从复制
mysql主从复制的作用
实现读写分离跨主机热备份数据作为数据库高可用的基础
mysql主从复制的分类
基于SQL语句的复制STATEMENT优点执行效率高占用空间小性能消耗低缺点无法保证在高并发高负载时候的主从复制精确度基于行的复制ROW优点精确度高缺点占用空间大性能消耗高混合类型的复制MIXED默认采用基于SQL语句的复制一旦发现基于语句无法保证精确复制时就会采用基于行的复制
mysql主从复制原理
关键词两个日志 bin log、relay log 三个线程 IO线程 SQL线程 DUMP线程
主库master如果发生数据更新会将写入操作记录到二进制日志bin log里从库slave探测到主库的二进制日志发生了更新就会开启IO线程向主库请求二进制日志事件主库master会为每个从库的IO线程请求开启DUMP线程并发送二进制日志事件给从库从库slave接收到二进制日志事件后会保存到自己的中继日志relay log里从库slave还会开启SQL线程读取中继日志里的事件并在本地进行重放将事件解析程SQL语句逐一执行从而实现主库和从库的数据一致
mysql主从复制的配置步骤
主从服务器先做时间同步修改主从数据库的配置文件配置 二进制日志、中继日志、server-id每个节点都不同、gtid 等相关参数在主库创建主从复制的用户并授予主从复制权限在从库使用 change master to 对接主库并 start slave 开启同步在从库使用 show slave status 查看 IO线程 和 SQL线程 的状态是否都是 YES
mysql主从复制的同步模式
异步复制 主库在执行完客户端提交的事务后就会立即响应给客户端半同步复制 主库在执行完客户端提交的事务后只要等待一个从库返回响应给主库才会响应给客户端全同步复制 主库在执行完客户端提交的事务后要等待所有从库都返回响应给主库才会响应给客户端
在什么情况下半同步复制会将为异步复制
当主库在半同步复制超时时间内没有收到从库的响应就会降为异步复制半同步复制超时时间参数为rpl_semi_sync_master_timeout默认值为10s
当主库发送完一个事务事件后主库在半同步复制超时时间内收到了从库的响应就会恢复为半同步复制mysql主从复制不一致问题如何解决 在主库使用mysqldump对数据不一致的库或表进行完全备份并show master status检查当前的二进制日志和偏移量 mysqldump -uroot -p密码 库名 表名 mysql_bak.sql show master status; 使用scp把备份文件远程复制到从库在从库关闭同步导入数据备份 scp mysql_bak.sql 从库IP:目录/ stop slave; mysql -uroot -p密码 库名 mysql_bak.sql 在从库使用change master to 重新进行主从复制对接再开启同步 change master to master_host‘主库IP’,master_port3306,master_user‘用户’,master_password‘密码’,master_log_file‘binlog文件名’,master_log_pos偏移量; change master to master_host‘主库IP’,master_port3306,master_user‘用户’,master_password‘密码’,master_auto_position1; #使用GTID主从复制模式 start slave;
主要步骤 导出主库数据恢复到从库重新开启主从同步。
mysql主从复制延迟问题
原因
主库可以并发多线程执行写入操作而从库的SQL线程默认是单线程串行化复制从库的复制效率可能会跟不上主库的写入速度如何判断发生了主从复制延迟
可通过在从库执行show slave status查看输出的Seconds_Behind_Master参数的值来判断是否发生了主从复制延迟。如果值为正值则表示已经出现主从复制延迟数值越大表示从库落后主库越多导致主从复制延迟有哪些因素
主库写入操作并发量太大事务数太多网络延迟从库硬件比主库差太多使用了全同步复制事务太大慢SQL语句太多
延迟问题的解决
网络优化将从库分布在宇主库相同的局域网或网络延迟较小的环境中
硬件优化从库配置更好的硬件使用更高规格的CPU和内存使用固态硬盘和RAID提升磁盘的读写性能
配置优化innodb_buffer_pool_size #从库加大Innodb引擎缓存池的大小让更多的数据读写在内存中完成减少磁盘IO压力slave_parallel_workers #从库使用多线程并行复制log-slave-updates0 #从库从主库复制的数据时不写入到二进制日志减少磁盘IO压力innodb_flush_log_at_trx_commit2 #从库不使用双1设置减少磁盘IO压力sync_binlog0
架构优化主从复制的同步模式采用 半同步复制 或 异步复制采用读写分离避免读写操作相互阻塞
操作优化将大型事务拆分成多个较小的事务优化一些SQL语句操作比如可将多个操作合并到一个SQL语句执行创建索引避免全表扫描环境准备
Master 服务器20.0.0.51 mysql-8.0.33
Slave1 服务器20.0.0.52 mysql-8.0.33
Slave2 服务器20.0.0.53 mysql-8.0.33具体流程
一、时间同步
###Master 服务器20.0.0.51
yum install -y ntp vim /etc/ntp.conf--末尾添加--
server 20.0.0.0 #设置本地是时钟源注意修改网段
fudge 20.0.0.0 stratum 8 #设置时间层级为8限制在15内service ntpd start###Slave1 服务器20.0.0.52
###Slave2 服务器20.0.0.53
yum install -y ntp ntpdate service ntpd start
/usr/sbin/ntpdate 20.0.0.51 #进行时间同步crontab -e
*/30 * * * * /usr/sbin/ntpdate 20.0.0.51
----------------------------------------------------------------------------
二、mysql配置
###Master 服务器20.0.0.51 mysql-8.0.33
vim /etc/my.cnf#修改
server-id1# 添加
log-binmysql-bin #添加主服务器开启二进制日志
binlog_formatmixed#选配项
expire_logs_days7 #设置二进制日志文件过期时间默认值为0表示logs不过期
max_binlog_size500M #设置二进制日志限制大小如果超出给定值日志就会发生滚动默认值是1GB
skip_slave_start1 #阻止从库崩溃后自动启动复制崩溃后再自动复制可能会导致数据不一致的#双1设置数据写入最安全
innodb_flush_log_at_trx_commit1 #redo log事务日志的刷盘策略每次事务提交时MySQL都会把事务日志缓存区的数据写入日志文件中并且刷新到磁盘中该模式为系统默认
sync_binlog1 #在进行每1次事务提交写入二进制日志以后Mysql将执行一次fsync的磁盘同步指令将缓冲区数据刷新到磁盘
#双1设置适合数据安全性要求非常高而且磁盘IO写能力足够支持的业务比如订单、交易、充值、支付消费系统。双1模式下当磁盘IO无法满足业务需求时比如11.11活动的压力。推荐一下性能较快的设置并使用带蓄电池后备电源防止系统断电异常。
innodb_flush_log_at_trx_commit2 #每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中但是并不会同时刷新到磁盘上。该模式下MySQL会每秒执行一次刷新磁盘操作
sync_binlog500 #在进行500次事务提交以后Mysql将执行一次fsync的磁盘同步指令将缓冲区数据刷新到磁盘
systemctl restart mysqldmysql -u root -p123456
# 创建用户
CREATE USER myslave20.0.0.% IDENTIFIED BY 123456;
ALTER USER myslave20.0.0.% IDENTIFIED WITH mysql_native_password BY 123456;# 给从服务器授权
GRANT ALL ON *.* TO myslave20.0.0.%;
FLUSH PRIVILEGES;# 查询 File 列显示日志名Position 列显示偏移量
show master status;#Slave1 服务器20.0.0.52
#Slave2 服务器20.0.0.53
vim /etc/my.cnf# 修改
server-id 2 / 3 #修改注意id与Master的不同两个Slave的id也要不同此处应该2一个3# 添加
relay-logrelay-bin #开启中继日志从主服务器上同步日志文件记录到本地#选配项
innodb_buffer_pool_size2048M #用于缓存数据和索引的内存大小让更多数据读写在内存中完成减少磁盘操作可设置为服务器总可用内存的 70-80%
sync_binlog0 #MySQL不做任何强制性的磁盘刷新指令而是依赖操作系统来刷新数据到磁盘
innodb_flush_log_at_trx_commit2 #每次事务log buffer会写入log file但一秒一次刷新到磁盘
log-slave-updates0 #slave 从 master 复制的数据会写入二进制日志文件里从库做为其他从库的主库时设置为 1
relay_log_recovery1 #当 slave 从库宕机后假如 relay-log 损坏了导致一部分中继日志没有处理则自动放弃所有未执行的 relay-log 并且重新从 master 上获取日志这样就保证了 relay-log 的完整性。默认情况下该功能是关闭的将 relay_log_recovery 的值设置为 1 时 可在 slave 从库上开启该功能建议开启。systemctl restart mysqldmysql -u root -p123456#配置同步注意 master_log_file 和 master_log_pos 的值要与 Master 查询的一致
CHANGE master to master_host20.0.0.51,master_port3306,master_usermyslave,master_password123456,master_log_filebinlog.000004,master_log_pos157;
start slave; #启动同步如有报错执行 reset slave;
show slave status\G #查看 Slave 状态//确保 IO 和 SQL 线程都是 Yes代表同步正常。
Slave_IO_Running: Yes #负责与主机的io通信
Slave_SQL_Running: Yes #负责自己的slave mysql进程#一般 Slave_IO_Running: No 的可能性
1、网络不通
2、my.cnf配置有问题
3、密码、file文件名、pos偏移量不对
4、防火墙没有关闭------------------------------------------------------------------------------
三、验证主从复制效果
Master 服务器20.0.0.51
create database db_test;Slave1 服务器20.0.0.52
Slave2 服务器 20.0.0.53
查看 show databases;读写分离
两种 基于程序代码内部实现优点性能好不需要额外的硬件设备支出缺点由开发人员负责实现和维护在一些现有的大型应用中现实读写分离对代码改动会较为困难 基于中间代理层实现优点实现起来较为简单不需要重构代码缺点需要额外的硬件设置支出由运维人员负责维护 典型代表mysql-proxy、mycat、ameoba
读写分离的原理
在主库master上负责处理事务性写入操作在从库slave上负责处理查询读操作再通过主从复制将主库上的数据同步给从库。读写分离的作用
通过读写分离可以分担数据库单节点的负载压力还可避免读写操作相互阻塞从而提高数据库的读写性环境准备
Master 服务器20.0.0.51 mysql-8.0.33
Slave1 服务器20.0.0.52 mysql-8.0.33
Slave2 服务器20.0.0.53 mysql-8.0.33Amoeba 服务器20.0.0.54 jdk1.6、Amoeba
客户端 服务器20.0.0.55 mysql(8.0.33有问题) / 或者直接使用navicat 连接20.0.0.54 amoeba 端口8066具体流程
一、Amoeba 服务器20.0.0.54配置
Amoeba 服务器20.0.0.54# 安装 Java 环境
查看java版本并删除
java -version
yum remove -y java*将jdk-6u14-linux-x64.bin 移入/opt并安装
cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod x jdk-6u14-linux-x64.bin./jdk-6u14-linux-x64.bin
//按yes按entermv jdk1.6.0_14/ /usr/local/jdk1.6vim /etc/profile# 添加
export JAVA_HOME/usr/local/jdk1.6
export CLASSPATH.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export AMOEBA_HOME/usr/local/amoeba
export PATH$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$AMOEBA_HOME/bin:$PATHsource /etc/profile
java -version# 安装 Amoeba
将amoeba-mysql-binary-2.2.0.tar.gz 移入 /opt
mkdir /usr/local/amoeba
tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop说明安装成功## 配置 Amoeba读写分离两个 Slave 读负载均衡##
#先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问
Master 服务器20.0.0.51
CREATE USER amoeba20.0.0.% IDENTIFIED WITH mysql_native_password BY 123456;
grant all on *.* to amoeba20.0.0.%;
flush privileges;# 再回到amoeba服务器配置amoeba服务
cd /usr/local/amoeba/conf/# 备份
cp amoeba.xml amoeba.xml.bak# 修改amoeba配置文件
vim amoeba.xml --30行--
property nameuseramoeba/property
--32行--
property namepassword123456/property--115行--
property namedefaultPoolmaster/property--117-去掉注释-
property namewritePoolmaster/property
property namereadPoolslaves/property# 备份
cp dbServers.xml dbServers.xml.bak# 修改数据库配置文件
vim dbServers.xml --23行--注释掉 作用默认进入test库 以防mysql中没有test库时会报错
!-- property nameschematest/property ----26--修改
property nameusermanager/property--28-30--去掉注释
property namepassword123456/property--45--修改设置主服务器的名Master
dbServer namemaster parentabstractServer
--48--修改设置主服务器的地址
property nameipAddress20.0.0.51/property--52--修改设置从服务器的名slave1
dbServer nameslave1 parentabstractServer
--55--修改设置从服务器1的地址
property nameipAddress20.0.0.52/property--58--复制上面6行粘贴设置从服务器2的名slave2和地址
dbServer nameslave2 parentabstractServer
property nameipAddress20.0.0.53/property--65行--修改
dbServer nameslaves virtualtrue
--71行--修改
property namepoolNamesslave1,slave2/property/usr/local/amoeba/bin/amoeba start #启动Amoeba软件按ctrlc 返回
netstat -anpt | grep java #查看8066端口是否开启默认端口为TCP 8066客户端 服务器20.0.0.55
yum install -y mariadb
mysql -u amoeba -p123456 -h 20.0.0.54 -P8066
//通过amoeba服务器代理访问mysql 在通过客户端连接mysql后写入的数据只有主服务会记录然后同步给从--从服务器在主服务器上
use db_test;
create table test (id int(10),name varchar(10),address varchar(20));在两台从服务器上
stop slave; #关闭同步
use db_test;
//在slave1上
insert into test values(1,zhangsan,this_is_slave1);//在slave2上
insert into test values(2,lisi,this_is_slave2);//在主服务器上
insert into test values(3,wangwu,this_is_master);//在客户端服务器上
use db_test;
select * from test; //客户端会分别向slave1和slave2读取数据显示的只有在两个从服务器上添加的数据没有在主服务器上添加的数据insert into test values(4,qianqi,this_is_client); //只有主服务器上有此数据//在两个从服务器上执行 start slave; 即可实现同步在主服务器上添加的数据
start slave;
