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)' root@openlitespeed-wordpress-1-vm:/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 🙂
- How to set or change root password in Ubuntu Linux ?
- Putty Fatal Error No supported authentication methods available
- How to find which users belongs to a specific group in linux
- Give write permissions for specific user or group for specific folder in linux
- How to unzip a zip file from Terminal (Google Cloud Shell)
- Build a Docker Image with a Dockerfile and Cloud Build in GCP?
- How to create GCP project on Google Cloud Platform
- GCP Virtual Private Cloud (VPC) Networks Fundamentals
- How to create GCP project on Google Cloud Platform
- Is it possible to change Google Cloud Platform Project ID ?
- Create non-root SSH user account and provide access to specific folders
- How to Start Stop Restart MariaDB on Linux OS ?