【MYSQL高级篇】MYSQL日志、二进制日志、主从复制、分库分表

‍小林同学学JAVA

已于 2024-10-28 22:10:32 修改

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： MYSQL数据库文章标签： android 数据库 mysql mybatis idea 后端

于 2024-04-14 17:19:34 首次发布

本文链接：https://blog.csdn.net/2301_77358195/article/details/137748458

MYSQL数据库专栏收录该内容

10 篇文章

订阅专栏

🔥作者主页：小林同学的学习笔录

🔥mysql专栏：小林同学的专栏

目录

1.日志

1.1 错误日志

1.2 二进制日志

1.2.1 介绍

1.2.2 作用

1.2.3 格式

1.2.4 查看

1.2.5 删除日志

1.3 查询日志

1.4 慢查询日志

2.主从复制

2.1 概述

2.2 原理

2.3 搭建

2.3.1 准备

2.3.2 主库配置

2.3.3 从库配置

2.3.4 测试

3.分库分表

3.1 介绍

3.1.1 问题分析

3.1.2 拆分策略

3.1.3 垂直拆分

3.1.4 水平拆分

3.1.5 实现技术

3.2 MyCat概述

3.2.1 介绍

3.2.2 下载

3.2.3 安装

3.2.4 mycat目录介绍

3.2.5 概念介绍

3.3 MyCat入门

3.3.1 需求

3.3.2 环境准备

3.3.3 配置

3.3.4 测试

3.3.4.1 启动

3.3.4.2 测试

深入了解日志以及主从复制的原理

为什么需要分库分表问题？图形化分析让你印象深刻

麒麟而非淇淋，不是干货不制作https://blog.csdn.net/2301_77358195

1.日志

1.1 错误日志

错误日志记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信

息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

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

1.2 二进制日志

1.2.1 介绍

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语

句，但不包括数据查询（SELECT、SHOW）语句。

1.2.2 作用

①. 灾难时的数据恢复

②. MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：

show variables like '%log_bin%';

参数说明：

log_bin_basename：当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文件名需要

再该basename的基础上加上编号(编号从000001开始)。

log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些。

1.2.3 格式

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

show variables like '%binlog_format%';

如果我们需要配置二进制日志的格式，只需要在 /etc/my.cnf 配置中加上

binlog_format=STATEMENT ，然后重启MySQL服务器就可以了。

演示:

加上age这一个字段，并且给这个字段赋值为3

可以看到修改前后的数据

1.2.4 查看

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

看，具体语法：

mysqlbinlog [ 参数选项 ] logfilename

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

注意：ROW事件记得加上-v或者-vv才能看到SQL语句

1.2.5 删除日志

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空

间。可以通过以下几种方式清理日志：

也可以在mysql的配置文件中配置二进制日志的过期时间，二进制日志过期会自动删除

show variables like '%binlog_expire_logs_seconds%';

1.3 查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况

下，查询日志是未开启的。

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

#该选项用来开启查询日志，可选值： 0 或者 1 ； 0 代表关闭， 1 代表开启
general_log=1

#设置日志的文件名，如果没有指定，默认的文件名为 host_name.log
general_log_file=mysql_query.log

然后重启mysql服务器

service mysql restart

开启了查询日志之后，在MySQL的数据存放目录，也就是 /var/lib/mysql/ 目录下就会出现

mysql_query.log 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中，

长时间运行后，该日志文件将会非常大，建议不要开启。

1.4 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有

SQL语句的日志。

MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量

show variables like 'slow_query_log';

①.如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

②.配置完毕重启mysql systemctl restart mysqld;

然后，再次查看开关情况，慢查询日志就已经打开了。

③.然后通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

tail -f /usr/local/mysql/data/localhost-slow.log

注意：在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL是不

会记录的。

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询，记录到慢查询日志文

件。可以使用log_slow_admin_statements和更改此行为 log_queries_not_using_indexes，如下

所述。

2.主从复制

2.1 概述

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上

对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

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

链状复制。

MySQL 复制的优点主要包含以下三个方面：

主库出现问题，可以快速切换到从库提供服务。

实现读写分离，降低主库的访问压力。

可以在从库中执行备份，以避免备份期间影响主库服务。

2.2 原理

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

从上图来看，复制分成三步：

①. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

②. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。

③. slave重做中继日志中的事件，将改变反映到它自己的数据进行同步。

2.3 搭建

2.3.1 准备

准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备(安

装、密码配置等操作)工作。其中：

192.168.200.200 作为主服务器master
192.168.200.201 作为从服务器slave

