3、重新启动Mycat
[root@centos7-mysql-1 bin]# ./mycat restart Stopping mycat2... Stopped mycat2. Starting mycat2...
4、 验证读写分离
#(1）在写主机数据库表mytbl中插入带系统变量数据，造成主从数据不一致
INSERT INTO mytbl VALUES(2,@@hostname) ;
(2)在Mycat里查询mytbl表，可以看到查询语句在主从两个主机间切换
3.2 双主双从
一个主机 m1 用于处理所有写请求， 它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后， m2 主机负责写请求， m1、 m2 互为备机。 架构图如下
| 编号 | 角色 | IP地址 | 机器名 |
| 1 | Master1 | 192.168.253.139 | mysql1 |
| 2 | Slavel | 192.168.253.140 | mysqlS1 |
| 3 | Master2 . | 192.168.253.141. | mysql1-2 |
| 4 | Slave2 . | 192.168.253.142, | mysqlS1-2 |
1、 搭建 MySQL 数据库主从复制（双主双从）
先结束一主一从
stop slave; reset master;
删除dbtest
① 双主机配置
Master1配置
修改配置文件： vim /etc/my.cnf #主服务器唯一ID server-id=1 #启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=mysql binlog-ignore-db=information_schema #设置需要复制的数据库 需要复制的主数据库名字（未创建的） binlog-do-db=mydb1 #设置logbin格式 binlog_format=STATEMENT # 在作为从数据库的时候， 有写入操作也要更新二进制日志文件 log-slave-updates #表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1， 取值范围是1 .. 65535 auto-increment-increment=2 # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535 auto-increment-offset=1
Master2配置
修改配置文件： vim /etc/my.cnf #主服务器唯一ID server-id=3 #启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=mysql binlog-ignore-db=information_schema #设置需要复制的数据库 binlog-do-db=需要复制的主数据库名字 #设置logbin格式 binlog_format=STATEMENT # 在作为从数据库的时候，有写入操作也要更新二进制日志文件 log-slave-updates #表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535 auto-increment-increment=2 # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535 auto-increment-offset=2
② 双从机配置
Slave1配置
修改配置文件： vim /etc/my.cnf #从服务器唯一ID server-id=2 #启用中继日志 relay-log=mysql-relay
Slave2配置
修改配置文件： vim /etc/my.cnf #从服务器唯一ID server-id=4 #启用中继日志 relay-log=mysql-relay
③ 双主机、 双从机重启 mysql 服务
systemctl restart mysqld systemctl status mysqld
④ 主机从机都关闭防火墙
systemctl status firewalld
⑤ 在两台主机上建立帐户并授权 slave
#在主机MySQL里执行授权命令 CREATE USER 'slave2'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'%'; #此语句必须执行。否则见下面。< ALTER USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; flush privileges;
#查询Master1的状态 show master status;
#查询Master2的状态 show master status;
⑥ 在从机上配置需要复制的主机
Slava1 复制 Master1， Slava2 复制 Master2
从1
CHANGE MASTER TO MASTER_HOST='192.168.253.139', MASTER_USER='slave2', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=156;
从2
CHANGE MASTER TO MASTER_HOST='192.168.253.141', MASTER_USER='slave2', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=156;
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes
⑦ 两个主机互相复制
Master2 复制 Master1， Master1 复制 Master2
M1
CHANGE MASTER TO MASTER_HOST='192.168.253.141', MASTER_USER='slave2', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=156; mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G;
M2
CHANGE MASTER TO MASTER_HOST='192.168.253.139', MASTER_USER='slave2', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=156; mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G;
⑨ 如何停止从服务复制功能
stop slave;
stop slave; reset master;
⑧ Master1 主机新建库、新建表、 insert 记录， Master2 和从机复制
M1
mysql> create database mydb1; Query OK, 1 row affected (0.00 sec) mysql> use mydb1; Database changed mysql> create table mytbl(id int,name varchar(50)); Query OK, 0 rows affected (0.02 sec) mysql> insert into mytbl values (1,'zhang3'); Query OK, 1 row affected (0.02 sec) mysql>
其他
mysql> show databases; +----------------------+ | Database | +----------------------+ | atguigu_master_slave | | atguigudb1 | | atguigudb2 | | atguigudb3 | | atguigudb33 | | dbtest1 | | dbtest2 | | information_schema | | mycat | | mydb1 | | mysql | | performance_schema | | sys | | testdb | +----------------------+ 14 rows in set (0.41 sec) mysql> use mydb1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from mytbl; +------+--------+ | id | name | +------+--------+ | 1 | zhang3 | +------+--------+ 1 row in set (0.01 sec) mysql>
2、 修改 Mycat 的集群配置实现多种主从
Mycat2.0的特点把集群概念凸显了出来，和mysql主从复制、集群配合实现多节点读写分离
（1）->双主双从集群角色划分
*m1：主机
*m2：备机，也负责读
*s1，s2：从机
（3）增加两个数据源
使用注解方式添加数据源
#登录Mycat，注解方式添加数据源，指向从机 /*+ mycat:createDataSource { "name" :" rwSepw2" ，"url" :"jdbc:mysql://192.168.253.141:3306/mydb1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true" , "user" "root", "password":"123456"}*/ ; /*+ mycat:createDataSource { "name" : "rwSepr2" ，"ur1" :"jdbc:mysql://192.168.253.142:3306/mydb1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true" , "user":"root", "password":"123456"}·*/ ; #查询配置数据源结果 /*＋·mycat:showDataSources {0}*/ ;
手动配置
/usr/local/mycat/conf/datasources
{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ_WRITE", "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"rwSepw2", #一定要看好 "password":"123456", "type":"JDBC", "url":"jdbc:mysql://192.168.253.141:3306/mydb1?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"root", "weight":0 }
更新集群信息,添加dr0从节点.实现读写分离
#更新集群信息,添加dr0从节点.- /*! mycat:createCluster{"name" :"prototype","masters" :["rwSepw"],"replicas":["rwSepr"]} */ ; #查看配置集群信息( /*+ mycat:showClusters {} */; #查看集群配置文件 vim /usr/local/mvcat/conf/clusters/prototype.clust
手动配置
/usr/local/mycat/conf/clusters
{ "clusterType":"MASTER_SLAVE", "heartbeat":{ "heartbeatTimeout":1000, "maxRetry":3, "minSwitchTimeInterval":300, "slaveThreshold":0 }, "masters":[ "rwSepw","rwSepw2" ], "maxCon":200, "name":"prototype", "readBalanceType":"BALANCE_ALL", "replicas":[ "rwSepw2","rwSepr","rwSepr2" ], "switchType":"SWITCH" }
3、重新启动Mycat
[root@centos7-mysql-1 bin]# ./mycat restart Stopping mycat2... Stopped mycat2. Starting mycat2...
4、 验证读写分离
#(1）在写主机数据库表mytbl中插入带系统变量数据，造成主从数据不一致
INSERT INTO mytbl VALUES(2,@@hostname) ;
(2)在Mycat里查询mytbl表，可以看到查询语句在主从两个主机间切换
mysql> select * from mytbl; +------+-----------------+ | id | name | +------+-----------------+ | 1 | zhang3 | | 2 | centos7-mysql-1 | +------+-----------------+ 2 rows in set (0.01 sec) mysql> select * from mytbl; +------+--------------------+ | id | name | +------+--------------------+ | 1 | zhang3 | | 2 | centos7-mysql-S1-2 | +------+--------------------+ 2 rows in set (0.01 sec) mysql> select * from mytbl; +------+------------------+ | id | name | +------+------------------+ | 1 | zhang3 | | 2 | centos7-mysql-S1 | +------+------------------+ 2 rows in set (0.01 sec)
