In this article, we have described the basic configuration settings that must be set when deploying a standalone server with MySQL or MariaDB. We’ll look at the initial configuration parameters that must be checked before starting the server.
First, you need to adjust the memory consumption of the MySQL/MariaDB service. It’s best to use the MySQLTuner script (https://github.com/major/MySQLTuner-perl).
You can install this script on CentOS, RHEL, Rocky, or Oracle Linux from the standard repos:
# yum install mysqltuner
On Ubuntu and Debian:
$ sudo apt install mysqltuner
Or you can download the Perl script sources from git:
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl # chmod +x mysqltuner.pl
If you installed the package through a repository, run the command:
If you downloaded the script manually:
# perl mysqltuner.pl > settings.txt
The script will ask for an account credential to connect to the database.
The script will analyze the environment and offer a number of settings, which you need to change in the MySQL or MariaDB configuration file (Variables to adjust).
Let’s consider what parameters in the configuration file determine the memory usage by the SQL server:
These values are summed up.
Sum the values of these parameters and multiply by the value of max_connections.
If you have configured all values using the mysqltuner, it is enough to manually check the values in the most critical parameters
Be sure to check the value of the bind-address parameter. If you don’t want MySQL to accept external connections, leave localhost here:
bind-address = 127.0.0.1
Check the log paths here. Add a log file for slow SQL queries:
log_error = /var/log/mysql/error.log
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2.0
Check a character set and collation. For example, for Swedish projects set the following:
The next two parameters set the maximum number of connections and tables in the database. They significantly affect the MariaDB/MySQL performance.
If you encounter the error Errcode: 24 “Too many open files” while using SQL databases, it means that the number of open files for the mysqld process has exceeded the system limit. To increase this limit, you need to change the service settings. For example:
# mkdir /etc/systemd/system/mysqld.service.d # touch limit.conf
The innodb_temp_data_file_path option allows you to manage the size of the temporary tablespace file. This file can grow to a huge size and cause you to run out of disk space. Restrict the size and growth of the temporary file. For example, to set a maximum size of 4 GB and allow growth in increments of 24 MB:
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G