MySQL Out of resources when opening file

On a MySQL server got the error

Out of resources when opening file '/tmp/#sql_318d_0.MAD' (Errcode: 24 "Too many open files")

This is because number of files allowed to open by MySQL is set to too low. To view current settings, in MySQL command prompt, run

select @@open_files_limit;

Or

show variables like "open%";

To increase the value, edit MySQL configuration file, this normally located in /etc/my.cnf, on some servers, it will be on /etc/mysql/my.cnf, add following under [mysqld] section.

open_files_limit = 5000

Now restart MySQL

systemctl restart mysql

Verify it is changed with command

select @@open_files_limit;