MySQL max connections 설정

MySQL Too many connections 오류 조치

MySQL max_connections 설정

1 확인

Copy

[root@zetadb ~]# mysql -uroot -p
... (생략)
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

Copy

mysql> show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 152   |
+----------------------+-------+
1 row in set (0.00 sec)

→ max_connections 설정이 151인데 152개의 커넥션 발생 (Too many connections)

Copy

mysql> show status like 'Aborted_connects';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_connects | 1     |
+------------------+-------+
1 row in set (0.00 sec)

→ Too many connections 오류로 커넥션 차단 총 1건 발생

2 온라인 설정

• 즉시 적용됨

Copy

mysql> set global max_connections=500;
Query OK, 0 rows affected (0.00 sec)

3 확인 2

Copy

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

4 영구 설정

• my.cnf에 max_connections 설정 추가
• mysqld 재시작시에 적용됨

Copy

[root@zetadb ~]# vi /etc/my.cnf

Copy

[mysqld]
max_connections = 500

5 확인 3

• 가능하면 MySQL 재시작하여 다시 확인

Copy

[root@zetadb ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Copy

[root@zetadb ~]# mysql -uroot -p
... (생략)
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

6 같이 보기

• my.cnf

7 참고 자료

• http://threestory.tistory.com/6