MySQL主从复制详解

1.主从复制简介

主从复制（source-replication）是将一台MySQL实例(source)，发生的DML、DDL等修改操作记录到binlog中，源源不断传输到一个或多个副本库，副本库应用日志，达到一个和主库数据接近一致的状态。

复制是默认异步的 从库不需要永久连接以接收来自主库的更新。

根据配置，可以复制数据库中的所有数据库，也可以选择某个数据库甚至某个库下的某张表。

MySQL中复制的优点包括：

横向扩展解决方案 - 在多个从库之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。

数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。

分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。

远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

2.主从复制的前提

• 需要两台或两台以上的数据库实例
• 主库必须开启二进制日志
• 主库必须有复制用户
• 主库的server_id和从库不同

3.应用场景

• 迁移、备份。
• 高可用。
• 读写分离
• 分布式架构

4.主从复制原理

5.主从复制搭建过程

5.1 2台以上数据库实例

MySQL详细搭建过程请点击：MySQL框架、体系及产品基础介绍

# systemctl start mysqld3307
# systemctl start mysqld3308
# systemctl start mysqld3309
# netstat -tulnp

```sql
# mysql -S /tmp/mysql3307.sock -e "select @@server_id;select @@server_uuid"
@@server_id                7
@@server_uuid              d639b892-ba7b-11ea-9d00-000c295bb94f

# mysql -S /tmp/mysql3308.sock -e "select @@server_id;select @@server_uuid"
@@server_id                8
@@server_uuid              d8e965c5-ba7b-11ea-9d1e-000c295bb94f

# mysql -S /tmp/mysql3309.sock -e "select @@server_id;select @@server_uuid"
@@server_id                9
@@server_uuid              dc20d2d8-ba7b-11ea-9f57-000c295bb94f |

5.2 主库开启binlog

# mysql -S /tmp/mysql3307.sock -e "select @@log_bin;"
@@log_bin                1

5.3 主库开启专门的复制用户

# mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to replicate@'192.168.6.%' identified by '123456'"

5.4 “补课”： 备份主库恢复至从库 从库数据初始化

# mysqldump -S /tmp/mysql3307.sock -A --master-data=2 >/tmp/full.sql

# mysql -S /tmp/mysql3308.sock -e "source /tmp/full.sql"
# mysql -S /tmp/mysql3309.sock -e "source /tmp/full.sql"

5.5 开启主从

a. 获取 change master to 信息
help change master to 
# grep '\-- \CHANGE' /tmp/full.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=444;

# mysql -S /tmp/mysql3308.sock
# mysql -S /tmp/mysql3309.sock

方式一：
CHANGE MASTER TO
  MASTER_HOST='192.168.6.26',
  MASTER_USER='replicate',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000008',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;
  
  方式二：
  CHANGE MASTER TO
  MASTER_HOST='192.168.6.26',  
  MASTER_USER='replicate',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_AUTO_POSITION=1

b. 开启复制线程

start slave;

5.6 检查状态

# mysql -S /tmp/mysql3308.sock -e "show slave status\G"|grep "Running:"
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

# mysql -S /tmp/mysql3309.sock -e "show slave status\G"|grep "Running:"
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

6.主从复制基本故障处理

6.1 IO线程报错
解决思路：
1.网络

# ping 192.168.6.26

    1）硬件层，路由，交换机，网络设备
    2）网线
    3）安全组规则
    4）插错网线口

2.端口

telnet 192.168.6.26 3307

#关闭防火墙

systemctl stop firewalld

#防火墙添加允许mysql端口

firewalld-cmd --add-service=mysql 
firewalld-cmd --add-port=3306/tcp

3.用户名

grant replication slave on *.* to replicate@'%' identified by '123456';

4.密码，先登录测试

mysql -ureplicate -p123456 -P3307 -h192.168.6.26

如果报错 #replicate@‘db03’，需在参数，跳过反向解析

vim /etc/my.cnf
skip_name_resolve

#搭建主从时，用户名、密码、主机域、端口一定要一致。

  CHANGE MASTER TO
  MASTER_HOST='192.168.6.26',  
  MASTER_USER='replicate',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_AUTO_POSITION=1

6.2 SQL线程报错
处理方法一：

#临时停止同步
mysql> stop slave;
#将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
#开启同步
mysql> start slave;

处理方法二：

#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数，把线程号添加到配置文件
slave-skip-errors=1032,1062,1007

但是方法一、方法二都是有风险存在的，只是跳过错误

处理方法三：
1）重新备份数据库，恢复到从库
2）给从库设置为只读

#在命令行临时设置
set global read_only=1;
#在配置文件中永久生效
read_only=1

7. 延时从库

普通的主从复制可能存在不足

• 逻辑损坏怎么办？
• 不能保证主库的操作，从库一定能做
• 高可用？
• 自动failover？
• 过滤复制

企业中一般会延时1-6小时

7.1 延时从库开启方法

方法一：

#停止主从
mysql>stop slave;

#设置延时为180秒
mysql> change master to master_delay= 180;

#开启主从
mysql> start slave;

#查看状态
mysql> show slave status\G
SQL_Delay: 60

方法二：

做主从的时候直接指定延时
 change master to
 master_host='192.168.6.26',
 ...
 master_delay=3600;

7.2 延时从库关闭方法

#设置延时为0
mysql> change master to master_delay = 0;

#开启主从
mysql> start slave;

更多精彩内容欢迎关注微信公众号