MySQL技能完整学习列表13、MySQL高级特性——1、分区表(Partitioning)——2、复制(Replication)——3、集群(Clustering)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
应用型负载均衡 ALB,每月750个小时 15LCU
简介: MySQL技能完整学习列表13、MySQL高级特性——1、分区表(Partitioning)——2、复制(Replication)——3、集群(Clustering)



 

1、分区表(Partitioning)

分区表是MySQL中一种将数据分散存储在多个物理子表中的技术,但从逻辑上看,它们仍然被当作一个表来对待。这种技术可以极大地提高大型数据库的性能、管理和可维护性。

为什么使用分区表

  1. 性能提升:通过将数据分散到多个物理部分,查询可以并行处理,从而提高性能。
  2. 管理方便:可以独立地备份、恢复或优化某一个分区,而不影响其他分区。
  3. 数据归档:对于具有时间序列数据的应用,可以使用分区表按日期归档数据。
  4. 均匀I/O分布:可以将不同的分区放在不同的物理设备上,从而平衡I/O负载。

分区类型

  1. RANGE分区:基于列的值范围来分区。
  2. LIST分区:基于列的离散值来分区。
  3. HASH分区:基于用户定义的表达式的哈希值来分区。
  4. KEY分区:类似于HASH分区,但MySQL服务器提供哈希函数。
  5. COLUMNS分区:可以使用多个列的值进行RANGE或LIST分区。

示例

1. RANGE分区示例

假设有一个销售记录表sales_records,其中有一个sale_date列记录了销售日期。我们可以按月进行RANGE分区:

CREATE TABLE sales_records (
    id INT NOT NULL,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
)
PARTITION BY RANGE( YEAR(sale_date) * 12 + MONTH(sale_date) ) (
    PARTITION p0 VALUES LESS THAN (2023*12+1),
    PARTITION p1 VALUES LESS THAN (2024*12+1),
    PARTITION p2 VALUES LESS THAN (2025*12+1)
);
2. LIST分区示例

如果我们按照地区销售,可以使用LIST分区:

CREATE TABLE sales_records (
    id INT NOT NULL,
    region ENUM('North', 'South', 'East', 'West') NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
)
PARTITION BY LIST(region) (
    PARTITION pNorth VALUES IN ('North'),
    PARTITION pSouth VALUES IN ('South'),
    PARTITION pEast VALUES IN ('East'),
    PARTITION pWest VALUES IN ('West')
);
3. HASH分区示例

假设我们想根据product_id的哈希值进行分区:

CREATE TABLE sales_records (
    id INT NOT NULL,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
)
PARTITION BY HASH(product_id)
PARTITIONS 4;  -- 创建4个分区

以上仅为简单的示例,实际应用中可能需要考虑更复杂的场景和需求。在使用分区表之前,建议深入了解其工作原理和限制,以确保它适合你的应用场景。

2、复制(Replication)

MySQL的复制功能允许数据从一个MySQL数据库服务器(称为主服务器或Master)复制到一个或多个MySQL数据库服务器(称为从服务器或Slave)。这种复制是异步的,并且从服务器可以处理来自应用的读请求,从而分摊主服务器的读负载。

为什么使用复制

  1. 负载均衡:通过将读请求分发到多个从服务器,可以提高应用的整体吞吐量。
  2. 数据备份:可以使用从服务器作为数据备份,防止主服务器发生故障。
  3. 数据分析:可以在从服务器上执行复杂的数据分析查询,而不影响主服务器的性能。

复制类型

  1. 基于语句的复制(Statement-Based Replication, SBR):复制SQL语句到从服务器并执行。
  2. 基于行的复制(Row-Based Replication, RBR):复制实际更改的行数据到从服务器。
  3. 混合复制(Mixed-Based Replication):根据情况选择SBR或RBR。

如何设置复制

1. 配置主服务器

编辑my.cnfmy.ini配置文件:

[mysqld]
server-id=1                 # 服务器唯一ID
log-bin=mysql-bin           # 启用二进制日志
binlog-do-db=mydatabase     # 需要复制的数据库名

重启MySQL服务。

创建复制用户并授权:

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

查看主服务器状态:

SHOW MASTER STATUS;

记下FilePosition的值,稍后在从服务器上配置时会用到。

2. 配置从服务器

编辑my.cnfmy.ini配置文件:

[mysqld]
server-id=2                 # 服务器唯一ID,确保与主服务器不同
relay-log=mysql-relay-bin   # 中继日志文件名

重启MySQL服务。

在从服务器上配置主服务器信息:

CHANGE MASTER TO 
    MASTER_HOST='master_ip_address', 
    MASTER_USER='repl_user', 
    MASTER_PASSWORD='password', 
    MASTER_LOG_FILE='noted_file_from_master_status', 
    MASTER_LOG_POS=noted_position_from_master_status;

启动从服务器复制线程:

START SLAVE;

检查从服务器状态:

SHOW SLAVE STATUS\G;

确保Slave_IO_RunningSlave_SQL_Running的值都是“Yes”。如果是其他值,检查错误日志以解决问题。

