MySQL主从复制

MySQL主从复制

一、准备工作

• MySQL：5.7.x
• 准备至少两台数据库
• 其中一个数据库作为master，其余作为slave

二、原理

这里只是进行了简单的描述，详细可参考：主从复制原理

• 主库（Master）执行了增、删、改操作后（主库数据发生了改变），则将改变写入主库的binlog二进制日志文件中
• 从库（Salve）会在一定时间间隔内对主库的二进制日志文件进行探测：
  • 如果日志文件发生了改变，从库会启动一个I/O线程读取主库二进制日志文件；
  • 同时主库也会为每个I/O线程启动一个dump线程（向从库发送二进制日志文件）；
  • 并且I/O线程会将dump线程传来的日志保存到从库的relay-log文件中
  • 从库启动一个SQL线程，用于解析relay-log日志文件，并生成sql逐一执行

三、一主一从

1、主数据库配置

（1）、修改配置文件

• Windows：my.ini
• Linux：my.cnf
  在配置文件中添加以下配置

[mysqld]
log-bin=mysql-bin # 非必需
server-id=1　　　　# 必需

（2）、启动mysql，使用root用户登录，创建新用户，用于数据同步

# 创建用户：
create user 'maste-user'@'localhost' identified by '123456';
# 用户授权:
grant replication slave  on *.* to 'master-user'@'localhost';
GRANT SELECT, DELETE,INSERT,UPDATE,REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'master-user'@'%';
# 刷新
flush privileges;

（3）、使用新建用户登录MySQL，查看master状态

show master status;

2、从数据库配置

（1）、修改配置文件

• Windows：my.ini
• Linux：my.cnf
  在配置文件中添加以下配置

[mysqld]
server-id=2　　　　# 必需

（2）、执行同步语句，指向主库

master_host：主库所在服务器IP
master_user：在 主库 中新增的用于数据同步的用户名
master_password：在 主库 中新增的用于数据同步的用户密码
master_log_file：在 主库 中查询出来的File字段
master_log_pos：在 主库中查询出来的Position字段

change master to master_host='127.0.0.1', master_user='repl', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=514;

（3）、使用命令启动slave

# 停止
stop slave;
# 开启
start slave;
# 查看状态
show slave status\G;

（4）、在从库创建一个只读用户

# 创建用户
create user 'read-only'@'localhost' identified by '123456';
# 授予读权限
grant select on *.* to 'read-only'@'localhost';
# 刷新
flush privileges;

3、验证

• 前提：主从库的数据库、数据一致
• 主库、从库使用新建的用户进行登录
• 在主库中修改某个数据后，在从库中进行查看，如果数据一致则配置成功

四、FAQ

1、主库、从库连接失败

• 主、从所在服务器是否能ping通
• 是否可以使用新建用户连接上数据库
• 从库执行同步语句时，主库的用户名、IP、密码是否正确