1. Johngo学长首页
  2. 技术点详细复盘专题
  3. 技术杂谈

MySQL的主从复制和分库分表初探

• 技术杂谈 • 阅读 113

主从复制 + 分库分表

要讲主从复制，首先来看看MySQL自带的日志文件。

日志

错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志文件。

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置：

show variables like '%log_error%';

通过tail指令查看日志文件的尾部记录的日志：

tail -50 /var/log/mysqld.log

实时查看文件尾部记录的日志：

tail -f /var/log/mysqld.log

二进制日志

基本概述

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。在MySQL8版本中，默认二进制日志是开启着的。

DDL：例如创建数据库、创建表、修改表等操作；
DML：增删改操作；

有什么用？

  • 数据库灾难时的数据恢复，一旦数据库崩了，可通过二进制日志进行数据恢复；
  • 用于 MySQL 的主从复制；

查看二进制日志相关信息：

show variables like '%log_bin%';

log_bin_basename：当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文件名需要在该basename的基础上加上编号(编号从000001开始)。
log_bin_index：binlog的索引文件，里面记录了当前服务器关联的 binlog 文件有哪些。

格式

MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式 含义 STATEMENT 基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中 ROW 基于行的日志记录，记录的是每一行的数据变更之前和之后的样子。（
默认

） MIXED 混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录

默认的日志记录格式采用的是”ROW”，可以通过命令 show variables like '%binlog_format%';查看；

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set, 1 warning (0.11 sec)

如果我们需要自定义配置二进制日志的格式，只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。

查看二进制日志

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看，具体语法：

mysqlbinlog [ 参数选项 ] logfilename

参数选项：
    -d 指定数据库名称，只列出指定的数据库相关操作。
    -o 忽略掉日志中的前n行命令。
    -v 将行事件(数据变更)重构为SQL语句 （如果是ROW格式的，需要加上该参数）
    -vv 将行事件(数据变更)重构为SQL语句，并输出注释信息

删除二进制日志

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

指令 含义 reset master 删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001 重新开始 purge master logs to ‘binlog.*’ 删除 * 编号之前的所有日志 purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ 删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志

也可以在 mysql 的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。默认二进制日志只存放30天，即2592000s，30天后自动删除。

查看过期时间
show variables like '%binlog_expire_logs_seconds%';

查询日志

查询日志中记录了客户端的所有操作语句（所有的增删改查以及DDL语句都会记录），而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。

show variables like '%general%';

如果需要开启查询日志，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启
general_log=1
设置日志的文件名 ， 如果没有指定， 默认的文件名为
host_name.log general_log_file=mysql_query.log

开启了查询日志之后，在MySQL的数据存放目录，也就是 /var/lib/mysql/ 目录下就会出现 mysql_query.log 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。

慢查询日志

顾名思义，记录的就是执行效率比较低，速度较慢的sql日志。慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为10 秒，最小为 0， 精度可以到微秒。

如果需要开启慢查询日志，需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数：

开启慢查询日志
slow_query_log=1
设置慢查询日志的时间参数为2，代表超过2s，就算慢查询
long_query_time=2

主从复制

概述

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

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

主从复制的优点：

  1. 主库出现问题（宕机或重启），可以快速切换到从库提供服务；
  2. 实现读写分离，降低主库的访问压力，主库执行写操作（增删改），从库执行读操作（查）；
  3. 可以在从库中执行备份，以避免备份期间影响主库服务；

第三点解释：
进行数据备份时要加上全局锁，避免数据不一致的情况发生，当前数据库就处于只读状态，其他的客户端不能执行增删改操作。有了主从复制后，可以在从库中加全局锁进行备份，主库中依然可以进行增删改等相关操作，而从库加了全局锁，查询是没有问题的。

主从复制原理

MySQL主从复制的核心就是 二进制日志，具体的过程如下：

MySQL的主从复制和分库分表初探

从库中有两组线程：

  • IOthread：发起请求连接 master 数据库，读取 master 数据库中的 binlog 日志文件，并写入到 slave 自身的中继日志 Relay log 中；
  • SQLthread：负责读取中继日志中的日志，重新执行日志中记录的操作，保证主从数据的一致性。

主从复制主要分为以下三步操作：

  1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中；
  2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log；
  3. slave 重做（重新执行）中继日志中的事件，将改变反映它自己的数据；

搭建一主一从

准备

准备两台服务器，都关闭防火墙：

systemctl stop firewalld;   # 关闭防火墙
systemctl disable firewalld;    # 关闭防火墙的开机自启

在两台服务器中分别安装好 MySQL，并检查 MySQL 的运行状态：

systemctl status mysql;

主库搭建

修改主库的配置文件 /etc/my.cnf：

vim /etc/my.cnf

mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
server-id=1
是否只读,1 代表只读, 0 代表读写（主库既可读又可写，设置为0）
read-only=0
忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql
指定同步的数据库，如果不指定某个具体的数据库，那表明所有数据库都需要同步
binlog-do-db=db01

重启主库：

systemctl restart mysqld

