Skip to content

Configuring the Database Server

In this lesson, you will configure the database server. With these settings, you can change various aspects of the server's operation, which will help tailor the server to specific tasks. MySQL stores its configuration in a file whose location depends on the OS and the type of MySQL server installation. To find the location of this file on Linux, you can run the following command:

mysqld --verbose --help

This command will output the values of all server configuration options. At the very beginning, you can find the files from which these options were loaded, for example, /etc/my.cnf and /etc/mysql/my.cnf. Let's take a look at these files:

cat /etc/my.cnf
cat /etc/mysql/my.cnf

The second file contains a comment about the file structure and an includedir command. This command specifies directories from which MySQL loads additional configuration files. In general, these are all files from the includedir directories whose names end with .cnf. If you look through all the files in these directories, you can find the one that actually stores the configuration options:

ls /etc/mysql/mysql.conf.d/
nano /etc/mysql/mysql.conf.d/mysqld.cnf

The mysqld.cnf file is the main configuration file:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

And here are the contents of the file:

#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
[mysqld]
#
# * Basic Settings
#
user        = mysql
# pid-file  = /var/run/mysqld/mysqld.pid
# socket    = /var/run/mysqld/mysqld.sock
# port      = 3306
# datadir   = /var/lib/mysql
# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir        = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
# max_allowed_packet    = 64M
# thread_stack      = 256K
# thread_cache_size       = -1
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
# max_connections        = 151
# table_open_cache       = 4000
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log        = 1
# slow_query_log_file   = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id     = 1
# log_bin           = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db      = include_database_name
# binlog_ignore_db  = include_database_name

Some details:

  1. The user under which the database daemon (service) runs (user). By default, MySQL runs under the mysql user, which is created automatically during installation. In practice, the service user is changed quite rarely, but it is useful to know where to look if you need to set up additional permissions in the system.

  2. The datadir directory, where MySQL stores database files and binary logs - special files that store the history of all changes that occurred in the database. Binary logs store information about both database structure changes and actual data changes. They can be used to restore the database to a state it was in at some point in the past. datadir is often changed when MySQL needs to be configured to work in containers (Docker). In that case, it is set to the path of a directory that is mounted from persistent storage.

  3. bind-address is the IP address on which the server accepts network requests from clients. By default, it is 127.0.0.1. The bind-address can also be set to the IP address of the virtual machine, in which case the server will accept connections from any other computer on the network. bind-address can also be changed to 0.0.0.0. This option is the most convenient, as it allows connections from other computers on the network, while the server will still work if the virtual machine changes its address.

127.0.0.1 is a special address that any computer uses to refer to itself. 0.0.0.0 is another special address that indicates that MySQL will accept requests on any IP address.

  1. port is the TCP port on which the server accepts network connections. By default, it is set to 3306.

  2. The configuration file allows you to configure logging. The log_error option specifies the path to the error log file.

  3. The set of slow_query_log options allows you to configure logging of slow SQL queries. Thanks to these logs, you can monitor and optimize the database performance (we will discuss this more in the database monitoring topic).

When you work with the MySQL configuration on your virtual machine, you should pay attention to a few things:

  1. Before making changes to the configuration file, make a backup copy of it. This way you can restore the server if you accidentally make incorrect changes:
cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup

The backup file name can be anything. The important thing is that it does not end with .cnf.

  1. To apply changes, you need to restart the MySQL service. On Linux, this can be done using the following command:
sudo systemctl restart mysql

After restarting the service, be sure to check its status:

systemctl status mysql

If the service does not start, the cause of the error can be found in the error log file:

tail /var/log/mysql/error.log