MySQL运维篇（一）

真滴book理喻

已于 2023-10-27 17:03:41 修改

阅读量725

点赞数 1

分类专栏： # MySQL 文章标签： mysql 运维数据库

于 2023-05-23 21:00:37 首次发布

本文链接：https://blog.csdn.net/m0_59749089/article/details/130834293

版权

MySQL 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

一.日志

1.1 MySQL的常见日志有哪些？

针对 InnoDB 存储引擎，MySQL 中常见的日志类型主要有下面几类，binlog 和 redo log 和 undo log 比较重要，需要我们重点关注。

错误日志（error log）：对 MySQL 的启动、运行、关闭过程进行了记录。
二进制日志（binary log，binlog）：主要记录的是更改数据库数据的 SQL 语句。
一般查询日志（general query log）：已建立连接的客户端发送给 MySQL 服务器的所有 SQL 记录，因为 SQL 的量比较大，默认是不开启的，也不建议开启。
慢查询日志（slow query log）：执行时间超过 long_query_time秒钟的查询，解决 SQL 慢查询问题的时候会用到。
事务日志(redo log 和 undo log) ：redo log 是重做日志，undo log 是回滚日志。
中继日志(relay log) ：relay log 是复制过程中产生的日志，很多方面都跟 binary log 差不多。不过，relay log 针对的是主从复制中的从库。
DDL 日志(metadata log) ：DDL 语句执行的元数据操作。

1.1 错误日志

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

错误日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置的SQL语句如下：

show variables like '%log_error%';

1.2 slow query log

slow query log 是慢查询日志，慢查询日志记录了执行时间超过参数 long_query_time 的所有查询语句，long_query_time 默认是 10s，通常设置为 1s，在解决 SQL 慢查询问题的时候经常会用到。

找到慢 SQL 是优化 SQL 语句性能的第一步，然后再用 EXPLAIN 命令对慢 SQL 进行分析，获取执行计划的相关信息。慢查询默认是关闭的，可以通过下面的命令来查看慢查询日志是否开启了。

 show variables like "slow_query_log";

如果没有开启，可以通过如下命令开启慢查询日志

 SET GLOBAL slow_query_log=ON

long_query_time 参数定义了一个查询消耗多长时间才可以被定义为慢查询，默认是 10s，通过以下命令即可查看：

SHOW VARIABLES LIKE '%long_query_time%';

并且，我们还可以对 long_query_time 参数进行修改：

SET GLOBAL long_query_time=1

在实际项目中，慢查询日志可能会比较大，直接分析的话不太方便，我们可以借助 MySQL 官方的慢查询分析调优工具 mysqldumpslow。

如何查询当前慢查询语句的个数？

在MySQL中有一个变量专门记录当前慢查询语句的个数，可以通过以下命令查看。

show global status like '%Slow_queries%';

如何优化慢 SQL？

MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息。

执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

另外，为了更精准定位一条 SQL 语句的性能问题，需要清楚地知道这条 SQL 语句运行时消耗了多少系统资源。可以通过如下命令查看 SQL 语句的资源使用情况，展示的消息包括 CPU 的使用，CPU 上下文切换，IO 等待，内存使用等。

SHOW PROFILE; 或 SHOW PROFILES;

1.3 binlog

binlog是什么

binlog 是指二进制日志文件，主要记录了对 MySQL 数据库执行了更改的所有操作，包括表结构变更（CREATE、ALTER、DROP TABLE…）、表数据修改（INSERT、UPDATE、DELETE...），不包括 SELECT、SHOW 这类没有对数据库造成更改的操作。

不过，并不是说没有对数据库造成修改成功，就不会被记录进 binlog。即使表结构变更和表数据修改操作并未对数据库造成更改，依然会被记录进 binlog。

可以使用如下命令查看所有二进制日志列表：

 show binary logs;

可以看到，binlog 日志文件名为文件名.00000* 形式。可以通过下面的命令查看日志的具体内容。这里一定要指定 limit，不然查询出来的日志文件内容太多。另外， MySQL 内置了 binlog 查看工具 mysqlbinlog，可以解析二进制文件。

