MySQL主从复制搭建及详解

共计 3762 个字符，预计需要花费 10 分钟才能阅读完成。

环境简介

主服务器：CentOS6.5 下 MySQL5.6.30
安装请参考这里网址：https://www.linuxidc.com/Linux/2018-08/153813.htm
从服务器：Win7 下 5.6.17

原理简介及优缺点

MySQL 从 3.23 版本开始提供复制功能，复制主要是指将主服务器的 DDL 和 DML 操作，通过二进制日志（binLog 日志），传到服务的服务器上，然后在从服务器上对这些日志从新执行，从而实现从服务器与主服务器的数据同步。MySQL 支持一台主服务器同时向多台从服务器进行复制，从服务器同时也可以作为其他服务器的主服务器，实现链状的复制。

优点

如果主服务器出现问题，可以快速切换到从服务器提供服务。
可以在从服务器上执行查询，降低主服务器的压力
可以在从服务器上执行备份，以避免备份期间影响主服务器的性能

局限

由于 MySQL 实现的是异步复制，所以主从服务器之间的数据存在一定差异，对实时性要求高的数据仍然需要从主服务器上获得。

前期准备

1. 在 linux 下创建账号
在 win 的命令提示符中输入 ipconfig，查看 ip
MySQL 主从复制搭建及详解
因为我的 win 下 ip 为 192.168.153.1，所以，我创建将 Host 设置为 192.168.153.%，关于原因，可参考这里

2. 赋予权限

REPLICATION SLAVE 权限针对所有的数据库，只能通过.，而不能 shool.*, 因为 REPLICATION SLAVE 是复制 binlog 日志。
MySQL 主从复制搭建及详解
3. 将主服务器中的数据，复制到从服务器，确保两者搭建主从之前，数据一致。关于这，之前写过一篇文章专门介绍，可以参考这里

配置

主服务器配置

1. 开启 binlog 日志，并设置 server_id
MySQL 主从复制搭建及详解

从服务器配置

1. 配置 server_id
设置：server-id=2
MySQL 主从复制搭建及详解
2. 指定主服务器配置格式如下
change master to
master_host=’master_host_name’, // 从服务器的主机
master_user=’replication_user_name’,// 执行复制用户名
master_password=’replication_password’,// 执行复制用户密码
master_log_file=’recorded_log_file_name’,// 二进制日志文件名
master_log_pos=’recorded_log_postion’;// 复制开始位置

根据我的配置信息
bin-log 信息
MySQL 主从复制搭建及详解
复制用户信息

我执行的配置代码如下：
change master to
master_host=’192.168.153.140’,
master_user=’lidequan’,
master_password=’lidequan’,
master_log_file=’bin-log.000003’,
master_log_pos=120;

查看从服务器是否已连接主服务器

执行 start slave;

MySQL 主从复制搭建及详解

执行 show processlist;

MySQL 主从复制搭建及详解

修改主服务器数据，查看同步效果

在这里修改主服务器数据，主要是执行插入，更新操作。
操作之前，数据信息如下
MySQL 主从复制搭建及详解

执行插入操作

insert into `class` (`name`) values (‘ 三年二班 ’),(‘ 三年五班 ’),(‘ 三年七班 ’);

MySQL 主从复制搭建及详解
此时从服务器中也有数据了

执行更新操作

update class set `name`=’ 三年三班 ’ where id=3;

观察下图，发现数据确实发生了变化
MySQL 主从复制搭建及详解

删除操作

观察下图，数据也是同步的
MySQL 主从复制搭建及详解

数据表定义操作

1. 添加一个 student 表

