柚子快報邀請碼778899分享：MYSQL數(shù)據(jù)庫故障排除與優(yōu)化

http://yzkb.51969.com/

目錄

MySQL 單實例故障排查

MySQL 主從故障排查

MySQL 優(yōu)化

MySQL 單實例故障排查

故障現(xiàn)象 1

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

問題分析：以上這種情況一般都是數(shù)據(jù)庫未啟動或者數(shù)據(jù)庫端口被防火墻攔截導致。

解決方法：啟動數(shù)據(jù)庫或者防火墻開放數(shù)據(jù)庫監(jiān)聽端口。

故障現(xiàn)象? 2 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

問題分析：密碼不正確或者沒有權(quán)限訪問

解決方法： 修改 my.cnf 主配置文件，在[mysqld]下添加 skip-grant-tables，重啟數(shù)據(jù)庫。最后修改密碼命令 mysql>use mysql; mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; mysql> flush privileges;

再刪除剛剛添加的 skip-grant-tables 參數(shù)，重啟數(shù)據(jù)庫，使用新密碼即可登錄。

重新授權(quán)

mysql>grant all on *.* to 'root'@'mysql-server' identified by '123456';

故障現(xiàn)象 3

在使用遠程連接數(shù)據(jù)庫時偶爾會發(fā)生遠程連接數(shù)據(jù)庫很慢的問題。

問題分析：由于開發(fā)機器是不能夠連接外網(wǎng)的，在進行 MySQL 連接時，DNS 解析是不可能完成的， 從而連接就慢了。

解決方法：修改 my.cnf 主配置文件，在[mysqld]下添加 skip-name-resolve，重啟數(shù)據(jù)庫可以解決*（注意在以后授權(quán)里面不能再使用主機名授權(quán)。）

故障現(xiàn)象 4

Can't open file: 'xxx_forums.MYI'. (errno: 145)

問題分析：

服務(wù)器非正常關(guān)機，數(shù)據(jù)庫所在空間已滿，或一些其它未知的原因，對數(shù)據(jù)庫表造 ?成了損壞。 可能是操作系統(tǒng)下直接將數(shù)據(jù)庫文件拷貝移動，會因為文件的屬組問題而產(chǎn)生這個錯誤。

解決方法：

可以使用下面的兩種方式修復(fù)數(shù)據(jù)表（第一種方法僅適合獨立主機用戶）：

使用 MySQL 自帶的專門用戶數(shù)據(jù)表檢查和修復(fù)工具 myisamchk。一般情況下只有在命令行下面才能運行 myisamchk 命令，常用的修復(fù)命令為：

myisamchk -r 數(shù)據(jù)文件目錄/數(shù)據(jù)表名.MYI；

通過 phpMyAdmin 修復(fù)， phpMyAdmin 帶有修復(fù)數(shù)據(jù)表的功能，進入到某一個表中后，點擊“操作”，在下方的“表維護”中點擊“修復(fù)表”即可。 注意：以上兩種修復(fù)方式在執(zhí)行前一定要備份數(shù)據(jù)庫。

修改文件的屬組（僅適合獨立主機用戶）：

復(fù)制數(shù)據(jù)庫文件的過程中沒有將數(shù)據(jù)庫文件設(shè)置為 MySQL 運行的帳號可讀寫（一般適用于 Linux 和 FreeBSD 用戶）。

故障現(xiàn)象 5 ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

問題分析：由于 mysql 數(shù)據(jù)庫的參數(shù)：max_connect_errors，其默認值是 10。當大量(max_connect_errors)的主機去連接 MySQL，總連接請求超過了 10 次，新的連接就再也無法連接上 MySQL 服務(wù)。同一個 ip 在短時間內(nèi)產(chǎn)生太多中斷的數(shù)據(jù)庫連接而導致的阻塞（ 超過 mysql 數(shù)據(jù)庫max_connection_errors 的最大值）

解決方法： 使用 mysqladmin flush-hosts 命令清除緩存，命令執(zhí)行方法如下：

mysqladmin -uroot -p -h 192.168.241.48 flush-hosts Enter password:

修改 mysql 配置文件，在[mysqld]下面添加 max_connect_errors=1000，然后重啟 MySQL。

故障現(xiàn)象 6

客戶端報 Too many connections。 問題分析：連接數(shù)超出 Mysql 的最大連接數(shù)限制。解決方法：

在 my.cnf 配置文件里面增大連接數(shù)，然后重啟 MySQL 服務(wù)。

max_connections = 10000

臨時修改最大連接數(shù)，重啟后不生效。需要在 my.cnf 里面修改配置文件，下次重啟生效。