show binlog events in 'binlog.000008' limit 10;

binlog 通过追加的方式进行写入，大小没有限制。并且，我们可以通过 max_binlog_size 参数设置每个 binlog 文件的最大容量，当文件大小达到给定值之后，会生成新的 binlog 文件来保存日志，不会出现前面写的日志被覆盖的情况。

binlog 的格式有哪几种？

一共有 3 种类型二进制记录方式，推荐使用 Row 模式：

Statement 模式：每一条会修改数据的 sql 都会被记录在 binlog 中，如 inserts, updates, deletes。
Row 模式: 每一行的具体变更事件都会被记录在 binlog 中。也就是说，Row 模式记录了修改前的表记录和修改后的表记录
Mixed 模式：Statement 模式和 Row 模式的混合。默认使用 Statement 模式，少数特殊场景自动切换到 Row 模式。

MySQL 5.1.5 之前 binlog 的格式只有 STATEMENT，5.1.5 开始支持 ROW 格式的 binlog，从 5.1.8 版本开始，MySQL 开始支持 MIXED 格式的 binlog。MySQL 5.7.7 之前，默认使用 Statement 模式。MySQL 5.7.7 开始默认使用 Row 模式。

相比较于 Row 模式来说，Statement 模式下的日志文件更小，磁盘 IO 压力也较小，性能更好。不过，其准确性相比于 Row 模式要差。

可以使用如下命令查看 binlog 使用的格式：

 show variables like '%binlog_format%';

binlog 主要用来做什么？

binlog 最主要的应用场景是主从复制，主备、主主、主从都离不开 binlog，需要依靠 binlog 来同步数据，保证数据一致性。

主从复制的原理如下图所示：

Master 主库在事务提交时，会把数据变更记录在二进制日志文件 binlog 中。
从库读取主库的二进制日志文件 binlog ，写入到从库的中继日志 Relay Log 。
slave重做中继日志中的事件，将改变反映它自己的数据。

除了主从复制之外，binlog 还能帮助我们实现数据恢复。当我们误删数据甚至是整个数据库的情况下，就可以使用 binlog 来帮助我们恢复数据。当然了，大前提是已经启用了 binlog 日志。

可以使用如下命令查看数据库是否启用了 binlog 日志，默认是开启的。

 show variables like 'log_bin';

binlog 的刷盘时机如何选择？

对于 InnoDB 存储引擎而言，事务在执行过程中，会先把日志写入到 binlog cache 中，只有在事务提交的时候，才会把 binlog cache 中的日志持久化到磁盘上的 binlog 文件中。写入内存的速度更快，这样做也是为了效率考虑。

因为一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为 binlog cache。我们可以通过 binlog_cache_size 参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘。

那么 binlog 是什么时候刷到磁盘中的呢？可以通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N，默认为 0：

0：不去强制要求，由系统自行判断何时写入磁盘
1：每次提交事务的时候都要将 binlog 写入磁盘
N：每 N 个事务，才会将 binlog 写入磁盘

MySQL5.7 之前， sync_binlog 默认值为 0。在 MySQL5.7 之后， sync_binlog 默认值为 1。如果对性能要求比较高或者出现磁盘 IO 瓶颈的话，可以适当将 sync_binlog 的值调大，不过，这样会增加数据丢失的风险。

什么情况下会重新生成 binlog？

当遇到以下 3 种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

MySQL 服务器停止或重启；
使用 flush logs 命令后；
binlog 文件大小超过 max_binlog_size 变量的阈值后；

1.4 redo log

什么是redolog

redo log 是 MySQL中的一种事务日志，记录了对数据库进行的修改操作。如果事务提交之前系统崩溃了，数据的修改仍然可以通过 redo log 进行恢复。MySQL通过使用 redo log 来保证事务的持久性和恢复能力。

redo log 如何保证事务的持久性？

