Mysql集群部署实现主从复制读写分离分表分库 2

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: Mysql集群部署实现主从复制读写分离分表分库

3 主从复制

主从复制的概念


为了使用Mycat进行读写分离,我们先要配置MySQL数据库的主从复制。

从服务器自动同步主服务器的数据,从而达到数据一致。

进而,我们可以写操作时,只操作主服务器,而读操作,就可以操作从服务器了。

原理:主服务器在处理数据时,生成binlog日志,通过对日志的备份,实现从服务器的数据同步。


2111d857be61478a873440ed4bba3e70.png

主服务器的配置


在第一个服务器上,编辑mysql配置文件

  // 编辑mysql配置文件
  vi /etc/my.cnf
  //在[mysqld]下面加上:
  log-bin=mysql-bin # 开启复制操作
  server-id=1 # master is 1
  innodb_flush_log_at_trx_commit=1
  sync_binlog=1

登录mysql,创建用户并授权

  // 登录mysql
  mysql -u root -p
  // 去除密码权限
  SET GLOBAL validate_password_policy=0;
  SET GLOBAL validate_password_length=1;
  // 创建用户
  CREATE USER 'hm'@'%' IDENTIFIED BY 'itheima';
  // 授权
  GRANT ALL ON *.* TO 'hm'@'%';

重启mysql服务,登录mysql服务

  // 重启mysql
  service mysqld restart
  // 登录mysql
  mysql -u root -p

查看主服务器的配置

  // 查看主服务器配置
  show master status;

2d614073bf2f4ef1803cceb6e32ef942.png

从服务器的配置


在第二个服务器上,编辑mysql配置文件

// 编辑mysql配置文件
vi /etc/my.cnf
// 在[mysqld]下面加上:
server-id=2

登录mysql

// 登录mysql
mysql -u root -p
// 执行
use mysql;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
source /usr/share/mysql/mysql_system_tables.sql;


重启mysql,重新登录,配置从节点

// 重启mysql
service mysqld restart
// 重新登录mysql
mysql -u root -p
// 执行
change master to master_host='主服务器ip地址',master_port=3306,master_user='hm',master_password='itheima',master_log_file='mysql-bin.000001',master_log_pos=4642;

重启mysql,重新登录,开启从节点

// 重启mysql
service mysqld restart
// 重新登录mysql
mysql -u root -p
// 开启从节点
start slave;
// 查询结果
show slave status\G;
//Slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功。

a370f710ade2408da60f1a1336e204d8.png


测试


sqlyog连接主服务器

-- 主服务器创建db1数据库,从服务器会自动同步
CREATE DATABASE db1;

sqlyog连接从服务器

-- 从服务器创建db2数据库,主服务器不会自动同步
CREATE DATABASE db2;

启动失败的解决方案

启动失败:Slave_IO_Running为 NO 
方法一:重置slave
slave stop;
reset slave;
start slave ;
方法二:重设同步日志文件及读取位置
slave stop;
change master to master_log_file=’mysql-bin.000001’, master_log_pos=1;
start slave ;

4 读写分离

读写分离的概念


写操作只写入主服务器,读操作读取从服务器。

在主服务器上修改server.xml


user标签主要用于定义登录mycat的用户和权限。如上面定义用户名mycat和密码123456,该用户可以访问的schema的HEIMADB逻辑库。

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">HEIMADB</property>
    <!-- 表级 DML 权限设置 -->
    <!--    
    <privileges check="false">
      <schema name="TESTDB" dml="0110" >
        <table name="tb01" dml="0000"></table>
        <table name="tb02" dml="1111"></table>
      </schema>
    </privileges>   
     -->
</user>

在主服务器上修改schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
  <dataNode name="dn1" dataHost="localhost1" database="db1" />
  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- 主服务器进行写操作 -->
    <writeHost host="hostM1" url="localhost:3306" user="root"
           password="itheima">
    <!-- 从服务器负责读操作 -->
    <readHost host="hostS1" url="192.168.203.135:3306" user="root" password="itheima" />
    </writeHost>
  </dataHost>
</mycat:schema>

配置详解


schema标签逻辑库的概念和mysql数据库中Datebase的概念相同,我们在查询这两个逻辑库中的表的时候,需要切换到该逻辑库下才可以查到所需要的表。


dataNode属性:该属性用于绑定逻辑库到某个具体的database上。


dataNode标签: dataNode标签定义了mycat中的数据节点,也就是数据分片。一个dataNode标签就是一个独立的数据分片。


name属性:定义数据节点的名字,这个名字需要是唯一的,我们需要在table标签上应用这个名字,来建立表与分片对应的关系。


dataHost属性:该属性用于定义该分片属于那个数据库实例,属性值是引用datahost标签定义的name属性。


