[B 站公开课] MySQL 数据同步与高可用架构设计

教程 ⋅ 青牛 ⋅ 于 2023-12-19 11:46:23 ⋅ 1083 阅读

公开课回放地址：https://www.bilibili.com/video/BV1eN4y1b7Ub

MySQL 数据同步与高可用架构设计 msyql实现主从同步

1.为什么要同步

1，业务量越来越大，一台机器可以接收的请求是有限的。并且单台机器的I/O也是有瓶颈的，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能

2，做数据热备份

3，如果因为sql编写，导致表锁，那么其他用户就没有办法实现数据读取服务，那么就可以设计多个数据库数据同步，主库负责写，从库负责读

2.主从同步原理

主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步

file

3.同步方式

1，一主一从

file

2，一主多从

file

3，互为主从

file

4，多主多从

file

4.一主多从配置

4.1服务器准备

通过海牛实验室启动三台linux服务器，设置'一主两从'的mysql集群

file

4.2 修改第一台服务器mysql配置文件my.cnf

添加如下配置


#mysql服务器id
server-id=1 
#binlog存放位置
log-bin=/opt/mysql-5.7.39-el7-x86_64/mysql-bin  
#忽略同步的库
binlog-ignore-db=mysql
#需要同步的库
binlog-do-db=hainiu
#同步方式 STATEMENT  ROW  MIXED
binlog_format=ROW

重启mysql

/opt/mysql-5.7.39-el7-x86_64/support-files/mysql.server stop
/opt/mysql-5.7.39-el7-x86_64/support-files/mysql.server start

4.3 修改剩下两台mysql配置文件

添加如下配置：切记server-id要变

server-id=2
relay-log=mysql-relay
relay-log-index=relay-log-bin.index

删除/data/mysql/auto.cnf文件

重启mysql

/opt/mysql-5.7.39-el7-x86_64/support-files/mysql.server stop
/opt/mysql-5.7.39-el7-x86_64/support-files/mysql.server start

4.4 在master机器进行授权

grant replication slave on *.* to 'root'@'%' identified by 'hainiu'; 
flush privileges;

查询Master1的状态 :

show master status;

file
字段含义:

File : 从哪个日志文件开始推送日志文件 
Position ： 从哪个位置开始推送日志 
Binlog_Ignore_DB : 指定不需要同步的数据库

4.5 在slave机器进行开启同步

指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。

change master to master_host= 'mysql-19739', master_user='root', 
master_password='hainiu', master_log_file='mysql-bin.000001', master_log_pos=589;
  1. 开启同步操作
start slave; 
show slave status\G;

file

4.6 验证主从同步

  1. 在主库中创建数据库，创建表，并插入数据 ：
create database hainiu; 

use hainiu; 

create table user( 
    id int(11) not null auto_increment, 
    name varchar(50) not null, 
    sex varchar(1), 
    primary key (id) 
)engine=innodb default charset=utf8; 

insert into user(id,name,sex) values(null,'Tom','1');
insert into user(id,name,sex) values(null,'Trigger','0'); 
insert into user(id,name,sex) values(null,'Dawn','1');
  1. 在从库中查询数据，进行验证 ：

在从库中，可以查看到刚才创建的数据库：

file

并看到数据：

file

Mycat实现读写分离

MyCat 是目前最流行的基于 java 语言编写的数据库中间件，是一个实现了 MySQL 协议 的服务器，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问， 而其后端可以用 MySQL 原生协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数 主流数据库服务器通信，其核心功能是分库分表。配合数据库的主从模式还可实现读写分离。

file

1，环境准备

1.1 安装jdk

rpm -ivh /public/software/java/jdk-8u144-linux-x64.rpm

修改环境变量

export JAVA_HOME=/usr/java/latest
export PATH=$PATH:$JAVA_HOME/bin
#让环境变量立即生效
source /etc/profile

1.2 mycat安装

从官网下载需要的安装包，并且上传到服务器中

解压文件到/usr/local文件夹下
 tar -zxvf  Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz  -C /usr/local/
配置环境变量
vim /etc/profile
添加如下配置信息：
export MYCAT_HOME=/usr/local/mycat
export PATH=$MYCAT_HOME/bin:$PATH

