那些年，我們都踩過(guò)的十個(gè)MySQL經(jīng)典大坑……
今天就給大家列舉 MySQL 數(shù)據(jù)庫(kù)中，最經(jīng)典的十大錯(cuò)誤案例，并附有處理問(wèn)題的解決思路和方法，希望能給剛?cè)胄校驍?shù)據(jù)庫(kù)愛(ài)好者一些幫助，今后再遇到任何報(bào)錯(cuò)，我們都可以很淡定地去處理。學(xué)習(xí)任何一門(mén)技術(shù)的同時(shí)，其實(shí)就是自我修煉的過(guò)程。沉下心，嘗試去擁抱數(shù)據(jù)的世界！
Top 1：Too many connections（連接數(shù)過(guò)多，導(dǎo)致連接不上數(shù)據(jù)庫(kù)，業(yè)務(wù)無(wú)法正常進(jìn)行）
問(wèn)題還原
mysql> show variables like '%max_connection%';| Variable_name | Value |max_connections | 151 | mysql> set global max_connections=1;Query OK, 0 rows affected (0.00 sec)[root@node4 ~]# mysql -uzs -p123456 -h 192.168.56.132ERROR 1040 (00000): Too many connections解決問(wèn)題的思路：
首先先要考慮在我們 MySQL 數(shù)據(jù)庫(kù)參數(shù)文件里面，對(duì)應(yīng)的max_connections 這個(gè)參數(shù)值是不是設(shè)置的太小了，導(dǎo)致客戶(hù)端連接數(shù)超過(guò)了數(shù)據(jù)庫(kù)所承受的最大值。該值默認(rèn)大小是151，我們可以根據(jù)實(shí)際情況進(jìn)行調(diào)整。對(duì)應(yīng)解決辦法：set global max_connections=500 但這樣調(diào)整會(huì)有隱患，因?yàn)槲覀儫o(wú)法確認(rèn)數(shù)據(jù)庫(kù)是否可以承擔(dān)這么大的連接壓力，就好比原來(lái)一個(gè)人只能吃一個(gè)饅頭，但現(xiàn)在卻非要讓他吃 10 個(gè)，他肯定接受不了。反應(yīng)到服務(wù)器上面，就有可能會(huì)出現(xiàn)宕機(jī)的可能。所以這又反應(yīng)出了，我們?cè)谛律暇€一個(gè)業(yè)務(wù)系統(tǒng)的時(shí)候，要做好壓力測(cè)試。保證后期對(duì)數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化調(diào)整。
其次可以限制Innodb 的并發(fā)處理數(shù)量，如果 innodb_thread_concurrency = 0（這種代表不受限制） 可以先改成 16或是64 看服務(wù)器壓力。如果非常大，可以先改的小一點(diǎn)讓服務(wù)器的壓力下來(lái)之后,然后再慢慢增大,根據(jù)自己的業(yè)務(wù)而定。個(gè)人建議可以先調(diào)整為 16 即可。MySQL 隨著連接數(shù)的增加性能是會(huì)下降的，可以讓開(kāi)發(fā)配合設(shè)置 thread pool，連接復(fù)用。在MySQL商業(yè)版中加入了thread pool這項(xiàng)功能,另外對(duì)于有的監(jiān)控程序會(huì)讀取 information_schema 下面的表，可以考慮關(guān)閉下面的參數(shù)。
innodb_stats_on_metadata=0set global innodb_stats_on_metadata=0Top 2：主從復(fù)制報(bào)錯(cuò)類(lèi)型
Last_SQL_Errno: 1062 （從庫(kù)與主庫(kù)數(shù)據(jù)沖突）
Last_Errno: 1062 Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000014, end_log_pos 1505針對(duì)這個(gè)報(bào)錯(cuò)，我們首先要考慮是不是在從庫(kù)中誤操作導(dǎo)致的。結(jié)果發(fā)現(xiàn)，我們?cè)趶膸?kù)中進(jìn)行了一條針對(duì)有主鍵表的 sql 語(yǔ)句的插入，導(dǎo)致主庫(kù)再插入相同 sql 的時(shí)候，主從狀態(tài)出現(xiàn)異常。發(fā)生主鍵沖突的報(bào)錯(cuò)。**
解決方法：
在確保主從數(shù)據(jù)一致性的前提下，可以在從庫(kù)進(jìn)行錯(cuò)誤跳過(guò)。一般使用 percona-toolkit 中的 pt-slave-restart 進(jìn)行。在從庫(kù)完成如下操作
[root@zs bin]# ./pt-slave-restart -uroot -proot1232017-07-20T14:05:30 p=...,u=root node4-relay-bin.000002 1506 1062之后最好在從庫(kù)中開(kāi)啟 read_only 參數(shù)，禁止在從庫(kù)進(jìn)行寫(xiě)入操作
Last_IO_Errno: 1593（server-id沖突）
Last_IO_Error: Fatal error: 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)#這個(gè)報(bào)錯(cuò)出現(xiàn)之后，就看一目了然看到兩臺(tái)機(jī)器的 server-id 是一樣的。在搭建主從復(fù)制的過(guò)程中，我們要確保兩臺(tái)機(jī)器的 server-id 是唯一的。這里再?gòu)?qiáng)調(diào)一下 server-id 的命名規(guī)則（服務(wù)器 ip 地址的最后一位+本 MySQL 服務(wù)的端口號(hào)）
解決方法：
在主從兩臺(tái)機(jī)器上設(shè)置不同的 server-id。
Last_SQL_Errno: 1032（從庫(kù)少數(shù)據(jù)，主庫(kù)更新的時(shí)候，從庫(kù)報(bào)錯(cuò)）
Last_SQL_Error:Could not execute Update_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000014, end_log_pos 1708解決問(wèn)題的辦法：
根據(jù)報(bào)錯(cuò)信息，我們可以獲取到報(bào)錯(cuò)日志和position號(hào)，然后就能找到主庫(kù)執(zhí)行的哪條sql，導(dǎo)致的主從報(bào)錯(cuò)。在主庫(kù)執(zhí)行：
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.logcat 1.log#170720 14:20:15 server id 3 end_log_pos 1708 CRC32 0x97b6bdec Update_rows: table id 113 flags: STMT_END_F### UPDATE `test`.`t`### WHERE### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='dd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */### SET### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='ddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */# at 1708#170720 14:20:15 server id 3 end_log_pos 1739 CRC32 0xecaf1922 Xid = 654COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;獲取到 sql 語(yǔ)句之后，就可以在從庫(kù)反向執(zhí)行 sql 語(yǔ)句。把從庫(kù)缺少的 sql 語(yǔ)句補(bǔ)全，解決報(bào)錯(cuò)信息。在從庫(kù)依次執(zhí)行：
mysql> insert into t (b) values ('ddd');Query OK, 1 row affected (0.01 sec)mysql> stop slave;Query OK, 0 rows affected (0.00 sec)mysql> exitBye[root@node4 bin]# ./pt-slave-restart -uroot -proot1232017-07-20T14:31:37 p=...,u=root node4-relay-bin.000005 283 1032Top 3：MySQL安裝過(guò)程中的報(bào)錯(cuò)
[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &[1] 3758[root@zs data]# 170720 14:41:24 mysqld_safe Logging to '/data/mysql/error.log'.170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql170720 14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql2017-07-20 14:41:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)./usr/local/mysql/bin/mysqld: File '/data/mysql/mysql-bin.index' not found (Errcode: 13 - Permission denied)2017-07-20 14:41:25 4388 [ERROR] Aborting解決思路：
遇到這樣的報(bào)錯(cuò)信息，我們要學(xué)會(huì)時(shí)時(shí)去關(guān)注錯(cuò)誤日志 error log 里面的內(nèi)容。看見(jiàn)了關(guān)鍵的報(bào)錯(cuò)點(diǎn)Permission denied。證明當(dāng)前 MySQL 數(shù)據(jù)庫(kù)的數(shù)據(jù)目錄沒(méi)有權(quán)限。
解決方法：
[root@zs data]# chown mysql:mysql -R mysql[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &[1] 4402[root@zs data]# 170720 14:45:56 mysqld_safe Logging to '/data/mysql/error.log'.170720 14:45:56 mysqld_safe Starting mysqld daemon with databases from /data/mysql#啟動(dòng)成功。如何避免這類(lèi)問(wèn)題，個(gè)人建議在安裝MySQL初始化的時(shí)候，一定加上--user=mysql，這樣就可以避免權(quán)限問(wèn)題。
./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --defaults-file=/etc/my.cnf --user=mysqlTop 4：數(shù)據(jù)庫(kù)密碼忘記的問(wèn)題
[root@zs ~]# mysql -uroot -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@zs ~]# mysql -uroot -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)#我們有可能剛剛接手別人的 MySQL 數(shù)據(jù)庫(kù)，而且沒(méi)有完善的交接文檔，公眾號(hào)：Java精選。root 密碼可以丟失或者忘記了。解決思路：
目前是進(jìn)入不了數(shù)據(jù)庫(kù)的情況，所以我們要考慮是不是可以跳過(guò)權(quán)限。因?yàn)樵跀?shù)據(jù)庫(kù)中，mysql數(shù)據(jù)庫(kù)中user表記錄著我們用戶(hù)的信息。
解決方法：
啟動(dòng) MySQL 數(shù)據(jù)庫(kù)的過(guò)程中，可以這樣執(zhí)行：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &這樣啟動(dòng)，就可以不用輸入密碼，直接進(jìn)入 mysql 數(shù)據(jù)庫(kù)了。然后在修改你自己想要改的root密碼即可。update mysql.user set password=password('root123') where user='root';Top 5：truncate刪除數(shù)據(jù)，導(dǎo)致自動(dòng)清空自增ID，前端返回報(bào)錯(cuò)not found。
這個(gè)問(wèn)題的出現(xiàn)，就要考慮下truncate 和 delete 的區(qū)別了。
看下實(shí)驗(yàn)演練：
#首先先創(chuàng)建一張表；CREATE TABLE `t` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`)) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8#插入三條數(shù)據(jù)：mysql> insert into t (b) values ('aa');Query OK, 1 row affected (0.00 sec)mysql> insert into t (b) values ('bb');Query OK, 1 row affected (0.00 sec)mysql> insert into t (b) values ('cc');Query OK, 1 row affected (0.00 sec)mysql> select * from t;+-----+------+| a | b |+-----+------+| 300 | aa || 301 | bb || 302 | cc |+-----+------+3 rows in set (0.00 sec)#先用 delete 進(jìn)行刪除全表信息，再插入新值。結(jié)果發(fā)現(xiàn)truncate把自增初始值重置了，自增屬性從1開(kāi)始記錄了。當(dāng)前端用主鍵id進(jìn)行查詢(xún)時(shí)，就會(huì)報(bào)沒(méi)有這條數(shù)據(jù)的錯(cuò)誤。個(gè)人建議不要使用truncate對(duì)表進(jìn)行刪除操作，雖然可以回收表空間，但是會(huì)涉及自增屬性問(wèn)題。這些坑，我們不要輕易鉆進(jìn)去。
Top 6：阿里云 MySQL 的配置文件中，需要注意一個(gè)參數(shù)設(shè)置就是：
lower_case_table_names = 0;默認(rèn)情況lower_case_table_names = 1;是不區(qū)分大小寫(xiě) . 如果報(bào)你小寫(xiě)的表名找不到, 那你就把遠(yuǎn)端數(shù)據(jù)庫(kù)的表名改成小寫(xiě) , 反之亦然 . 注意 Mybatis 的 Mapper 文件的所有表名也要相應(yīng)修改Top 7：數(shù)據(jù)庫(kù)總會(huì)出現(xiàn)中文亂碼的情況
解決思路：
對(duì)于中文亂碼的情況，記住老師告訴你的三個(gè)統(tǒng)一就可以。還要知道在目前的mysql數(shù)據(jù)庫(kù)中字符集編碼都是默認(rèn)的UTF8
處理辦法：
數(shù)據(jù)終端，也就是我們連接數(shù)據(jù)庫(kù)的工具設(shè)置為 utf8 2、操作系統(tǒng)層面；可以通過(guò) cat /etc/sysconfig/i18n 查看；也要設(shè)置為 utf8 3、數(shù)據(jù)庫(kù)層面；在參數(shù)文件中的 mysqld 下，加入 character-set-server=utf8。
Emoji 表情符號(hào)錄入 mysql 數(shù)據(jù)庫(kù)中報(bào)錯(cuò)。
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)解決思路：針對(duì)表情插入的問(wèn)題，一定還是字符集的問(wèn)題。處理方法：我們可以直接在參數(shù)文件中，加入
vim /etc/my.cnf[mysqld]init-connect='SET NAMES utf8mb4'character-set-server=utf8mb4注：utf8mb4 是 utf8 的超集。Top 8：使用 binlog_format=statement 這種格式，跨庫(kù)操作，導(dǎo)致從庫(kù)丟失數(shù)據(jù)，用戶(hù)訪問(wèn)導(dǎo)致出現(xiàn)錯(cuò)誤數(shù)據(jù)信息。
#當(dāng)前數(shù)據(jù)庫(kù)二進(jìn)制日志的格式為：binlog_format=statement
在主庫(kù)設(shè)置binlog-do-db=mydb1（只同步mydb1這一個(gè)庫(kù)）
在主庫(kù)執(zhí)行use mydb2；
insert into mydb1.t1 values ('bb');這條語(yǔ)句不會(huì)同步到從庫(kù)。
但是這樣操作就可以；
use mydb1;
insert into mydb1.t1 values ('bb');因?yàn)檫@是在同一個(gè)庫(kù)中完成的操作。
#在生產(chǎn)環(huán)境中建議使用binlog的格式為row，而且慎用binlog-do-db參數(shù)。Top 9：MySQL 數(shù)據(jù)庫(kù)連接超時(shí)的報(bào)錯(cuò)
org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfully to the server was 43200 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection 'autoReconnect=true' to avoid this problem.org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with sessionorg.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch updatecom.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08003org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/error: ** BEGIN NESTED EXCEPTION **大多數(shù)做 DBA 的同學(xué)，可能都會(huì)被開(kāi)發(fā)人員告知，你們的數(shù)據(jù)庫(kù)報(bào)了這個(gè)錯(cuò)誤了。趕緊看看是哪里的問(wèn)題。
這個(gè)問(wèn)題是由兩個(gè)參數(shù)影響的，wait_timeout 和 interactive_timeout。數(shù)據(jù)默認(rèn)的配置時(shí)間是28800（8小時(shí)）意味著，超過(guò)這個(gè)時(shí)間之后，MySQL 數(shù)據(jù)庫(kù)為了節(jié)省資源，就會(huì)在數(shù)據(jù)庫(kù)端斷開(kāi)這個(gè)連接，Mysql服務(wù)器端將其斷開(kāi)了，但是我們的程序再次使用這個(gè)連接時(shí)沒(méi)有做任何判斷，所以就掛了。
解決思路：
先要了解這兩個(gè)參數(shù)的特性；這兩個(gè)參數(shù)必須同時(shí)設(shè)置，而且必須要保證值一致才可以。我們可以適當(dāng)加大這個(gè)值，8小時(shí)太長(zhǎng)了，不適用于生產(chǎn)環(huán)境。因?yàn)橐粋€(gè)連接長(zhǎng)時(shí)間不工作，還占用我們的連接數(shù)，會(huì)消耗我們的系統(tǒng)資源。
解決方法：
可以適當(dāng)在程序中做判斷；強(qiáng)烈建議在操作結(jié)束時(shí)更改應(yīng)用程序邏輯以正確關(guān)閉連接；然后設(shè)置一個(gè)比較合理的timeout的值（根據(jù)業(yè)務(wù)情況來(lái)判斷）
Top 10 ：can't open file (errno:24)
有的時(shí)候，數(shù)據(jù)庫(kù)跑得好好的，突然報(bào)不能打開(kāi)數(shù)據(jù)庫(kù)文件的錯(cuò)誤了。
解決思路：
首先我們要先查看數(shù)據(jù)庫(kù)的error log。然后判斷是表?yè)p壞，還是權(quán)限問(wèn)題。還有可能磁盤(pán)空間不足導(dǎo)致的不能正常訪問(wèn)表；操作系統(tǒng)的限制也要關(guān)注下；用 perror 工具查看具體錯(cuò)誤！
linux:/usr/local/mysql/bin # ./perror 24OS error code 24: Too many open files超出最大打開(kāi)文件數(shù)限制！ulimit -n查看系統(tǒng)的最大打開(kāi)文件數(shù)是65535，不可能超出！那必然是數(shù)據(jù)庫(kù)的最大打開(kāi)文件數(shù)超出限制！在 MySQL 里查看最大打開(kāi)文件數(shù)限制命令：show variables like 'open_files_limit';發(fā)現(xiàn)該數(shù)值過(guò)小，改為2048，重啟 MySQL，應(yīng)用正常
處理方法：
repair table ；chown mysql權(quán)限#清理磁盤(pán)中的垃圾數(shù)據(jù)今后還會(huì)繼續(xù)總結(jié) MySQL 中的各種報(bào)錯(cuò)處理思路與方法，希望跟各位老鐵們，同學(xué)們一起努力。
