一、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。
ROW模式:
- 特点:基于变更的数据行进行记录,每一行数据的修改都会被详细记录。
- 优点:能够清晰地记录每一行数据修改的细节,不容易出现存储过程或触发器调用无法正确复制的问题。
- 缺点:可能会产生大量的日志内容,尤其是执行大量数据更新时。
- 应用场景:适用于对数据一致性要求较高的场景,能够确保数据同步的准确性。
STATEMENT模式:
- 特点:基于SQL语句级别的记录,记录的是执行的SQL语句而不是每行数据的具体变更。
- 优点:日志量较小,占用磁盘空间少,主从复制的数据也较少,审计数据库更改更加方便。
- 缺点:无法用于非确定性语句,可能会出现主从库执行结果不一致的情况。
- 应用场景:适用于大量数据的更新操作,可以减少从库的延迟。
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 其他潜在问题
还有一些其他潜在问题,如数据库日志满、二进制日志文件满、长时间运行的事务或锁等待等,都可能导致写入失败。