Chapter #21: Installing and Configuring a MariaDB Server
Learn how to install and configure MariaDB server with a focus on security, configuration files, backup strategies, and monitoring for better performance.
A database server is a critical component of the network infrastructure necessary for today’s applications. Without the ability to store, retrieve, update, and delete data (when needed), the usefulness and scope of web and desktop apps becomes very limited.
In addition, knowing how to install, manage, and configure a database server (so that it operates as expected) is an essential skill that every system administrator must have.
In this chapter we will briefly review how to install and secure a MariaDB database server and then we will explain how to configure it.
Installing and Securing a MariaDB Server
MariaDB replaced MySQL, which still can be found in Ubuntu (along with MariaDB). For brevity, we will only use MariaDB in this tutorial, but please note that - besides having different names and development philosophies - both Relational Database Management Systems (RDBMSs for short) are almost identical.
This means that the client-side commands are the same on both MySQL and MariaDB, and the configuration files are named identically and located in the same places.
To install MariaDB, do:
sudo yum update && yum install mariadb mariadb-server #RHEL
sudo apt update && sudo apt install mariadb-client mariadb-server #Ubuntu
Note that, in Ubuntu, you will be asked to enter a password for the RDBMS root user.
Once the above packages have been installed, make sure the database service is running and has been activated to start on boot (in RHEL, you will need to perform this operation manually, whereas in Ubuntu, the installation process will have already taken care of it for you):
sudo systemctl start mariadb
sudo systemctl enable mariadb
Then run the mysql_secure_installation script, which will allow you to:
- Set/reset the password for the RDBMS root user
- Remove anonymous logins (so only users with valid accounts can log in)
- Disable root access for machines other than localhost
- Remove the test database (which anyone can access)
- Activate the changes from steps 1 through 4
Configuring the Database Server
The default configuration options are read from the following files in the given order: /etc/mysql/my.cnf, /etc/my.cnf, and ~/.my.cnf. Most often, only /etc/my.cnf exists.
It is on this file that we will set the server-wide settings (which can be overridden with the same settings in ~/.my.cnf for each user).
The first thing that we need to note about my.cnf is that settings are organized into categories (or groups) where each category name is enclosed with square brackets.
Server system configurations are given in the [mysqld] section, where typically you will find only the first two settings in the table below.
The rest are other frequently used options (where indicated, we will change the default value with a custom one of our choosing):
| Setting | Description | Default Value | Custom Setting |
|---|---|---|---|
| datadir | Directory where the data files are stored. | datadir=/var/lib/mysql |
(unchanged) |
| socket | Name and location of the socket file used for local client connections. A socket file is a resource for passing info between applications. | socket=/var/lib/mysql/mysql.sock |
(unchanged) |
| bind_address | Address where the DB server listens for TCP/IP connections. 0.0.0.0 means listen on all IPs. |
bind_address=0.0.0.0 |
bind_address=192.168.0.13 |
| port | Port the DB server listens on. Default is 3306. |
port=3306 |
port=20500 (make sure nothing else uses this port — changing default ports can discourage scans) |
| innodb_buffer_pool_size | Memory buffer (in bytes) for frequently accessed InnoDB/XtraDB data and indexes. | innodb_buffer_pool_size=134217728 (128 MB) |
innodb_buffer_pool_size=256M |
| skip_name_resolve | Controls whether hostnames are resolved on incoming connections. Setting to 1 disables resolution and speeds up queries. |
skip_name_resolve=0 |
skip_name_resolve=1 |
| query_cache_size | Size (in bytes) for query cache (stores results of SELECT queries). |
query_cache_size=0 (disabled) |
query_cache_size=100M ⚠️ Note: Query cache is deprecated and can hurt performance in high-write/multi-core systems. |
| max_connections | Maximum number of simultaneous client connections. | max_connections=151 |
max_connections=30 |
| thread_cache_size | Number of reusable threads after client disconnects. More efficient than creating new threads. Should be about half of max_connections. |
(depends on system) | thread_cache_size=15 |