热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

主存同步mysql_MySQL阶段五——主从复制原理、主从延迟原理与解决

MySQL主从复制原理、主从延迟原理与解决MySQL主从复制画图描述:MySQL主从复制原理上图详解:①用户做crud操作,写入数据库&#

MySQL主从复制原理、主从延迟原理与解决

MySQL主从复制画图描述:

1a48656b8706f9a4712c8281f55915b3.png

MySQL主从复制原理上图详解:

①用户做crud操作,写入数据库,更新结果记录到binlog中;

② 主从同步是主找从的,从库IO发起请求,主库的主进程看从库的master change中给的参数是否合法,如果合法主进程交给IO进程进行3操作,否则拒绝;

③ 主库根据master的位置点,从这个位置点的binlog日志一直到binlog最后,将其准备发送给从库;

④ 将找到的binlog日志发给从库,并且还会发送新的日志点;

⑤ 从库收到binlog日志,将其写入relay-log(中继日志)中;

⑥ 从库IO进程再向master info保存主库传过来的最后的binlog日志的位置点;

⑦从库IO是循环发起请求的,发了再要,不会顾及SQL读取中继的操作。

从库IO根据新的日志点,向主库发起请求,主库执行3操作再,再发送新的binlog给从库,从库再执行5操作;

⑧ 其实当第一次向relay-log中放数据时,SQL进程就已经知道,SQL进程将relay-log中的sql语句转换成数据,写入从库,从而实现同步;(relay-log和master info也不会交互)

⑨ SQL读取中继日志,并不会一次性全部读完,会把读取到的日志点存放到relay-log.info中。

主从同步实现之前应该具备的条件和做的准备:

①从库有IO和SQL两个线程,主库有IO一个线程

② 开启主从同步之前,主从库相对与一个日志点之前的数据是一致的;

(即先要将主库全备,并且记录全备的binlog:show master status;然后将全备的内容放入从库,即可完成)

③ 开启主从同步之前,要在主库建立从库进行同步的账号;

(3306mysql>grant replication slave on *.* to‘rep’@’192.168.168.101’identified by‘123’;)

④ 主库要打开binlog开关;

⑤ 从库要与主库进行主从同步,要做一下配置

(

3307mysql>CHANGE MASTER TO

MASTER_HOST=’192.168.168.101’,

MASTER_PORT=3306,

MASTER_USER=’rep’,

MASTER_PASSWORD=’123’,

MASTER_LOG_FILE=’mysql-bin.000002,’

MASTER_LOG_POS=238;

)

注:master_host参数里面最好不要是域名或者localhost,最好是IP

⑥ 在从库mysql>start slave;开启从库的IO和SQL进程,并且查看mysql>show slave status\G;查看(slave_IO_Running:yes slave_SQL_Rnning:yes scends_behind_master:0)如果这三个参数是这样,基本上,主从复制配置完成。

-二.配置mysql主从复制方案(脚本实现)

环境:多实例环境(主:3306、从:3307)

主:确保logbin开启,server-id唯一,my.cnf中参数不能重复。

在主数据库中创建用于主从同步的账号:

grant replication slave on *.* to rep@'192.168.168.109' identified by '123';

备份脚本:rep3306[root@qinbinPC rep]# cat rep3306

#!/bin/bash

MYUSER=root

MYPASS="qb123"

MYSOCK=/data/3306/mysql.sock

MAIN_PATH=/server/backup

DATA_PATH=/server/backup

LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log

DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz

MYSQL_PATH=/application/mysql/bin

MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"

MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A -B --master-data=2 --single-transaction -e"

cat |$MYSQL_CMD <

flush table with read lock;

system echo "--show master status result--">> $LOG_FILE;

system $MYSQL_CMD -e "show master status"|tail -l>>$LOG_FILE;

system ${MYSQL_DUMP} |gzip >$DATA_FILE;

EOF

$MYSQL_CMD -e "unlock tables;"

然后检查&#xff1a;[root&#64;qinbinPC rep]# cd /server/backup/

