Mysql主从复制从0到1搭建

mysql主从复制介绍

  1. 主从复制是基于mysql binlog日志来实现的
  2. 当主库发生新的操作时都会记录新的binlog
  3. 从库获取主库的binlog来进行回放
  4. 主从复制的过程是异步的

主从复制的前提

 

  1. 2个或以上的数据库实例
  2. 主库需要开启二进制日志
  3. server_id要不同,区分不同的节点
  4. 主库需要建立专用的复制用户 (replication slave)
  5. 从库应该通过备份主库,恢复的方法进行"补课"
  6. 人为告诉从库一些复制信息(ip port user pass,二进制日志起点)
  7. 从库应该开启专门的复制线程

部署主从复制

  1. 检查配置文件主库是否开启二进制日志,两个节点是否配置节点id(建议主库小于从库)
    [mysqld]
    #数据路径
    datadir=/home/database
    #socket文件位置
    socket=/tmp/mysql.sock
    #软件安装目录
    basedir=/home/mysql/
    #服务器id号唯一
    server_id=68
    #运行端口
    port=3306
    #开启事务手动提交
    autocommit=0
    #开启二进制日志,设定二进制日志存储位置
    log_bin=/home/mysql-bin
    #配置log_bin日志的模式
    binlog_format=row
    #开启gtid模式进行记录
    gtid-mode=on
    #开启gtid强一致性
    enforce-gtid-consistency=true
    #开启慢日志记录
    log-slave-updates=1
    #设置慢日志的存放位置
    slow_query_log_file=/home/slow.log
    #设定慢日志超时时间记录
    long_query_time=1
    #设置不走索引的语句也进行记录
    log_queries_not_using_indexes=1
    #定义错误日志的位置
    log_error=/home/mysqlerror.log
    [mysql]
    #socket文件位置
    socket=/tmp/mysql.sock
    #链接数据库显示数据库名
    #\u 表示用户名,  \h 表示主机名, \d 表示当前数据库
    prompt="\\u@\\h:\\d>"
    View Code
  2. 主库创建一个用来同步复制数据的用户
    create user 'repl'@'%'identified by '123'; # 创建用户
    grant replication slave on *.* to 'repl'@'%';  # 授权
    #replication slave专属复制用户
    View Code
  3. 将主库全备信息导入从库,如果是空数据库可省却此步
  4. 告知从库信息
    #链接从库
    #执行代码
    CHANGE MASTER TO 
    MASTER_HOST='xxx.xxx.xxx.xxx',  # 指定主库ip地址
    MASTER_USER='xxx',                    # 指定主库用于同步复制的用户名
    MASTER_PASSWORD='xxx',            # 指定主库用于同步复制的用户密码
    MASTER_PORT=3306,                        # 指定主库的端口号
    
    MASTER_LOG_FILE='mysql-bin.000005',        # 指定导入主库的全备信息起始点二进制文件
    MASTER_LOG_POS=750,                            # 指定导入主库的全备信息起始点位置
    
    MASTER_AUTO_POSITION=1,                        # 如果使用gtid方式则添加此条,此处需要看情况进行设置
    
    MASTER_CONNECT_RETRY=10;                            # 指定主服务器宕机或链接丢失情况下,从服务器线程重新尝试连接主服务器之前睡眠的秒数
    
     # GTID 复制和普通复制的区别
    (0)在主从复制环境中,主库发生过的事务,在全局都是由唯一GTID记录的,更方便Failover
    (1)额外功能参数(3个)
    (2)change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
    (3)在复制过程中,从库不再依赖master.info文件,而是直接读取最后一个relaylog的 GTID号
    (4) mysqldump备份时,默认会将备份中包含的事务操作,以以下方式
       ####  SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';
        告诉从库,我的备份中已经有以上事务,你就不用运行了,直接从下一个GTID开始请求binlog就行。
    View Code
  5. 从库开启复制线程连接主库
    start slave;
    View Code
  6. 检查主从复制状态信息
    show slave status \G
    #打印内容
    #  Slave_IO_Running: Yes
    #  Slave_SQL_Running: Yes
    View Code

主从复制原理

主从复制涉及到的文件

  主库:binlog 二进制日志

  从库:

    relaylog 中继日志

    master.info 主库信息文件

    relaylog.info relaylog 应用的信息

主从复制涉及到的线程

  主库:

    Binlog_Dump Thread:DUMP_T

  从库:

    SLAVE_IO_THREAD:IO_T

    SLAVE_SQL_THREAD:SQL_T

原理图:

 

从库执行change master to 命令(主库的连接信息+复制的起点)

从库会将以上信息,记录到master.info文件

从库执行 start slave 命令,立即开启IO_T和SQL_T 从库 IO_T,读取master.info文件中的信息,获取到IP,PORT,User,Pass,binlog的位置信息

