今天就給大家列舉 MySQL 資料庫中，最經典的十大錯誤案例，並附有處理問題的解決思路和方法，希望能給剛入行，或資料庫愛好者一些幫助，今後再遇到任何報錯，我們都可以很淡定地去處理。學習任何一門技術的同時，其實就是自我修煉的過程。沈下心，嘗試去擁抱數據的世界！
Top 1：Too many connections（連線數過多，導致連線不上資料庫，業務無法正常進行）
問題還原
mysql> showvariableslike'%max_connection%';
| Variable_name | Value |
max_connections | 151 |
mysql> setglobal max_connections=1;Query OK, 0 rows affected (0.00 sec)
[root@node4 ~]# mysql -uzs -p123456 -h 192.168.56.132
ERROR 1040 (00000): Too many connections
解決問題的思路：
首先先要考慮在我們 MySQL 資料庫參數檔裏面，對應的
max_connections 這個參數值是不是設定的太小了
，導致客戶端連線數超過了資料庫所承受的最大值。該值預設大小是151，我們可以根據實際情況進行調整。對應解決辦法：set global max_connections=500
但這樣調整會有隱患，因為我們無法確認資料庫是否可以承擔這麽大的連線壓力，就好比原來一個人只能吃一個饅頭，但現在卻非要讓他吃 10 個，他肯定接受不了。反應到伺服器上面，就有可能會出現宕機的可能。所以這又反應出了，我們在新上線一個業務系統的時候，要做好壓力測試。保證後期對資料庫進行最佳化調整。
其次可以
限制Innodb 的並行處理數量
，如果 innodb_thread_concurrency = 0（這種代表不受限制） 可以先改成 16或是64 看伺服器壓力。如果非常大，可以先改的小一點讓伺服器的壓力下來之後,然後再慢慢增大,根據自己的業務而定。個人建議可以先調整為 16 即可。MySQL 隨著連線數的增加效能是會下降的，可以讓開發配合設定 thread pool，連線復用。在MySQL商業版中加入了thread pool這項功能,另外對於有的監控程式會讀取 information_schema 下面的表，可以考慮關閉下面的參數。
innodb_stats_on_metadata=0
set global innodb_stats_on_metadata=0
Top 2：（主從復制報錯型別）
Last_SQL_Errno: 1062 （從庫與主庫數據沖突）
Last_Errno: 1062
Last_Error: Could not execute Write_rows eventontable test.t;
Duplicate entry '4' for key 'PRIMARY',
Error_code: 1062; handlererror HA_ERR_FOUND_DUPP_KEY;
the event's master log mysql-bin.000014, end_log_pos 1505
針對這個報錯，我們首先要考慮是不是在
從庫中誤操作導致的
。結果發現，我們在從庫中進行了一條針對有主鍵表的 sql 語句的插入，導致主庫再插入相同 sql 的時候，主從狀態出現異常。發生主鍵沖突的報錯。**
解決方法：
在確保主從數據一致性的前提下，可以在從庫進行錯誤跳過。一般使用 percona-toolkit 中的 pt-slave-restart 進行。 在從庫完成如下操作
[root@zs bin]# ./pt-slave-restart -uroot -proot123
2017-07-20T14:05:30 p=...,u=root node4-relay-bin.000002 1506 1062
之後最好在從庫中開啟 read_only 參數，禁止在從庫進行寫入操作
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 manualbeforeusing it)
#這個報錯出現之後，就看一目了然看到兩台機器的 server-id 是一樣的。
在搭建主從復制的過程中，我們要確保兩台機器的 server-id 是唯一的。這裏再強調一下 server-id 的命名規則（伺服器 ip 地址的最後一位+本 MySQL 服務的埠號）
解決方法：
在主從兩台機器上設定不同的 server-id。
Last_SQL_Errno: 1032（從庫少數據，主庫更新的時候，從庫報錯）
Last_SQL_Error:
Could not execute Update_rows eventontable test.t; Can't find record
in 't', Error_code: 1032; handlererror HA_ERR_KEY_NOT_FOUND; the
event's master log mysql-bin.000014, end_log_pos 1708
解決問題的辦法：
根據報錯資訊，我們可以獲取到報錯日誌和position號，然後就能找到主庫執行的哪條sql，導致的主從報錯。在主庫執行：
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.log
cat 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 = 654
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
獲取到 sql 語句之後，就可以在從庫反向執行 sql 語句。把從庫缺少的 sql 語句補全，解決報錯資訊。在從庫依次執行：
mysql> insertinto t (b) values ('ddd');
Query OK, 1 row affected (0.01 sec)
mysql> stopslave;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@node4 bin]# ./pt-slave-restart -uroot -proot123
2017-07-20T14:31:37 p=...,u=root node4-relay-bin.000005 283 1032
Top 3：MySQL安裝過程中的報錯
[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 withdatabasesfrom /data/mysql170720
14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended
17072014:41:24 mysqld_safe Starting mysqld daemon withdatabasesfrom /data/mysql2017-07-20
14:41:250 [Warning] TIMESTAMPwith implicit DEFAULTvalueis 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'notfound (Errcode: 13 - Permission denied)
2017-07-2014:41:254388 [ERROR] Aborting
解決思路：
遇到這樣的報錯資訊，我們要學會時時去關註錯誤日誌 error log 裏面的內容。看見了關鍵的報錯點
Permission denied。證明當前 MySQL 資料庫的數據目錄沒有許可權。
解決方法：
[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 withdatabasesfrom /data/mysql
#啟動成功。
如何避免這類問題，個人建議在安裝
MySQL初始化的時候，一定加上--user=mysql，
這樣就可以避免許可權問題。
./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --defaults-file=/etc/my.cnf --user=mysql
Top 4：資料庫密碼忘記的問題
[root@zs ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@zs ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
#我們有可能剛剛接手別人的 MySQL 資料庫，而且沒有完善的交接文件，公眾號：Java精選。root 密碼可以遺失或者忘記了。
解決思路：
目前是進入不了資料庫的情況，所以我們要考慮是不是
可以跳過許可權
。因為在資料庫中，mysql資料庫中user表記錄著我們使用者的資訊。
解決方法：
啟動 MySQL 資料庫的過程中，可以這樣執行：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &
這樣啟動，就可以不用輸入密碼，直接進入 mysql 資料庫了。然後在修改你自己想要改的root密碼即可。
update mysql.user setpassword=password('root123') whereuser='root';
Top 5：truncate 刪除數據，導致自動清空自增ID，前端返報酬錯 not found。
這個問題的出現，就要
考慮下truncate 和 delete 的區別了。
看下實驗演練：
#首先先建立一張表；
CREATETABLE`t` (
`a`int(11) NOTNULL AUTO_INCREMENT,
`b`varchar(20) DEFAULTNULL,
PRIMARY KEY (`a`),
KEY`b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=300DEFAULTCHARSET=utf8
#插入三條數據：
mysql> insertinto t (b) values ('aa');
Query OK, 1 row affected (0.00 sec)
mysql> insertinto t (b) values ('bb');
Query OK, 1 row affected (0.00 sec)
mysql> insertinto 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 進行刪除全表資訊，再插入新值。
結果發現truncate把自增初始值重設了，自增內容從1開始記錄了。當前端用主鍵id進行查詢時，就會報沒有這條數據的錯誤。個人建議不要使用truncate對表進行刪除操作，雖然可以回收表空間，但是會涉及自增內容問題。這些坑，我們不要輕易鉆進去。
Top 6：阿裏雲 MySQL 的配置檔中，需要註意一個參數設定就是：
lower_case_table_names = 0;預設情況
lower_case_table_names = 1;是不區分大小寫 . 如果報你小寫的表名找不到, 那你就把遠端資料庫的表名改成小寫 , 反之亦然 . 註意 Mybatis 的 Mapper 檔的所有表名也要相應修改
Top 7：資料庫總會出現中文亂碼的情況
解決思路：
對於中文亂碼的情況，記住老師告訴你的三個統一就可以。還要知道在目前的mysql資料庫中字元集編碼都是預設的UTF8
處理辦法：
1、數據終端，也就是我們連線資料庫的工具設定為 utf8 2、作業系統層面；可以透過 cat /etc/sysconfig/i18n 檢視；也要設定為 utf8 3、資料庫層面；在參數檔中的 mysqld 下，加入 character-set-server=utf8。
Emoji 表情符號錄入 mysql 資料庫中報錯。
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1
at 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)
解決思路： 針對表情插入的問題，一定還是字元集的問題。 處理方法： 我們可以直接在參數檔中，加入
vim /etc/my.cnf
[mysqld]
init-connect='SETNAMES utf8mb4'
character-set-server=utf8mb4
註：utf8mb4 是 utf8 的超集。
Top 8：使用 binlog_format=statement 這種格式，跨庫操作，導致從庫遺失數據，使用者存取導致出現錯誤數據資訊。
#當前資料庫二進制日誌的格式為：binlog_format=statement
在主庫設定binlog-do-db=mydb1（只同步mydb1這一個庫）
在主庫執行use mydb2；
insertinto mydb1.t1 values ('bb');這條語句不會同步到從庫。
但是這樣操作就可以；
use mydb1;
insertinto mydb1.t1 values ('bb');因為這是在同一個庫中完成的操作。
#在生產環境中建議使用binlog的格式為row，而且慎用binlog-do-db參數。
Top 9：MySQL 資料庫連線超時的報錯
org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01
org.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 usein your application, increasing the server configured valuesforclient timeouts, orusing the Connector/J connection'autoReconnect=true'to avoid this problem.
org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state withsession
org.hibernate.exception.JDBCConnectionException: Could notexecute JDBC batch update
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
org.hibernate.util.JDBCExceptionReporter - SQLError:0, SQLState: 08003
org.hibernate.util.JDBCExceptionReporter - Nooperations allowed afterconnection closed. Connection was implicitly closed due to underlying exception/error:
** BEGINNESTEDEXCEPTION **
#大多數做 DBA 的同學，可能都會被開發人員告知，你們的資料庫報了這個錯誤了。趕緊看看是哪裏的問題。
這個問題是由
兩個參數影響的，wait_timeout 和 interactive_timeout。
數據預設的配置時間是28800（8小時）意味著，超過這個時間之後，MySQL 資料庫為了節省資源，就會在資料庫端斷開這個連線，Mysql伺服器端將其斷開了，但是我們的程式再次使用這個連線時沒有做任何判斷，所以就掛了。
解決思路：
先要了解這兩個參數的特性；這兩個參數必須同時設定，而且必須要保證值一致才可以。我們可以適當加大這個值，8小時太長了，不適用於生產環境。因為一個連線長時間不工作，還占用我們的連線數，會消耗我們的系統資源。
解決方法：
可以適當在程式中做判斷；強烈建議在操作結束時更改應用程式邏輯以正確關閉連線；然後設定一個比較合理的timeout的值（根據業務情況來判斷）
Top 10 ：can't open file (errno:24)
有的時候，資料庫跑得好好的，突然報不能開啟資料庫檔的錯誤了。
解決思路：
首先我們要先檢視資料庫的error log。然後判斷是表損壞，還是許可權問題。還有可能磁盤空間不足導致的不能正常存取表；作業系統的限制也要關註下；用 perror 工具檢視具體錯誤！
linux:/usr/local/mysql/bin # ./perror 24
OS error code 24: Too many open files
超出最大開啟檔數限制！ulimit -n檢視系統的最大開啟檔數是65535，不可能超出！那必然是資料庫的最大開啟檔數超出限制！
在 MySQL 裏檢視最大開啟檔數限制命令：show variables like 'open_files_limit';
發現該數值過小，改為2048，重新開機 MySQL，套用正常
處理方法：
repairtable ；
chown mysql許可權
#清理磁盤中的垃圾數據
今後還會繼續總結 MySQL 中的各種報錯處理思路與方法，希望跟各位老鐵們，同學們一起努力。多溝通多交流！
來源：張甦 blog.51cto.com/sumongodb/1949426
>>
END
精品資料，超贊福利，免費領
微信掃碼/長按辨識 添加【技術交流群】
群內每天分享精品學習資料
最近開發整理了一個用於速刷面試題的小程式；其中收錄了上千道常見面試題及答案(包含基礎、並行、JVM、MySQL、Redis、Spring、SpringMVC、SpringBoot、SpringCloud、訊息佇列等多個型別)，歡迎您的使用。
👇👇
👇點選"閱讀原文"，獲取更多資料（持續更新中）
