mysql主从复制及读写分离环境的搭建(基于二进制日志文件)

测试环境:
ubuntu 18.04(虚拟机), mysql5.7

主要步骤:
0. 准备工作
1. 配置mysql主库
2. 配置mysql从库
3. 在主库中创建用户供从库用于复制
4. 备份主库数据,恢复到从库中
5. 在从库中配置主库的相关信息
6. 测试主从复制
7. 参考资料

准备工作

示例：(以下服务器示例ip请改成对应真实ip)

准备工作：

1.服务器准备：

192.168.199.163 主服务器 master

192.168.199.157 从服务器slave

2.启动mysql

service mysqld start

3.关闭防火墙

Ubuntu :

1.查看防火墙状态 : sudo ufw status

2.(如果开启)关闭 ：sudo ufw disable

CentOS7 :

1.查看默认防火墙状态 : firewall-cmd --state

2.停止firewall : systemctl stop firewalld.service

3.禁止firewall开机启动 : systemctl disable firewalld.service

1. 配置mysql主库

（1） 启用二进制日志功能,在my.cnf文件,加入如下配置:

# 二进制日志路径
log_bin = /var/log/mysql/mysql-bin.log  
# 主库实例id，建议填写ip后两位
server-id = 63

• log_bin 为二进制日志路径,需确保对该文件具有读写权限，自行新建文件路径及log文件
• server-id 主库实例id，建议填写ip后两位。server-id 取值为(1 ~ (2³²)−1)，这个值要有唯一性(不能与主从复制结构中的其他mysql实例重复).

（2） 对于InnoDB引擎,加入以下两个选项,可保证主从复制时的持久性和一致性.

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

• 确保主库的skip_networking选项为关闭状态,否则从库将无法与主库通信
  可在主库中查看该值的设置:

# 若为ON,需要在my.cnf中将其设置为OFF
show variables like '%skip_networking%'; 

（3） 重启mysql服务

sudo systemctrl restart mysql

（4）查看server_id是否修改成功

# 正常返回你配置的server_id号，若id号为0则没有配置成功
SHOW VARIABLES LIKE 'server_id';

2. 配置mysql从库

（1）对于从库，只需要在my.cnf文件里面配置server-id就可以了，其他设置是可选的。
官方建议从库开启log_bin,二进制日志可用于数据备份以及从mysql故障中恢复数据。

# 二进制日志路径
log_bin = /var/log/mysql/mysql-bin.log  
# 主库实例id，建议填写ip后两位
server-id = 57

（2） 重启mysql服务

sudo systemctrl restart mysql

（3）查看server_id是否修改成功

# 正常返回你配置的server_id号，若id号为0则没有配置成功
SHOW VARIABLES LIKE 'server_id';

3. 在主库中创建用户提供给从库用于复制

主库必须提供给从库一个具有replication slave权限的账号给从库用于复制，多个从库可使用同一个账号复制,也可为每一个从库单独创建用户。

create user 'slave'@'%' identified by 'mypassword';
grant replication slave on *.* to 'slave'@'%';
flush privileges;

4. 备份主库数据,恢复到从库中

可使用mydumper或者其他工具进行备份和恢复，本次测试主要是用mydumper。

安装mydumper:

sudo apt install mydumper

（1）在从库中使用mysqldump备份主库数据:

# --master-data选项会在备份时给数据库加锁, 在备份生成的dbname.sql中包含主库的二进制日志坐标信息
mysqldump -uroot -p -h192.168.199.163 --master-data dbname > dbname.sql 

• dbname可换成对应数据库名称，例如 ：vein 。
• 将 192.168.199.163替换为主库ip

（2）在从库中恢复主库的数据:

mysql -uroot -p dbname < dbname.sql

（3）在主库中查看主服务器信息（记录下来，后面同步需要用到）

# 获取主库二进制日志坐标(File和Position),记录这两个值，同步的时候需要用到
show master status

注 : 若使用其他工具备份，需要在备份前给主库加锁，同时获取主库的二进制日志坐标:

-- 备份前主库加锁
flush tables with read lock
-- 加锁后获取主库二进制日志坐标(File和Position),记录这两个值，同步的时候需要用到
show master status

5. 在从库中配置主库的连接信息

（1）在从库中配置主库连接信息, 执行sql:

# 在从库中执行
change master to 
master_host='master hostname or ip address',
master_user='replication_user_name', 
master_password='replication_password',
master_log_file='master_log_file',
master_log_pos=record_log_postion; 

• master_host : 替换为主库的域名或ip
• master_user : 替换为主库中创建的具有replication slave的账号
• master_password : 替换为上述账号的密码
• master_log_file='recorded_log_file_name', 主库的二进制日志坐标信息
• master_log_pos=record_log_postion; 主库的二进制日志坐标信息

注 : 使用带 --master-data 选项的 mysqldump 命令备份的数据库文件中已经包含了master_log_file和master_log_pos这两项配置的信息,从库恢复主库的数据时,已经修改了这两个配置的值,这里可以不用重复设置

（2）完成后,从库中执行以下sql,开始主从复制:

start slave

（3）使用以下命令查看从库的复制状态

show slave status

若配置有误,show slave status会显示相关的错误信息，若 Slave_IO_Running和Slave_SQL_Running是Yes 则成功。

6. 测试主从复制

登录主库,更新任意一个表的某个字段,如:

update admin set name = '测试主从复制' where id = 1;

登录从库,查询在主库中更新的表:

select name from admin where id = 1;

从库中admin.name的值与主库中admin.name的值一致则主从复制配置成功.

7. 参考资料

• Setting Up Binary Log File Position Based Replication