mysql搭建双主双从服务，实现主从复制&&使用mycat实现读写分离

素颜马尾好姑娘i 2022-12-25 12:54 197阅读 0赞

一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。架构图如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70]

# 1. 搭建 MySQL 数据库主从复制（双主双从） #

## 1.1 双主配置 ##

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=需要复制的主数据库名字
    #设置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

## 1.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

## 1.3. 双主机、双从机重启 mysql 服务 ##

## 1.4. 主机从机都关闭防火墙 ##

## 1.5. 在两台主机上建立帐户并授权 slave ##

#在主机MySQL里执行授权命令
    GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
    #查询Master1的状态
    show master status;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 1]  
\#查询Master2的状态  
show master status;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 2]  
分别记录下**File**和**Position**的值  
执行完此步骤后\*\*`不要`\*\*再操作主服务器MYSQL，防止主服务器状态值变化。

## 1.6. 在从机上配置需要复制的主机 ##

Slava1 复制 Master1，Slava2 复制 Master2

#复制主机的命令
    CHANGE MASTER TO MASTER_HOST='主机的IP地址',
    MASTER_USER='slave',
    MASTER_PASSWORD='123123',
    MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

Slava1的复制命令：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 3]  
Slava2的复制命令：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 4]

#启动两台从服务器复制功能
    start slave;
    #查看从服务器状态
    show slave status\G;
    #Slava1的复制Master1

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 5]  
复制Master2：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 6][]  
下面两个参数都是Yes，则说明主从配置成功！  
**Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes**

## 1.7. 两个主机互相复制 ##

Master2 复制 Master1，Master1 复制 Mast  
Master2的复制命令：![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 7]  
Master1的复制命令：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 8]  
Master1的复制Master2：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 9]  
Master1的复制Master2：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 10]  
下面两个参数都是Yes，则说明主从配置成功！  
**Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes**

## 1.8.Master1 主机新建库、新建表、insert 记录，Master2 和从机复制 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 11]

## 1.9.如何停止从服务复制功能 ##

stop slave;

## 1.10.如何重新配置主从 ##

stop slave;
    reset master;

# 2. mycat完成主从复制 #

修改 Mycat 的配置文件 schema.xml:修改的balance属性，通过此属性配置读写分离的类型.

负载均衡类型，目前的取值有4 种：
    （1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
    （2）balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从
    模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
    （3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
    （4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

为了双主双从读写分离balance设置为`1`

<dataNode name="dn1" dataHost="host1" database="testdb" />
     <dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" >
     <heartbeat>select user()</heartbeat>
     
     <writeHost host="hostM1" url="192.168.140.128:3306" user="root" password="123123">
     
     <readHost host="hostS1" url="192.168.140.127:3306" user="root" password="123123" />
     </writeHost>
     <writeHost host="hostM2" url="192.168.140.126:3306" user="root" password="123123">
     
     <readHost host="hostS2" url="192.168.140.125:3306" user="root" password="123123" />
     </writeHost>
     </dataHost>

`balance="1"`: 全部的readHost与stand by writeHost参与select语句的负载均衡。  
`writeType="0"`: 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个  
writeType=“1”，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐  
writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties 。  
switchType=“1”: 1 默认值，自动切换。

*  \-1 表示不自动切换
 *  2 基于 MySQL 主从同步的状态决定是否切换。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 12]

# 3.启动 Mycat #

①控制台启动 ：去 mycat/bin 目录下执行 ./mycat console  
②后台启动 ：去 mycat/bin 目录下 ./mycat start  
为了能第一时间看到启动日志，方便定位问题，我们选择①控制台启动。

启动MyCat： 
    ./mycat start 
    查看启动状态： 
    ./mycat status 
    停止： 
    ./mycat stop 
    重启： 
    ./mycat restart

mycat连接mysql8.0提示密码错误的解决方案（由于安全问题需要添加 --default-auth=mysql\_native\_password）：

mysql -uroot -p123456 -P8066 -h10.1.33.31 --default-auth=mysql_native_password

# 4. 验证读写分离 #

\#在写主机Master1数据库表mytbl中插入带系统变量数据，造成主从数据不一致  
INSERT INTO mytbl VALUES(3,@@hostname);  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 13]  
在Mycat里查询mytbl表,可以看到查询语句在Master2（host81）、Slava1（host80）、Slava2（host82）

主从三个主机间切换：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 14]

# 5. 抗风险能力 #

停止数据库Master1  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 15]  
在Mycat里插入数据依然成功，Master2自动切换为写主机  
INSERT INTO mytbl VALUES(3,@@hostname);  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 16]  
启动数据库Master1  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 17]  
在Mycat里查询mytbl表,可以看到查询语句在Master1（host79）、Slava1（host80）、Slava2（host82）  
主从三个主机间切换：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 18][]  
Master1、Master2 互做备机，负责写的主机宕机，备机切换负责写操作，保证数据库读写分离高可用性。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70]: /images/20221120/40c7f7a6fb75406494c7b68c73eb78b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 1]: /images/20221120/ec9c26bf62a74f988baa0d8f3e95869d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 2]: /images/20221120/c538dc369aa24d8390bb2baf7fcf2c3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201201224217848.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201201224235602.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20201201224350723.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20201201224338763.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20201201224524619.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20201201224540428.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20201201224615784.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20201201224634387.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20201201224843773.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20201201225710887.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20201201225815452.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/20201201225848228.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/20201201225919677.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/20201201225942168.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 17]: https://img-blog.csdnimg.cn/20201201225956973.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg_size_16_color_FFFFFF_t_70 18]: https://img-blog.csdnimg.cn/20201201230038531.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zzandpbg==,size_16,color_FFFFFF,t_70