In this article, I’ll explain and show you how to modified max_connection in AWS RDS mysql.
Let’s assume you’re using RDS t2.micro instance here, meanwhile encounter the problem too many connections. The simple solution so far is to reboot the instance. Let’s ssh login from terminal and connect to your instance to check the max_connection.
mysql> show variables like "max_connections"; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 24 | +-----------------+-------+ 1 row in set (0.01 sec)
And now you try the follow command attempt to increase the value to 200.
set global max_connections = 200;
It give you error message like :
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
This is because AWS RDS does not allow you to direct access to your database configuration file. All the modified value need to be done in RDS console with parameters group. So back to RDS console, you check for the value max_connection, it give you the value like {DBInstanceClassMemory/12582880}. This value is calculated by amazon, ideally you shouldn’t change it, unless you really know what are you doing. This article is mean for tutorial purpose, so take your own risk and get better understanding before you proceed.
You now try edit the value by select the parameter group and click edit parameters button. From the filters value, search max_connections and replace the value from {DBInstanceClassMemory/12582880} to 200. then save changes. Now you hit another error that does not allow you modified the value. This is because, we are not suppose to direct modified the default set of configuration value.
Click the blue button Create Parameter Group, select the db instance and give a group name and description and create group.
Once you’ve create the value, most parameter group values are made a copy base on the default value. So now, select the newly created parameter group and click edit parameters. Search the maximum value and enter 200 save it. You now can compare two parameter group value by checked the 2 boxes and click compare parameters.
Verify existing parameter group
Back to instance tab, from the configuration Details, you can see the current db instance still assign with the default parameter group. All you need to do is update to new parameter group.
Update parameter group
Select your db instance, click the Instance Action button, and select the Modify. Scroll down to the bottom section, Database Options. from the selection box, select the parameter group you’ve just created then continue and reboot the database apply the changes.