备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL 的主从(异步复制)

Table of Contents

• 概述
• 1.MySQL主从拓扑图
• 2.MySQL配置从库的用处
• 3.复制数据的步骤
• 一.主从搭建步骤
• 1.数据库安装
• 2.主库开启binlog
• 3.授权
• 4.登陆主库查看此时日志状态
• 5.导出主库当前数据
• 6.从库指定serverid
• 7.从库导入主库数据
• 8.指定开始同步位置
• 9.开启复制
• 二.复制相关参数
• 三.slave启停常用命令
• 四.保证主从一致
• 五.常见问题
• 5.1 IO线程出问题
• 5.2 SQL线程问题
• 5.2.1 ERROR 1062
• 5.2.2 ERROR 1032
• 5.2.3 ERROR 1452
• 5.2.4 将从库设置为readonly
• 5.3 主从延迟问题
• 5.3.1.从库同步延迟的现象
• 5.3.2.产生原因
• 5.3.3.解决方案
• 参考文献:

概述

1.MySQL主从拓扑图

2.MySQL配置从库的用处

2.1. 读写分离
通过MySQL复制可以实现读写分离，将读操作分布到多个不同的服务器上，减轻服务器的压力。
2.2. 备份
从库可以作为数据的异地实时备份。
2.3. 故障切换
当主库遇到故障，系统可以切换到从库，实现故障切换。
2.4. 升级测试
从库可作为测试服务器的数据库。

3.复制数据的步骤

3.1 在主库上把数据更改记录到二进制日志（Binary Log）中。
3.2 备库将主库上的日志复制到自己的中继日志（Relay Log）中。
3.3 备库读取中继日志中的事件，将其在备库上重新执行一遍。

一.主从搭建步骤

1.数据库安装

自己写的MySQL 8.0编译安装： MySQL 8.0 CENTOS 7环境编译安装

2.主库开启binlog

配置文件[mysqld]下添加如下:

log-bin
server-id=1

重启mysql服务

mysqladmin -uroot -p -S /u01/my3306/mysql.sock shutdown  
/u01/my3306/bin/mysqld_safe --defaults-file=/u01/my3306/my.cnf --user=mysql &

3.授权

在主库创建同步账号

CREATE USER 'repl'@'10.31.1.113' IDENTIFIED BY 'test';
grant replication slave, replication client on *.* to 'repl'@'10.31.1.113';
flush privileges;

mysql> CREATE USER 'repl'@'10.31.1.113' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave, replication client on *.* to 'repl'@'10.31.1.113';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

4.登陆主库查看此时日志状态

show master status;

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000009 |      441 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.导出主库当前数据

备份此时主库的内容

-- 备份之前锁表
mysql> flush table with read lock;
mysqldump -uroot -pabc123 -A -B --events --master-data=2 > /backup/mysql/db-$(date +%F)-all.sql
-- 备份完成后取消锁
mysql> unlock tables;

6.从库指定serverid

配置文件[mysqld]下添加如下:

server-id=2

server-id不能重复

重启mysql服务

mysqladmin -uroot -p -S /u01/my3306/mysql.sock shutdown  
/u01/my3306/bin/mysqld_safe --defaults-file=/u01/my3306/my.cnf --user=mysql &

7.从库导入主库数据

通过scp将主库的备份文件同步过来

mysql -uroot -p  </backup/mysql/db-2020-07-24-all.sql

8.指定开始同步位置

具体同步位置参考步骤4

change master to
master_host='10.31.1.112',
master_user='repl',
master_password='test',
MASTER_LOG_FILE='binlog.000009',
MASTER_LOG_POS=441 ;

mysql> change master to
    -> master_host='10.31.1.112',
    -> master_user='repl',
    -> master_password='test',
    -> MASTER_LOG_FILE='binlog.000009',
    -> MASTER_LOG_POS=441 ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

9.开启复制

-- 启动复制
start slave;
-- 查看复制状态
show slave status\G

mysql>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.31.1.112
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000009
          Read_Master_Log_Pos: 441
               Relay_Log_File: ipctest-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000009
             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: 441
              Relay_Log_Space: 532
              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
                  Master_UUID: 08260f93-cbe5-11ea-bd0d-000c293fa60d
             Master_Info_File: mysql.slave_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: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

二.复制相关参数

三.slave启停常用命令

从库执行

四.保证主从一致

五.常见问题

5.1 IO线程出问题

Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

问题排查：在主库上
[root@master mysql3306]# cat mysql-bin.index
/home/mysql3306/mysql3306/mysql-bin.000001
发现binlog文件名不正确，需要重新设置。

处理办法
stop slave;
reset slave all;
change master to master_host=‘192.168.3.10’,master_user=‘rep’,master_password=‘rep’, master_log_file=‘mysql-bin.000001’,master_log_pos=154,MASTER_PORT=3306;
start slave;

5.2 SQL线程问题

5.2.1 ERROR 1062

主从数据库不一致的时，slave已经有该条记录，但是我们又在master上插入了同一条记录，此时就会报错.
在从库上执行跳过该insert语句
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
问题解决！

5.2.2 ERROR 1032

从库删除一条数据，主库更新该条删除的数据，发生报错
手工把缺失的数据补上
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
问题解决！

5.2.3 ERROR 1452

无法在外键的表中插入或者更新参考主键没有的数据

5.2.4 将从库设置为readonly

建议大家线上打开这两个参数，防止主从不一致的产生。
set global read_only=1
只读参数，需要大家注意的对于拥有super权限的用户，该参数不起作用
set global super_read_only=1（5.7新参数）
如果想对拥有super权限用户只读，使用super_read_only，开启该参数后，read_only会自动开启。

5.3 主从延迟问题

5.3.1.从库同步延迟的现象

• show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大
• show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
• MySQL的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

5.3.2.产生原因

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。
首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高
次要原因：读写binlog带来的性能影响，网络传输延迟。

5.3.3.解决方案

• 更换从库更快的硬盘
• 网络，网卡，更换带宽更大的网卡
  mysql考虑 从库作为备份数据库来说
• 增加从库的innodb_buffer_pool_size,可以缓存更多数据防止由于转换造成的IO压力
• 增加innodb_log_file_size和innodb_log_files_in_group，减少buffer落盘
• 修改参数innodb_flush_method，提高写入性能（SSD强烈推荐使用）
• 从库binlog关闭（如果可以） log_slave_updates关闭
• 修改innodb_flush_log_at_trx_commit为0或者2
• 修改master_info_repository和relay_log_info_repository为TABLE，防止直接落盘压力

参考文献:

1.https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
2.https://zhuanlan.zhihu.com/p/115011360
3.http://blog.itpub.net/15412087/viewspace-2153117/
4.http://blog.itpub.net/31559985/viewspace-2220695/
5.http://blog.chinaunix.net/uid-20639775-id-3254611.html