CREATE TABLE student(
`id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT’ 学生编号 ’,
`name` CHAR(10) NOT NULL DEFAULT ” COMMENT’ 学生名 ’,
`class_id` INT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT’ 班级编号 ’,
PRIMARY KEY(`id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

MySQL 主从复制搭建及详解

2. 往 student 表中添加一个字段

ALTER TABLE student ADD COLUMN age TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT’ 学生年龄 ’;

MySQL 主从复制搭建及详解

指定负责的数据库或者表

可以通过 replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table 或者 replicate-wild-do-table 来指定主从数据库复制到从数据库的数据库或者表。

关于数据复制的一些重要参数

在上面搭建主从服务器的时候，使用了 MASTER_HOST,MASTER_PORT,MASTER_USER,MASTER_PASSWORD,MASTER_LOG_FILE,MASTER_LOG_POS 这些参数都要在从服务器上配置，下面再来说几个常用的启动选项，如 log_slave_updates、read_only、master_verify_checksum

log_slave_updates

log_slave_updates 这个参数用来配置从服务器上的更新操作是否写入二进制日志，默认是不打开的。
首先我们来看一下刚刚 win 下 mysql 的 binlog 日志内容
MySQL 主从复制搭建及详解
可以发现，刚刚我们执行了增、删、改等操作，它并没有记录。
我们可以看到 log_slave_updates 是没有启动的

且该属性是只读属性，不可以动态的设置，只能在配置文件中设置, 如下图设置将会报错

read_only

read-only 选项：对所有的非临时表进行只读控制。但是有两种特殊情况
1. 对 replication threads 例外，以保证 slave 能够正常的进行 replication。
2. 对于拥有 super 权限的用户，可以 ignore 这个选项。
MySQL 主从复制搭建及详解
当以没有拥有 super 权限的用户登录时候，会提示如下：

这样就确保了从数据只负责读数据操作，而拒绝写数据的操作。
补充：
SUPER 权限：
1. 可以有 change master to, kill 其他用户的线程的权限。
2. Purge binary logs 来删除 binary log, set global 来动态设置变量的权限。
3. 执行 mysqladmin debug 命令，开启或者关闭 log，在 read-only 打开时执行 update/insert 操作。
4. 执行 start slave, stop slave.
5. 当连接数已经达到 max_connections 的最大值时，也可以连接到 server。

master_verify_checksum

由于软硬件或者网络传输出错，导致主服务器上运行的 sql 语句与从服务器上运行的 sql 语句不一致，很难找到问题原因，mysql 的开发人员在 5.6 Milestone Development Release 版本中加入了 replication event checksum(主从复制事件校验)功能。master_verify_checksum 主要用于复制事件校验。当一个 event 被写入 binary log(二进制日志)的时候，checksum 也同时写入 binary log，然后在 event 通过网络传输到从服务器 (slave) 之后，再在从服务器中对其进行验证并写入从服务器的 relay log。由于每一步都记录了 event 和 checksum，所以我们可以很快地找出问题所在。

管理与维护

查看从服务器状态

使用 show slave stauts;
MySQL 主从复制搭建及详解
在查看这些信息中，比较重要的是”slave_io_runing”和”slave_sql_runing”这两个进程
slave_io_runing : 此进程负责从服务器从主服务器上读取 Binlog 日志，并写入从服务器上的中继日志中。
Slave_SQL_Runing: 此进程负责读取并执行中继日志中的 binlog 日子。
只要期中有一个进程的状态时 no，则表示复制进程停止。

总结

主从配置
一、主服务器上配置
1. 创建用户，并赋予 REPLICATION SLAVE 权限
2. 开启 binlog 日志，并设置 server_id
二、从服务器配置
1. 指定 server_id
2. 指定主服务器配置

备注：

1.win 下 mysql 开启与关闭（前提是需要配置 path 路径）
MySQL 主从复制搭建及详解

2.server-id 做什么用的

mysql 的同步的数据中是包含 server-id 的，用于标识该语句最初是从哪个 server 写入的，所以 server-id 一定要有的，如果设置多个从服务器，每个从服务器必须有一个唯一的 server-id 值，必须与主服务器的以及其它从服务器的不相同。
每一个同步中的 slave 在 master 上都对应一个 master 线程，该线程就是通过 slave 的 server-id 来标识的；每个 slave 在 master 端最多有一个 master 线程，如果两个 slave 的 server-id 相同，则后一个连接成功时，前一个将被踢掉。
在 mysql 做主从同步时，要保证一条数据不会陷入死循环，这里就是靠 server-id 来实现的。

：

��文永久更新链接地址：https://www.linuxidc.com/Linux/2018-08/153812.htm