Ubuntu_18_mysql_ulimit_and_pam.txt

# maximum capability of system
user@ubuntu:~$ cat /proc/sys/fs/file-max
708444

# If this limit is too low, you can increase the available limit to say 200000
user@ubuntu:~$ sudo vim /etc/sysctl.conf

# add the following line to it
fs.file-max = 200000

# run this to refresh with new config
user@ubuntu:~$ sudo sysctl -p

# edit the following file
user@ubuntu:~$ sudo vim /etc/security/limits.conf

# add following lines to it

mysql soft  nofile  65535   
mysql hard  nofile  65535

# edit the following file
user@ubuntu:~$ sudo vim /etc/pam.d/common-session

# add this line to it
session required pam_limits.so

# Check the mysql limits
# enable mysql shell
user@ubuntu:~$ sudo vi /etc/passwd

# give a shell to the mysql user
- mysql:x:112:116:MySQL Server,,,:/var/lib/mysql:/bin/false
+ mysql:x:112:116:MySQL Server,,,:/var/lib/mysql:/bin/bash

# become root and become mysql 
user@ubuntu:~$ sudo su
root@ubuntu:~$ su mysql

# check the new available limit
mysql@ubuntu:~$ ulimit -n
65535

# exit to root account
mysql@ubuntu:~$ exit

# allow mysql server to get it's required value when start, edit the following file
root@ubuntu:~$ vi /lib/systemd/system/mysql.service

#add this line
LimitNOFILE=65535

# reload systemd configurations
root@ubuntu:~$ systemctl daemon-reload

 root@ubuntu:~$ vi /etc/passwd
# reset the mysql shell to /bin/false
- mysql:x:112:116:MySQL Server,,,:/var/lib/mysql:/bin/bash
+ mysql:x:112:116:MySQL Server,,,:/var/lib/mysql:/bin/false