MySQL主从复制

MySQL主从复制¹

1 概述

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时想多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

优点：

1. 主库出现问题，可以快速切换到从库提供服务。
2. 实现读写分离，降低主库的访问压力。
3. 可以在主库中备份执行，以避免备份期间影响主库服务。

2 原理

1. master 主库在事务提交时，会把数据变更记录到二进制文件 binlog 中。
2. slave 从库读取主库的二进制文件 binlog，写入到slave 的中继日志 relay log。
3. slave 重做中继日志中的事件，将改变反映它自己的数据。

3 搭建

3.1. 服务器准备

3.2. 主库配置

1. 修改配置文件/etc/my.cnf

   ## mysql 服务ID，保证整个集群环境中唯一，取值范围：1 - 2的32次方 -1，默认 为1
   server-id=1
   ## 是否只读，1：只读；0：读写；仅限制普通用户
   read-only=0
   ## 超级管理员只读  
   # super-read-only=1
   ## 忽略的数据库，指不需要同步的数据库
   # binlog-ignore-db=mysql
   ## 指定同步的数据库
   # binlog-do-db=db01
   

2. 重启mysql服务

   systemctl restart mysqld
   

3. 创建远程连接账号，并赋予主从复制权限

   ## 创建itcast用户，并设置密码，该用户可以在任意主机连接该mysql服务
   CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'root@123456';
   ## 为 'itcast'@'%' 用户分配主从复制权限
   GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';
   

4. 查看二进制文件坐标

   ## 查看二进制文件坐标
   show master status ;
   

   字段含义：

   • file：从哪个日志文件开始推送日志文件
   • position：从哪个位置开始推送日志
   • binlog_ignore_db：指定不需要同步的数据库

3.3. 从库配置

1. 修改配置文件/etc/my.cnf

   ## mysql 服务ID，保证整个集群环境中唯一，取值范围：1 - 2的32次方 -1
   ## 和主库不同即可
   server-id=2
   ## 是否只读，1：只读；0：读写；仅限制普通用户
   read-only=1
   ## 超级管理员只读  
   # super-read-only=1
   

2. 重启mysql服务

   systemctl restart mysqld
   

3. 登陆MySQL，配置远程登录的主库

   • 8.0.23 版后SQL（兼容老版本）

     CHANGE REPLICATION SOURCE TO SOURCE_HOST='xxx.xxx',SOURCE_USER='xxx',SOURCE_PASSWORD='xxx',SOURCE_LOG_FILE='xxx',SOURCE_LOG_POS=xxx;
     

   • 8.0.23 版本前

     CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;
     

   • 参数含义

     参数名	含义	8.0.23 前版本
     SOURCE_HOST	主库ip地址	MASTER_HOST
     SOURCE_USER	连接主库的用户名	MASTER_USER
     SOURCE_PASSWORD	连接主库的密码	MASTER_PASSWORD
     SOURCE_LOG_FILE	主库 binlog日志文件名	MASTER_LOG_FILE
     SOURCE_LOG_POS	主库 binlog日志文件位置	MASTER_LOG_POS

4. 开启同步操作

   # 8.0.22 之后 兼容之前
   start replica;
   stop replica;	# 关闭
   # 8.0.22 之前
   start slave;
   stop slave;		# 关闭
   

5. 查看主从同步状态

   # 查看主从同步状态
   show replica status;	# 8.0.22 之后
   show slave status;		# 8.0.22 之前
   

   主要参数：

   • Replica_IO_Running：YES - 执行IO的线程是否正常 2 原理 ③
   • Replica_SQL_Running：YES - 执行SQL的线程是否正常 2 原理 ⑤

4 测试

1. 主库创建 数据库、表，并插入数据

   ## 主库创建 数据库、表，并插入数据
   create database db01;
      
   create table db01.tb_user
   (
       id   int(11) auto_increment primary key not null comment '主键自增ID',
       name varchar(10) comment '姓名',
       sex  int(2) comment '性别'
   );
      
   insert into tb_user values (1,'TOM',1),(2,'Masic',1),(3,'Andy',0);
   

2. 从库查看数据

   show databases ;
   use db01;
      
   show tables ;
      
   desc tb_user;
      
   select  * from tb_user;
   

3. 注意：

   从库修改数据会导致主从停止复制，此时要stop replica ，重新配置远程主库并更改 binglog和 binlog position。

1. 参考黑马程序员 ↩