MySQL主从复制

MySQL主从复制

MySQL内建的复制功能是构建大型，高性能应用程序的基础
通过将MySQL的某一台主机（master）的数据复制到其他主机（slaves）上，并重新执行一遍来执行 复制过程中一台服务器充当主服务器，而其他一个或多个其他服务器充当从服务器

1、MySQL支持的复制类型

基于语句（statement）的复制
在主服务器上执行SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比 较高。
基于行（row）的复制
把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从MySQL 5.0开始支持。混合型（mixed）的复制
默认采用基于语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制。

2、为什么要做主从复制灾备

数据分布
负载平衡读写分离
提高并发能力

3、主从复制基本架构

4、主从复制原理

主要基于MySQL二进制日志
主要包括三个线程（2个I/O线程，1个SQL线程）

1、MySQL将数据变化记录到二进制日志中；
2、Slave将MySQL的二进制日志拷贝到Slave的中继日志中；
3、Slave将中继日志中的事件在做一次，将数据变化，反应到自身（Slave）的数据库

详细步骤：

1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave
2、从库的IO线程和主库的dump线程建立连接。
3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。
4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。
5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中
6、从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

5、MySQL复制常用的拓扑结构

主从类型（Master-Slave）
主主类型（Master-Master）
级联类型（Master-Slave-Slave）

6、如何实现主从复制

在主服务器（master）上
启用二进制日志
选择一个唯一的server-id 创建具有复制权限的用户
在从服务器（slave）上
启用中继日志
（二进制日志可开启，也可不开启）
选择一个唯一的server-id
连接至主服务器，并开始复制

7、MySQL主从复制实战

环境准备

两台机器一主一从。
主库（MySQL Master）：[ip为172.25.10.130 port为3306]
从库（MySQL Slave ）：[ip为172.25.10.132 port为3306]

主库配置
1） 设置server-id值并开启binlog参数
[mysqld]
log_bin = mysql-bin
server_id = 130

[root@localhost ~]# vim /etc/my.cnf.d/mysql-server.cnf 

重启数据库

[root@localhost ~]# systemctl restart mysqld.service

2） 建立同步账号

mysql> create user 'rep1'@'172.25.10.%' identified with mysql_native_password by 'redhat123';
mysql> grant replication slave on *.* to 'rep1'@'172.25.10.%';
mysql> show grants for'rep1'@'172.25.10.%';

3）锁表设置只读
为后面备份准备，注意生产环境要提前申请停机时间；

mysql> flush tables with read lock;

提示：如果超过设置时间不操作会自动解锁。
mysql> show variables like '%timeout%'; 测试锁表后是否可以创建数据库：

4） 查看主库状态
查看主库状态，即当前日志文件名和二进制日志偏移量
mysql> show master status;

5） 备份数据库数据
退出数据库后操作

[root@localhost ~]# mysqldump -uroot -p -A -B |gzip > mysql_bak.$(date +%F).sql.gz

密码为数据库的密码

6） 解锁
进入到MySQL进行解锁
mysql> unlock tables;

7） 主库备份数据上传到从库

scp mysql_bak.2022-09-11.sql.gz 172.25.10.132:/root

将本地路径下的压缩包拷贝到从库的root目录下
其中密码是root@172.25.10.132（从库）root的密码

可以查看从库主机上面的root目录下有该文件

到此，主库设置完成操作

从库上设置

1） 设置server-id值并关闭binlog参数
#log_bin = /data/mysql/data/mysql-bin
server_id = 132

重启数据库
[root@localhost ~]# systemctl restart mysqld.service

2） 还原从主库备份数据

[root@localhost ~]# cd /root
[root@localhost ~]# gzip -d mysql_bak.2022-09-11.sql.gz 
[root@localhost ~]# mysql -uroot -p < mysql_bak.2022-09-11.sql

检查还原：
mysql -uroot -p -e 'show databases;'

3） 设定从主库同步
根据主库的信息进行同步

mysql> change master to 
MASTER_HOST='172.25.10.130', 
MASTER_PORT=3306,
MASTER_USER='rep1', 
MASTER_PASSWORD='redhat123', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=868;

4） 启动从库同步开关
start slave;

检查状态：
show slave status\G

8、MySQL主从复制的状况监测

主从状况监测主要参数
Slave_IO_Running: IO 线 程 是 否 打 开 YES/No/NULL Slave_SQL_Running: SQL线程是否打开 YES/No/NULL Seconds_Behind_Master: NULL #和主库比同步的延迟的秒数
可能导致主从延时的因素主从时钟是否一致
网络通信是否存在延迟
是否和日志类型，数据过大有关从库性能，有没开启binlog 从库查询是否优化

常见状态错误排除

发现IO进程错误，检查日志，排除故障：

找到原因：从5.6开始复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样
解决方法：(从库是克隆机器) 修改从库的uuid
vim auto.cnf server-uuid=
或者

常见状态错误排除
show slave status;
报错：Error xxx doesn’t exist
解决方法：
stop slave;
set global sql_slave_skip_counter = 1;
start slave;

测试主从同步：
主库创建一个数据库：
mysql -uroot -p -e 'create database test_m';

从库检查：
mysql -uroot -p -e 'show databases;' |grep "test_m"