MyCat学习：使用MySQL搭建主从复制(双主双从模式)

首先&＃xff0c;上一篇我们建立了一主一丛模式的MySQL读写分离 点击这里 那么接下来&＃xff0c;我们搭建双主双从模式的读写分离&＃xff0c;并且使用MyCat登录访问

原理&＃xff1a;master1&＃xff0c;master2&＃xff0c;slave1&＃xff0c;slave2 —> master1 和 slave1主从复制&＃xff0c;master2 和 slave2主从复制&＃xff0c;同时 master2 做为master1的从机&＃xff0c;进行互备主从

当然&＃xff0c;如果你之前没有搭建过主从复制&＃xff0c;下面这两步骤可以不需要做

• 首先需要清除当前之前设置的主从模式&＃xff0c;删除testdb数据库
• stop slave; reset master;&＃xff08;可以主机从机都执行&＃xff09;
  一、master1配置文件&＃xff1a;

server-id&＃61;1 # 定义服务器唯一ID
log-bin&＃61;mysql-bin # 启用二进制日志
binlog-ignore-db&＃61;mysql # 忽略 mysql 库&＃xff08;如果配置了binlog-do-db的话可以不需要设置这个&＃xff09;
binlog-do-db&＃61;testdb # 设置需要主从复制的库
binlog_format&＃61;STATEMENT
log-slave-updates # 在做为从数据库的时候&＃xff0c;有写入的操作也需要记录下二进制日志文件
auto-increment-increment&＃61;2 #表示自增长字段每次递增的量&＃xff0c;指自增字段的起始值&＃xff0c;其默认值是1&＃xff0c;取值范围是1&＃xff5e;65535
auto-increment-offset&＃61;1 # 表示自增字段从哪个数开始&＃xff0c;一次递增多少&＃xff0c;取值范围是 1&＃xff5e;65535


二、master2配置文件&＃xff1a;

server-id&＃61;3
log-bin&＃61;mysql-bin
binlog-ignore-db&＃61;mysql
binlog-do-db&＃61;testdb
binlog_format&＃61;STATEMENT
log-slave-updates
auto-increment-increment&＃61;2
auto-increment-offset&＃61;2 # 注意起始值不一样


三、slave1配置文件&＃xff1a;

server-id&＃61;2
relay-log&＃61;mysql-relay


四、slave1配置文件&＃xff1a;

server-id&＃61;4
relay-log&＃61;mysql-relay


五、双主机&＃xff0c;双从机重启 mysql 服务
六、主机从机都必须关闭防火墙
七、在两台主机上建立账户并授权 slave

create user &＃39;slave&＃39;&＃64;&＃39;%&＃39; identified by &＃39;qkm19981013&＃39;;
grant replication slave on *.* to &＃39;slave&＃39;&＃64;&＃39;%&＃39;;
flush privileges;


八、查看 master1 的主机状态&＃xff0c;记录下binlog日志文件名和position点的值&＃xff0c;在slave1上执行以下语句&＃xff1a;

change master to master_host&＃61;&＃39;192.168.0.12&＃39;,master_port&＃61;3307,master_user&＃61;&＃39;slave&＃39;,master_password&＃61;&＃39;qkm19981013&＃39;,master_log_file&＃61;&＃39;mysql-bin.000001&＃39;,master_log_pos&＃61;156;


九、查看 master2 的主机状态&＃xff0c;记录下binlog日志文件名和position点的值&＃xff0c;在salve2上执行以下语句&＃xff1a;

change master to master_host&＃61;&＃39;192.168.0.12&＃39;,master_port&＃61;3309,master_user&＃61;&＃39;slave&＃39;,master_password&＃61;&＃39;qkm19981013&＃39;,master_log_file&＃61;&＃39;mysql-bin.000001&＃39;,master_log_pos&＃61;156;


十、在slave1和slave2上开启主从复制

start slave;
show slave status\G;


十一、最后一步&＃xff0c;搭建 master1和master2的互备主从&＃xff0c;即master1复制master2&＃xff0c;master2复制&＃xff0c;master1:

master1设置master2为主机&＃xff1a;
change master to master_host&＃61;&＃39;192.168.0.12&＃39;,master_port&＃61;3309,master_user&＃61;&＃39;slave&＃39;,master_password&＃61;&＃39;qkm19981013&＃39;,master_log_file&＃61;&＃39;mysql-bin.000001&＃39;,master_log_pos&＃61;156;
start slave;
show slave status\G;
master2设置master1为主机&＃xff1a;
change master to master_host&＃61;&＃39;192.168.0.12&＃39;,master_port&＃61;3307,master_user&＃61;&＃39;slave&＃39;,master_password&＃61;&＃39;qkm19981013&＃39;,master_log_file&＃61;&＃39;mysql-bin.000001&＃39;,master_log_pos&＃61;156;
start slave;
show slave status\G;


最后的效果就是&＃xff0c;我们在 master1上面建立 testdb 数据库之后&＃xff0c;其他三个MySQL都同步建立 testdb 数据库了&＃xff0c;接着再建立 mytbl 表&＃xff0c;其他三个也都建立&＃xff0c;往表中插入 insert into mytbl values(1, &＃64;&＃64;hostname); 之后出现了四个数据库的数据不一致&＃xff0c;&＃64;&＃64;hostname是系统变量&＃xff0c;每个系统不一样&＃xff0c;所以不同&＃xff0c;这也验证了bin-log默认的格式是复制 SQL 语句这一说法。

需要注意的一点

mysql 8.0 默认使用 caching_sha2_password 身份验证机制 —— 从原来的 mysql_native_password 更改为 caching_sha2_password。
从 5.7 升级 8.0 版本的不会改变现有用户的身份验证方法&＃xff0c;但新用户会默认使用新的 caching_sha2_password 。
所以在 slave 连接 master的时候&＃xff0c;show slave status\G; 可能会出现&＃xff1a;authentication plugin &＃39;caching_sha2_password’的异常&＃xff0c;这是因为MySQL8.0之后改变了密码验证器&＃xff0c;我们只需要把在主机建立的授权用户的密码验证器改成mysql_native_password就好了&＃xff0c;语句如下&＃xff1a;

ALTER USER &＃39;slave&＃39;&＃64;&＃39;192.168.0.12&＃39; IDENTIFIED WITH mysql_native_password BY &＃39;qkm19981013&＃39;;


MyCat登录访问

balance 设置成 1&＃xff0c;再添加上 写主机信息
<dataHost name&＃61;"host1" maxCon&＃61;"1000" minCon&＃61;"10" balance&＃61;"1" writeType&＃61;"0" dbType&＃61;"mysql" dbDriver&＃61;"native" switchType&＃61;"1" slaveThreshold&＃61;"100"><heartbeat>select user()heartbeat><writeHost host&＃61;"hostM1" url&＃61;"192.168.0.12:3307" user&＃61;"root" password&＃61;"qkm19981013"><readHost host&＃61;"hostS1" url&＃61;"192.168.0.12:3308" user&＃61;"root" password&＃61;"qkm19981013"/>writeHost><writeHost host&＃61;"hostM2" url&＃61;"192.168.0.12:3309" user&＃61;"root" password&＃61;"qkm19981013"><readHost host&＃61;"hostS2" url&＃61;"192.168.0.12:3310" user&＃61;"root" password&＃61;"qkm19981013"/>writeHost>dataHost>