lyen lyen - 3 months ago 41
MySQL Question

Increasing mysql max_connections to 1024 in a docker container

I want to increase

max_connections
to 1024 in a docker container of mysql 5.6 hosted on AWS EC2. The instance type of the EC2 is
m4.large
.

I've set max_connections to 1024 by adding
max_connections=1024
in
my.cnf
, but I only saw 214 when typing
show varialbes like 'max_connections';
mysql command. Here is the
my.cnf
I have.:

my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
skip-host-cache
skip-name-resolve
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

max_connections=1024
max_connect_errors=100000

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1

#log-error = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/


I've tried the solution mentioned in MySQL Max_connections stuck on 214 ?. But this one doesn't work in my case. The following are my settings:

common-session

# here are the per-package modules (the "Primary" block)
session [default=1] pam_permit.so
# here's the fallback if no module succeeds
session requisite pam_deny.so
# prime the stack with a positive return value if there isn't one already;
# this avoids us returning an error just because nothing sets a success code
# since the modules above will each just jump around
session required pam_permit.so
# and here are more per-package modules (the "Additional" block)
session required pam_unix.so

session required pam_limits.so
# end of pam-auth-update config


limits.conf

#<domain> <type> <item> <value>
* soft nofile 65536
* hard nofile 65536
* soft nproc 100000
* hard nproc 127466


I've also tried the solution here.

mysql.service

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/bin/mysqld_safe
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure

LimitNOFILE=infinity
LimitMEMLOCK=infinity


This solution doesn't work for me either.

I pass the above files from the host machine into the docker container by using
mountPoints
and
volumes
in task definition of ECS.

Here are the mountPoints and volumes of my ECS task definition:

"mountPoints": [
{
"containerPath": "/docker-entrypoint-initdb.d/user.sql",
"sourceVolume": "user",
"readOnly": true
},
{
"containerPath": "/etc/mysql/my.cnf",
"sourceVolume": "mysql_config",
"readOnly": false
},
{
"containerPath": "/etc/security/limits.conf",
"sourceVolume": "limits_conf",
"readOnly": null
},
{
"containerPath": "/etc/systemd/system/mysql.service",
"sourceVolume": "mysql_service",
"readOnly": null
},
{
"containerPath": "/etc/pam.d/common-session",
"sourceVolume": "common_session",
"readOnly": null
}
],

"volumes": [
{
"host": {
"sourcePath": "/home/docker/mysql/user.sql"
},
"name": "user"
},
{
"host": {
"sourcePath": "/home/docker/mysql/my.cnf"
},
"name": "mysql_config"
},
{
"host": {
"sourcePath": "/home/docker/mysql/limits.conf"
},
"name": "limits_conf"
},
{
"host": {
"sourcePath": "/home/docker/mysql/mysql.service"
},
"name": "mysql_service"
},
{
"host": {
"sourcePath": "/home/docker/mysql/common-session"
},
"name": "common_session"
}
],


What else can I do so that
max_connections
can reach 1024?

If you need to know more information of my docker container, just let me know.

Answer

Finally, I find out that I have to execute SET GLOBAL max_connections=1024; after launching the docker container.

What's even weirder is that adding SET GLOBAL max_connections=1024; command in /home/docker/mysql/user.sql file doesn't increase max_connections. I have to login mysql server and type this command manually.