mysql 双主模式实战

参考前面0-安装文档,安装两台mysql用于实战测试

Tips:

如果mysql安装是通过虚拟机克隆的, 则以下配置文件 /var/lib/mysql/auto.cnf

中的uuid可能一样, 导致同步不了, 停止其中一个mysql, 删除该文件, 重启后再配置一下即可

Mater1

• My.cnf

  log_bin=mysql-bin
  server-id=1
  sync-binlog=1
  binlog-ignore-db=performance_schema
  binlog-ignore-db=mysql
  binlog-ignore-db=information_schema
  binlog-ignore-db=sys
  
  relay_log=mysql-relay-bin
  log_slave_updates=1
  # 指定自动递增
  auto_increment_offset=1
  auto_increment_increment=2

• 重启

• 登录mysql

• 授权

  //如遇密码安全等提示, 先关闭密码安全校验, 参考主从架构
  set global validate_password_policy=0;
  set global validate_password_length=1;
  
  grant replication slave on *.* to root@'%' identified by '密码'; 
  grant all privileges on *.* to root@'%' identified by '密码';
  flush privileges;
  
  //查看主库状态信息，例如master_log_file='mysql-bin.000007',master_log_pos=154 
  show master status; 

master2

与master1一致, 注意将server-id, auto_increment_offset, auto_increment_increment等参数设置成不一样的

设置相互复制

master1

change master to master_host='10.211.55.5',master_port=3306,master_user='root',master_password ='123456',master_log_file='mysql-bin.000001',master_log_pos=884;
//信息是master2的, 通过show master status获得

start slave;

master2

change master to master_host='10.211.55.6',master_port=3306,master_user='root',master_password ='123456',master_log_file='mysql-bin.000004',master_log_pos=599;
//信息是master1的, 通过show master status获得

start slave;

完成以后,相互测试即可