案例一

Too many connections

(連接數(shù)過多,導(dǎo)致連接不上數(shù)據(jù)庫,業(yè)務(wù)無法正常進(jì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.132

ERROR?1040?(00000):?Too?many?connections

解決問題的思路：

1、首先先要考慮在我們 MySQL 數(shù)據(jù)庫參數(shù)文件里面,對(duì)應(yīng)的 max_connections 這個(gè)參數(shù)值是不是設(shè)置的太小了,導(dǎo)致客戶端連接數(shù)超過了數(shù)據(jù)庫所承受的最大值.

• 該值默認(rèn)大小是 151,我們可以根據(jù)實(shí)際情況進(jìn)行調(diào)整.
• 對(duì)應(yīng)解決辦法：set global max_connections=500

但這樣調(diào)整會(huì)有隱患,因?yàn)槲覀儫o法確認(rèn)數(shù)據(jù)庫是否可以承擔(dān)這么大的連接壓力,就好比原來一個(gè)人只能吃一個(gè)饅頭,但現(xiàn)在卻非要讓他吃 10 個(gè),他肯定接受不了.反應(yīng)到服務(wù)器上面,就有可能會(huì)出現(xiàn)宕機(jī)的可能.

所以這又反映出了,我們?cè)谛律暇€一個(gè)業(yè)務(wù)系統(tǒng)的時(shí)候,要做好壓力測(cè)試.保證后期對(duì)數(shù)據(jù)庫進(jìn)行優(yōu)化調(diào)整.

2、其次可以限制 InnoDB的并發(fā)處理數(shù)量,如果 innodb_thread_concurrency = 0(這種代表不受限制) 可以先改成 16 或是 64 看服務(wù)器壓力.

如果非常大,可以先改的小一點(diǎn)讓服務(wù)器的壓力下來之后,然后再慢慢增大,根據(jù)自己的業(yè)務(wù)而定,個(gè)人建議可以先調(diào)整為 16 即可.

MySQL 隨著連接數(shù)的增加性能是會(huì)下降的,在 MySQL 5.7 之前都需要讓開發(fā)配合設(shè)置 thread pool,連接復(fù)用.MySQL 5.7?之后數(shù)據(jù)庫自帶 thread pool 了,連接數(shù)問題也得到了相應(yīng)的解決.

另外對(duì)于有的監(jiān)控程序會(huì)讀取 information_schema 下面的表,可以考慮關(guān)閉下面的參數(shù)：

• innodb_stats_on_metadata=0
• set global innodb_stats_on_metadata=0

案例二

主從復(fù)制報(bào)錯(cuò)類型

Last_SQL_Errno: 1062? (從庫與主庫數(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ò),我們首先要考慮是不是在從庫中誤操作導(dǎo)致的.結(jié)果發(fā)現(xiàn),我們?cè)趶膸熘羞M(jìn)行了一條針對(duì)有主鍵表的SQL語句的插入,導(dǎo)致主庫再插入相同 sql 的時(shí)候,主從狀態(tài)出現(xiàn)異常.發(fā)生主鍵沖突的報(bào)錯(cuò).

解決方法：在確保主從數(shù)據(jù)一致性的前提下,可以在從庫進(jìn)行錯(cuò)誤跳過.一般使用 percona-toolkit 中的 pt-slave-restart 進(jìn)行.

在從庫完成如下操作：

• [root@zs bin]# ./pt-slave-restart -uroot -proot123
• 2017-07-20T14:05:30 p=…,u=root node4-relay-bin.000002??????? 1506 1062

之后最好在從庫中開啟 read_only 參數(shù),禁止在從庫進(jìn)行寫入操作.

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ù)制的過程中,我們要確保兩臺(tái)機(jī)器的 server-id 是唯一的.這里再強(qiáng)調(diào)一下 server-id 的命名規(guī)則(服務(wù)器 ip 地址的最后一位+本 MySQL 服務(wù)的端口號(hào)).

解決方法：在主從兩臺(tái)機(jī)器上設(shè)置不同的 server-id.