我们知道 InnoDB 存储引擎是以页为单位来管理存储空间的，我们往 MySQL 插入的数据最终都是存在于页中的，准确点来说是数据页这种类型。为了减少磁盘 IO 开销，还有一个叫做 Buffer Pool的区域，存在于内存中。当我们的数据对应的页不存在于 Buffer Pool 中的话， MySQL 会先将磁盘上的页缓存到 Buffer Pool 中，这样后面我们直接操作的就是 Buffer Pool 中的页，这样大大提高了读写性能。

一个事务提交之后，我们对 Buffer Pool 中对应的页的修改可能还未持久化到磁盘。这个时候，如果 MySQL 突然宕机的话，这个事务的更改是不是直接就消失了呢？

很显然是不会的，如果是这样的话就明显违反了事务的持久性。

MySQL InnoDB 引擎使用 redo log 来保证事务的持久性。redo log 主要做的事情就是先记录日志，比如某个页面某个偏移量处修改了几个字节的值以及具体被修改的内容是什么，然后再修改页。redo log 中的每一条记录包含了表空间号、数据页号、偏移量、具体修改的数据，甚至还可能会记录修改数据的长度，记录的内容也取决于 redo log 的类型。

在事务提交时，我们会将 redo log 按照刷盘策略刷到磁盘上去，这样即使 MySQL 宕机了，重启之后也能恢复未能写入磁盘的数据，从而保证事务的持久性。也就是说，redo log 让 MySQL 具备了崩溃恢复能力。

InnoDB 将 redo log 刷到磁盘上有几种情况：

事务提交：当事务提交时，log buffer 里的 redo log 会被刷新到磁盘。可以通过innodb_flush_log_at_trx_commit 参数控制，后面会提到。
log buffer 空间不足时：log buffer 中缓存的 redo log 已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘。
事务日志缓冲区满：InnoDB 使用一个事务日志缓冲区（transaction log buffer）来暂时存储事务的重做日志条目。当缓冲区满时，会触发日志的刷新，将日志写入磁盘。
Checkpoint（检查点）：InnoDB 定期会执行检查点操作，将内存中的已修改但尚未写入磁盘的数据刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。
后台刷新线程：InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地将已修改但尚未写入磁盘的数据页刷新到磁盘，并将相关的重做日志一同刷新。也就是说，一个没有提交事务的 redo log 记录，也可能会被刷盘。
正常关闭服务器：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

总之，InnoDB 在多种情况下会刷新 redo log 到磁盘，以保证数据的持久性和一致性。

我们要注意设置正确的刷盘策略 innodb_flush_log_at_trx_commit 。根据 MySQL 配置的刷盘策略的不同，MySQL 宕机之后可能会存在轻微的数据丢失问题。

innodb_flush_log_at_trx_commit 的值有 3 种，也就是共有 3 种刷盘策略：

0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作。这种方式性能最高，但是也最不安全，因为如果 MySQL 挂了或宕机了，可能会丢失最近 1 秒内的事务。
1：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作。这种方式性能最低，但是也最安全，因为只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。
2：设置为 2 的时候，表示每次事务提交时都只把 log buffer 里的 redo log 内容写入 page cache（文件系统缓存）。page cache 是专门用来缓存文件的，这里被缓存的文件就是 redo log 文件。这种方式的性能和安全性都介于前两者中间。

性能：0 > 2 > 1 ，安全性：1 > 2 > 0 。

刷盘策略 innodb_flush_log_at_trx_commit 的默认值为 1，设置为 1 的时候才不会丢失任何数据。为了保证事务的持久性，我们必须将其设置为 1。当然了，如果你的项目能容忍轻微的数据丢失的话，那将 innodb_flush_log_at_trx_commit 的值设置为 2 或许是更好的选择。

下图是 MySQL 5.7 官方文档对于 innodb_flush_log_at_trx_commit 参数的详细介绍，我这里就不做过多阐述了。

redo log 采用循环写的方式进行写入，如下图所示。

