主主互备,类似主从备份,只是两个主从备份的组合
master端:192.168.1.240
slave端:192.168.1.241
1.master端
vim my.cnf写入下面数据
server-id=2 //设置标识,具有唯一性
log-bin=mysql-bin //开启二进制日志
auto_increment_increment=2 //步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1 //起始值。一般填第n台主MySQL。此时为第一台主MySQL
binlog-do-db=test //设置要同步到slave的数据库
2.创建一个账户slave,并赋予相关权限,用于让slave登入
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
3.进入mysql,查看file名和position,等会有用
mysql>show master status;
====================================================================
1.slave端
vim my.cnf写入下面数据
server-id=3
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
binlog-do-db=test
2.创建一个账户slave,并赋予相关权限,用于让slave登入
CREATE USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY 'password1';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
3.进入mysql,查看file名和position,等会有用
>show master status;
--------------------------------------------------------------------
进入slave端
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.240', MASTER_USER='slave', MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=837;
备注:
MASTER_HOST='192.168.8.241' //master的主机IP
MASTER_USER='slave' //上面master创建的用户
MASTER_PASSWORD='password' //该用户的密码
MASTER_LOG_FILE='mysql-bin.000004' //master端file
MASTER_LOG_POS=837 //master端position
进入master端
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.240', MASTER_USER='slave1', MASTER_PASSWORD='password1',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=837;
备注:
MASTER_HOST='192.168.8.241' //slave的主机IP
MASTER_USER='slave' //上面slave创建的用户
MASTER_PASSWORD='password1' //该用户的密码
MASTER_LOG_FILE='mysql-bin.000004' //slave端file
MASTER_LOG_POS=837 //slave端position
3.分别进入master端和slave端,运行,查看slave
>start slave; //stop slave等,开启关闭slave;
>show slave status\G; //查看状态
看到如下两个参数为yes即为启动成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
备注:两个mysql,必须配置结构一模一样,才能开启这个主从服务器,才不会出现失败现象;
转载:https://www.cnblogs.com/kylinlin/p/5258719.html