MySQL 运维篇

发表于 更新于 分类于

MySQL 客户端管理

系统数据库

image-20241018231950720

常用工具

image-20241018231940461

import只能导入dump加-T参数导出的txt文本，source用来执行.sql文件

日志

错误日志

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

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

1
2
3
4
5
6
#先登录mysql
mysql -uroot -p1234

#通过此系统变量查看日志文件的位置
show variables like '%log_error%';
12345

binlog

主要功能

记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。包括所有的DDL和DML语句，但不包括DQL语句。binlog由server层的执行器生成，历史比redolog和undolog都早，和存储引擎没有关系。

  1. 主要用于主从复制、灾难时的数据库恢复 、安全审计、增量备份
  2. 可以做数据的异构同步，如 Canal。实现数据多端一致性或实时搜索

类型

STATEMENT

记录SQL语句，如果有动态函数会恢复不准确，这里就属于逻辑日志

每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行相同的sql再执行。

优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为他只需要记录在master上所执行的语句的细节以及执行语句的上下文信息。

缺点：在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现，类似的问题。

image-20241029171133144

image-20241029191323110

ROW（推荐）

记录行数据修改之前和之后的数据

Binlog日志中仅记录哪一条记录被修改了，修改成什么样了，会非常清楚的记录下每一行数据修改的细节，Master修改了哪些行，slave也直接修改对应行的数据

优点：row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和出发无法被正确复制问题。

缺点：在row模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

image-20241029171203288

image-20241029191145845

MIXED

从官方文档中看到，之前的 MySQL 一直都只有基于 statement 的复制模式，直到 5.1.5 版本的 MySQL 才开始支持 row 复制。从 5.0 开始，MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给 MySQL Replication 又带来了更大的新挑战。

另外，看到官方文档说，从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。

在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。

新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

写入过程

事务开始以后的执行过程都写到内存中的binlog cache，事务提交后会write写入 page cache，仍然在内存中，需要通过刷盘操作fsync才能持久化到磁盘。

  • sync_binlog = 1(default) 表示每次提交事务都要在写入page cache的同时刷盘，性能较低但安全；
  • sync_binlog = 0 表示每次提交事务以后只写入page cache，刷盘时机交给系统确定，性能高但是不安全，会导致宕机以后page cache的binlog全部丢失；
  • sync_binlog = N 表示累计N个事务才write，折中方案。

image-20241029174232239

删除

手动删除

MySql运维篇—008：日志：错误日志、二进制日志、查询日志、慢查询日志，主从复制：概述 虚拟机更改ip注意事项、原理、搭建步骤_mysql 错误日志-CSDN博客

image-20241029191529628

自动删除

image-20241029191602409

image-20241029191632832

和其他日志的异同

彻底讲透：MySQL中的三种日志（Undo Log、Redo Log和Binlog）_undolog日志类型-CSDN博客

RedoLog & BinLog
持久化粒度不同
  • redolog让InnoDB有了事务崩溃恢复能力，保证了事务中的持久性，确保事务已经提交的修改能正确写入磁盘中。
  • binlog则是保证整个MySQL数据库集群架构的数据一致性。
写入时机不同—两阶段写入
  • redolog在事务执行过程中可以不断写入，依赖于存储引擎：

image-20241029174344508

  • binlog只有在事务提交时才写入，依赖于执行器（调用完存储引擎API）。

image-20241029175255030

  • 这就导致虽然两个都能保证持久化能力，但两个是相对独立的，如果其中一个出现错误，就会出现数据不一致的情况，因此需要分阶段写入：将redolog的状态分成两个阶段：prepare和commit，在提交事务时，先写入binlog，再进行redolog commit。

image-20241029175500294

  • 遇到事故时停机重启，根据redolog恢复数据，redolog处于commit阶段，表明binlog正常写入；redolog处于prepare阶段，binlog正常写入，这时也是完整的，也会提交；redolog处于prepare阶段，找不到对应binlog，此次更改作废。
顺序写入但方式不同
  • redolog是一个日志文件组循环写，当数据成功刷到磁盘就会擦除日志

image-20241029173203896

binlog一直追加写，写满一个文件就创建新文件继续写，保存的是全量日志。事务更改提交以后刷盘失败的恢复能用redolog，如果是整个数据库层级的操作，比如主从复制或者数据备份，就必须用全量日志binlog。

