[DB][MySQL] DB新舊轉移的注意事項

Posted On 2018-09-11

[DB][MySQL] DB新舊轉移的注意事項

	Old	New
OS Version	Ubuntu 12.04.1 LTS	Ubuntu 16.04.5 LTS
DB Version	MySQL 5.5.28	MySQL 5.7.23

本次進行新舊DB轉移的過程中發生了一些意想不到的情況，已下紀錄相關問題及排除過程

問題1

不要被設定檔的預設值給騙了!

這個真的是個超級大黑坑，新舊版的設定檔名稱預設都一樣，所以如果沒注意到以為其實新舊版的參數名稱都一樣的話那就大錯特錯了= =

# thread_concurrency 5.7版後這個參數被取消，但是5.7版的**預設設定檔**內卻還有這個參數存在，只是被註解掉而已，如果你以為都有這個參數而把註解拿掉的話，你的MySQL就開不起來了

# log_slow_queries(舊版)，slow_query_log(新版)，名字的排列不一樣，偏偏5.7版的**預設設定檔**內還是用舊版的排列順序，所以你只把註解拿掉的話也是GG

# 另外要開啟Slow Log的話一定要有下面這兩行才行
slow_query_log          = on or 1 # (2個都代表開啟的意思)
slow_query_log_file     = /var/log/mysql/mysql-slow.log


# 另外被取消的有下列這些
innodb_adaptive_hash_index_partitions
innodb_log_block_size
innodb_sched_priority_cleaner

問題2

新主機的Loading很容易會飆高，但是新主機的硬體規格比舊主機還要好，為什麼會這樣呢?

後來查到MySQL預設的連線數量是有設定的，例如每個帳號的連線數量就是無限制，DB連線數量最大是214，檔案開啟數量則是1024等，也由於預設值的關係造成當DB連線數會很容易達到最大上限值，造成主機的負荷量提高，有趣的是當我們在/etc/mysql/mysql.conf.d/mysqld.cnf的設定檔中設定了max_connections=700，max_user_connections=600的時候，卻發現MySQL的最大連線數還是在214，這邊紀錄問題排除過程

## 主機上
vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
....
....
max_connections          = 700
max_user_connections     = 600

## DB中
show global variables like '%max_connecti%';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 214   |
+-----------------+-------+
1 row in set (0.00 sec)

## 造成這個問題是有兩個原因，一是系統的「一個process最大可開起的檔案數」的值預設是1024，可以使用 ulimit -a，或是從mysql下面的指令看到這個值：
show global variables like '%open_files_limit%';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

## 這個值會限定MySQL同時連線的數量，所以要先改這個數值。這是linux系統本身的設定，所以必須使用sudo將下面的設定加入到/etc/security/limits.conf這個檔案。
vim /etc/security/limits.conf

* soft nofile 65536
* hard nofile 65536

# 注意，開到65536是非常大的，基本上可以說是最大值了，所以請根據自己的主機規格和安全性的考慮來調整這個數字，當設定完成，可以重啟系統讓設定值生效。
reboot

## 第二個原因是在啟動mysql的時候要解除open file的限制，方法是在啟動mysql的service檔裡加上LimitNOFILE=65535的設定
vim /lib/systemd/system/mysql.service

LimitNOFILE=65535
# 同上述，65535很大，請記得調整，調整完後重啟服務
systemctl daemon-reload
systemctl restart mysql && systemctl status mysql

## **但是實測後發現，只改systemd就可以的樣子，詳細的區別可能要去看官方文件和測試才會知道了**

問題3

連線數過高除了上述提高DB的連線量之外，還有一種方式就是限制每個DB帳號的連線數量，MySQL預設是開無限制，所以需要透過指令或GUI介面來修改，這邊紀錄指令的方式

SELECT user,max_user_connections FROM mysql.user WHERE USER='test_User' AND host='192.168.1.%';

+-------------------+----------------------+
| user              | max_user_connections |
+-------------------+----------------------+
| test_User        |                    0 |
+-------------------+----------------------+
1 row in set (0.00 sec)

# If this is a nonzero value, change it back with:
GRANT USAGE ON *.* TO 'test_User'@'192.168.1.%' WITH MAX_USER_CONNECTIONS 0;
or
UPDATE mysql.user SET max_user_connections = 0 WHERE user='test_User' AND host='192.168.1.%';
FLUSH PRIVILEGES;

問題4

上述的部分調整都需要重啟或重載DB的設定檔才能成功，但目前因為Sleep的Process數量太多，DB處理不過來後只好繼續開新的Process，短時間內想要改善Server的話可以使用製作程式的方式來砍掉全部正在Sleep的Process

#!/bin/bash
mysql -u root -p<YOUR PASSWORD> -e "select concat('KILL ',id,';')  into outfile '/var/lib/mysql-files/sleep_processes.txt' FROM information_schema.processlist WHERE Command = 'Sleep';"
mysql -u root -p<YOUR PASSWORD> -e "source /var/lib/mysql-files/sleep_processes.txt;"
rm -rf /var/lib/mysql-files/sleep_processes.txt