登录mysql，创建远程连接的账号，并授予主从复制权限：

mysql -uroot -p123

创建 ypf 用户，并设置密码123123，该用户可在任意主机连接该MySQL服务，@'%'表示这个用户可以在任意主机上来访问当前服务器
CREATE USER 'ypf'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123123';

为 'ypf'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'ypf'@'%';

通过指令，查看二进制日志坐标：

show master status;

输出字段解释：

  • file : 写入到哪个 binlog 日志文件；
  • position ： 从哪个位置开始推送日志；
  • binlog_ignore_db : 指定不需要同步的数据库；

从库配置

修改从库的配置文件 /etc/my.cnf：

vim /etc/my.cnf

mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
server-id=2
是否只读,1 代表只读, 0 代表读写（从库只读，设置为1）
read-only=1

重启主库：

systemctl restart mysqld

登录mysql，设置主库配置：

mysql -uroot -p123

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.200', SOURCE_USER='ypf', SOURCE_PASSWORD='Root@123123', SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=663;

如果是 8.0.23 之前的MySQL版本，执行如下SQL：
CHANGE MASTER TO MASTER_HOST='192.168.200.200', MASTER_USER='ypf', MASTER_PASSWORD='Root@123123', MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=663;

参数名 含义 8.0.23之前 SOURCE_HOST 主库IP地址 MASTER_HOST SOURCE_USER 连接主库的用户名 MASTER_USER SOURCE_PASSWORD 连接主库的密码 MASTER_PASSWORD SOURCE_LOG_FILE binlog日志文件名 MASTER_LOG_FILE SOURCE_LOG_POS binlog日志文件位置 MASTER_LOG_POS

最后两个参数在主库中执行 show master status;可查看参数值

开启主从复制：

start replica;  #8.0.22之后
start slave;    #8.0.22之前

查看主从同步状态：

show replica status;    #8.0.22之后   如果表比较大，展示数据比较乱，可以在命令后面加上\G
show slave status;      #8.0.22之前

测试主从是否同步

在主库 192.168.200.200 上创建数据库、表，并插入数据：

create database db01;
use db01;

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

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

在从库 192.168.200.201 中查询数据，验证主从是否同步。

上面配置的是从当前二进制日志的指定位置（ SOURCE_LOG_POS参数设定）往后进行主从复制，如果需要把之前主库的数据同步到从库，那可以先把主库的数据导出到一个sql脚本中，然后在从库中执行sql脚本，这样保证了主从的初始数据是一致的。

分库分表

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

  1. IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽不够，网络IO瓶颈。
  2. CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

因此，就需要将存储在一个数据库中的数据分散存储在多台数据库中，缓解单台数据库的磁盘存储及访问的压力。

什么是分库分表？

分库：就是一个数据库分成多个数据库，部署到不同机器上。

分表：就是一个数据库表分成多个表。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来 缓解单一数据库的性能问题，从而提升数据库性能。

为什么要分库？

  1. 业务量剧增，MySQL单机磁盘容量会撑爆，拆成多个数据库，磁盘使用率大大降低。
  2. 我们知道数据库连接是有限的。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！当前非常火的 微服务架构出现，就是为了应对高并发。它把 订单、用户、商品等不同模块，拆分成多个应用，并且把单个数据库也拆分成多个不同功能模块的数据库（ 订单库、用户库、商品库），以分担读写压力。

为什么要分表？

  1. 单表数据量太大的话，SQL的查询就会变慢。如果一个查询SQL 没命中索引，千百万数据量级别的表可能会拖垮整个数据库。
  2. 即使SQL命中了索引，如果表的数据量 超过一千万的话， 查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就会变慢。

拓展：
MySQL默认的存储引擎是 InnoDB，使用的索引结构是 B+树，InnoDB存储引擎最小储存单元是页，一页大小就是16k。对于叶子节点，假设一行数据的大小为1k，那一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。
所以，对于非叶子节点，设每页能存的键值为n，则 n * 8 + (n + 1) * 6 = 16*1024，可求得n约为1170，那每页可以存储的指针数为1171。因此，一棵高度为2的B+树，能存放 1171 * 16 = 18736条这样的数据行。同理一棵高度为 3的B+树，能存放 1171 *1171 *16 =21939856，接近2200W的数据行。
B+树的高度一般为1~3层，如果B+树到了4层，查询的时候会 多查磁盘的次数（磁盘IO次数），SQL就会变慢。

MySQL的主从复制和分库分表初探

讲一下水平/垂直、分库/分表？

水平分库

以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。

MySQL的主从复制和分库分表初探

特点：

拆分后，每台机器中，每个库的表结构一样，每个库的数据都不一样，所有库的并集是全量数据。（相当于这个数据库的每张表横向分割成多个部分分别保存到不同的机器的数据库中）

水平分表

以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

MySQL的主从复制和分库分表初探

特点：

拆分后，每台机器中，每张表的表结构都一样，每个表的数据都不一样，所有表的并集是全量数据。

垂直分库

以表为依据，根据业务将不同表拆分到不同库中。