write pos 表示 redo log 当前记录写到的位置， check point 表示当前要擦除的位置。当 write pos 追上 check point 时，表示 redo log 文件被写满了。这个时候，MySQL 没办法执行更新操作，也就是说数据库更新操作会被阻塞，因为无法再写入 redo log 日志。为了保证 MySQL 更新操作的正常执行，需要执行 CheckPoint 刷盘机制。CheckPoint 会按照一定的条件将内存中的脏页刷到磁盘上。成功刷盘之后，checkpoint 会向后移动，也就是顺时针方向。这样的话，才能继续写入 redo log 日志，阻塞的更新操作才能继续执行。

什么情况下会出现数据丢失？

如果你理解了 redo log 原理的话，应该可以很容易想到下面这两种情况：

redo log 写入 log buffer 但还未写入 page cache ，此时数据库崩溃，就会出现数据丢失（刷盘策略 innodb_flush_log_at_trx_commit 的值为 0 时可能会出现这种数据丢失）；

redo log 已经写入 page cache 但还未写入磁盘，操作系统奔溃，也可能出现数据丢失（刷盘策略innodb_flush_log_at_trx_commit 的值为 2 时可能会出现这种数据丢失）。

页修改之后为什么不直接刷盘呢？

很多人可能要问了：为什么每次修改 Buffer Pool 中的页之后不直接刷盘呢？这样不就不需要 redo log 了嘛！

这种方式必然是不行的，性能非常差。最大的问题就是 InnoDB 页的大小一般为 16KB，而页又是磁盘和内存交互的基本单位。这就导致即使我们只修改了页中的几个字节数据，一次刷盘操作也需要将 16KB 大小的页整个都刷新到磁盘中。而且，这些修改的页可能并不相邻，也就是说这还是随机 IO。

采用 redo log 的方式就可以避免这种性能问题，因为 redo log 的刷盘性能很好。首先，redo log 的写入属于顺序 IO。其次，一行 redo log 记录只占几十个字节。

另外，Buffer Pool 中的页（脏页）在某些情况下（比如 redo log 快写满了）也会进行刷盘操作。不过，这里的刷盘操作会合并写入，更高效地顺序写入到磁盘。

binlog 和 redolog 有什么区别？

binlog 主要用于数据库还原，属于数据级别的数据恢复，主从复制是 binlog 最常见的一个应用场景。redolog 主要用于保证事务的持久性，属于事务级别的数据恢复。
redolog 属于 InnoDB 引擎特有的，binlog 属于所有存储引擎共有的，因为 binlog 是 MySQL 的 Server层实现的。
redolog 属于物理日志，主要记录的是某个页的修改。binlog 属于逻辑日志，主要记录的是数据库执行的所有 DDL 和 DML 语句。
binlog 通过追加的方式进行写入，大小没有限制。redo log 采用循环写的方式进行写入，大小固定，当写到结尾时，会回到开头循环写日志。
......

1.5 undo log

undo log 如何保证事务的原子性？

每一个事务对数据的修改都会被记录到 undo log ，当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事务开始之前的状态。

undo log 属于逻辑日志，记录的是 SQL 语句，比如说事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句。

除了保证事务的原子性，undo log 还有什么用？

InnoDB存储引擎中 MVCC 的实现用到了 undo log 。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo log读取之前的行版本信息，以此实现非锁定读取。

二.主从复制

2.1 概述

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。 MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

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

主库出现问题，可以快速切换到从库提供服务。
实现读写分离，降低主库的访问压力。
可以在从库中执行备份，以避免备份期间影响主库服务。

2.2 原理

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

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

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

2.3 搭建

1.准备

准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备(安装、密码配置等操作)工作。其中：

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

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服务器

systemctl restart mysqld

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

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

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

show master status ;

字段含义说明：

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

3.从库配置

第一步：修改配置文件 /etc/my.cnf

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

第二步：重新启动MySQL服务

systemctl restart mysqld

第三步：登录mysql，设置主库配置

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

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

CHANGE MASTER TO MASTER_HOST='192.168.200.200', MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=663;

第四步：开启同步操作

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

第五步：查看主从同步状态

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

4.测试

第一步：在主库 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 中查询数据，验证主从是否同步