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

[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
    

Reference

開啟 slow query log, 儲存執行慢的 MySQL 語句

mysql 5.7 unknown variable ‘log_slow_queries=/var/log/mysql/mysql-slow.log’

MySQL的最大連線數一直被設成214

Set Limit for open files for MySQL on Ubuntu 16.04 with systemd

Raising the Maximum Number of File Descriptors (Open Files) on Ubuntu 14.04 Trusty

修改UBUNTU ULIMIT限制

How to permanently raise ulimit ‘open files’ and MySQL ‘open_files_limit’

Cannot set limit of MySQL open-files-limit from 1024 to 65535

MySQL values open_files_limit and max_connections are not applied

Ubuntu 16.04 Server MySql open_file_limit won’t go higher than 65536

Understanding /etc/security/limits.conf file

查詢與設定 Linux 使用者可開啟的行程數量上限值,限制開啟程式的數目

提高 MySQL 的 open files limit

How to set ulimit values

How to Increase Number of Open Files Limit in Linux

How to get rid of “maximum user connections” error ?

Shell Script to auto kill mysql sleep processes

Add a Comment