database属性:该属性用于定义该分片属于那个具体数据库实例上的具体库,因为这里使用两个纬度来定义分片,就是:实例+具体的库。因为每个库上建立的表和表结构是一样的。所以这样做就可以轻松的对表进行水平拆分。


dataHost标签:该标签在mycat逻辑库中也是作为最底层的标签存在,直接定义了具体的数据库实例、读写分离配置和心跳语句。


balance属性: 负载均衡类型

balance=0: 不开启读写分离,所有读操作都发送到当前可用的writeHost上。

balance=1: 全部的readHost与Stand by writeHost都参与select语句的负载均衡

balance=2: 所有的读操作都随机在writeHost,readHost上分发。

balance=3: 所有的读请求都随机分配到writeHost对应的readHost上执行,writeHost不负担读压力。


switchType属性:

-1:表示不自动切换。

1 :默认值,表示自动切换

2:表示基于MySQL主从同步状态决定是否切换,心跳语句: show slave status.

3:表示基于mysql galary cluster的切换机制,适合mycat1.4之上的版本,心跳语句show status like “%esrep%”;


writeHost标签,readHost标签:这两个标签指定后端数据库的相关配置给mycat,用于实例化后端连接池。唯一不同的是,writeHost指定写实例、readHost指定读实例,组合这些读写实例来满足系统的要求。


host属性:用于标识不同的实例,对于writehost,一般使用M1;对于readhost一般使用S1.

url属性:后端实例连接地址,如果使用native的dbDriver,则一般为address:port这种形式,用JDBC或其他的dbDriver,则需要特殊指定。当使用JDBC时则可以这么写:jdbc:mysql://localhost:3306/。

user属性:后端存储实例的用户名。

password属性:后端存储实例的密码

测试


重启主服务器的mycat

// 重启mycat
cd /root/mycat/bin
./mycat restart
// 查看端口监听
netstat -ant|grep 8066

sqlyog连接mycat

-- 创建学生表
CREATE TABLE student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
);
-- 查询学生表
SELECT * FROM student;
-- 添加两条记录
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');
-- 停止主从复制后,添加的数据只会保存到主服务器上。
INSERT INTO student VALUES (NULL,'王五');

sqlyog连接主服务器

-- 主服务器:查询学生表,可以看到数据
SELECT * FROM student;

sqlyog连接从服务器

-- 从服务器:查询学生表,可以看到数据(因为有主从复制)
SELECT * FROM student;
-- 从服务器:删除一条记录。(主服务器并没有删除,mycat中间件查询的结果是从服务器的数据)
DELETE FROM student WHERE id=2;

5 分库分表

分库分表的概念


将庞大的数据进行拆分

水平拆分:根据表的数据逻辑关系,将同一表中的数据按照某种条件,拆分到多台数据库服务器上,也叫做横向拆分。例如:一张1000万的大表,按照一模一样的结构,拆分成4个250万的小表,分别保存到4个数据库中。

垂直拆分:根据业务的维度,将不同的表切分到不同的数据库之上,也叫做纵向拆分。例如:所有的订单都保存到订单库中,所有的用户都保存到用户库中,同类型的表保存在同一库,不同的表分散在不同的库中。

Mycat水平拆分


修改主服务器的server.xml


0:本地文件方式


在mycat/conf/sequence_conf.properties文件中:

GLOBAL.MINDI=10000最小值

GLOBAL.MAXID=20000最大值,建议修改到9999999999


1:数据库方式


分库分表中保证全局主键自增唯一,但是需要执行mycat函数,配置sequence_db_conf.properties


2:时间戳方式


mycat实现的时间戳,建议varchar类型,要注意id的长度

<!-- 修改主键的方式 -->
<property name="sequnceHandlerType">0</property>

修改主服务器的sequence_conf.properties

#default global sequence
GLOBAL.HISIDS=      # 可以自定义关键字
GLOBAL.MINID=10001  # 最小值
GLOBAL.MAXID=20000  # 最大值
GLOBAL.CURID=10000

修改主服务器的schema.xml

table标签定义了逻辑表,所有需要拆分的表都需要在这个标签中定义。

rule属性:拆分规则。mod-long是拆分规则之一,主键根据服务器数量取模,在rule.xml中指定。如果是3个数据库,那么数据取模后,平均分配到三个库中。

name属性:定义逻辑表的表名,这个名字就如同在数据库中执行create table命令指定的名字一样,同一个schema标签中定义的表名必须是唯一的。

dataNode属性: 定义这个逻辑表所属的dataNode,该属性的值需要和dataNode标签中name属性的值相互对应。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100">
    <table name="product" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/>
  </schema>
  <dataNode name="dn1" dataHost="localhost1" database="db1" />
  <dataNode name="dn2" dataHost="localhost1" database="db2" />
  <dataNode name="dn3" dataHost="localhost1" database="db3" />
  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- write -->
    <writeHost host="hostM1" url="localhost:3306" user="root"
           password="itheima">
    <!-- read -->
    <readHost host="hostS1" url="192.168.203.135:3306" user="root" password="itheima" />
    </writeHost>
  </dataHost>
