How to set max_open_files in MariaDB / MySQL in CentOS 7

Set the system wide open file limit:

vi /etc/security/limits.conf

Change/Add the following:

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240

Now do this for /etc/sysctl

vi /etc/sysctl

Add the following

fs.file-max = 1024000

Set the changes

sysctl -w fs.file-max=1024000
sysctl -p
# check changes
cat /proc/sys/fs/file-max

Set the mysqld.service limit (as settings here will override *.cnf ones)

Set both /etc/systemd/system.conf and /etc/systemd/user.conf

vi /etc/systemd/system.conf
vi /etc/systemd/user.conf

Add the following under [Manager] for both:

DefaultLimitNOFILE=1024000

ALSO, you may need to look in /etc/systemd/system to see if anything is overriding stuff.

/etc/systemd/system
grep -Rl LimitNOFILE

Then change all instances of "LimitNOFILE" with:

systemctl edit [name of service].service

Or do this via "vi"

LimitNOFILE=infinity
LimitMEMLOCK=infinity

You may even need to use the following:

LimitAS=infinity
LimitRSS=infinity
LimitCORE=infinity
LimitNOFILE=infinity

START METHOD1

Find out which .conf files are being used:

systemctl status mysqld
# You'll get something like the following
Drop-In: /etc/systemd/system/mariadb.service.d
           └─override.conf

So now that we see Drop-In: /etc/systemd/system/mariadb.service.d, we'll do the following:

cd /etc/systemd/system/mariadb.service.d
# If you're using regular mysql, then the above path will likely be different
vi /etc/systemd/system/mariadb.service.d/override.conf

Add the following:

[Service]
LimitNOFILE=infinity
LimitMEMLOCK=infinity

If that doesn't work, then "infinity" variable was set to mean a specific number, like "65536"... If that's the case, set the same number as you did in "/etc/my.cnf.d/server.cnf"
or set a really high number like "2048000"

[Service]
LimitNOFILE=2048000
LimitMEMLOCK=2048000

END METHOD 1

START METHOD 2

Find the location of all potential *.service files

cd /
find -iname maria*.service
# or
find -iname mysql*.service

Then edit each one, as in example below:

vi /usr/local/directadmin/custombuild/configure/systemd/mysqld57.service
vi /usr/local/directadmin/custombuild/configure/systemd/mysql.service
vi /usr/local/directadmin/custombuild/configure/systemd/mysqld.service
vi /etc/systemd/system/mysqld.service

vi /etc/systemd/system/mariadb.service
vi /usr/share/mysql/systemd/mariadb.service
vi /usr/local/directadmin/custombuild/configure/systemd/mariadb.servicevi 

Change/Add the following, under "[Service]":

LimitNOFILE=infinity
LimitMEMLOCK=infinity

If that doesn't work, then "infinity" variable was set to mean a specific number, like "65536"... If that's the case, set the same number as you did in "/etc/my.cnf.d/server.cnf"
or set a really high number like "2048000"

LimitNOFILE=2048000
LimitMEMLOCK=2048000

END METHOD 2

Set the *.cnf settings:

vi /etc/my.cnf.d/server.cnf
# or where ever your .cnf may be

Change/Add the following:

open_files_limit               = 1024000

Now reload/restart what's necessary:

systemctl daemon-reload
systemctl restart mysqld; systemctl status mysqld