Skip to content

Configuration#

The MySQL Server we have running is configured via a configuration file. In fact, several or more configuration files.

To make navigating and viewing the files easier, let's change our current shell on the Ubuntu server to a root shell (#) in a more permanent basis: sudo -i

1
2
~$ sudo -i
~#

Now we're doing everything as root, so *be careful`!

Navigate to the location of the MySQL configuration: cd /etc/mysql/

Note

If you did the previous projects and exercises, you'll know that the /etc/ path is mostly used for configuration files for the software/services running on your system.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
~# cd /etc/mysql
/etc/mysql# ls -l
total 24
drwxr-xr-x 2 root root 4096 Apr  7 00:32 conf.d
-rw------- 1 root root  317 Apr  7 00:32 debian.cnf
-rwxr-xr-x 1 root root  120 Jan 28 19:36 debian-start
lrwxrwxrwx 1 root root   24 Apr  7 00:32 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- 1 root root  839 Aug  3  2016 my.cnf.fallback
-rw-r--r-- 1 root root  682 Aug 19  2021 mysql.cnf
drwxr-xr-x 2 root root 4096 Apr  7 00:32 mysql.conf.d

Our focus is on the mysql.cnf file, the conf.d/ and mysql.conf.d/ directories. Let's look at the contents of the mysql.cnf file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# 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

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

It's the !includedir lines at the bottom that we're interested in. They tell us that the mysql.cnf file doesn't contain any configuration at all, and in fact it's very likely coming from all the files found in two directories: /etc/mysql/conf.d/ and /etc/mysql/mysql.conf.d/. The term includedir tells us the dir or the directory, as a whole, is being included.

Let's look in those directories: ls -l conf.d/

1
2
3
4
/etc/mysql# ls -l conf.d/
total 8
-rw-r--r-- 1 root root  8 Aug  3  2016 mysql.cnf
-rw-r--r-- 1 root root 55 Aug  3  2016 mysqldump.cnf

And ls -l mysql.conf.d/

1
2
3
4
/etc/mysql# ls -l mysql.conf.d/
total 8
-rw-r--r-- 1 root root  132 Aug 19  2021 mysql.cnf
-rw-r--r-- 1 root root 2220 Aug 19  2021 mysqld.cnf

Which file or files do we care about the most? We're concerned with the configuration of the MySQL Server process, so the file we want to investigate is the mysql.conf.d/mysqld.conf

Note

The d in mysqld.conf stands for daemon, another word meaning service. If you see a file like, serverd.conf, ntpd.conf or just some-word followed by d, and it's in /etc/, then it's likely the configuration file for a daemon.

Let's look at the file's contents: grep -v '#' mysql.conf.d/mysqld.cnf | grep -v '^$'

Note

The grep command is a command used for applying (very complicated, should you need it) pattern matching against text. Above I've used a simple match on the # character, but in then I've matched on a regular expression: ^$. The manual page for grep is (you guessed it): man grep

I strongly suggest you review the manual page and get a feel for some simple grep commands. It has a section on "Regular Expressions" and at the bottom, a section called EXAMPLE that's also useful.

1
2
3
4
5
6
7
8
[mysqld]
user            = mysql
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
key_buffer_size         = 16M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
max_binlog_size   = 100M

I've stripped everything else from the file so that we can focus on what's currently being set for the current server as it's running now. There are a lot of default values set behind the scenes, too.

There are two settings defined, above, of importance to us: bind-address and log_error.

The bind_address is the network IP address that MySQL Server will "bind" to. That is, MySQL Server will "bind" a network socket to the IP address 127.0.0.1 (the loopback address, also known by its hostname localhost) on TCP/3306. The port number 3306 isn't shown above because the default value is, in fact 3306. I suggest leaving this as is unless you have a (good) reason to change it. It adds very little to zero security benefit to change the default port.

And then we have the log_error option, which has a value of /var/log/mysql/error.log. Let's quickly look inside that file: cat /var/log/mysql/error.log

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
/etc/mysql# cat /var/log/mysql/error.log
2022-04-07T00:32:33.134018Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.28-0ubuntu0.20.04.3) initializing of server in progress as process 76800
2022-04-07T00:32:33.141272Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-07T00:32:34.877160Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-07T00:32:36.589405Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-04-07T00:32:37.824418Z 6 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.28-0ubuntu0.20.04.3).
...
2022-04-07T00:32:50.297678Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28-0ubuntu0.20.04.3) starting as process 77109
2022-04-07T00:32:50.304493Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-07T00:32:50.929032Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-07T00:32:51.260978Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-04-07T00:32:51.261011Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-04-07T00:32:51.274933Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-04-07T00:32:51.275020Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-0ubuntu0.20.04.3'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2022-04-07T00:33:50.653833Z 9 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

That's a lot of information. The only reason I want you to know about this file is simple: if there is an error or problem with your MySQL installation, this should contain information about the problem.

The concept of the log file or "error log" applies to a lot of other services and software too - they write log files we can use to get information about the services and what they're doing, or failing to do, as they operate. Not all services do this, but most of the services running most of the Internet (like MySQL, nginx, Apache, etc.) do write log files and you can even highly customise the logs to get less or more information out of them. The lesson here is: get to understand where a service or process is writing its log files to so you can investigate problems.

And of course the concept of the configuration file also applies to most other services you'll find in the wild. We've identified the location of the configuration file (or potentially files because of the !includedir option) and then reviewed the file so that we know how MySQL Server is configured to run.

Next#

We don't need to change any MySQL configuration at this point in time. Next we're going to look at backing up a database and restoring it.