MySQL学习笔记(二)

一、MySQL日常运维巡检项目

1、性能监控与调优

• 使用MySQL自身的SHOW STATUS、SHOW VARIABLES、EXPLAIN等命令，或结合第三方工具（如Percona Toolkit、MySQL Enterprise Monitor、Prometheus + MySQL exporter等）定期收集并分析性能指标，如查询响应时间、CPU利用率、内存使用情况、磁盘I/O、磁盘空间使用、会话数、连接数等，一般公司都有相关的监控平台，DBA有完善的权限管理机制可供其他开发人员查询。
• 根据监控数据优化索引、查询语句、表结构设计、存储引擎选择等，确保数据库高效运行。
• 业务方使用一些大的加工任务、要监控长事物执行时间、减少死锁。
• 定期整理废弃表、大表的删除清理方式、数据库表要有明确的表名命名和使用场景，减少磁盘占用和提升数据库IO性能。

MySQL的InnoDB存储引擎包含一个重要的组件叫做InnoDB Buffer Pool，它是用来缓存数据和索引的。Buffer Pool缓存了从数据文件中读取的数据，以便更快地访问。如果数据已经在Buffer Pool中，那么就不需要从磁盘上读取数据文件了。

InnoDB Buffer Pool的大小可以通过innodb_buffer_pool_size配置选项来设置。这个参数控制了InnoDB存储引擎可以缓存的数据量大小。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

你可以在my.cnf或my.ini配置文件中设置它

[mysqld]

innodb_buffer_pool_size = 2G

2、备份与恢复

• 制定并执行备份策略，包括全量备份、增量备份、差异备份等，可使用mysqldump、xtrabackup等工具，可以使用shell脚本+mysqldump来做简单备份，使用云数据库一般会开启配置相关的备份任务，检查是否执行成功。
• 测试备份的有效性，定期执行恢复演练以确保在真实故障情况下能快速恢复数据。
• 存储备份文件的安全性，可能需要异地备份或云存储，确保备份文件的安全性。

3、高可用性与容灾

• 配置主从复制（MySQL replication）以实现数据的实时同步，提升系统的可用性和扩展性。
• 实施读写分离策略，减轻主库压力、以及主从同步的延迟性和主从同步的模式GTID。
• 配置MySQL集群如InnoDB Cluster或MySQL Group Replication以增强容错能力、以及云上一主一备一级联的跨AZ容灾、金融级高可用的PXC集群。
• 使用如Keepalived、Heartbeat等工具配合虚拟IP实现故障切换，确保高可用服务、以及业务连接的高可用方式。
• 数据库的连接高可用，数据库连接池参数配置连接探活、主备切换的可用性等关键参数。

   数据库主从同步各个线程示意图：

 

上图主从复制分了五个步骤进行：

 步骤一：主库的更新事件(update、insert、delete)被写到binlog

 步骤二：从库发起连接，连接到主库。

 步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。

 步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log

 步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

   数据库表占用擦盘空间大小计算SQL如下:

    查看指定数据库容量大小：

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

  查询单个库中所有表磁盘占用大小：

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

  主从延迟过高的一些监控处理解决方案

1、网络问题

网络问题，会导致binlog从主库发生到主从时，出现问题。

我们可以增加网络的带宽，由100M升级到300M。

2、服务器性能问题

一般情况下，主库的性能要比从库的要好。

如果高并发的写入数据，会导致产生大量的binlog数据，在从库通过replay log回放的过程会比较慢。

从而导致从库写入数据的速度。

这种情况下，可以升级从库的服务器配置，跟主库保持一致。

3、避免大事务问题

业务系统中的大事务，不光会导致主库写数据的速度变慢，还会导致主从数据同步时，从库写数据的速度同样变慢。

我们需要避免大事务问题，对业务代码中的大事务做排查，缩小事务的范围。

有些业务代码，可以放到事务之外的，尽可能放到事务之外执行，比如：有些查询方法。

有些可以异步执行的代码，尽可能异步执行。

4、MySQL版本太低

MySQL的低版本，只支持单线程同步binlog，同步速度非常慢。

这种情况下，可以升级MySQL版本到5.6以上，支持多线程同步。

5、 从库太多

在主从同步时，如果从库太多，可能会导致同步速度变慢。

主从同步，要所有从库的数据，都同步成功了，才算真正的成功了。

针对这种情况，建议减少，从库的数量，一般不建议超过5个。

4、日志管理

• 定期清理和归档binlog、error log、slow query log等日志文件，以便分析和排查问题。binlog的保存时间，binlog的类型，如以及磁盘空间ROW、STATEMENT和MIXED。

1. ‌ROW模式‌：

   • 特点：基于变更的数据行进行记录，每一行数据的修改都会被详细记录。
   • 优点：能够清晰地记录每一行数据修改的细节，不容易出现存储过程或触发器调用无法正确复制的问题。
   • 缺点：可能会产生大量的日志内容，尤其是执行大量数据更新时。
   • 应用场景：适用于对数据一致性要求较高的场景，能够确保数据同步的准确性‌。

