MySQL: max_connections 수정

Check current max_connections:

SHOW VARIABLES LIKE 'max_connections';

 

Changing the maximum number of connections that MySQL can handle involves modifying the max_connections setting. This parameter determines how many concurrent connections MySQL can accept. Here’s how you can adjust this setting, either temporarily or permanently:

Temporarily Changing Max Connections

1. Via MySQL Command Line:

Connect to your MySQL server using a MySQL client (like the MySQL command line tool).

Execute the following SQL command to change the max connections:

SET GLOBAL max_connections = 1000;

Replace 1000 with the number of connections you want to allow.

This change is temporary and will be lost after the MySQL server restarts.

 

 

Permanently Changing Max Connections

1. Via MySQL Configuration File:

- Locate your MySQL configuration file (my.cnf or my.ini).

   The location of this file can vary depending on your operating system and MySQL version.

- Linux: Typically /etc/mysql/my.cnf

- Windows: Often C:\ProgramData\MySQL\MySQL Server x.x\my.ini

- Open the configuration file in a text editor with administrative or root privileges.

- Find the [mysqld] section in the file, and add or change the max_connections parameter:

[mysqld]
max_connections = 1000

  Replace 1000 with the desired number of max connections.

 

Save the file and restart the MySQL server to apply the changes. Depending on your system, you can restart MySQL using a command like:

 

Linux

sudo systemctl restart mysql

  or

sudo service mysql restart

 

Windows: Open Services via Control Panel or by typing services.msc into the Run dialog, then find MySQL and restart it.

 

Considerations

• Server Resources: Increasing the max_connections setting will allow more concurrent connections, but this also requires more server resources (CPU, memory). Ensure your server hardware can handle the increased load.
• Performance Tuning: Consider adjusting other settings like innodb_buffer_pool_size and thread_cache_size to optimize performance for a higher connection count.
• Monitoring: Monitor the MySQL server performance and connection usage after making changes to ensure that the system remains stable and performs well.

By adjusting these settings as described, you can change how many simultaneous connections your MySQL server can handle.