</mycat:schema>

修改主服务器的rule.xml

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- 数据库的数量 -->
    <property name="count">3</property>
</function>

测试


mycat操作

-- 创建product表
CREATE TABLE product(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  price INT
);
-- 添加6条数据
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'苹果手机',6999);
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'华为手机',5999); 
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'三星手机',4999); 
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'小米手机',3999); 
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'中兴手机',2999); 
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'OOPO手机',1999); 
-- 查询product表
SELECT * FROM product; 

主服务器操作

-- 在不同数据库中查询product表
SELECT * FROM product;

从服务器操作

-- 在不同数据库中查询product表
SELECT * FROM product;

Mycat垂直拆分


修改主服务器的schema

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100">
    <table name="product" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/>
    <!-- 动物类数据表 -->
    <table name="dog" primaryKey="id" autoIncrement="true" dataNode="dn4" />
    <table name="cat" primaryKey="id" autoIncrement="true" dataNode="dn4" />
       <!-- 水果类数据表 -->
    <table name="apple" primaryKey="id" autoIncrement="true" dataNode="dn5" />
    <table name="banana" primaryKey="id" autoIncrement="true" dataNode="dn5" />
  </schema>
  <dataNode name="dn1" dataHost="localhost1" database="db1" />
  <dataNode name="dn2" dataHost="localhost1" database="db2" />
  <dataNode name="dn3" dataHost="localhost1" database="db3" />
  <dataNode name="dn4" dataHost="localhost1" database="db4" />
  <dataNode name="dn5" dataHost="localhost1" database="db5" />
  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- write -->
    <writeHost host="hostM1" url="localhost:3306" user="root"
           password="itheima">
    <!-- read -->
    <readHost host="hostS1" url="192.168.203.135:3306" user="root" password="itheima" />
    </writeHost>
  </dataHost>
</mycat:schema>

测试


-- 创建dog表
CREATE TABLE dog(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO dog(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'哈士奇');
-- 查询dog表
SELECT * FROM dog;
-- 创建cat表
CREATE TABLE cat(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO cat(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'波斯猫');
-- 查询cat表
SELECT * FROM cat;
-- 创建apple表
CREATE TABLE apple(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO apple(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'红富士');
-- 查询apple表
SELECT * FROM apple;
-- 创建banana表
CREATE TABLE banana(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO banana(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'香蕉');
-- 查询banana表
SELECT * FROM banana;

sqlyog连接主服务器

-- 查询dog表
SELECT * FROM dog;
-- 查询cat表
SELECT * FROM cat;
-- 查询apple表
SELECT * FROM apple;
-- 查询banana表
SELECT * FROM banana;

sqlyog连接从服务器

-- 查询dog表
SELECT * FROM dog;
-- 查询cat表
SELECT * FROM cat;
-- 查询apple表
SELECT * FROM apple;
-- 查询banana表
SELECT * FROM banana;
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
mysql主从复制概述和配置
【10月更文挑战第22天】MySQL 主从复制是一种将主服务器的数据复制到一个或多个从服务器的技术,实现读写分离,提高系统性能和可用性。主服务器记录变更日志,从服务器通过 I/O 和 SQL 线程读取并应用这些变更。适用于读写分离、数据备份和恢复、数据分析等场景。配置步骤包括修改配置文件、创建复制用户、配置从服务器连接主服务器并启动复制进程。
115 1
|
2月前
|
监控 关系型数据库 MySQL
深入了解MySQL主从复制:构建高效稳定的数据同步架构
深入了解MySQL主从复制:构建高效稳定的数据同步架构
138 1
|
2月前
|
负载均衡 监控 关系型数据库
MySQL 官宣:支持读写分离了!!
【10月更文挑战第8天】MySQL的读写分离功能显著提升了数据库性能、可用性和可靠性。通过将读写操作分配至不同服务器,有效减轻单个服务器负载,提高响应速度与吞吐量,并增强系统稳定性。此外,它还支持便捷的扩展方式,可通过增加只读服务器提升读操作性能。实现读写分离的方法包括软件层面(如使用数据库中间件)和硬件层面(使用独立服务器)。使用时需注意数据一致性、负载均衡及监控管理等问题。
157 0
|
2天前
|
关系型数据库 MySQL 数据库
docker高级篇(大厂进阶):安装mysql主从复制
docker高级篇(大厂进阶):安装mysql主从复制
46 24
|
2月前
|
存储 关系型数据库 MySQL
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
111 1
MySQL主从复制原理和使用
|
2月前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
Mysql中搭建主从复制原理和配置
|
3月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
125 6
|
11天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
81 15
|
5天前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
|
12天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
下一篇
DataWorks