1、修改server.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
                <property name="defaultSchema">TESTDB</property>
        </user>
</mycat:server>

2、修改schema.xml文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="hainiu" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!--master mysql-->
                <writeHost host="hostM1" url="11.237.80.4:3306" user="root"
                                   password="hainiu">
                <!--slaver mysql-->

                        <readHost host="hostS1" url="11.99.173.49:3306" user="root" password="hainiu"></readHost>
                        <readHost host="hostS2" url="11.99.173.37:3306" user="root" password="hainiu"></readHost>
                </writeHost>
        </dataHost>
</mycat:schema>

dataHost标签的balance指的是负载均衡类型，目前的取值有三种：

当balance=0 时，不开启读写分离，所有读操作都发生在当前的writeHost上

当balance=1 ，所有读操作都随机发送到当前的writeHost对应的readHost和备用的writeHost

当balance=2，所有的读操作都随机发送到所有的writeHost,readHost上

当balance=3 ，所有的读操作都只发送到writeHost的readHost上

dataHost标签的writeType指的是写类型，目前的取值有三种：

writeType="0"，所有写操作发送到配置的第一个writeHost，第一个挂了切换到生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties
writeType="1"，所有写操作都随机的发送到配置的writeHost，1.5以后废弃不推荐
writeType="2"，不执行写操作

dataHost标签的switchType指的是自动切换类型，目前的取值有四种：

switchType="-1"，表示不自动切换
switchType="1"，默认值，自动切换
switchType="2"，基于MySQL主从同步的状态决定是否切换

file

修改内存设置

file

修改日志级别为debug ，文件为log4j2.xml

file

1.3启动mycat

mycat的启动有两种方式，一种是控制台启动，一种是后台启动，在初学的时候建议大家使用控制台启动的方式，当配置文件写错之后，可以方便的看到错误，及时修改，但是在生产环境中，使用后台启动的方式比较稳妥。

控制台启动：去mycat/bin目录下执行cd ../

后台启动：去mycat/bin目录下执行 ./mycat start

查看进程：

file

连接mycat

mysql -h linux-40327 -u root -p -P8066

file

查看表：

file

测试读写分离：

测试插入数据

insert into user(id,name,sex) values(null,'jerry','4');

file

file

测试查询数据：

file

file

file

Mysql双主双从配置

file

1 环境准备

再添加三台服务器

file

2 配置两主多从

2.1 修改旧master

将原来一主一从中hainiu数据库删除掉

然后修改原来主数据库Master1的my.cnf配置文件，添加如下配置：

log-slave-updates

file

重启数据库：

/opt/mysql-5.7.39-el7-x86_64/support-files/mysql.server stop
/opt/mysql-5.7.39-el7-x86_64/support-files/mysql.server start

2.2 修改新Master

新master配置如下：

server-id=4
log-bin=/opt/mysql-5.7.39-el7-x86_64/mysql-bin
binlog-ignore-db=mysql
binlog-do-db=hainiu
binlog_format=ROW
log-slave-updates

新slave1，slave2配置如下： 切记server-id要变化

server-id=5
relay-log=mysql-relay
relay-log-index=relay-log-bin.index

删除/data/mysql/auto.cnf文件，重启mysql

/opt/mysql-5.7.39-el7-x86_64/support-files/mysql.server stop
/opt/mysql-5.7.39-el7-x86_64/support-files/mysql.server start

修改新master 在新master机器进行授权

grant replication slave on *.* to 'root'@'%' identified by 'hainiu'; 
flush privileges;

查询Master1的状态 :

show master status;

file

修改两个slave，实现新Master同步

指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。

change master to master_host= 'mysql-19739', master_user='root', 
master_password='hainiu', master_log_file='mysql-bin.000001', master_log_pos=589;
  1. 开启同步操作
start slave; 
show slave status\G;

file

2.3 实现双主master同步

两个主机互相复制

Master1

change master to master_host= 'mysql-19739', master_user='root', 
master_password='hainiu', master_log_file='mysql-bin.000001', master_log_pos=589;

启动两台主服务器复制功能 , 查看主从复制的运行状态

start slave; 
show slave status\G;

file

Master2

