沒遇到過這十個MySQL 資料庫經典錯誤，你一定不是個好工程師

案例一

Too many connections

（連線數過多，導致連線不上資料庫，業務無法正常進行）

問題還原：

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.132

ERROR 1040 (00000): Too many connections

解決問題的思路：

1、首先先要考慮在我們 MySQL 資料庫引數檔案裡面，對應的 max_connections 這個引數值是不是設定的太小了，導致客戶端連線數超過了資料庫所承受的最大值。

• 該值預設大小是 151，我們可以根據實際情況進行調整。

• 對應解決辦法：set global max_connections=500

但這樣調整會有隱患，因為我們無法確認資料庫是否可以承擔這麼大的連線壓力，就好比原來一個人只能吃一個饅頭，但現在卻非要讓他吃 10 個，他肯定接受不了。反應到伺服器上面，就有可能會出現宕機的可能。

所以這又反映出了，我們在新上線一個業務系統的時候，要做好壓力測試。保證後期對資料庫進行最佳化調整。

2、其次可以限制 InnoDB的併發處理數量，如果 innodb_thread_concurrency = 0（這種代表不受限制） 可以先改成 16 或是 64 看伺服器壓力。

如果非常大，可以先改的小一點讓伺服器的壓力下來之後，然後再慢慢增大，根據自己的業務而定，個人建議可以先調整為 16 即可。

MySQL 隨著連線數的增加效能是會下降的，在 MySQL 5.7 之前都需要讓開發配合設定 thread pool，連線復用。MySQL 5.7 之後資料庫自帶 thread pool 了，連線數問題也得到了相應的解決。

另外對於有的監控程式會讀取 information_schema 下麵的表，可以考慮關閉下麵的引數：

• innodb_stats_on_metadata=0

• set global innodb_stats_on_metadata=0

案例二

主從複製報錯型別

Last_SQL_Errno: 1062  （從庫與主庫資料衝突） 

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

針對這個報錯，我們首先要考慮是不是在從庫中誤操作導致的。結果發現，我們在從庫中進行了一條針對有主鍵表的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 manual before using it)

這個報錯出現之後，就能一目瞭然看到兩臺機器的 server-id 是一樣的。

在搭建主從複製的過程中，我們要確保兩臺機器的 server-id 是唯一的。這裡再強調一下 server-id 的命名規則（伺服器 ip 地址的最後一位+本 MySQL 服務的埠號）。

解決方法：在主從兩臺機器上設定不同的 server-id。

Last_SQL_Errno: 1032（從庫少資料，主庫更新的時候，從庫報錯）

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

解決問題的辦法：根據報錯資訊，我們可以獲取到報錯日誌和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> 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> exit
Bye
[root@node4 bin]# ./pt-slave-restart -uroot -proot123

2017-07-20T14:31:37 p=…,u=root node4-relay-bin.000005         283 1032

案例三

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 with databases from /data/mysql170720

14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended

170720 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

解決思路：遇到這樣的報錯資訊，我們要學會時時去關註錯誤日誌 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 with databases from /data/mysql

啟動成功。

如何避免這類問題，個人建議在安裝 MySQL 初始化的時候，一定加上–user=mysql，這樣就可以避免許可權問題。

./mysql_install_db –basedir=/usr/local/mysql/ –datadir=/data/mysql/ –defaults-file=/etc/my.cnf –user=mysql

案例四

資料庫密碼忘記的問題

[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 資料庫，而且沒有完善的交接檔案。root 密碼可以丟失或者忘記了。

解決思路：目前是進入不了資料庫的情況，所以我們要考慮是不是可以跳過許可權。因為在資料庫中，MySQL 資料庫中 user 表記錄著我們使用者的資訊。

解決方法：啟動 MySQL 資料庫的過程中，可以這樣執行：

/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf  –skip-grant-tables &

這樣啟動，就可以不用輸入密碼，直接進入 MySQL 資料庫了。然後在修改你自己想要改的 root 密碼即可。

update mysql.user set password=password(‘root123′) where user=’root’;

案例五

truncate 刪除資料，導致自動清空自增 ID，前端傳回報錯 not found

這個問題的出現，就要考慮下 truncate 和 delete 的區別了，看下實驗演練：

首先先建立一張表：

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

插入三條資料：

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 進行刪除全表資訊，再插入新值。

結果發現 truncate 把自增初始值重置了，自增屬性從 1 開始記錄了。當前端用主鍵 id 進行查詢時，就會報沒有這條資料的錯誤。

個人建議不要使用 truncate 對錶進行刪除操作，雖然可以回收表空間，但是會涉及自增屬性問題。這些坑，我們不要輕易鑽進去。

案例六

阿裡雲 MySQL 的配置檔案

阿裡雲 MySQL 的配置檔案中，需要註意一個引數設定就是：

• lower_case_table_names = 0；預設情況。

• lower_case_table_names = 1；是不是區分大小寫。

如果報你小寫的表名找不到，那你就把遠端資料庫的表名改成小寫，反之亦然。註意 Mybatis 的 Mapper 檔案的所有表名也要相應修改。

案例七

資料庫總會出現中文亂碼的情況

有同學經常會問，為什麼我的資料庫總會出現中文亂碼的情況。一堆中文亂碼不知道怎麼回事？當向資料庫中寫入建立表，並插入中文時，會出現這種問題。此報錯會涉及資料庫字符集的問題。

解決思路：對於中文亂碼的情況，記住老師告訴你的三個統一就可以。還要知道在目前的 MySQL 資料庫中字符集編碼都是預設的 UTF8。

處理辦法：

• 資料終端，也就是我們連線資料庫的工具設定為 utf8。

• 作業系統層面，可以透過 cat /etc/sysconfig/i18n 檢視，也要設定為 utf8。

• 資料庫層面，在引數檔案中的 mysqld 下，加入 character-set-server=utf8。

Emoji 表情符號錄入 MySQL 資料庫中報錯：

Caused by: java.sql.SQLException: Incorrect string value: ‘😗🅒 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=’SET NAMES utf8mb4′
character-set-server=utf8mb4

註：utf8mb4 是 utf8 的超集。

案例八

使用 binlog_format=statement 這種格式，跨庫操作，導致從庫丟失資料，使用者訪問導致出現錯誤資料資訊

當前資料庫二進位制日誌的格式為：binlog_format=statement

在主庫設定 binlog-do-db=mydb1（只同步mydb1這一個庫）。

在主庫執行 use mydb2；

insert into mydb1.t1 values (‘bb’)；這條陳述句不會同步到從庫。

但是這樣操作就可以；

use mydb1；

insert into mydb1.t1 values (‘bb’)；因為這是在同一個庫中完成的操作。

在生產環境中建議使用binlog的格式為row，而且慎用 binlog-do-db 引數。

案例九