mysql 主从图_MySQL主从配置图文详解

#编辑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参数值，后面步骤会用到

3.配置从数据库

#编辑从数据库配置文件，添加以下内容#

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主从配置完成，可以新增数据查看是否同步了