15. MySQL主从复制

基本原理

• slave会从master读取binlog来进行数据同步
• master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events
• slave将master的binary log events拷贝到它的中继日志（relay log）
• slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

基本原则

• 每个slave只有一个master
• 每个slave只能有一个唯一的服务器ID
• 每个master可以有多个salve

最大问题

延时

一主一从

1.Windows主机修改my.ini配置文件

# 主服务器唯一ID
server-id=1

# 启用二进制日志
log-bin=D:\Program Files\mysql-5.6.46-winx64\mysqlbin

# 设置不要复制的数据库
binlog-ignore-db=mysql

# 设置需要复制的数据库
binlog-do-db=mydb

# 设置binlog格式
binlog_format=STATEMENT

binlog格式*

binlog_format=STATEMENT默认

1. STATEMENT默认

   会在logbin日志里面记录所有的写操作SQL，但是有一个问题，如果执行的SQL中带有函数，例如上图的now()这种，如果在主机上执行一遍，再到从机上执行一遍，那就可能造成主从复制不一致。

2. ROW

   会记录执行完SQL之后每一行的改变，这样可以解决STATEMENT的主从复制不一致问题，但是它也有问题，比如说需要大批量修改数据或者整表修改的时候，假如有10W条数据，做主从复制的话，它就需要在主机记录一遍，到从机再记录一遍，有效率问题。

3. MIXED

   会判断写操作里面有没有函数，如果有函数就用ROW模式，没函数就用STATEMENT模式，它的问题是，一旦SQL中出现系统变量，例如上图的@@host name，它就会出现主从复制不一致

mysql主从复制起始时，从机不继承主机数据

2.Linux从机修改my.cnf配置文件

vim /etc/my.cnf
 
# 增加以下两行
# 需要与主机区分
server-id=2
 
# 开启终极日志
relay-log=mysql-relay

3.主机从机重启服务

• Windows：可以直接在任务管理器找到MySQL服务重启
• Linux：systemctl restart mysqld

4.主机从机都关闭防火墙

• windows手动关闭
• linux：systemctl stop firewalld

5.在Windows主机上建立账户并授权slave

# 给repl做主从复制的权限，%代表所有的远程用户
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';

# 查询master的状态，记录下File和Position的值
show master status;

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

6.在Linux从机上配置需要复制的主机

# 设置主从关系，此处一定要确定好主机的IP
CHANGE MASTER TO MASTER_HOST='192.168.2.229',MASTER_USER='repl',
MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysqlbin.000001',MASTER_LOG_POS=120;

# 启动从服务器复制功能
start slave;

7.查看连接状态

show slave status\G;

必须保证IO与SQL的Running状态为yes，否则建立失败

假如出现Slave_IO_Running：Connecting，一定要仔细检查配置，我这边就出现过这个错误，但是我的是主机IP一直没写对，我是跟着视频来的，视频中说网段最后是.1，我就下意识的以为应该是一样的，其实不是一样的，本机的IP还是要去查一下，Windows：ipconfig，Linux：ifconfig，要以查出来的为准，所以这是一个非常低级的错误，不知道以后会不会有人看到，如果正好看到了，那也算是我帮你们踩坑了。

8.如何重新配置主从

如果真要重新设置主机、从机，可以看一下这篇博客

9.测试

# 在主服务器操作建库、建表、插入数据
create database mydb_indi
use mydb_indi;
create table mytbl(id int, name varchar(20));
insert into mytbl values(1,'zhang3');

查看从服务器数据库

主从复制，搭建成功！