5.2.1 MySQL/MariaDB

Table of Contents

Overview

MySQL and MariaDB are relational database management systems that store data in tables, use SQL as their query language, and are widely used on Linux servers. They are typically used as backends for web applications, internal business systems, and many open source tools. MariaDB began as a fork of MySQL and remains largely compatible with it, which is why they are often grouped together in administration tasks.

From a Linux server perspective, administering MySQL or MariaDB involves installing the server, securing it, managing users and permissions, creating and tuning databases, and planning for performance, backup, and replication. This chapter focuses on practical administration tasks and concepts that are specific to MySQL and MariaDB themselves, not general database theory.

Installation and Service Management

On most Linux distributions, MySQL or MariaDB is installed with the native package manager. MariaDB often replaces MySQL as the default in many distributions, while others provide both.

On Debian and Ubuntu, you typically install MariaDB with apt:

sudo apt update
sudo apt install mariadb-server

On Fedora and RHEL compatible systems, you install with dnf:

sudo dnf install mariadb-server

Some distributions offer mysql-server instead or as an alternative. You should check which server is provided and supported by your distribution and by the applications you plan to run.

Once installed, the database server is a systemd service that you control with systemctl. Common commands include:

sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo systemctl status mariadb

If you are using MySQL instead, the service might be named mysqld or mysql depending on the packaging. You must always verify the actual unit name with systemctl list-units | grep -i mysql or mariadb.

Ensuring the service starts at boot is critical for production servers. You use systemctl enable only after confirming that the server starts correctly and there are no obvious errors in the logs.

Initial Security Configuration

After installation, a default configuration may leave the database server with weak security. Most MySQL and MariaDB packages provide a helper script, typically mysql_secure_installation, that guides you through basic hardening steps.

When you run:

sudo mysql_secure_installation

you are typically asked to set the root password for the database server, remove anonymous users, disallow remote root login, remove the test database, and reload the privilege tables. You should answer in a way that enforces least privilege and reduces attack surface.

Always set a strong, unique password for the MySQL or MariaDB root user and disable remote root logins in production environments.

Some distributions may use the Linux root account authentication (for example via unix_socket plugin) instead of a separate password. In this case, you connect as database root by using sudo mysql without specifying a password, and you might not want to replace this mechanism unless your security policy requires it.

Connecting to the Server

On a Linux server, you administer MySQL or MariaDB primarily through the command line client, usually called mysql. To connect as the database root user, you might use:

sudo mysql

if socket-based authentication is enabled, or:

mysql -u root -p

if the root user uses a password. For application-level users, you typically specify the database user and database name:

mysql -u appuser -p appdb

For remote administrative access, you use the -h option to specify the host:

mysql -h dbserver.example.com -u adminuser -p

Remote access requires that network binding is configured in the server configuration, the appropriate firewall rules are set, and the user account is granted access from the connecting host. In secure environments, remote access is often tunneled through SSH rather than exposing the MySQL or MariaDB port directly.

Basic Administrative SQL

Within the mysql client, you perform administrative tasks using SQL or special administrative statements. To see which databases exist, you run:

SHOW DATABASES;

To create a database:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Choosing utf8mb4 is typical for modern applications because it supports the full range of Unicode characters.

To inspect the tables inside a database, you switch to it and then list its tables:

USE appdb;
SHOW TABLES;

To see how a particular table is defined, you can use:

DESCRIBE users;

or:

SHOW CREATE TABLE users\G

The \G format prints the output vertically, which is more readable for longer definitions.

For global server status and variables, you use:

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;

You can filter by variable or status name:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Threads_connected';

These commands are essential for performance monitoring and tuning.

User Accounts and Authentication

MySQL and MariaDB use their own user system, distinct from Linux system users. A database user is identified by both a username and a host component. For example, appuser@localhost and appuser@'%' are considered different accounts.

To create a user account, you use CREATE USER. A simple example is:

CREATE USER 'appuser'@'localhost'
  IDENTIFIED BY 'strong_password_here';

Using localhost restricts this account to connections originating from the same machine, which is suitable for local applications. To allow connections from any host, you might use:

CREATE USER 'appuser'@'%'
  IDENTIFIED BY 'strong_password_here';

However, granting access from any host is usually not recommended unless paired with network controls. It is more secure to specify exact hostnames or IP ranges when possible.

MariaDB and more recent MySQL versions support additional authentication plugins, such as unix_socket or PAM. For example, using socket authentication you can map a database user to a Linux user without a separate password. This is often limited to local administrative roles to simplify secure access management.

To view existing users and their host components, you can query the internal tables:

SELECT User, Host FROM mysql.user;

Privileges and Access Control

Privileges in MySQL and MariaDB define what a user can do. They can be granted globally, per database, per table, and even per column in some cases. Common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and ALTER. Administrative privileges include SUPER, RELOAD, PROCESS, and others.

To give a user full access to a single database, you typically run:

GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

You should prefer more limited grants when possible. For example, if an application only needs to read data, you can grant only SELECT:

GRANT SELECT ON appdb.* TO 'reportuser'@'localhost';

FLUSH PRIVILEGES is required in older versions or when you manipulate privilege tables directly. With normal GRANT and REVOKE statements in current versions, privilege changes are applied immediately.

To inspect what privileges a particular user has, you can use:

SHOW GRANTS FOR 'appuser'@'localhost';

This is useful for auditing and for diagnosing permission errors that applications encounter.

Never grant ALL PRIVILEGES on . to application users, and avoid giving SUPER or other administrative privileges to nonadministrative accounts.

Common Configuration Files and Options

MySQL and MariaDB configuration is usually stored in files such as /etc/my.cnf or /etc/mysql/my.cnf, with additional configuration snippets included from a directory like /etc/mysql/conf.d/ or /etc/my.cnf.d/. You must check your distribution for the exact paths.

These configuration files use sections, with [mysqld] for server options, [client] for client default options, and other sections for specific tools. An example minimal server configuration snippet might look like:

[mysqld]
bind-address = 127.0.0.1
max_connections = 200
innodb_buffer_pool_size = 2G

The bind-address option controls which network interface and IP address the server listens on. 127.0.0.1 limits connections to localhost. Setting it to 0.0.0.0 allows connections on all interfaces, which must be paired with proper firewalling and secure user grants.

The innodb_buffer_pool_size defines how much memory is allocated for caching InnoDB data and indexes. For InnoDB-focused workloads, this is one of the most important performance settings. A common starting heuristic for dedicated database servers is to allocate a significant fraction of RAM to this buffer, but you must leave sufficient memory for the operating system and other processes.

After changing configuration files, you must restart or at least reload the server:

sudo systemctl restart mariadb

In some cases, you can use SQL statements like:

SET GLOBAL max_connections = 500;

to apply changes without editing configuration files. However, such changes are temporary and last only until the server is restarted. Persistent configuration belongs in the configuration files.

Storage Engines and Table Types

MySQL and MariaDB support storage engines, which provide different ways to store and manage table data. The most important engine for modern usage is InnoDB, which provides transactions, row-level locking, and crash recovery. Other engines exist, such as MyISAM, MEMORY, ARCHIVE, and more specialized or plugin engines.