[root&#64;qinbinPC backup]# ls

mysql_backup_2017-05-13.sql  mysqllogs_2017-05-13.log

[root&#64;qinbinPC backup]# cat mysqllogs_2017-05-13.log

*************************** 1. row ***************************

Slave_IO_State: Queueing master event to the relay log

Master_Host: 192.168.168.109

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000020

Read_Master_Log_Pos: 332

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000020

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 332

Relay_Log_Space: 403

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

用于复制备份的脚本&#xff1a;[root&#64;qinbinPC rep]# cat rep3307

#!/bin/bash

MYUSER&#61;root

MYPASS&#61;"qb123"

MYSOCK&#61;/data/3307/mysql.sock

MAIN_PATH&#61;/server/backup

DATA_PATH&#61;/server/backup

LOG_FILE&#61;${DATA_PATH}/mysqllogs_&#96;date &#43;%F&#96;.log

DATA_FILE&#61;${DATA_PATH}/mysql_backup_&#96;date &#43;%F&#96;.sql.gz

MYSQL_PATH&#61;/application/mysql/bin

MYSQL_CMD&#61;"$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"

#RECOVER

cd ${DATA_PATH}

gzip -d mysql_backup_&#96;date &#43;%F&#96;.sql.gz

$MYSQL_CMD

#config slave

cat |$MYSQL_CMD<

CHANGE MASTER TO

MASTER_HOST&#61;&#39;192.168.168.109&#39;,

MASTER_PORT&#61;3306,

MASTER_USER&#61;&#39;rep&#39;,

MASTER_PASSWORD&#61;&#39;123&#39;,

MASTER_LOG_FILE&#61;&#39;mysql-bin.000020&#39;,

MASTER_LOG_POS&#61;332;

EOF

$MYSQL_CMD -e "start slave;"

$MYSQL_CMD -e "show slave status\G" >$LOG_FILE

#mail -s "mysql slave result" 1743825379&#64;qq.com 

-三、生产场景读写分离授权方案

方案一&#xff1a;

主库&#xff1a;grant select,insert,update,delete on &#39;blog&#39;.* to &#39;blog&#39;&#64;&#39;10.0.0.%&#39; identified by &#39;123&#39;;

从库&#xff1a;主库账号同步到从库&#xff0c;然后再回收一些权限&#xff1a;revoke insert,update,delete on blog.* from &#39;blog&#39;&#64;&#39;10.0.0.%&#39;;

从库也可以不收回权限&#xff0c;在my.cnf中的[mysqld]下加read-only也可以&#xff0c;但是需要注意&#xff1a;read-only参数对有授权super或all peivileges的权限的用户不起作用。

方案二&#xff1a;

主库&#xff1a;web_w 123 10.0.0.1 3306 (select,insert,delete,update);

从库&#xff1a;web_r 123 10.0.0.2 3306 (select);

风险&#xff1a;使用web_w连接从库时&#xff0c;权限比较大。

方案三&#xff1a;

mysql库不同步&#xff0c;在主库和从库创建权限不一样的用户。

风险&#xff1a;从库切换主库时&#xff0c;连接用户权限问题。

解决&#xff1a;保留一个从库专门准备接替从库。

-四、主库宕机&#xff0c;从库换主&#xff0c;继续同步

01.确保所有relay log全部更新完毕。

在没有从库上执行stop slave;show processlist;

直到看到Has read all relay log;表示从库更新都执行完毕&#xff1a;

(找一个数据库中master日志点最近的)

02.登录

#mysql -uroot -p&#39;123&#39; -S /data/3306/mysql.sock

>stop slave;

>retset master;

>quit;

03.进到数据库目录&#xff0c;删除master.info relay-log.info

检查授权表&#xff0c;read-only等参数。

04.提升为主库

vim /data/3306/my.cnf

开启log-bin

如果存在log-slave-updates read-only等一定注释。

然后重启服务&#xff0c;提升主库完毕。

05.其他从库操作

先检查(用于同步账号是否都还在)

登录从库&#xff1a;

>stop slave;

>change master to master_host&#61;&#39;新从库IP&#39;&#xff1b;

>start slave;

>show slave status\G

-五、主从复制常见故障总结

01.show master status&#xff1b;没有位置点

原因&#xff1a;binlog没有打开

(my.cnf里面查看binlog是log-bin,登录show variables like &#39;log_bin&#39;)

02.MASTER_HOST&#61;不能是域名或者localhost

03.锁表&#xff0c;解锁受interactive_timeout和wait_timeout两个参数控制&#xff0c;过了时间会自动解锁。

04.错误&#xff1a;last_IO_Error,...,&#39;Could not find first log file name in binary log index file&#39;

原因&#xff1a;master_log_file&#61;&#39; mysql.bin.000001 &#39;;加了空格

05.多实例连接从库的时候不能启动一直提示running&#xff0c;原因是非正常关闭数据库&#xff0c;导致脚本出错。

解决&#xff1a;rm -f /data/3306/mysql.sock /data/3306/*.pid

06.当从库已经建立一个数据库&#xff0c;进行主从复制的时候报错&#xff0c;这种sql错误是可以接受的&#xff0c;可以&#xff1a;

>stop slave;

>set global sql_slave_skip_counter&#61;1;

>start slave;

或者根据错误号&#xff0c;跳过错误&#xff0c;slave-skip-errors&#61;1032,1062,1007

之前见过一个说法&#xff1a;“使用半夜mysqldump带--master-data&#61;1全备恢复到从库&#xff0c;从库执行change master to,无须加位置点”

我在虚拟机&#xff0c;多实例环境做主从同步&#xff0c;做主库备份的时候加上参数--master-data&#61;1(没有锁表)&#xff0c;在从库进行连接的时候没有加MASTER_LOG_FILE&#61;’mysql-bin.000002,’MASTER_LOG_POS&#61;’238’;这两个参数&#xff0c;master.info里面有位置点(如果没有锁表备份&#xff0c;之后又操作主库数据)&#xff0c;但是实际上是从头同步。

希望与大家一起交流&#xff01;

/

一、MySQL数据库主从同步延迟

要了解MySQL数据库主从同步延迟原理&#xff0c;我们先从MySQL的数据库主从复制原理说起&#xff1a;

MySQL的主从复制都是单线程的操作&#xff0c;主库对所有DDL和DML产生的日志写进binlog&#xff0c;由于binlog是顺序写&#xff0c;所以效率很高。

Slave的IO Thread线程从主库中bin log中读取取日志。

Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的&#xff0c;不是顺序的&#xff0c;成本高很多。

由于SQL  Thread也是单线程的&#xff0c;如果slave上的其他查询产生lock争用&#xff0c;又或者一个DML语句(大事务、大查询)执行了几分钟&#xff0c;那么所有之后的DML会等待这个DML执行完才会继续执行&#xff0c;这就导致了延时。

二、MySQL数据库主从同步延迟产生原因

1、Master负载

2、Slave负载

3、网络延迟

4、机器配置(cpu、内存、硬盘)

总之&#xff0c;当主库的并发较高时&#xff0c;产生的DML数量超过slave的SQL Thread所能处理的速度&#xff0c;或者当slave中有大型query语句产生了锁等待那么延时就产生了。

三、MySQL数据库主从同步延迟解决方案

1、salve较高的机器配置

2、Slave调整参数

为了保障较高的数据安全性&#xff0c;配置sync_binlog&#61;1&#xff0c;innodb_flush_log_at_trx_commit &#61; 1 等设置。而Slave可以关闭binlog&#xff0c;innodb_flush_log_at_trx_commit也可以设置为0来提高sql的执行效率

3、并行复制

MySQL的复制延迟是一直被诟病的问题之一&#xff0c;欣喜的是&#xff0c;MySQL 5.7版本已经支持”真正”的并行复制功能。MySQL 5.7并行复制的思想简单易懂&#xff0c;简而言之&#xff0c;就是”一个组提交的事务都是可以并行回放的”&#xff0c;因为这些事务都已进入到事务的prepare阶段&#xff0c;则说明事务之间没有任何冲突(否则就不可能提交)。MySQL 5.7以后&#xff0c;复制延迟问题永不存在。

这里需要注意的是&#xff0c;为了兼容MySQL 5.6基于库的并行复制&#xff0c;5.7引入了新的变量slave-parallel-type&#xff0c;该变量可以配置成DATABASE(默认)或LOGICAL_CLOCK。可以看到&#xff0c;MySQL的默认配置是库级别的并行复制&#xff0c;为了充分发挥MySQL 5.7的并行复制的功能&#xff0c;我们需要将slave-parallel-type配置成LOGICAL_CLOCK。

b5c98001ccde0ae3e73532b6a5d2d68a.png



推荐阅读
  • 本文介绍了在rhel5.5操作系统下搭建网关+LAMP+postfix+dhcp的步骤和配置方法。通过配置dhcp自动分配ip、实现外网访问公司网站、内网收发邮件、内网上网以及SNAT转换等功能。详细介绍了安装dhcp和配置相关文件的步骤,并提供了相关的命令和配置示例。 ... [详细]
  • Nginx使用AWStats日志分析的步骤及注意事项
    本文介绍了在Centos7操作系统上使用Nginx和AWStats进行日志分析的步骤和注意事项。通过AWStats可以统计网站的访问量、IP地址、操作系统、浏览器等信息,并提供精确到每月、每日、每小时的数据。在部署AWStats之前需要确认服务器上已经安装了Perl环境,并进行DNS解析。 ... [详细]
  • Skywalking系列博客1安装单机版 Skywalking的快速安装方法
    本文介绍了如何快速安装单机版的Skywalking,包括下载、环境需求和端口检查等步骤。同时提供了百度盘下载地址和查询端口是否被占用的命令。 ... [详细]
  • Nginx使用(server参数配置)
    本文介绍了Nginx的使用,重点讲解了server参数配置,包括端口号、主机名、根目录等内容。同时,还介绍了Nginx的反向代理功能。 ... [详细]
  • 本文介绍了Oracle数据库中tnsnames.ora文件的作用和配置方法。tnsnames.ora文件在数据库启动过程中会被读取,用于解析LOCAL_LISTENER,并且与侦听无关。文章还提供了配置LOCAL_LISTENER和1522端口的示例,并展示了listener.ora文件的内容。 ... [详细]
  • 图解redis的持久化存储机制RDB和AOF的原理和优缺点
    本文通过图解的方式介绍了redis的持久化存储机制RDB和AOF的原理和优缺点。RDB是将redis内存中的数据保存为快照文件,恢复速度较快但不支持拉链式快照。AOF是将操作日志保存到磁盘,实时存储数据但恢复速度较慢。文章详细分析了两种机制的优缺点,帮助读者更好地理解redis的持久化存储策略。 ... [详细]
  • 本文介绍了在Mac上搭建php环境后无法使用localhost连接mysql的问题,并通过将localhost替换为127.0.0.1或本机IP解决了该问题。文章解释了localhost和127.0.0.1的区别,指出了使用socket方式连接导致连接失败的原因。此外,还提供了相关链接供读者深入了解。 ... [详细]
  • 本文介绍了Web学习历程记录中关于Tomcat的基本概念和配置。首先解释了Web静态Web资源和动态Web资源的概念,以及C/S架构和B/S架构的区别。然后介绍了常见的Web服务器,包括Weblogic、WebSphere和Tomcat。接着详细讲解了Tomcat的虚拟主机、web应用和虚拟路径映射的概念和配置过程。最后简要介绍了http协议的作用。本文内容详实,适合初学者了解Tomcat的基础知识。 ... [详细]
  • 安装mysqlclient失败解决办法
    本文介绍了在MAC系统中,使用django使用mysql数据库报错的解决办法。通过源码安装mysqlclient或将mysql_config添加到系统环境变量中,可以解决安装mysqlclient失败的问题。同时,还介绍了查看mysql安装路径和使配置文件生效的方法。 ... [详细]
  • Centos7.6安装Gitlab教程及注意事项
    本文介绍了在Centos7.6系统下安装Gitlab的详细教程,并提供了一些注意事项。教程包括查看系统版本、安装必要的软件包、配置防火墙等步骤。同时,还强调了使用阿里云服务器时的特殊配置需求,以及建议至少4GB的可用RAM来运行GitLab。 ... [详细]
  • 本文介绍了Hyperledger Fabric外部链码构建与运行的相关知识,包括在Hyperledger Fabric 2.0版本之前链码构建和运行的困难性,外部构建模式的实现原理以及外部构建和运行API的使用方法。通过本文的介绍,读者可以了解到如何利用外部构建和运行的方式来实现链码的构建和运行,并且不再受限于特定的语言和部署环境。 ... [详细]
  • http:my.oschina.netleejun2005blog136820刚看到群里又有同学在说HTTP协议下的Get请求参数长度是有大小限制的,最大不能超过XX ... [详细]
  • 本文介绍了RPC框架Thrift的安装环境变量配置与第一个实例,讲解了RPC的概念以及如何解决跨语言、c++客户端、web服务端、远程调用等需求。Thrift开发方便上手快,性能和稳定性也不错,适合初学者学习和使用。 ... [详细]
  • 计算机存储系统的层次结构及其优势
    本文介绍了计算机存储系统的层次结构,包括高速缓存、主存储器和辅助存储器三个层次。通过分层存储数据可以提高程序的执行效率。计算机存储系统的层次结构将各种不同存储容量、存取速度和价格的存储器有机组合成整体,形成可寻址存储空间比主存储器空间大得多的存储整体。由于辅助存储器容量大、价格低,使得整体存储系统的平均价格降低。同时,高速缓存的存取速度可以和CPU的工作速度相匹配,进一步提高程序执行效率。 ... [详细]
  • 本文介绍了如何使用iptables添加非对称的NAT规则段,以实现内网穿透和端口转发的功能。通过查阅相关文章,得出了解决方案,即当匹配的端口在映射端口的区间内时,可以成功进行端口转发。详细的操作步骤和命令示例也在文章中给出。 ... [详细]
author-avatar
SU大肥婆_545
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有