1.环境
机器 | mysql | 主从 |
---|---|---|
yzm1 | 192.168.192.128:3306 | master |
yzm2 | 192.168.192.129:3306 | slave |
yzm3 | 192.168.192.130:3306 | slave |
2.主库搭建(yzm1)
编辑mysql配置文件
vim /etc/my.cnf
添加内容
server-id=128
log-bin=master-bin
log-bin-index=master-bin.index
启动master服务
service mysql restart
创建主从交互用户
create user 'yzm1'@'%' identified by 'yzm1';
查看用户
use mysql;
select user,host from user;
yzm1用户授权
GRANT REPLICATION SLAVE on *.* to 'yzm1'@'%';
查看权限
show grants for 'yzm1'@'%';
刷新权限
flush privileges;
查看master状态
show master status;
3.从库搭建(yzm2和yzm3)
编辑配置文件
vim /etc/my.cnf
添加内容,注意:id不能跟master的一样
server-id=129
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
启动salve数据库
service mysql restart
关联master用户,master_log_file和master_log_pos必须跟master提供的一致
change master to master_host='192.168.192.128',master_port=3306,master_user='yzm1',master_password='yzm1',master_log_file='master-bin.000001',master_log_pos=858;
启动slave
start slave;
查看salve状态
show slave status \G
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
IO线程跟SQL线程必须都是Yes才算搭建好主从
提示错误信息:
Last_IO_Error: error connecting to master ‘yzm1@192.168.192.128:3306’ - retry-time: 60 retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.
意思就是说:salve通过yzm1用户与master建立连接失败了,密码需要加密一下
回到master主库,加密yzm1用户的密码
ALTER USER 'yzm1'@'%' IDENTIFIED WITH mysql_native_password BY 'yzm1';
停止salve
stop salve;
salve从库重新与master建立连接
change master to master_host='192.168.192.128',master_port=3306,master_user='yzm1',master_password='yzm1',master_log_file='master-bin.000001',master_log_pos=1317;
start salve;
主库查看salve列表
show slave hosts;
4.同步测试
master新增数据库testdb,salve同步新增
如果slave新增数据库,则master不会新增