目录
mysql单示例故障排查
故障现象1
ERROR 2002 (HY000) : Can't connect to local MySQL serverthrough socket
"/data/mysql/mysql.sock'(2)
问题分析:以上这种情况一般都是数据库未启动、mysql配置文件未指定socket文件或者数据库端口被防火墙拦截导致。
解决方法:启动数据库或者防火墙开放数据库监听端口。
故障现象2
ERROR 1045 (28000) : Access denied for user‘localhost’(using root password: NO)
问题分析：密码不正确或者没有权限访问
解决方法:修改my.cnf主配置文件,在[mysqld]下添加skip-grant-taibles=on,重启数据库。再删除刚刚添加的skip-grant-tables数,重启数据库,使用新密码即可登录。
故障现象3
在使用远程数据库时偶尔会发生远程连接数据库很慢的问题
问题分析:如果MySQL主机查询DNS很慢或是有很多客户端主机时会导致连接很慢.由于开发机器是不能够连接外网的,在进行MySQL连接B时,DNS解析是不可能完成的,从而也就明白了为什么连接那么慢了
解决方法:修改 my.cnf主配置文件,在[mysqld]下添加skip-nameresolve重启数据库可以解决。注意在以后授权里面不能再使用主机名授权。
故障现象4
Can't open file: 'xxx forums. MYIterrno:145)
问题分析:服务器非正常关机,数据库所在空间已满,或一些其它未知的原因,对数据库表造成了损坏。可能是操作系统下直接将数据库文件拷贝移动,会因为文件的属组问题而产生这个错误.
解决方法:可以使用下面的两种方式修复数据表(第一种方法仅适合独立主机用户)
使用MySQL自带的专门用户数据表检查和修复工具myisamchk。-一般情况下只有在命令行下面才能运行myisamchk命令。常用的修复命令为
myisamchk-r数据文件目录/数据表名.MYI;
通过phpMyAdmin修复,phpMyAdmin带有修复数据表的功能,进入到某一个表中后,点击"操作",在下方的"表维护"中点击"修复表"即可。
注意：以上俩种方式执行前一定要备份数据库
修改文件的属组（仅适合独立的主机）
复制数据库文件的过程中没有将数据库文件设置为MySQL运行的帐号可读写(一般适用于Linux和FreeBSD用户)。
故障现象5
ERROR 1129 (HY000): Host'xxx.xxx.xxx.xxx' is blocked because of manyconnection errors;
unblock with 'mysqladmin flush-hosts'
问题分析:由于mysql数据库的参数:max_connect_errors,其默认直是10。当大量(max_connect_errors)的主机去连接 MySQL,总连接请求超过了10次,新的连接就再也无法连接上MySQL服务。同一个ip在短时间内产生太多中断的数据库连接而导致的阻塞(超过mysql数据库max_connectionerrors的最大值
解决方法:
使用mysqladminflush-hosts命令清除缓存,命令执行方法如下
mysqladmin -uroot -p -h 192.168.241.48 flush-hosts
Enter password:
修改mysql配置文件,在[mysqld]下面添加max_connect_errors=1000,然后重启MySQL。
故障现象6
客户端报Toomany connections。
问题分析:连接数超出Mysql的最大连接数限制。
解决方法:在my.cnf配置文件里面增大连接数,然后重启MySQL服务
max connections = 10000
临时修改最大连接数,重启后不生效。需要在my.cnf里面修改配置文件,下次重启生效。
set GLOBAL max_connections=10000;
故障现象7
Warning: World-writable config file'/etc/my.cnf' is ignepredERROR! MySQL is running but PID file could not be found
问题分析:MySQL的配置文件/etc/my.cnf权限不对。
解决方法:chmod 644 /et/my.cnf
故障现象8
InnoDB: Error: page 14178 log sequence number 29455369832
InnoDB: is in the future! Current system log sequence number 29455369832
问题分析:innodb数据文件损坏。
解决方法:修my.cnf配置文件,在[mysqld]下添加innodb_force_recovery=4,启动数据库后备份数据文件,然后去掉该参数,利用备份文件恢复数据
mysql主从故障排查
故障现象1
从库的Slave_I0_Running为NO
The slave I/0 thread stops because master and slave haveequal MySQL serverids: these ids must be different for replication towork (or the--replicate-same-server-id option must be used on slave butthis does notalways make sense;please check the manual before usingit).
问题分析:主库和从库的server-id值一样。
解决方法:修改从库的server-id的值,修改为和主库不-样。修改完后重启,再同步即可。
故障现象2
(2)
故障现象 2
从库的Slave_10_Running为NO
问题分析:造成从库线程为NO的原因会有很多,主要原因是主键冲突或者主库删除或更新数据,从库找不到记录,数据被修改导致。通常状态码报错有1007、1032、1062、1452
解决方法一:
mysql> stop slave;
set_GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql
mysql>
start slave;
解决方法二:
设置用户权限,设置从库只读权限
set global read only=true;
故障现象3
Error initializing relay log position: I/0 error reading the hheader fromthe binary log
分析问题:从库的中继日志relay-bin损坏。
解决方法:手工修复,重新找到同步的binlog和pos点,然后重新同步即可
mysql>CHAN GEMASTER TO MASTER_LOG_FILE='mysql-bin.xxx', MASTER_LOG_POS=xxxx
mysql优化
硬件方面
主要的是cpu，内存，磁盘
关于cpu
CPU对于MySQL应用,推荐使用S.M.P.架构的多路对称CPU。例如:可以使用两颗IntelXeon3.6GHz的CPU。现在比较推荐用4的服务器来专门做数据库服务器,不仅仅是针对于MySQL。
关于内存
物理内存对于一台使用MySQL的DatabaseServer来说,服务器内存建议不要小于2GB,推荐使用4GB以上的物理内存。不过内存对于现在的服务器而言可以说是一个可以忽略的问题,工作中遇到了高端服务器基本上内存都超过了32G。
关于磁盘
磁盘寻道能力(磁盘I/0)。以目前市场上普遍高转速SAS硬盘(15000转/秒)为例,这种硬盘理论上每秒寻道15000次,这是物理特性决定的,没有办法改变。MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以通常认为磁盘I/0是制约MySQL性能的最大因素之一,通常是使用
RAID-0+1磁盘阵列,注意不要尝试使用RAID-5,MySQL在RAID-5磁盘阵列上的效率并不高。如果不考虑硬件的投入成本,也可以考虑固态(SSD)硬盘专门作为数据库服务器使用。数据库的读写性能肯定会提高很多。
msyql配置文件
核心性能优化项
缓冲池大小（innodb_buffer_pool_size）：InnoDB 存储引擎使用缓冲池来缓存数据和索引。增加缓冲池大小可以减少磁盘 I/O，提升查询性能。建议将其设置为服务器物理内存的 60% - 80%，例如在内存为 32GB 的服务器上，可设置为innodb_buffer_pool_size = 24G。
日志文件大小（innodb_log_file_size）：该参数控制 InnoDB 事务日志文件的大小。增大日志文件大小可以减少日志切换的频率，提高写入性能。但过大的日志文件在崩溃恢复时会花费更多时间，一般建议设置为缓冲池大小的 25% - 50%。
线程缓存（thread_cache_size）：用于缓存空闲线程，避免频繁创建和销毁线程带来的开销。可根据服务器的并发情况适当调整，例如设置为thread_cache_size = 64。
查询优化项
** 避免使用 SELECT ***：在查询语句中，尽量明确指定所需的列，而不是使用SELECT *。因为SELECT *会返回表中的所有列，可能会包含不必要的数据，增加网络传输和内存消耗。
使用 JOIN 优化：在多表连接查询时，确保连接条件上有合适的索引。同时，合理选择 JOIN 类型（INNER JOIN、LEFT JOIN 等），根据业务需求减少不必要的数据返回。
避免子查询嵌套：深度嵌套的子查询可能会导致查询优化器难以生成高效的执行计划，尽量将子查询改写为 JOIN 语句。
日志与监控
慢查询日志：开启慢查询日志（slow_query_log = 1）可以记录执行时间超过阈值（long_query_time）的 SQL 语句。通过分析慢查询日志，能够定位性能瓶颈，针对性地进行优化。
通用查询日志：通用查询日志（general_log = 1）记录所有的 SQL 语句，不过由于其会产生大量数据，通常只在调试阶段开启。
监控工具：使用SHOW STATUS命令可以查看 MySQL 的运行状态信息，如查询次数、连接数等。此外，像 Percona Toolkit、MySQL Enterprise Monitor 等工具能提供更全面的监控和分析功能。
innodb高级优化
自适应哈希索引（innodb_adaptive_hash_index）：InnoDB 会根据访问模式自动创建哈希索引，以加速查询。一般情况下保持默认开启状态即可，但在某些特殊场景下，如写操作非常频繁时，关闭该功能可能会提升性能。
自动增长列（innodb_autoinc_lock_mode）：该参数控制自增长列的锁定模式。设置为2（交错模式）可以在高并发插入时提高性能，减少锁等待。
sql方面
合理使用索引：索引是提升查询性能的关键，但并非越多越好。创建索引时要考虑列的选择性（即该列中不同值的比例），选择性越高，索引效果越好。同时，避免创建冗余索引，以免影响插入、更新和删除操作的性能。
避免函数操作：在查询条件中对列使用函数操作，会导致索引失效。例如，应避免使用SELECT * FROM table WHERE YEAR(date_column) = 2024，而应改为SELECT * FROM table WHERE date_column >= '2024-01-01' AND date_column < '2025-01-01'。
创建测试表并插入数据
-- 创建测试表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入测试数据
INSERT INTO users (name, age, email) VALUES
('Alice', 25, 'alice@example.com'),
('Bob', 30, 'bob@example.com'),
('Charlie', 35, 'charlie@example.com');
-- 插入更多数据以模拟真实场景
DELIMITER //
CREATE PROCEDURE insert_more_users()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 10000 DO
INSERT INTO users (name, age, email) VALUES
(CONCAT('User_', i), FLOOR(RAND() * 60), CONCAT('user_', i, '@example.com'));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_more_users();使用explain进行sql优化的步骤及实验验证
-- 未优化前的查询及EXPLAIN分析
EXPLAIN SELECT * FROM users WHERE age = 30;
通过EXPLAIN的输出可以看到，type为ALL，表示进行了全表扫描，这在数据量较大时性能会非常差。
优化步骤：添加索引
-- 添加索引
CREATE INDEX idx_age ON users (age);
优化后查询及explain分析
-- 优化后的查询及EXPLAIN分析
EXPLAIN SELECT * FROM users WHERE age = 30;
此时，EXPLAIN的输出中type变为了ref，表示使用了索引进行查询，性能得到了显著提升。