MySQL阶段五——主从复制原理、主从延迟原理与解决-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介:

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


MySQL主从复制画图描述:


wKiom1kkLyrSi5E7AAB_mfYhi7g870.png-wh_50


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;开启从库的IOSQL进程,并且查看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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[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 <<EOF
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;"


然后检查:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[root@qinbinPC rep]# cd /server/backup/
[root@qinbinPC backup]# ls
mysql_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: 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

用于复制备份的脚本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@qinbinPC rep]# cat rep3307
#!/bin/bash
MYUSER=root
MYPASS="qb123"
MYSOCK=/data/3307/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"
#RECOVER
cd ${DATA_PATH}
gzip -d mysql_backup_`date +%F`.sql.gz
$MYSQL_CMD<mysql_backup_`date +%F`.sql
#config slave
cat |$MYSQL_CMD<<EOF
CHANGE MASTER TO
MASTER_HOST='192.168.168.109',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000020',
MASTER_LOG_POS=332;
EOF
$MYSQL_CMD -e "start slave;"
$MYSQL_CMD -e "show slave status\G" >$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。


wKioL1nJBOOTY1TkAAC_NqJu3PA420.png


本文转自 叫我北北 51CTO博客,原文链接:http://blog.51cto.com/qinbin/1929063


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章