MySQL阶段五——主从复制原理、主从延迟原理与解决-白红宇

MySQL阶段五——主从复制原理、主从延迟原理与解决

阅读量：6675 次

发布时间：2019-06-25

本文共 7168 字，大约阅读时间需要 23 分钟。

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

MySQL主从复制画图描述：

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/bashMYUSER=rootMYPASS="qb123"MYSOCK=/data/3306/mysql.sockMAIN_PATH=/server/backupDATA_PATH=/server/backupLOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.logDATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gzMYSQL_PATH=/application/mysql/binMYSQL_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 <
     
      > $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;"

然后检查：

[root@qinbinPC rep]# cd /server/backup/[root@qinbinPC backup]# lsmysql_backup_2017-05-13.sql  mysqllogs_2017-05-13.log[root@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: 0Master_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

用于复制备份的脚本：

[root@qinbinPC rep]# cat rep3307#!/bin/bashMYUSER=rootMYPASS="qb123"MYSOCK=/data/3307/mysql.sockMAIN_PATH=/server/backupDATA_PATH=/server/backupLOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.logDATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gzMYSQL_PATH=/application/mysql/binMYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"#RECOVERcd ${DATA_PATH}gzip -d mysql_backup_`date +%F`.sql.gz$MYSQL_CMD
     
      <
      
       $LOG_FILE#mail -s "mysql slave result" 1743825379@qq.com <$LOG_FILE

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

方案一：

主库：grant select,insert,update,delete on 'blog'.* to 'blog'@'10.0.0.%' identified by '123';

从库：主库账号同步到从库，然后再回收一些权限：revoke insert,update,delete on blog.* from 'blog'@'10.0.0.%';

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

方案二：

主库：web_w 123 10.0.0.1 3306 (select,insert,delete,update);

从库：web_r 123 10.0.0.2 3306 (select);

风险：使用web_w连接从库时，权限比较大。

方案三：

mysql库不同步，在主库和从库创建权限不一样的用户。

风险：从库切换主库时，连接用户权限问题。

解决：保留一个从库专门准备接替从库。

-四、主库宕机，从库换主，继续同步

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

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

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

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

02.登录

#mysql -uroot -p'123' -S /data/3306/mysql.sock

>stop slave;

>retset master;

>quit;

03.进到数据库目录，删除master.info relay-log.info

检查授权表，read-only等参数。

04.提升为主库

vim /data/3306/my.cnf

开启log-bin

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

然后重启服务，提升主库完毕。

05.其他从库操作

先检查（用于同步账号是否都还在）

登录从库：

>stop slave;

>change master to master_host='新从库IP'；

>start slave;

>show slave status\G

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

01.show master status；没有位置点

原因：binlog没有打开

（my.cnf里面查看binlog是log-bin,登录show variables like 'log_bin'）

02.MASTER_HOST=不能是域名或者localhost

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

04.错误：last_IO_Error,...,'Could not find first log file name in binary log index file'

原因：master_log_file=' mysql.bin.000001 ';加了空格

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

解决：rm -f /data/3306/mysql.sock /data/3306/*.pid

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

>stop slave;

>set global sql_slave_skip_counter=1;

>start slave;

或者根据错误号，跳过错误，slave-skip-errors=1032,1062,1007

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

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

希望与大家一起交流！

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

要了解MySQL数据库主从同步延迟原理，我们先从MySQL的数据库主从复制原理说起：

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

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

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

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

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

1、Master负载

2、Slave负载

3、网络延迟

4、机器配置（cpu、内存、硬盘）

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

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

1、salve较高的机器配置

2、Slave调整参数

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

3、并行复制

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

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

转载于:https://blog.51cto.com/qinbin/1929063

你可能感兴趣的文章

基于AllegroGraph实现Protege设计知识库模型的存储步骤

查看>>

EL表达式获取参数值${param.name}等

查看>>

远程桌面不能复制粘贴的解决办法

查看>>

iOS开发-UITapGestureRecognizer手势

查看>>

tcpdump wireshark 实用过滤表达式（针对ip、协议、端口、长度和内容）实例介绍...

查看>>