2.3.2 主库配置

①.修改配置文件 /etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
server-id=1

#是否只读,1 代表只读, 0 代表读写
read-only=0

#忽略的数据, 指不需要同步的数据库
#binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=db01

后面两个根据需要配置

②.重启MySQL服务器

service mysqld restart

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

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

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

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

show master status ;

图1；

字段含义说明：

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

2.3.3 从库配置

①.修改配置文件 /etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
server-id=2

#是否只读,1 代表只读, 0 代表读写
read-only=1

②.重新启动MySQL服务

service mysqld restart

③.登录mysql，设置主库的配置

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.85.131', SOURCE_USER='itcast',
SOURCE_PASSWORD='root', SOURCE_LOG_FILE='binlog.000015',
SOURCE_LOG_POS=655;

上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本，执行如下SQL：

CHANGE MASTER TO MASTER_HOST='192.168.85.131', MASTER_USER='itcast',
MASTER_PASSWORD='root', MASTER_LOG_FILE='binlog.000015',
MASTER_LOG_POS=655;

④.开启同步操作

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

⑤.查看主从同步状态

show replica status \G; #8.0.22之后
show slave status \G; #8.0.22之前

2.3.4 测试

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

create database itcast;

use itcast;

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

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

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

3.分库分表

3.1 介绍

3.1.1 问题分析

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据

存储，存在以下性能瓶颈：

①. IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽

不够，网络IO瓶颈。

②. CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，

CPU出现瓶颈。

为了解决上述问题，我们需要对数据库进行分库分表处理。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的

性能问题，从而达到提升数据库性能的目的。

3.1.2 拆分策略

分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表

3.1.3 垂直拆分

①. 垂直分库

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

特点：

每个库的表结构都不一样。
每个库的数据也不一样。
所有库的并集是全量数据。

②. 垂直分表

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

特点：

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

3.1.4 水平拆分

①.水平分库

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

特点：

每个库的表结构都一样。
每个库的数据都不一样。
所有库的并集是全量数据。

②.水平分表

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

特点：

每个表的表结构都一样。
每个表的数据都不一样。
所有表的并集是全量数据。

在业务系统中，为了缓解磁盘IO及CPU的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分

库，还是分表，都需要根据具体的业务需求具体分析。

3.1.5 实现技术

shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处

理。需要自行编码配置实现，只支持java语言，性能较高。

MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前

者。

3.2 MyCat概述

3.2.1 介绍

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使

用mycat，对于开发人员来说根本感觉不到mycat的存在。

开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什

么数据，都无需关心。具体的分库分表的策略，只需要在MyCat中配置即可。

3.2.2 下载

下载地址：http://dl.mycat.org.cn/

3.2.3 安装

mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境

需要安装MYSQL、JDK、MYCAT

3.2.4 mycat目录介绍

bin : 存放可执行文件，用于启动停止mycat
conf：存放mycat的配置文件
lib：存放mycat的项目依赖包（jar）
logs：存放mycat的日志文件

3.2.5 概念介绍

在MyCat的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构。

在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的

数据存储还是在物理结构，也就是数据库服务器中存储的。

3.3 MyCat入门

3.3.1 需求

由于 tb_order 表中数据量很大，磁盘IO及容量都到达了瓶颈，现在需要对 tb_order 表进行数据分

片，分为三个数据节点，每一个节点主机位于不同的服务器上

具体的结构，参考下图：

3.3.2 环境准备

准备3台服务器：

192.168.200.210：MyCat中间件服务器，同时也是第一个分片服务器。
192.168.200.213：第二个分片服务器。
192.168.200.214：第三个分片服务器。

并且在上述3台数据库中创建数据库 db01 。

3.3.3 配置

①.schema.xml

位置：

mycat安装目录下的conf配置文件里面

参数说明：

在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"/>
</schema>

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>

<writeHost host="master" url="jdbc:mysql://192.168.200.210:3306?
useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1234" />
</dataHost>
<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>

<writeHost host="master" url="jdbc:mysql://192.168.200.213:3306?
useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1234" />
</dataHost>
<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>

<writeHost host="master" url="jdbc:mysql://192.168.200.214:3306?
useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"
user="root" password="1234" />
</dataHost>
</mycat:schema>

②.server.xml

需要在server.xml中配置用户名、密码，以及用户的访问权限信息，具体的配置如下:

<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">DB01</property>


</user>
<user name="user">
<property name="password">123456</property>
<property name="schemas">DB01</property>
<property name="readOnly">true</property>
</user>