google cloud platform

MariaDB – How to set max_connections permanently ?

This tutorial guides you on how to set max_connections permanently in MariaDB. Are you struggling to update or set max_connections parameter ? MariaDB does not take the updated parameter value that is set ?, then you can refer this tutorial.

MariaDB – How to set max_connections permanently

When I was trying to load test my WordPress setup on Google Cloud which was using MariaDB as database, I wanted to check what is the maximum connections that my database will allow simultaneously.

Therefore, I used the following commands to check the value of ‘max_connections‘ in database. It returned the value as 100 which is default.

:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 103083
Server version: 10.4.14-MariaDB-1:10.4.14+maria~focal-log mariadb.org binary dis                                                                                                                                                             tribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like "max_connections";
    
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.072 sec)

Note, the following is the other way to check the default value which was set for max_connections i.e., using grep command

/usr/local/lsws# mysqladmin variables | grep max_connections
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
[email protected]:/usr/local/lsws# mysqladmin -u root -p variables | grep max_connections
Enter password:
| extra_max_connections                                  | 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| max_connections                                        | 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |

Then I tried to set new value for ‘max_connections‘ from MySQL/ MariaDB command line using SET command as shown below.

MariaDB [(none)]> set global max_connections := 150;
Query OK, 0 rows affected (0.040 sec)

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 150   |
+-----------------+-------+
1 row in set (0.002 sec)

After setting new value using SET command in MariaDB command line, I tried restarting MariaDB server using the following command systemctl restart mariadb. Then again I tried checking the variable value and it is clearly seen that the updated value “150” has been reset to default “100” after MariaDB server restarted.

MariaDB [(none)]> show variables like 'max_connections';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    36
Current database: *** NONE ***

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

Therefore, you need to find some other way to set it permanently. In the following section you will see how to set that value permanently.

Set max_connections permanently

To set max_connections permanently what I did was, just modified the value of “max_connections” parameter in the /etc/mysql/my.cnf file as shown below. Afterwards, tried to restart MariaDB service to check whether it is permanently set.

# sudo vi /etc/mysql/my.cnf
//Notes
//update max_connections = 150

# sudo systemctl restart mariadb

After, restarting MariaDB service, when I tried to check the “max_connections” it is updated and set permanently.

MariaDB [(none)]> show variables like 'max_connections';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    36
Current database: *** NONE ***

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 150   |
+-----------------+-------+
1 row in set (0.003 sec)

That’s it. The above solution worked for me in the following Ubuntu version.

~# cat /etc/os-release

NAME="Ubuntu"
VERSION="20.04.1 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.1 LTS"
VERSION_ID="20.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=focal
UBUNTU_CODENAME=focal

Hope it helped 🙂

References

Leave a Reply

avatar
  Subscribe  
Notify of