MySQL学习笔记(二)

一、MySQL日常运维巡检项目

1、性能监控与调优

  • 使用MySQL自身的SHOW STATUSSHOW VARIABLESEXPLAIN等命令,或结合第三方工具(如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.cnfmy.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 其他潜在问题

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

大道之简

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值