MySQL主从配置图文详解

# 环境配置#
master IP:192.168.46.137  slave IP:192.168.46.138  database:v1

# 编辑 mysql 的配置文件，增加以下内容 #
vim etc/my.cnf
server-id=1
binlog-do-db=v1
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin
 编辑完成后，重启 mysql，systemctl restart mysqld.service(CentOS7 直接使用 systemctl 命令)
创建一个复制用户，具有 replication slave 权限 
grant replication slave on *.* to 'user3'@'192.168.46.138' identified by 'user3';
 用户名:user3  密码:user3
flush privileges;
# 查看主库状态 #
show master status;

记住 FileSet 和 Position 参数值，后面步骤会用到

# 编辑从数据库配置文件，添加以下内容 #
vim etc/my.cnf
server-id=2
replicate-do-db=v1
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin
 注意: 两个数据库配置文件里的 server-id 不能相同 
 重启 mysql
mysql -u root -pxxx
change master to master_host='192.168.46.137',master_user='user3',master_password='user3',master_log_file='mysql-bin.000003',master_log_pos=120;
# 启动 slave 线程 #
start slave;
# 查看 slave 状态, 注意 G 后面没有分号 #
show slave status\G

 
  红色箭头所指两个参数, 如果都为 Yes, 说明配置成功, 如果 Slave_IO_Running 为 connecting, 请检查防火墙，端口是否开放，FIle，Position 参数是否一致，网络是否畅通等

到此，mysql 主从配置完成，可以新增数据查看是否同步了