MySQL主从搭建

主从复制简介

主从复制时基于二进制日志进行复制的
主库的修改会记录二进制日志
从库请求二进制日志解析SQL语句并执行，从而达到主从数据同步

前提

两台以上mysql实例 ,server_id,server_uuid不同
主库开启二进制日志
主库建立专用同步用户
主从开启时间点，主从数据需要一致 
告诉从库复制IP，user，passwd，以及二进制文件和复制起点
开启复制线程  (start slave;)

搭建环境：

主库：192.168.177.200

从库：192.168.177.201

mysql版本：5.7.41

主库

---

设置主库server-id并开启主库二进制

vim /etc/my.cnf
#开启二进制日志指定文件路径
log-bin=/web/logs/mysql/binlog/mysql-bin
#设置serverID
server-id=1

主从server-id不能相同
每个 MySQL 服务器在主从复制架构中需要有一个唯一的标识符。server_id用于区分不同的服务器实例。如果多个服务器具有相同的 server_id，那么在复制过程中会产生冲突和混乱。
如果主从服务器的 server_id 相同，可能会导致双主循环复制的问题，可能会使数据库陷入混乱或崩溃。

主库中创建专用复制用户

mysql -u root -p                                                       //登录mysql终端
CREATE USER 'repl'@'192.168.177.%' IDENTIFIED BY '123456';             //创建用户
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.177.%';              //授予权限
flush privileges;                                                      //刷新权限

查看二进制文件

mysql>  show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 1059
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 6533bab9-8a0a-11ef-b891-000c299acf13:1-5

从库

---

设置从库server-id

vim /etc/my.cnf
#设置serverID
server-id=2

执行同步SQL语句

CHANGE MASTER TO
  MASTER_HOST='192.168.177.200',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=1059,
  MASTER_CONNECT_RETRY=10;
  
  CHANGE MASTER TO            
  MASTER_HOST                 //主库的 IP 地址
  MASTER_USER                 //连接主库进行复制的用户名
  MASTER_PASSWORD             //连接主库的用户密码
  MASTER_PORT                 //主库的端口号
  MASTER_LOG_FILE             //指定主库同步二进制日志文件名
  MASTER_LOG_POS              //二进制文件开始同步位置
  MASTER_CONNECT_RETRY        //连接主库失败后重试时间间隔(s)

开启同步进程

start slave;

查看同步状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.177.200
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1059
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主从复制原理

线程

主:
DUMP THREAD
从:
IO  THREAD
SQL THREAD

文件

主:
mysql-bin.000003             二进制日志
从: 
dbname-relay-bin.000002      中继日志
master.info                  主库信息记录日志
relay-log.info               记录中继应用情况信息

描述

1.  change master to 时，ip pot user password binlog position写入到从库master.info进行记录
2.  start slave 时，从库会启动IO线程和SQL线程
3.  IO_T，读取master.info信息，获取主库信息连接主库
4.  主库会生成一个准备binlog DUMP线程，来响应从库
5.  IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
6.  DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从库的IO_T
7.  IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
8.  IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.  SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
10. SQL_T回放完成之后，会更新relay-log.info文件。
11. relay-log会有自动清理的功能。
细节：
1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求

主从故障监控

线程监控

主库：

mysql> show full processlist\G;
*************************** 1. row ***************************
     Id: 4
   User: repl
   Host: 192.168.177.201:45880
     db: NULL
Command: Binlog Dump
   Time: 1450
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

从库：

mysql> show slave status\G;
*************************** 1. row ***************************
主库相关信息：
                  Master_Host: 192.168.177.200
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1636
中继日志：
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 897
复制线程状态：
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
主从延时相关状态：(非人为)
        Seconds_Behind_Master: 0
延时从库有关的状态：(人为)
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
GTID 复制有关的状态：
           Retrieved_Gtid_Set: 6533bab9-8a0a-11ef-b891-000c299acf13:6-8
            Executed_Gtid_Set: 6533bab9-8a0a-11ef-b891-000c299acf13:6-8,
                               65c5f12a-8a0a-11ef-8a2e-000c29c3145d:1-2
                Auto_Position: 0