注意事项与监控

  1. 数据一致性:使用复制时,必须确保主从服务器的数据保持一致。可以使用工具如pt-table-checksum来检查数据一致性。
  2. 网络延迟:如果主从服务器之间存在网络延迟,可能导致复制延迟。可以监控Seconds_Behind_Master指标来检查延迟情况。

3、集群(Clustering)

MySQL集群是一种数据库架构,旨在通过在多台服务器之间分布数据和查询负载来提高性能、可靠性和可扩展性。这种架构通常用于需要高可用性、容错和可扩展性的大型应用。

为什么使用集群

  1. 高可用性:通过冗余配置,可以在服务器故障时保持服务运行。
  2. 负载均衡:通过在多个节点之间分布查询,可以提高查询吞吐量。
  3. 可扩展性:可以方便地添加更多服务器来扩展性能。
  4. 容错性:通过数据复制,可以在硬件故障时防止数据丢失。

MySQL集群类型

  1. MySQL Replication Cluster:基于主从复制,用于读写分离和负载均衡。
  2. MySQL NDB Cluster:一个高性能分布式数据库系统,使用NDB存储引擎。
  3. MySQL Group Replication:一个基于多主复制的插件,提供同步复制和自动故障检测。
  4. InnoDB Cluster:一个集成的解决方案,结合了Group Replication、MySQL Router和MySQL Shell。

示例:设置InnoDB Cluster

1. 安装MySQL

在每个节点上安装MySQL。确保所有节点上的MySQL版本相同。

2. 配置MySQL服务器

编辑my.cnf文件,确保以下设置:

[mysqld]
server-id=1                 # 唯一服务器ID,每个节点不同
gtid_mode=ON               # 启用GTID模式
enforce_gtid_consistency=ON # 强制GTID一致性
log_bin=mysql-bin          # 启用二进制日志
3. 创建复制用户

在主节点上执行:

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
4. 配置InnoDB Cluster

使用MySQL Shell:

mysqlsh --uri root@localhost:3306

在MySQL Shell中执行:

var cluster = dba.createCluster('myCluster');
cluster.addInstance('root@node2:3306'); // 添加其他节点
cluster.status(); // 检查集群状态
5. 测试集群

在主节点上创建数据库和表,并插入一些数据。然后,从其他节点上查询数据,以确保复制正常工作。

注意事项与监控

  1. 网络稳定性:集群中的节点需要稳定的网络连接以确保数据一致性。
  2. 监控复制状态:定期检查复制状态以确保数据同步。可以使用SHOW STATUS命令或第三方监控工具。
  3. 负载均衡:使用负载均衡器(如MySQL Router)来分发查询到不同的节点,以提高性能。
  4. 扩展性计划:在设计集群时考虑未来的扩展需求,以确保可以平滑地添加新节点。
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
11天前
|
监控 关系型数据库 MySQL
云数据库:从零到一,构建高可用MySQL集群
在互联网时代,数据成为企业核心资产,传统单机数据库难以满足高并发、高可用需求。云数据库通过弹性扩展、分布式架构等优势解决了这些问题,但也面临数据安全和性能优化挑战。本文介绍了如何从零开始构建高可用MySQL集群,涵盖选择云服务提供商、创建实例、配置高可用架构、数据备份恢复及性能优化等内容,并通过电商平台案例展示了具体应用。
|
5月前
|
分布式计算 关系型数据库 MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
76 3
|
5月前
|
消息中间件 分布式计算 关系型数据库
大数据-140 - ClickHouse 集群 表引擎详解5 - MergeTree CollapsingMergeTree 与其他数据源 HDFS MySQL
大数据-140 - ClickHouse 集群 表引擎详解5 - MergeTree CollapsingMergeTree 与其他数据源 HDFS MySQL
87 0
|
3月前
|
SQL 安全 关系型数据库
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
事务是MySQL中一组不可分割的操作集合,确保所有操作要么全部成功,要么全部失败。本文利用SQL演示并总结了事务操作、事务四大特性、并发事务问题、事务隔离级别。
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
|
3月前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
|
4月前
|
关系型数据库 MySQL
mysql事务特性
原子性:一个事务内的操作统一成功或失败 一致性:事务前后的数据总量不变 隔离性:事务与事务之间相互不影响 持久性:事务一旦提交发生的改变不可逆
|
4月前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前,自增变量(`AUTO_INCREMENT`)的行为在服务器重启后可能会发生变化,导致意外结果。MySQL 8.0 引入了自增变量的持久化特性,将其信息存储在数据字典中,确保重启后的一致性。这提高了开发和管理的稳定性,减少了主键冲突和数据不一致的风险。默认情况下,MySQL 8.0 启用了这一特性,但在升级时需注意行为变化。
116 1
|
5月前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
179 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
5月前
|
Java 关系型数据库 MySQL
springboot学习五:springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库,并进行基本的增删改查操作的教程。
934 0
springboot学习五:springboot整合Mybatis 连接 mysql数据库
|
5月前
|
Java 关系型数据库 MySQL
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
302 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql