linux——mysql故障排查與生產環境優化

一，mysql數據庫常見的故障

1，故障現象1

2，故障現象2

3，故障現象3

４，故障現象４

５，故障現象５

６，故障現象６

二，mysql主從故障排查

1，故障現象1

2，故障現象2

三，mysql硬件方面的優化

1，CPU優化

2，內存優化

3，存儲優化

四，mysql配置文件優化

1，連接相關方面的優化

2，lnnoDB引擎優化

3，查詢優化

4，日志配置

5，核心性能優化

五，什么是mysql的引擎

1，mysql引擎是用來干什么的

2，lnnoDB(默認引擎)

3，Mylsam(歷史引擎)

4，lnonDB與mylsam的區別

六，SQL方面的優化

一，mysql數據庫常見的故障

1，故障現象1

mysql: [Warning] Using a password on the command line interface can be insecure.

?問題分析：這只是一個警告信息，當你使用類似?mysql -u root -p123456?的命令登陸數據庫時，

密碼明文暴露：密碼會在命令行歷史記錄（~/.bash_history）中留存
進程可見性：在進程列表（ps aux）中其他用戶可能看到密碼
日志風險：如果命令被記錄或審計，密碼會被明文存儲

解決方法：登陸數據庫時不要把密碼輸入到外邊，使用類似命令進行登陸 mysql -uroot -p

2，故障現象2

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/data/mysql.sock' (2)

問題分析：這種情況一般都是數據庫未啟動，mysql中的配置文件為指定sock文件或者數據庫端口被防火墻攔截導致。

解決方法：查看數據庫是否啟動，防火墻開放數據庫監聽端口，/etc/my.cnf配置文件指定sock目錄位置例如：socket=/usr/local/mysql/data/mysql.sock

3，故障現象3

忘記mysql登陸密碼如何解決。

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

?問題分析：密碼不正確或者沒有權限訪問

解決方法：

[root@bogon ~]# vim /etc/my.cnf        ##編輯mysql配置文件，添加一行
[mysqld]
skip-grant-tables=on        ##跳過密碼認證進入到數據庫systemctl restart mysqld    ##修改完之后重啟生效[root@bogon ~]# mysql -uroot　　　　　　　　 ##無需指定密碼登陸到數據庫update mysql.user set authentication_string='' where user='root' and Host='localhost';
flush privileges;alter user 'root'@'localhost' identified by '123456';　　　##修改新密碼為123456##刪除skip-grant-tables=on，使用新密碼登陸即可

４，故障現象４

使用遠程連接數據庫發生遠程連接數據庫很慢的問題

問題分析：如果 MySQL 主機查詢 DNS 很慢或是有很多客戶端主機時會導致連接很慢.由于開發機器是不能夠連接外網的，在進行MySQL連接時，DNS 解析是不可能完成的，從而也就明白了為什么連接那么慢了。

解決方法：修改 my.cnf 主配置文件，在[mysgld]下添加 skip-name-resolve,重啟數據庫可以解決。注意在以后授權里面不能再使用主機名授權。

５，故障現象５

MySQL連接數過載

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 connection errors 的最大值)。

解決方法：

＃＃使用flush-hosts命令清理緩存
mysqladmin -uroot-p -h 192.168.10.102 flush-hosts　　　　　　＃＃使用此命令清理緩存
Enter password：方法二：
修改 mysql 配置文件，在[mysqld]下面添加 max connect errors=1000,

６，故障現象６

客戶端報 Too many connections.

問題分析：連接數超出mysql的最大連接數限制

解決方法：

[root@bogon ~]# vim /etc/my.cnf             ##修改mysql數據庫的最大連接數，修改完成后需要重啟
max_connections=2048

７，故障現象７

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

問題分析:MySQL 的配置文件/etc/my.cnf 權限不對，

解決方法:

chmod 644 /etc/my.cnf　　將MySQL配置文件權限改為６４４

二，mysql主從故障排查

1，故障現象1

使用show slave status\G 查看從數據庫復制狀態，slave_IO_running為NO

問題分析：從庫和主庫的server-id值一樣

解決方法：修改從庫和主庫的server-id值不一樣，關閉u防火墻或者開啟3306端口，然后重啟mysql數據庫，重新同步。

2，故障現象2

從數據庫的SQL線程為no

Slave_SQL_Running: No?

問題原因：MySQL 復制中的 SQL 線程停止了工作

解決方法：

##在主庫上執行命令SHOW MASTER STATUS;重新查看file和position##在從庫上重新執行主從連接命令
change master to master_host='192.168.10.101',master_user='myslave',master_password='123456',master_log_file='file',master_log_pos=posttion;##從數據庫stop slave;   start slave   ##重啟slave并查看狀態

三，mysql硬件方面的優化

1，CPU優化

CPU 對于 MySQL 應用，推薦使用 S.M.P.架構的多路對稱 CPU。例如:可以使用兩顆 Intel Xeon 3.6GHz的 CPU。現在比較推薦用 4U 的服務器來專門做數據庫服務器，不僅僅是針對于 MySQL。

2，內存優化

物理內存對于一臺使用 MySQL 的 Database Server 來說，服務器內存建議不要小于 2GB，推薦使用 4GB 以上的物理內存。不過內存對于現在的服務器而言可以說是一個可以忽略的問題,工作中遇到了高端服務器基本上內存都超過了32G。

3，存儲優化

以目前市場上普遍高轉速 SAS 硬盤(15000 轉/秒)為例，這種硬盤理論上每秒尋道 15000 次，這是物理特性決定的，沒有辦法改變。 MySQL 每秒鐘都在進行大量、復雜的查詢操作，對磁盤的讀寫量可想而知。所以通常認為磁盤 I/0 是制約 MySQL 性能的最大因素之一，通常是使用RAID-0+1 磁盤陣列，注意不要嘗試使用 RAID-5，MySQL 在 RAID-5 磁盤陣列上的效率并不高。如果不考慮硬件的投入成本，也可以考慮固態(SSD)硬盤專門作為數據庫服務器使用。數據庫的讀寫性能肯定會提高很多。

四，mysql配置文件優化

MySQL 配置文件/etc/my.cnf的優化可以顯著提升數據庫性能。以下是主要的優化方向和詳細配置建議：

1，連接相關方面的優化

max_connections = 500 ????????#最大連接數，根據應用需求調整
thread_cache_size = 32? ???????#線程緩存大小，減少連接創建開銷
table_open_cache = 4000 ?????#表緩存數量

2，lnnoDB引擎優化

innodb_log_file_size = 1G????????# 重做日志文件大小，大事務需要更大的日志
innodb_log_buffer_size = 64M? ? ? ? ?# 日志緩沖區大小
innodb_flush_log_at_trx_commit = 1 (數據安全) 或 2 (性能優先)? ? ?# 1=每次提交都刷盤(最安全)，2=每秒刷盤(性能更好)
innodb_file_per_table = ON ????????# 每個表使用獨立表空間
innodb_flush_method = O_DIRECT ????????# Linux下推薦值，減少雙緩沖

3，查詢優化

sort_buffer_size = 4M????????# 排序緩沖區大小
join_buffer_size = 4M????????# 連接操作緩沖區大小
read_buffer_size = 2M????????# 全表掃描時的緩沖區
read_rnd_buffer_size = 4M? ? ?# 隨機讀緩沖區

4，日志配置

slow_query_log=ON? ? ? ? ?#啟用慢查詢日志，0N
long_query_time=1? ? ? ? ? #定義慢査詢閾值
log_error=/var/log/mysql/error.log? ?##指定錯誤日志路徑
binlog_format? ? ? ? #指定二進制文件日志格式(主從復制需要)
expire_logs_days=7? ? ? ? ?#自動清理舊的二進制日志天數。

5，核心性能優化

內存配置

innodb_buffer_pool_size：#總內存的50-70%
innodb_log_buffer_size：#大事務需增大
? ? key_buffer_size：MyISAM? #專用(如不使用可設小值)

I/O優化

innodb_io_capacity和innodb_io_capacity_max(SSD環境可增大)
innodb_flush_neighbors(SSD建議關閉)
innodb_read_io_threads和innodb_write_io_threads

并發控制

innodb_thread_concurrency(通常設為CPU核心數×2)
thread_cache_size(減少線程創建開銷)
table_open_cache(減少表打開開銷)

五，什么是mysql的引擎

1，mysql引擎是用來干什么的

MySQL 引擎（存儲引擎）是數據庫管理系統的核心組件，負責數據的存儲、檢索、索引和事務處理。不同的引擎提供不同的功能特性，用戶可以根據業務需求選擇最適合的引擎。

數據存儲與管理：

引擎決定數據在磁盤上的存儲格式（如 InnoDB 的聚簇索引、MyISAM 的索引與數據分離）。
負責數據文件的組織、緩存和讀取（如 InnoDB 的緩沖池緩存熱點數據）。

事務處理：

原子性（Atomicity）：事務中的操作要么全部成功，要么全部失敗。
一致性（Consistency）：事務執行前后數據保持一致狀態。
隔離性（Isolation）：多個事務并發執行時互不干擾（通過鎖或 MVCC 實現）。
持久性（Durability）：事務提交后數據永久保存（通過日志保證）。

2，lnnoDB(默認引擎)

特點如下：

表級鎖：所有操作鎖整張表，并發性能差。
不支持事務：不保證數據原子性和持久性。
不支持外鍵：無法強制關聯表間的數據完整性。
索引與數據分離：索引和數據文件分開存儲（.MYI?索引文件、.MYD?數據文件）。
全文索引：支持全文檢索（MySQL 5.6 前 InnoDB 不支持）。

查看所有支持的索引：SHOW ENGINES;

##永久指定mysql引擎，編輯 MySQL 配置文件my.cnf，在[mysqld]部分添加：
[mysqld]
default-storage-engine=InnoDB      指定mysql引擎為InnoDB##創建表時指定引擎
CREATE TABLE my_table (id INT PRIMARY KEY) ENGINE=InnoDB;##查看已有表使用的引擎
mysql> show table status like 'users'\G
*************************** 1. row ***************************Name: usersEngine: InnoDB

適用場景：

高并發事務型業務
數據一致性要求高的場景
高并發讀寫混合場景
大數據量存儲

3，Mylsam(歷史引擎)

特點如下：

表級鎖：所有操作鎖整張表，并發性能差。
不支持事務：不保證數據原子性和持久性。
不支持外鍵：無法強制關聯表間的數據完整性。
索引與數據分離：索引和數據文件分開存儲（.MYI?索引文件、.MYD?數據文件）。
全文索引：支持全文檢索

適用場景：

讀多寫少的靜態數據場景
全文搜索場景
臨時表或統計分析
輕量級應用

六，SQL方面的優化

SQL優化是確保數據庫高效運行的關鍵，其核心在于通過減少資源消耗(如CPU、內存、磁盤 I/0)來提升査詢響應速度，避免慢查詢導致用戶體驗下降或系統崩潰。

準備用于測試的數據庫和表

#創建測試庫
Create database test;#創建用戶表
Use test;CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL,age INT NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);#插入 10 萬條測試數據（使用存儲過程生成）
DELIMITER $$
CREATE PROCEDURE insert_users()
BEGINDECLARE i INT DEFAULT 0;WHILE i < 100000 DOINSERT INTO users (name, email, age) VALUES (CONCAT('user', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));SET i = i + 1;END WHILE;
END$$
DELIMITER ;select * from users;          ##當表里面的數據量非常龐大時，使用此語句查看會非常慢

使用explain對sql優化

mysql> select * from users where name='user111';     
+--------+---------+---------------------+-----+---------------------+
| id     | name    | email               | age | created_at          |
+--------+---------+---------------------+-----+---------------------+
|    112 | user111 | user111@example.com |  38 | 2025-05-09 08:16:12 |
| 100112 | user111 | user111@example.com |  38 | 2025-05-09 08:17:00 |
+--------+---------+---------------------+-----+---------------------+
2 rows in set (0.04 sec)              ##普通查看數據的速度mysql> explain select * from users where name='user111';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 199578 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)      ##使用explain查看數據的速度

添加索引優化查詢速度

mysql> alter table users add index idx_name(name);   ##添加名為idx_name值為name的索引
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> select * from users where name='user111';
+--------+---------+---------------------+-----+---------------------+
| id     | name    | email               | age | created_at          |
+--------+---------+---------------------+-----+---------------------+
|    112 | user111 | user111@example.com |  38 | 2025-05-09 08:16:12 |
| 100112 | user111 | user111@example.com |  38 | 2025-05-09 08:17:00 |
+--------+---------+---------------------+-----+---------------------+
2 rows in set (0.00 sec)              ##驗證并觀看查詢時間