Last_SQL_Errno: 1032(從庫少數(shù)據(jù),主庫更新的時(shí)候,從庫報(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

解決問題的辦法：根據(jù)報(bào)錯(cuò)信息,我們可以獲取到報(bào)錯(cuò)日志和position號(hào),然后就能找到主庫執(zhí)行的哪條sql,導(dǎo)致的主從報(bào)錯(cuò).

在主庫執(zhí)行：

/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語句之后,就可以在從庫反向執(zhí)行SQL語句.把從庫缺少的SQL語句補(bǔ)全,解決報(bào)錯(cuò)信息.

在從庫依次執(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> 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安裝過程中的報(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?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

解決思路：遇到這樣的報(bào)錯(cuò)信息,我們要學(xué)會(huì)時(shí)時(shí)去關(guān)注錯(cuò)誤日志 error log 里面的內(nèi)容.看見了關(guān)鍵的報(bào)錯(cuò)點(diǎn)Permission denied,證明當(dāng)前 MySQL 數(shù)據(jù)庫的數(shù)據(jù)目錄沒有權(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)成功.

如何避免這類問題,個(gè)人建議在安裝 MySQL 初始化的時(shí)候,一定加上–user=mysql,這樣就可以避免權(quán)限問題.

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

案例四

數(shù)據(jù)庫密碼忘記的問題

[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 數(shù)據(jù)庫,而且沒有完善的交接文檔.root 密碼可以丟失或者忘記了.

解決思路：目前是進(jìn)入不了數(shù)據(jù)庫的情況,所以我們要考慮是不是可以跳過權(quán)限.因?yàn)樵跀?shù)據(jù)庫中,MySQL 數(shù)據(jù)庫中 user 表記錄著我們用戶的信息.

解決方法：啟動(dòng) MySQL 數(shù)據(jù)庫的過程中,可以這樣執(zhí)行：

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

這樣啟動(dòng),就可以不用輸入密碼,直接進(jìn)入 MySQL 數(shù)據(jù)庫了.然后在修改你自己想要改的 root 密碼即可.

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

案例五

truncate 刪除數(shù)據(jù),導(dǎo)致自動(dòng)清空自增 ID,前端返回報(bào)錯(cuò) not found

這個(gè)問題的出現(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 開始記錄了.當(dāng)前端用主鍵 id 進(jìn)行查詢時(shí),就會(huì)報(bào)沒有這條數(shù)據(jù)的錯(cuò)誤.

個(gè)人建議不要使用 truncate 對(duì)表進(jìn)行刪除操作,雖然可以回收表空間,但是會(huì)涉及自增屬性問題.這些坑,我們不要輕易鉆進(jìn)去.

案例六

阿里云 MySQL 的配置文件

阿里云 MySQL 的配置文件中,需要注意一個(gè)參數(shù)設(shè)置就是：

• lower_case_table_names = 0；默認(rèn)情況.
• lower_case_table_names = 1；是不是區(qū)分大小寫.

如果報(bào)你小寫的表名找不到,那你就把遠(yuǎn)端數(shù)據(jù)庫的表名改成小寫,反之亦然.注意 Mybatis 的 Mapper 文件的所有表名也要相應(yīng)修改.

案例七

數(shù)據(jù)庫總會(huì)出現(xiàn)中文亂碼的情況

有同學(xué)經(jīng)常會(huì)問,為什么我的數(shù)據(jù)庫總會(huì)出現(xiàn)中文亂碼的情況.一堆中文亂碼不知道怎么回事?當(dāng)向數(shù)據(jù)庫中寫入創(chuàng)建表,并插入中文時(shí),會(huì)出現(xiàn)這種問題.此報(bào)錯(cuò)會(huì)涉及數(shù)據(jù)庫字符集的問題.

解決思路：對(duì)于中文亂碼的情況,記住老師告訴你的三個(gè)統(tǒng)一就可以.還要知道在目前的?MySQL?數(shù)據(jù)庫中字符集編碼都是默認(rèn)的 UTF8.

處理辦法：

• 數(shù)據(jù)終端,也就是我們連接數(shù)據(jù)庫的工具設(shè)置為 utf8.
• 操作系統(tǒng)層面,可以通過 cat /etc/sysconfig/i18n 查看,也要設(shè)置為 utf8.
• 數(shù)據(jù)庫層面,在參數(shù)文件中的 mysqld 下,加入 character-set-server=utf8.

Emoji 表情符號(hào)錄入 MySQL 數(shù)據(jù)庫中報(bào)錯(cuò)：

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)

解決思路：針對(duì)表情插入的問題,一定還是字符集的問題.

處理方法：我們可以直接在參數(shù)文件中,加入：

vim /etc/my.cnf

[mysqld]

init-connect=’SET NAMES utf8mb4′
character-set-server=utf8mb4

注：utf8mb4 是 utf8?的超集.

案例八

使用 binlog_format=statement 這種格式,跨庫操作,導(dǎo)致從庫丟失數(shù)據(jù),用戶訪問導(dǎo)致出現(xiàn)錯(cuò)誤數(shù)據(jù)信息

當(dāng)前數(shù)據(jù)庫二進(jìn)制日志的格式為：binlog_format=statement

在主庫設(shè)置 binlog-do-db=mydb1(只同步mydb1這一個(gè)庫).

在主庫執(zhí)行 use mydb2；

insert into mydb1.t1 values (‘bb’)；這條語句不會(huì)同步到從庫.

但是這樣操作就可以；

use mydb1；

insert into mydb1.t1 values (‘bb’)；因?yàn)檫@是在同一個(gè)庫中完成的操作.

在生產(chǎn)環(huán)境中建議使用binlog的格式為row,而且慎用 binlog-do-db 參數(shù).

案例九