■ 現象
● 1. my.cnf(my.ini)に設定値を書く
my.cnf
max_connections=500
table_open_cache=2000
● 2. mysqldを起動して値を見てみる
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 214 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 400 |
+------------------+-------+
1 row in set (0.00 sec)
なんじゃこりゃあああ
● 3. おもむろにエラーログをみてみる
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
↓
# less /var/log/mysqld.log | grep Warning
/var/log/mysqld.log
2016-09-18 18:19:25 6809 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
2016-09-18 18:19:25 6809 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 500)
2016-09-18 18:19:25 6809 [Warning] Buffered warning: Changed limits: table_open_cache: 400 (requested 2000)
ほわああああああああああ
■ 対処法
● 1. /etc/security/limits.confに以下の記述を追加する
/etc/security/limits.conf
mysql hard nofile 65535
mysql soft nofile 65535
● 2. /usr/lib/systemd/system/mysqld.serviceに以下の記述を追加する
/usr/lib/systemd/system/mysqld.service
LimitNOFILE=65535
● 3. サービスの再読み込みと起動を行う
# systemctl daemon-reload
# systemctl start mysqld
※上記例はCentOS7.2のコマンド
● 4. 値が反映されたか確認する
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL VARIABLES LIKE 'table_open_cache';
おわり!