UndoLog & BinLog
逻辑日志，粒度与方向不同

  • UndoLog属于底层的InnoDB，记录反向操作，粒度更细，用于事务的回滚和MVCC（避免锁竞争，提高并发性能）

  • Binlog属于Server层的执行器，记录正向操作，用于数据库备份和复制

总结
  • Undo Log和Redo Log是为了实现ACID特性中的A（原子性）、C（一致性）和D（持久性）。Undo Log保证了事务内部的一致性以及事务失败后的回滚能力；而Redo Log则是为了保证即使在意外情况下也能恢复已提交事务的更改。
  • Binlog的设计则更多地关注于高可用性、数据冗余以及分布式环境下的数据同步。它提供了跨节点间的数据复制功能，同时也为数据库管理员提供了审计跟踪和数据恢复手段。

类比实际生活：

  • 首先，Undo Log这位“传令兵”，负责记录每一步操作的反向指令，一旦有差池，便能瞬间执行“撤退”命令，让事务回滚至初始状态，确保了原子性与一致性，就如同战场上的兵马未动粮草先行，进退有序。

    其次，Redo Log这位“记事官”，专司记录每一次物理操作的详细步骤，即便遭遇突袭或断电，也能根据记录重新搭建战局，恢复已提交事务的影响，确保数据持久不灭，犹如战场上刀光剑影过后，总能找到重整旗鼓的依据。

    最后，Binlog这位“信使”，肩负着跨营地同步信息的重要使命，无论主从服务器之间还是备份恢复之时，只需将它传递的信息忠实执行，就能保证所有营地的数据步调一致，仿佛军令如山，一呼百应，千里之外亦可响应无误。

查询日志

image-20241029191825721

慢查询日志

所有查询时间，扫描记录数超过阈值的SQL语句

image-20241029191835513

主从复制

优点

  • 主库出现问题，快速切换服务
  • 读写分离降低主库压力
  • 从库执行备份，避免主库被全局锁定

原理

image-20241029192549256

从上图来看，复制分成三步:
1.Master 主库在事务提交时，会把数据变更记录（DDL.DML）在二进制日志文件 Binlog 中。

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

3.Slave 从库重做中继日志中的事件，将改变反映它自己的数据

分库分表

读写分离 分库分表 几种水平分表方案与具体实践_水平分表的几种方式-CSDN博客

分布式唯一订单id——雪花算法

雪花算法：1位是占位符 中间41位是时间戳（支持69年） 接着10位是机器ID，最后 12位是序列id，

每次生成 id 需要 now 和 lastTimeStamp 做比较，如果超了就说明是另外的毫秒了序列id归零，如果没超说明还在同一毫秒内，序列id自增，last = now，可以看出一毫秒一台机器可以生成4096个不同的序列id。

优点：生成速度比较快、生成的 ID 有序递增、比较灵活（可以对 Snowflake 算法进行简单的改造比如加入业务 ID）

缺点：需要解决重复 ID 问题（ID 生成依赖时间，在获取时间的时候，可能会出现时间回拨的问题，也就是服务器上的时间突然倒退到之前的时间，进而导致会产生重复 ID）、依赖机器 ID 对分布式环境不友好（当需要自动启停或增减机器时，固定的机器 ID 可能不够灵活）。

1
((now - twEpoch) << 22) | (datacenterId << 17) | (workerId << 12) | sequence;

UUID

优点：生成速度通常比较快、简单易用

缺点：存储消耗空间大（32 个字符串，128 位）、 无序（非自增）、不安全（基于 MAC 地址生成 UUID 的算法会造成 MAC 地址泄露)、没有具体业务含义、需要解决重复 ID 问题（当机器时间不对的情况下，可能导致会产生重复 ID）

水平分表（Sharding）

几种水平分表方案与具体实践_水平分表的几种方式-CSDN博客

  • 首先保证全局唯一ID与查询优化：
    • 分布式ID生成（雪花算法、Redis自增ID）避免主键冲突。
    • 冗余字段或索引表：通过异步维护冗余字段（如商户ID+订单ID）支持多维度查询。

现在很多公司都是用的类似于 TiDB 这种分布式关系型数据库，不需要我们手动进行分库分表（数据库层面已经帮我们做了），也不需要解决手动分库分表引入的各种问题，直接一步到位，内置很多实用的功能（如无感扩容和缩容、冷热存储分离）！如果公司条件允许的话，个人也是比较推荐这种方式！

