Mysql 5.7 主从复制功能 详细配置教程

原理介绍

• MySQL之间数据复制的基础是二进制日志文件（binary log file）
• 一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中
• 其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化
• 如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中
• 然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

准备工作

• 三台版本一致的mysql数据库
• 主 192.168.0.100
• 从1 192.168.0.110
• 从2 192.168.0.120

主库修改

1.找到mysql配置文件my.cnf或my.ini(各个系统的存放位置不同,可以针对操作系统进行百度)

进入配置文件在[mysqld]部分插入或修改

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=100 #设置server-id,设置为当前ip的最后一个段的数字,这样不会乱

2.重启mysql

3.分别为从库创建对应的用户

首先连接mysql    使用  (mysql -u 用户名 -p 密码) 进行连接  远程可以使用 (mysql -h 服务器ip -u 用户名 -p 密码)

CREATE USER 'slave1'@'192.168.0.110' IDENTIFIED BY '密码';#创建用户
CREATE USER 'slave2'@'192.168.0.120' IDENTIFIED BY '密码';#创建用户
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.0.110';#分配权限
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'192.168.0.120';#分配权限
flush privileges;   #刷新权限

 4.查看主库状态,日志文件名,日志位置(记录文件名与位置)

SHOW MASTER STATUS;

从库修改

1.同样找到msyql配置文件修改server-id

两个从库分别修改为

[mysqld]
server-id=110 #设置server-id，192.168.0.110

[mysqld]
server-id=120 #设置server-id，192.168.0.120

2.重启从库 两台mysql

3.打开mysql回话执行同步sql

从库1 sql

CHANGE MASTER TO MASTER_HOST='192.168.0.110', MASTER_USER='slave1', MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=154;

开启从库模式

start slave;

从库2 sql

CHANGE MASTER TO MASTER_HOST='192.168.0.110', MASTER_USER='slave2', MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=154;

开启从库模式 

start slave;

 4.查看从库状态

show slave status\G;

 

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。

测试

在主库创建test库新建test表插入一条数据

前往从库查看

从库1

从库2

 

数据同步成功

其他相关 

在mysql配置文件的[mysqld]可添加修改如下选项

# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = information_schema  
  
# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = test

参考文章 : MySQL主从复制(Master-Slave)实践 - 知其然，后知其所以然 - 博客园