windows下搭建数据库双机热备,Mycat实现读写分离

我会带着你远行 2023-01-15 02:17 20阅读 0赞

#                                                           数据库双机热备 #

## 主机: ##

192.168.1.14     和   192.168.1.15

### 1.分别添加配置文件信息 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 1][]

14 服务器配置添加

# 主从复制
    server-id=14 #设置server-id
    log-bin=mysql-bin #开启二进制文件 
    #同步的数据库，除此之外别的不同步（和下面binlog-ignore-db二选一）
    binlog_do_db=testdb 
    auto-increment-increment = 2 #字段变化增量值
    auto-increment-offset = 1    #初始字段ID为1
    slave-skip-errors = all      #忽略所有复制产生的错误

15服务器添加配置

server-id=15 #设置server-id
    log-bin=mysql-bin #开启二进制文件
    binlog_do_db=testdb
    auto-increment-increment = 2 #字段变化增量值
    auto-increment-offset = 2    #初始字段ID为1
    slave-skip-errors = all      #忽略所有复制产生的错误

### 更多配置信息详细描述 ###

#主标服务标识号,必需唯一
    server-id = 1
    
    #因为MYSQL是基于二进制的日志来做同步的,每个日志文件大小为 1G
    log-bin=/var/log/mysql/mysql-bin.log
    
    #要同步的库名
    binlog-do-db = test
    
    #不记录日志的库,即不需要同步的库
    binlog-ignore-db=mysql
    
    #用从属服务器上的日志功能
    log-slave-updates
    
    #经过1日志写操作就把日志文件写入硬盘一次(对日志信息停止一次同步)。n=1是最安全的做法，但效率最低。默认设置是n=0。
    sync_binlog=1
    
    # auto_increment，控制自增列AUTO_INCREMENT的行为用于MASTER-MASTER之间的复制，避免涌现重复值,
    #auto_increment_increment=n有多少台服务器，n就设置为多少,
    #auto_increment_offset＝1设置步长,这里设置为1,这样Master的auto_increment字段发生的数值是:1,3, #5, 7, …等奇数ID
    
    auto_increment_offset=1
    auto_increment_increment=2
    
    #停止镜像处理的数据库
    replicate-do-db = test
    
    #不停止镜像处理的数据库
    replicate-ignore-db= mysql

### 2.重启14.15服务器 ###

### 3.14和15服务器分别创建角色和权限 ###

在mysql的bin目录下,  以管理员身份打开cmd窗口后，运行mysql -uroot -p，输入密码 登陆。

先后执行下面3条命令创立用户供从库连接用（用户名MySlave，密码123456），并刷新权限

14服务器上执行:

CREATE USER 'MySlave'@'192.168.1.15' IDENTIFIED BY '123456';
    GRANT REPLICATION SLAVE ON *.* TO 'MySlave'@'192.168.1.15';
    FLUSH PRIVILEGES;

15服务器上执行:

CREATE USER 'MySlave'@'192.168.1.14' IDENTIFIED BY '123456';
    GRANT REPLICATION SLAVE ON *.* TO 'MySlave'@'192.168.1.14';
    FLUSH PRIVILEGES;

### 4.执行命令: show master status; 分别查看14和15Master状态，记录二进制文件名和位置 ,如图位置 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 2][]

### 5.cmd命令行登陆MySQL数据库，14和15服务器分别执行下面命令进行手动同步, 14连接15的二进制文件名和位置 ,15连接14的二进制文件名和位置 ,执行命令: ###

CHANGE MASTER TO MASTER_HOST='192.168.1.14',MASTER_PORT=3306,MASTER_USER='MySlave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=303322;

**注意:**上面 **MASTER\_LOG\_FILE** 和 **MASTER\_LOG\_POS** 部分,替换成主master的二进制文件名和位置

### 6.  14,15服务器分别启动salve同步进程 ###

start slave;

### 7. 分别查看14和15的slave状态, 执行命令： ###

show slave status\G;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 3][]

### 8.测试 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 4][]

### 9.后记 ###

**彻底清除slave同步复制关系操作！见文章地址:https://www.cnblogs.com/musen/p/11162783.html**

stop slave;

reset slave all;

show slave status\\G;

Emptyset (0,00 sec)

此时真正实现了清除slave同步复制关系！

测试情形一 : 15宕机,  14写入数据 ,然后14也宕机 ,重启15 ,没有恢复数据  , 重启14 , 15恢复数据

测试情形二: 15宕机  ,14写入数据 ,然后14也宕机 ,重启15  , 15写入数据,id和14重复并插入新数据 , 重启14 , 恢复数据 ;   但14/15 id重复行,各自保持原插入数据;

测试情形三: 15宕机,  14修改共有数据 , 然后14也宕机 , 重启15 ,  15也修改共有数据,  14/15两边数据保持一致

重启后恢复时间大概需要一分钟内完成

id建议不要用自增id ,随机id比较好,避免id重复导致数据不一致.

#                                                                       Mycat实现读写分离 #

### 下载mycat 地址 : [http://dl.mycat.org.cn/][http_dl.mycat.org.cn] ###

### 配置mycat server.xml文件 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 5][]

### 配置 schema.xml文件 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 6][]

### 启动mycat ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 7][]

### 启动成功 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 8][]

### Navicat连接mycat ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 9][]

### 打开mycat ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 10][]

由于我后来把备份库从testdb 切换成了 zhlyweb, 所以显示的表都是zhlyweb库的表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 11][]

Navicat12连接mycat打开表会报错,错误信息说表不存在 , 这是navicat版本问题,换低版本Navicat就可以了

测试双机热备高可用,宕机14库 ,大概30秒后切换主从 读写正常.

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70]: /images/20221022/3def8c4998f34a8096309ee3af4f6dba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 1]: /images/20221022/b0e8bfdd5cd643ceba510a6e397bfc18.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 2]: /images/20221022/1f6045b6c3fa4fbd946a4870f6f209c6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 3]: /images/20221022/4d2ff05df216434b8bfaa1e3db6a4ec7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 4]: /images/20221022/21162739d2ab44cb9b850ea7157fd2be.png
[http_dl.mycat.org.cn]: http://dl.mycat.org.cn/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 5]: /images/20221022/21a99eb5435840c8846b3b9b7ebe96a7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 6]: /images/20221022/479e3341dc5048fd8dfe18c5628faa55.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 7]: /images/20221022/688797dc35304fefaf87c08bab48e46e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 8]: /images/20221022/9a9ae7a7731d4523a824b94fa9d8c92b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 9]: /images/20221022/144245fdf1ff41a5bfebf9a74bde98bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 10]: /images/20221022/102d2250a0304dc5955ef7d561b7feac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 11]: /images/20221022/0a41f25960ef47bcb2533465f5943f12.png