从库IO_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互

IO_T根据binlog的位置信息(mysql-bin.000004 , 444),请求主库新的binlog

主库通过DUMP_T将最新的binlog,通过网络TP给从库的IO_T

IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info 

IO_T将TCP/IP缓存中数据,转储到磁盘relaylog中.

SQL_T读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息

SQL_T会按照上次的位置点回放最新的relaylog,再次更新relay.info信息

从库会自动purge应用过relay进行定期清理

补充说明: 一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给IO_T,增强了主从复制的实时性.

主从复制监控

show slave status \G # 主库执行
#打印结果:
Slave_IO_State: Waiting for master to send event
# 与主库有关的信息(master.info)
                  Master_Host: 192.168.1.8             //主库的地址
                  Master_User: repl                            //主库用于主从复制的用户
                  Master_Port: 3306                            //主库端口号
                Connect_Retry: 10                                //指定主服务器宕机或链接丢失情况下,从服务器线程重新尝试连接主服务器之前睡眠的秒数
              Master_Log_File: mysql-bin.000005    //当前主从复制下主库所使用的二进制日志文件
          Read_Master_Log_Pos: 1175                            //当前主从复制下主库的位置点
# 从库relay应用信息相关(relay-log.info)         
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 745
        Relay_Master_Log_File: mysql-bin.000005
# 从库线程运行状态(排错使用)       
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# 过滤复制相关的信息
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 1175
              Relay_Log_Space: 956
              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
# 主库SSL证书
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: 6
                  Master_UUID: 60fe8aae-d62e-11ea-bbbe-00505683acb6
             Master_Info_File: /home/3308/data/master.info
# 延时从库(设定延时)    
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
# GTID复制相关
           Retrieved_Gtid_Set: 60fe8aae-d62e-11ea-bbbe-00505683acb6:3-4
            Executed_Gtid_Set: 063ea32f-d54e-11ea-8584-00505683acb6:1-4,
60fe8aae-d62e-11ea-bbbe-00505683acb6:3-4
                Auto_Position: 0
                
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

 主从复制故障处理

IO线程损坏

1、连接主库:connecting

解决方法:

从库:
stop slave ;
reset slave all;
change master to 。。。
start slave

2、请求主库二进制同步失败

解决方法:

从库:
stop slave ;
reset slave all; 

CHANGE MASTER TO 
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;

start slave;

SQL线程损坏

relay-log损坏

回放relaylog

约束冲突(主键、唯一键、非空...)

合理处理方法:

把握一个原则,一切以主库为基准

出现问题尽量进行反操作

最直接稳妥的方法就是重新构建主从

不推荐的解决方案:
方法一:

stop slave; 
set global sql_slave_skip_counter = 1;
start slave;

#将同步指针向下移动一个,如果多次不同步,可以重复操作。
start slave;

方法二:
/etc/my.cnf
slave-skip-errors = 1032,1062,1007

有效避免主从sql线程故障的方法:

1、从库只读

2、使用读写分离中间件

主从延时的原因

主库方面:

1、binlog写入不及时,sync_binlog=1

2、默认情况下dump_t是串行传输binlog,在并发事务量大的情况下或者大事务,会导致传送日志较慢。解决方法:必须GTID,使用group commit方式来支持DUMP_T并行

3、主库繁忙

从库方面:

1、由于从库是单sql线程,不管传的日志多少,只能一次执行一个事务,5.6版本有了GTID可以实现多sql线程,但是只能基于不同库的事务进行回放。5.7版本增强了GTID,增加了seq_no,并且新增了并发sql线程(logical_clock),MTS技术

2、主从硬件配置差异过大

3、主从参数配置

4、主从版本不一致

5、主从索引不一致

主从复制延时策略设置

1、延时从库

sql线程延时:数据已经写入relaylog中,sql线程延时运行,防止出现主库删库,从库跟随删除,一般建议3-6小时

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300; //以秒为单位
mysql>start slave;

# 查询主从状态
show slave status \G
SQL_Delay: 300                         //延时设置的时间
SQL_Remaining_Delay: NULL  //延时剩余时间

2、延时从库逻辑故障处理

监控到数据库逻辑故障
停止从库sql线程,记录已经回放的位置点(作为截取日志起点)
mysql> stop slave sql_thread;
mysql> show slave status \G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 626
截取从库relaylog日志恢复到主库drop之前
恢复业务
从库替代主库进行工作
或
从库导出故障库还原到主库

快速解除主从关系

# 从库
stop slave;
reset slave all;
# 主库
reset master;

主从复制过滤

# 从库配置过滤
vi /etc/my.cnf-
# 添加配置命令
replicate_do_db=repl //需要复制的数据库,如果有多个可以写多行

 

posted @ 2021-10-27 16:46  悠悠听风  阅读(99)  评论(0编辑  收藏  举报