MySQL的主从复制和分库分表初探

特点：

拆分后，每台机器中，每张表的表结构都不一样，每个表的数据也不一样，所有库的并集是全量数据。（相当于这个数据库的每张表纵向分割成多个部分分别保存到不同的机器的数据库中）

应用场景：

在业务发展初期，业务功能模块比较少，为了快速上线和迭代，往往采用单个数据库来保存数据。但是随着业务蒸蒸日上，系统功能逐渐完善。这时候，可以按照系统中的不同业务进行拆分，比如拆分成 用户库、订单库、积分库、商品库，把它们部署在不同的数据库服务器。

垂直分表

以字段为依据，根据字段属性将不同字段拆分到不同表中。

MySQL的主从复制和分库分表初探

特点：

拆分后，每台机器中，每张表的表结构都不一样，每个表的数据也不一样，一般通过一列（主键/外键）关联。所有表的并集是全量数据。

应用场景：

如果一个单表包含了几十列甚至上百列，管理起来很混乱，每次都 select *的话，还占用IO资源。这时候，我们可以将一些 不常用的、数据较大或者长度较长的列拆分到另外一张表。

分库分表有哪些策略？

  • 范围分片
  • 取模分片
  • 一致性hash分片

范围分片

MySQL的主从复制和分库分表初探

根据指定的字段及其配置的范围与数据节点的对应情况， 来决定该数据属于哪一个分片。比如在订单表中，我们可以利用表的主键，0-500万之间的值，存储在0号数据节点(数据节点的索引从0开始) ； 500万-1000万之间的数据存储在1号数据节点 ； 1000万-1500万的数据节点存储在2号节点， 依此类推。

优点：

这种方案有利于扩容，不需要数据迁移。假设数据量增加到2千万，我们只需要水平增加一张表就好了，之前 0~1500万的数据，不需要迁移。

缺点：

这种方案会有热点问题，因为订单id是一直在增大的，也就是说最近一段时间都是汇聚在一张表里面的。比如最近一个月的订单都在 1000万~2000万之间，平时用户一般都查最近一个月的订单比较多，请求都打到 order_1表啦，这就导致 数据热点问题。

该分片规则，主要是针对于数字类型的字段适用。

取模分片

根据指定的字段值与节点数量（机器数）进行求模运算，根据运算结果， 来决定该数据属于哪一个分片。

MySQL的主从复制和分库分表初探

优点：

不会存在明显的热点问题。

缺点：

  1. 如果一开始按照取模分成3个表了，未来某个时候，表数据量又到瓶颈了，需要扩容，这就比较棘手了。比如你从3张表，又扩容成 6张表，那之前 id=5的数据是在（ 5%3=2），现在应该放到（ 5%6=5），也就是说 历史数据要做迁移了。
  2. 不适用于字符串类型。如果主键是UUID（字符串），那就不适用了。

一致性hash分片

所谓一致性哈希，相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置，有效的解决了分布式数据的扩容问题。

MySQL的主从复制和分库分表初探

什么时候需要考虑分库分表？

什么时候分库？

当你的业务发展很快，用户急剧上涨，并且还是多个服务共享一个单体数据库，数据库成为了性能瓶颈，就需要考虑分库了。比如用户、商品、支付、订单等，都可以抽取出来，整成一个独立的服务（微服务），并且拆分对应的数据库（用户库、商品库、订单库）。

MySQL的主从复制和分库分表初探

什么时候分表？

如果你的系统处于快速发展时期，如果每天的订单流水都新增几十万，并且，订单表的查询效率明变慢时，就需要规划分表了。一般B+树索引的高度是2~3层最佳（3层的B+树都可容纳超2000W的数据行），如果 数据量千万级别，可能高度就变4层了，数据量就会明显变慢了。

分库分表后存在什么问题？

分布式ID

我们往往直接使用 数据库自增特性来生成主键ID，这样确实比较简单。而在分库分表的环境中，数据分布在不同的分片上，不能再借助数据库自增长特性直接生成，否则会造成不同分片上的数据表主键会重复。

最简单可以考虑 UUID，或者使用 雪花算法生成分布式ID。

分页问题

  1. 将不同分片上查到的结果进行汇总，再分页；
  2. 把分页交给前端，前端传来pageSize和pageNo，在各个数据库节点都执行分页，然后汇聚总数量前端。这种方法，缺点就是会造成空查。

数据迁移，容量规划，扩容等问题

很少有项目会在初期就开始考虑分片设计的，一般都是在业务高速发展面临性能和存储的瓶颈时才会提前准备。使用一致性Hash算法可以避免后期分片集群扩容起来需要迁移旧的数据这一问题。

参考

黑马程序员：https://www.bilibili.com/video/BV1Kr4y1i7ru

公众号：捡田螺的小男孩 -《我们为什么要分库分表？》

Original: https://www.cnblogs.com/afei688/p/16727408.html
Author: 阿飞的客栈
Title: MySQL的主从复制和分库分表初探

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/643358/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

亲爱的 Coder【最近整理，可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球