案例一

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:?‘e???e?…’?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 參數.

案例九