数据库管理与高可用-MySQL故障排查与生产环境优化

目录

#1.1MySQL单案例故障排查

  1.1.1MySQL常见的故障排查

  1.1.2MySQL主从故障排查

#2.1MySQL优化

  2.1.1硬件方面的优化

  2.1.2进程方面的优化

#3.1MySQL存储引擎

  3.1.1 MyISAM存储引擎

  3.1.2 InnoDB存储引擎

---

1.1MySQL单案例故障排查

  1.1.1MySQL常见的故障排查

 （1）故障现象1

问题解析：以上情况一般都是数据库未启动，mysql配置文件未指定socket文件或者数据库端口被防火墙拦截导致。

解决方法：启动数据库或者防火墙开放数据库监听端口。

   （2）故障现象2

问题分析：密码不正确或者没有权限访问。

解决方法：修改my.cnf主配置文件，在[mysqld]下添加skip-grant-tables=on,重启数据库。最后修改密码命令。

密码不正确：

mysql>update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

mysql>flush privileges;

mysql>alter user 'root'@'localhost' identified by '123456';

  （3）故障现象3

          在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题。

问题解析：如果MySQL主机查询DNS很慢或者是很多客户端主机时会导致连接很慢，由于开发机器是不能够连接外网的，在进行MySQL连接时，DNS解析式不可能完成的，从而也就明白了为什么连接那么慢了。

解决方法：修改my.cnf主配置文件，在[mysqld]下添加skip-name-resolve,重启数据库可以解决。注意在以后授权里面不能再使用主机名授权。

   （4）故障现象4

Warning: World - writable config file '/etc/my.cnf' is ignored
ERROR! MySQL is running but PID file could not be found

问题分析：MySQL的配置文件/etc/my.cnf权限不对。

解决方法：chmod 644 /etc/my.cnf

1.1.2MySQL主从故障排查

  （1）故障现象1

从库的 Slave_IO_Running 为 NO
The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

问题分析：主库和从库的server-id值一样。

解决方法：修改从库的server-id的值，修改为何主库不一样。修改完后重启，再同步即可。

 （2）故障现象2

从库的Slave_IO_Running为NO

问题分析：造成从库线程为NO的原因会很多，主要原因是主键冲突或者主库删除或更新数据，从库找不到记录，数据被修改导致。通常状态码报错有1007,1032,1062,1452.

解决方法：设置用户权限，设置从库只读权限。

set global read_only=true;

 （3）故障现象3

Error initializing relay log position: I/O error reading the header from the binary log

问题分析：从库的中继日志relay-bin损坏。

解决方法：手工修复，重新找到同步的binlog和pos点，然后重新同步即可。

mysql>chan gemaster to master_log_file='mysql-bin.xxx,master_log_pos=xxx';

2.1MySQL优化

 2.1.1硬件方面的优化

   （1）关于CPU

        针对 x86、ARM 等不同 CPU 架构，启用对应的优化选项（如CONFIG_X86_64或CONFIG_ARM64），开启硬件特性支持（如 AVX、NEON 指令集加速）。

   （2）关于内存

     内存优化的核心目标是提升内存利用率、降低内存泄漏风险、减少内存访问延迟，并确保进程间内存隔离与安全性。

    （3）关于磁盘

     磁盘优化的核心目标是提升 I/O 吞吐量、降低访问延迟、减少磁盘磨损，并保障数据可靠性与一致性。

2.1.2进程方面的优化

   进程优化的核心目标是提升资源利用率、降低进程间竞争、避免资源泄漏，并确保关键进程的稳定性与优先级。

3.1MySQL存储引擎

       MySQL 的存储引擎设计体现了 “按需选择” 的理念：InnoDB 凭借事务和行级锁成为主流选择，而 MyISAM、 等引擎则在特定场景下提供补充能力。实际应用中需根据业务需求、数据规模和性能目标灵活选择，或通过混合引擎（如 InnoDB 主表 + Memory 缓存表）优化整体架构。

 3.1.1MyISAM存储引擎

     特点

1. 非事务安全

   • 不支持事务和行级锁，仅支持表级锁（Table-Level Locking），并发性能较差。

2. 存储结构

   • 数据和索引分别存储为 .frm（表结构）、.MYD（数据）、.MYI（索引）文件。

   • 使用非聚簇索引，索引和数据分离存储。

   • 性能特性

     • 读取速度快，插入数据时锁表时间短（适合批量插入）。

     • 支持快速计数（COUNT(*) 直接读取元数据）。

适用场景

       读多写少、无需事务的场景（如日志系统、统计报表）。

        对存储空间敏感的场景（MyISAM 表文件更小）。

3.1.2 InnoDB存储引擎

  特点

1. 事务安全（ACID 兼容）

   • 支持事务（Transaction），通过 COMMIT/ROLLBACK 保证数据一致性。

   • 提供行级锁（Row-Level Locking），锁粒度小，并发性能较好。

2. 外键约束（Foreign Key）

   • 支持表间外键关联，保证数据完整性。

3. 存储结构

   • 数据和索引存储在共享表空间（默认）或独立表空间（innodb_file_per_table=ON）中。

   • 使用聚簇索引（Clustered Index），数据文件按主键顺序组织，辅助索引指向主键值。

4. 崩溃恢复

   • 支持重做日志（Redo Log）和回滚日志（Undo Log），崩溃后可快速恢复数据。

适用场景

    高并发读写、需要事务支持的场景（如电商订单、用户管理系统）。

    数据一致性要求高的业务（如金融、交易系统）。