Tagged: mariadb

“too many connections mysql” error occurred in MariaDB, CentOS 7

Problem: Too many connections error occurred and you can’t access via PHPMyAdmin or command line.

This is because no more connection can be made, so you have to stop MySQL service and change the setting.

Solution: first stop MySQL service, I user MariaDB so the service will be mariadb:

systemctl stop mariadb
systemctl start mariadb

then enter MySQL prompt via MySQL, and enter below query to check current maximum connection:

SHOW VARIABLES LIKE "max_connections";

The default value is 151. You can set a higher one like

SET GLOBAL max_connections = 1000;

3/3/2020: And you should add a timeout parameter to close any connections that no longer used by user. Append parameter in /etc/my.cnf:

sudo vim /etc/my.cnf

Then add wait_timeout = 600 under [mysqld] to timeout any idle connection exceed 10 minutes.

Enable remote MySQL/MariaDB connection

Problem: Want an outside notebook/website able to remote connect AWS server’s database.

Solution: First allow all IP to access 3306 in the security group on the AWS portal.

then change my.cnf file, add bind-address:

[mysqld]
    ...
    skip-networking
    ...
    bind-address = <some ip-address>
    ...

the ip address should be 127.0.0.1 or localhost

At last, go to the mysql command-line client by entering mysql, and enter below command to grant access for outside IP address

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD' with grant option;

so that specific user from specific IP can remote connect to this database.

Close Bitnami banner
Bitnami