set GLOBAL max_connections=10000;

故障現(xiàn)象 7

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 權(quán)限不對。解決方法：

chmod 644 /et/my.cnf

故障現(xiàn)象 8

InnoDB: Error: page 14178 log sequence number 29455369832 InnoDB: is in the future! Current system log sequence number 29455369832

問題分析：innodb 數(shù)據(jù)文件損壞。

解決方法：修改 my.cnf 配置文件，在[mysqld]下添加 innodb_force_recovery=4, 啟動數(shù)據(jù)庫后備份數(shù)據(jù)文件，然后去掉該參數(shù)，利用備份文件恢復(fù)數(shù)據(jù)。

MySQL 主從故障排查

故障現(xiàn)象 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 的值，修改為和主庫不一樣。修改完后重啟，再同步即可。

故障現(xiàn)象 2

從庫的 Slave_IO_Running 為 NO

問題分析：造成從庫線程為 NO 的原因會有很多，主要原因是主鍵沖突或者主庫刪除或更新數(shù)據(jù)，從庫找不到記錄，數(shù)據(jù)被修改導致。通常狀態(tài)碼報錯有 1007、1032、1062、1452 等。

解決方法一：

mysql> stop slave; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> start slave;

解決方法二：

設(shè)置用戶權(quán)限，設(shè)置從庫只讀權(quán)限

set global read_only=true;

故障現(xiàn)象 3

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

分析問題：從庫的中繼日志 relay-bin 損壞。

解決方法：手工修復(fù)，重新找到同步的 binlog 和 pos 點，然后重新同步即可。

mysql>CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx;

MySQL 優(yōu)化

硬件方面

服務(wù)器硬件，最主要的無非 CPU、內(nèi)存、磁盤三大關(guān)鍵因素。

（1）關(guān)于 CPU

CPU 對于 MySQL 應(yīng)用，推薦使用 S.M.P.架構(gòu)的多路對稱 CPU。例如：可以使用兩顆Intel Xeon 3.6GHz 的 CPU?，F(xiàn)在比較推薦用 4U 的服務(wù)器來專門做數(shù)據(jù)庫服務(wù)器，不僅僅是針對于 MySQL。 （2）關(guān)于內(nèi)存

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

（3）關(guān)于磁盤

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

MySQL 配置文件

通常默認的 my.cnf 配置文件無法發(fā)揮出 MySQL 最高的性能，所以需要根據(jù)不同的硬件進行優(yōu)化，配置文件的優(yōu)化也是重點。下面是物理內(nèi)存為 32G 的數(shù)據(jù)庫優(yōu)化參數(shù)，具體從全局、二進制日志、主從、innodb、myisam 幾個方面優(yōu)化，僅供參考。 （1）default-time-zone=+8:00

默認 MySQL 使用的是系統(tǒng)時區(qū)，修改為北京時間，也就是所說的東八區(qū)。

（2）interactive_timeout = 120

服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù)。

（3）wait_timeout = 120

服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)。

（4）open_files_limit = 10240

MySQL 服務(wù)器打開文件句柄數(shù)限制。

（5）group_concat_max_len = 102400

MySQL 默認的拼接最大長度為 1024 個字節(jié)，由于 1024 個字節(jié)會出現(xiàn)不夠用的情況， 根據(jù)實際情況進行修改。 （6）user=mysql

使用 mysql 用戶運行。

（7）character-set-server=utf8、init_connect='SET NAMES utf8'

設(shè)置字符集為 utf8

（8）back_log = 600

在 MySQL 暫時停止響應(yīng)新請求之前，短時間內(nèi)的多少個請求可以被存在堆棧中。如果系統(tǒng)在短時間內(nèi)有很多連接，則需要增大該參數(shù)的值，該參數(shù)值指定到來的 TCP/IP 連接的監(jiān)聽隊列的大小。默認值 50。 （9）max_connections = 5000

MySQL 允許最大的進程連接數(shù)，如果經(jīng)常出現(xiàn) Too Many Connections 的錯誤提示， 則需要增大此值。 （10）max_connect_errors = 6000

設(shè)置每個主機的連接請求異常中斷的最大次數(shù)。當超過該次數(shù)，MySQL 服務(wù)器將禁止 host 的連接請求，直到 MySQL 服務(wù)器重啟或通過flush hosts 命令清空此host 的相關(guān)信息。

柚子快報邀請碼778899分享：MYSQL數(shù)據(jù)庫故障排除與優(yōu)化

http://yzkb.51969.com/

文章鏈接