change master to master_host= 'mysql-19739', master_user='root', 
master_password='hainiu', master_log_file='mysql-bin.000001', master_log_pos=589;

启动两台主服务器复制功能 , 查看主从复制的运行状态

start slave; 
show slave status\G;

file

2.4 验证双主多从同步

在Master1上创建数据库:

create database hainiu;

file

Master2 中也有hainiu数据库

file

在Master2中创建表；


use hainiu; 

create table user( 
    id int(11) not null auto_increment, 
    name varchar(50) not null, 
    sex varchar(1), 
    primary key (id) 
)engine=innodb default charset=utf8; 

insert into user(id,name,sex) values(null,'Tom','1'); 
insert into user(id,name,sex) values(null,'Trigger','0'); 
insert into user(id,name,sex) values(null,'Dawn','1'); 

insert into user(id,name,sex) values(null,'Jack Ma','1'); 
insert into user(id,name,sex) values(null,'Coco','0'); 
insert into user(id,name,sex) values(null,'Jerry','1');

所有机器都有表

file

3 Mycat实现两主多从配置

3.1 停止mycat

./mycat stop

3.2 修改<dataHost>的 balance属性，

通过此属性配置读写分离的类型 ;

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="hainiu" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!--master mysql1-->
                <writeHost host="hostM1" url="11.237.80.4:3306" user="root"
                                   password="hainiu">
                <!--slaver mysql-->

                        <readHost host="hostS1" url="11.99.173.49:3306" user="root" password="hainiu"></readHost>
                        <readHost host="hostS2" url="11.99.173.37:3306" user="root" password="hainiu"></readHost>
                </writeHost>
                <!--master mysql1-->
                <writeHost host="hostM2" url="11.237.80.34:3306" user="root"
                                   password="hainiu">
                <!--slaver mysql-->

                        <readHost host="hostS3" url="11.26.164.152:3306" user="root" password="hainiu"></readHost>
                        <readHost host="hostS4" url="11.99.173.37:3306" user="root" password="hainiu"></readHost>
                </writeHost>
        </dataHost>
</mycat:schema>

dataHost标签的balance指的是负载均衡类型，目前的取值有三种：

当balance=0 时，不开启读写分离，所有读操作都发生在当前的writeHost上

当balance=1 ，所有读操作都随机发送到当前的writeHost对应的readHost和备用的writeHost

当balance=2，所有的读操作都随机发送到所有的writeHost,readHost上

当balance=3 ，所有的读操作都只发送到writeHost的readHost上

dataHost标签的writeType指的是写类型，目前的取值有三种：

writeType="0"，所有写操作发送到配置的第一个writeHost，第一个挂了切换到生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties
writeType="1"，所有写操作都随机的发送到配置的writeHost，1.5以后废弃不推荐
writeType="2"，不执行写操作

dataHost标签的switchType指的是自动切换类型，目前的取值有四种：

switchType="-1"，表示不自动切换
switchType="1"，默认值，自动切换
switchType="2"，基于MySQL主从同步的状态决定是否切换

测试双主集群：

用mycat插入两条数据

file

file

file

file

停掉master1，再插入数据

file

file

重启master1之后，海牛优先插入master2

file

file

读取数据：

file

file

file

file

file

file

版权声明:原创作品,允许转载，转载时务必以超链接的形式表明出处和作者信息。否则将追究法律责任。来自海汼部落－青牛,http://hainiubl.com/topics/76424
点赞
成为第一个点赞的人吧 :bowtie:
回复数量: 0
    暂无评论~~
    • 请注意单词拼写，以及中英文排版，参考此页
    • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
    • 支持表情，可用Emoji的自动补全, 在输入的时候只需要 ":" 就可以自动提示了 :metal: :point_right: 表情列表 :star: :sparkles:
    • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif，教程
    • 发布框支持本地存储功能，会在内容变更时保存，「提交」按钮点击时清空
    Ctrl+Enter
    新 建 话 题

    作者：青牛

    创始人
    海汼部落创始人，80后程序员一枚，曾就职于金山，喜欢倒腾技术做产品

    青牛 的其他话题

    分类下其他主题

    随机推荐话题

    QQ群1：551936262 海汼部落QQ群1

    关注海汼部落技术社区