如果必须要手动分库分表的话，ShardingSphere 是首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

Sharding vs Partitioning

Dimension Sharding Partitioning
存储位置 不同机器 同一机器
可扩展性 High (Scale Out) Limited (Scale UP)
可用性 High 跟单机类似
并发查询性能 取决于机器数目 取决于机器的CPU
查询时间 低，除非某台机器过载 中低，主要局限于CPU

Partitioning 可以分为 水平 和 垂直，水平就是按行拆，垂直就是按列拆（主要是可以按照业务划分冷热数据列），而 Sharding 就是水平 Partitioning 的分布式版本。

Image

分表带来的问题

  1. join 无法进行，需要手动封装、聚合数据
  2. 数据库自带的事务失效，要有分布式事务（ShardingSphere支持）
  3. 分布式id
  4. 跨库聚合查询：分库分表会导致常规聚合查询操作，如 group by，order by 等变得异常复杂。这是因为这些操作需要在多个分片上进行数据汇总和排序，而不是在单个数据库上进行。为了实现这些操作，需要编写复杂的业务代码，或者使用中间件来协调分片间的通信和数据传输。这样会增加开发和维护的成本，以及影响查询的性能和可扩展性。

规则分片策略

分片策略：包含分片键（将数据库表水平拆分的字段）以及分片算法

分片键选择

减少单次查询涉及的分片数量，分割尽量均匀，值稳定也很重要，需要支持动态扩展避免重新分片。

  1. 主键：按照时间将订单水平分表，分成 order2020_2023 order2024_2027，使用雪花算法id获取年份作为分片键，然后交由中间件进行路由查询。
  2. 其他字段（以地区为例）：同理，比如按照地区分表，每次查询的时候可以带上地区，交给中间件进行路由查询
  3. 复合键
  4. 不存在于数据库：可能来自上下文，需要在业务层强制路由
  5. 单纯拆分数据（不推荐）：直接拆成三个表，均匀
分片算法
  • RoundRobin：id对表数取余，对应方法4，数据很均匀，但是一旦节点变动，重新分片的开销也很大
    • hash
    • 一致性哈希，不过一般用于es、redis，主要适用于字段比较随机，不进行范围查询的数据，节点经常弹性扩展
  • 范围法：按照字段处于的范围分，容易定位，对应方法 1,2

分片键缺失：全数据量查询

中间件

使用ShardingSphere（分库分表、分布式事务）、MyCat（代理层，SQL 路由、读写分离）等工具自动路由查询，侵入性较低。也可以使用 MP 拦截器自行开发分页插件。

其他策略（Redis缓存路由、热数据 | 垂直分表）

  1. 结合Redis：
    • 缓存分表路由信息（如user_shard:1000 -> shard_2），加速查询。
    • 热数据缓存：将分表后的热数据单独缓存，降低DB压力。
  2. 垂直分表 （一般不推荐）
    • 不要过度分表或分库，要从实际需求出发，可以是已经碰到的情况下，也可以是预估出不分表不行的情况
    • 所要分的表与其他表关联度不是很大，如果它经常被做连接查询，而且它的主键在别的表经常以外键形式存在，那么就不建议直接水平分表，可以先考虑垂直分表把数据量大的部分独立出去再做水平切分。将大表按列拆分（如用户基础信息表user_base与扩展信息表user_extend），减少单行数据量。

数据迁移

  1. 停机，写脚本逐条迁移
  2. 不停机（双写）：每条对老库的增删改都要写入新库（不存在则插入）自己需要写脚本让那些没有被改的数据同步到的新库，不断进行比对，直到相同为止。工具可以借助 canal

冷热分离

冷热数据分离

任务调度：可以利用 xxl-job 或者其他分布式任务调度平台定时去扫描数据库，找出满足冷数据条件的数据，然后批量地将其复制到冷库中，并从热库中删除。这种方法修改的代码非常少，非常适合按照时间区分冷热数据的场景。

监听 binlog ：将满足冷数据条件的数据从 binlog 中提取出来，然后复制到冷库中，并从热库中删除。这种方法可以不用修改代码，但不适合按照时间维度区分冷热数据的场景。