MySQL数据库_主从复制：概述、原理、搭建、配置、测试-平芜编程栈

1概述

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

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

MySQL 复制的优点主要包含以下三个方面：

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

2 原理

MySQL主从复制的核心就是二进制日志，具体的过程如下：

主库会生成一个log dump线程,用来给从库I/O线程传binlog;
SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;
从库会生成两个线程,一个I/O线程,一个SQL线程;
I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;

从上图来看，复制分成三步：

1. Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。

2.从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log。

3. slave重做中继日志中的事件，将改变反映它自己的数据。

3搭建

3.1 准备

Linux操作系统中MySQL数据库安装请跳转参考以下博客：

【MySQL部署】MySQL 8.0.X部署教程_Linux上离线部署MySQL 8.0.X操作指南（二进制压缩包部署+独立目录部署，不在自动默认路径配置下安装）https://blog.csdn.net/weixin_45806267/article/details/152129662?ops_request_misc=%257B%2522request%255Fid%2522%253A%25227e7dbb75298730a786e27db7eb470fed%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=7e7dbb75298730a786e27db7eb470fed&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-6-152129662-null-null.nonecase&utm_term=MySQL&spm=1018.2226.3001.4450

准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备(安装、密码配置等操作)工作。其中：

192.168.200.200作为主服务器master
192.168.200.201作为从服务器slave

3.2主库配置

3.2.1. 主库修改配置文件vim/etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2的32次方-1，默认为1 server-id=1 #是否只读,1 代表只读, 0 代表读写 read-only=0 #忽略的数据, 指不需要同步的数据库 #binlog-ignore-db=mysql #指定同步的数据库 #binlog-do-db=db01

3.2.2. 主库重启MySQL服务器

systemctl restart mysqld

3.2.3. 主库登录mysql，创建远程连接的账号，并授予主从复制权限

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

3.2.4. 主库进入mysql>通过指令，查看二进制日志坐标

记录下 File 、Position 字段的值
其中 File 为操作日志文件，Position 为指针位置，这两个字段是数据同步的关键

show master status; （以下为示例） mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1173 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) file : 从哪个日志文件开始推送日志文件 position ： 从哪个位置开始推送日志 binlog_ignore_db : 指定不需要同步的数据库

字段含义说明：

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

3.3从库配置

3.3.1. 从库修改配置文件vim/etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可 server-id=2 #是否只读,1 代表只读, 0 代表读写 read-only=1

3.3.2. 从库重新启动MySQL服务

#重启MySQL服务（centos 7+） systemctl restart mysqld

3.3.3. 从库登录mysql，设置主库配置

服务器命令以root进入MySQL：mysql -u root -p 输入密码

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.200', SOURCE_USER='dbtb', SOURCE_PASSWORD='Root@142536', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=1173;

上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本，执行如下SQL：

CHANGE MASTER TO MASTER_HOST='192.168.200.200', MASTER_USER='dbtb', MASTER_PASSWORD='Root@142536', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1173;

以上为示例其中dbtab为用户名，Root142536为密码，182.168.200.200为第二步中主数据库的ip，“MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1173;”中的XXX为主库配置中的3.2.4查询结果）中对应的值

3.3.4. 从库开启同步操作

start replica ; #8.0.22之后 start slave ; #8.0.22之前

3.3.5. 从库查看主从同步状态

show replica status; #8.0.22之后 show replica status\G; #8.0.22之后 show slave status; #8.0.22之前 show slave status\G; #8.0.22之前

主要关注字段: Slave_IO_Running,Slave_SQL_Running，这两个字段值为 Yes 则从库同步状态良好。至此，mysql主从集群的配置已完成。

3.3.6.从库设置只读用户

1.登录从库，添加只读用户 A. 先创建用户（含密码） CREATE USER 'dbread'@'%' IDENTIFIED BY 'Root@142536'; B. 再授权只读（全库只读） GRANT SELECT ON *.* TO 'dbread'@'%'; C. 刷新权限 FLUSH PRIVILEGES; 2.这一步可选 第1步创建的dbread用户只有 SELECT 权限，这没问题；但如果要保证“从库不被误写”， 建议在从库实例层面也打开只读： SET GLOBAL read_only = ON; SET GLOBAL super_read_only = ON; 并在 my.cnf 里持久化（重启不丢）： read_only=1 super_read_only=1 #uper_read_only 能防止拥有高权限的账号（比如 SUPER/管理员）误写，从运维角度更保险。 3.这一步可选 快速验证： SHOW GRANTS FOR 'wow'@'%'; SELECT @@read_only, @@super_read_only;

4 注意

MySQL主从集群只会同步创建集群时指定的二进制日志编号往后的数据操作，对于原有的旧数据不会同步，如果要把之前的数据也同步到从库，先把主库数据导出导入到从库，先保证主从库的初始数据一致，然后再从当前位置往后进行同步。

5 数据同步测试

5.1.在主库上创建数据库、表，并插入数据

create database db01; use db01; create table tb_user( id int(11) primary key not null auto_increment, name varchar(50) not null, sex varchar(1) )engine=innodb default charset=utf8mb4; insert into tb_user(id,name,sex) values(null,'Tom', '1'),(null,'Trigger','0'), (null,'Dawn','1');

5.2.在从库查询数据，验证主从是否同步

从库服务器命令以root进入MySQL：mysql -u root -p 输入密码 show databases; use db01； select * from tb_user;

MySQL数据库_主从复制：概述、原理、搭建、配置、测试

1概述