2. ‌STATEMENT模式‌：

   • 特点：基于SQL语句级别的记录，记录的是执行的SQL语句而不是每行数据的具体变更。
   • 优点：日志量较小，占用磁盘空间少，主从复制的数据也较少，审计数据库更改更加方便。
   • 缺点：无法用于非确定性语句，可能会出现主从库执行结果不一致的情况。
   • 应用场景：适用于大量数据的更新操作，可以减少从库的延迟‌。

3. ‌MIXED模式‌：

   • 特点：混合了ROW模式和STATEMENT模式的优点，根据操作类型自动选择最适合的模式。
   • 优点：结合了ROW模式和STATEMENT模式的优点，既能保证数据一致性，又能减少日志量。
   • 应用场景：适用于需要兼顾数据一致性和性能的场景‌。

• 分析slow query log识别并优化慢查询，一般公司系统会对接各种监控类似Druid的数据库监控、及时关注处理。

5、安全性管理

• 设计和实施用户权限管理策略，最小化权限授予原则。
• 定期更改数据库用户的密码，并使用强密码策略。
• 开启审计功能，监控可疑行为。
• 使用SSL/TLS加密数据传输通道。

6、容量规划与扩展

• 根据业务发展预测和评估数据库的增长趋势，适时进行容量扩展、包括实例数目。
• 监测表空间使用情况，合理规划磁盘空间分配。
• 数据库的连接资源监控、如最大连接数max_connetions

7、系统维护

• 定期检查操作系统状态、内核版本、glibc版本与MySQL版本之间的兼容性、检查业务使用版本
• 优化MySQL配置文件（my.cnf），调整系统参数以适应业务变化。
• 协同运维团队，更新系统补丁和MySQL补丁，确保安全更新及时到位。

8、应急响应

• 建立和完善应急预案，应对可能出现的各种异常状况，如宕机、数据丢失、性能瓶颈、主从同步失败、延迟过大、CPU彪高、占用网络带宽高、以及扩容问题等问题。
• 定期进行灾难恢复演练，确保在紧急情况下能够迅速恢复服务。

9、写入失败

日常开发过程中MySQL数据库写入失败，这可能是由于多种原因导致的，包括权限不足、磁盘空间满、表锁定、数据类型不匹配、主键冲突、SQL语句错误、网络问题等等。

 1.1 权限不足

 如果MySQL用户没有足够的权限，比如没有INSERT权限，那么写入操作将会失败。

-- 假设service用户没有向test_table表插入数据的权限
GRANT INSERT ON test_database.test_table TO 'service'@'localhost';

  1.2 磁盘空间满

如果MySQL数据库所在的磁盘空间已满，将无法继续写入数据。

-- 查看磁盘空间使用情况
df -h

 1.3 表锁定或事务未提交

如果表被锁定或者当前事务未提交，写入操作也会失败。

-- 锁定表
LOCK TABLES test_table WRITE;

-- 尝试插入数据（这将失败，因为表被锁定）
INSERT INTO test_table (column1, column2) VALUES (value1, value2);

-- 解锁表
UNLOCK TABLES;

1.4 数据类型不匹配

如果尝试插入的数据类型与表中定义的数据类型不匹配，写入操作也会失败。

-- 假设test_table的column1是整数类型
CREATE TABLE test_table (column1 INT);

-- 尝试插入字符串类型的数据（这将失败）
INSERT INTO test_table (column1) VALUES ('string_value');

 1.5 主键或唯一约束冲突

如果尝试插入的数据违反了表的主键或唯一约束，写入操作将失败。

-- 假设test_table的column1是主键
CREATE TABLE test_table (column1 INT PRIMARY KEY);

-- 插入一个值
INSERT INTO test_table (column1) VALUES (1);

-- 尝试插入相同的值（这将失败，因为主键冲突）
INSERT INTO test_table (column1) VALUES (1);

1.6 SQL语句错误

 如果SQL语句本身存在语法错误，写入操作也会失败。

-- 错误的SQL语句（缺少逗号）
INSERT INTO test_table (column1 column2) VALUES (value1, value2);

-- 正确的SQL语句
INSERT INTO test_table (column1, column2) VALUES (value1, value2);

1.7 网络问题

如果MySQL服务器无法通过网络被访问，写入操作也会失败。

-- 检查网络连接是否正常
ping mysql_server_ip

1.8 字符集问题

如果客户端和服务器的字符集设置不一致，也可能导致写入失败。

-- 查看字符集设置
SHOW VARIABLES LIKE 'character_set%';

1.9 数据库配置问题

MySQL的配置文件（如my.cnf）设置不当，也可能导致写入失败。

-- 检查MySQL配置文件
cat /etc/my.cnf

1.10 索引失效导致性能问题

虽然索引失效不直接导致写入失败，但会严重影响写入性能。

以下是一些常见的索引失效场景：

• • 使用IS NOT NULL条件。

• • 使用NOT IN或NOT EXISTS条件。

• • 使用OR条件。

• • 使用LIKE '%value%'进行模糊查询。

• • 在索引列上使用函数或进行计算。

1.11 其他潜在问题

还有一些其他潜在问题，如数据库日志满、二进制日志文件满、长时间运行的事务或锁等待等，都可能导致写入失败。