Kahibaro
Discord Login Register ☰
☰
Previous
Up
Next

MySQL/MariaDB

Table of Contents

Understanding MySQL vs MariaDB

MySQL and MariaDB are both relational database management systems (RDBMS) that implement SQL and share a common ancestry.

Key points for admins:

Always check:

Installation Basics

Distribution specifics (exact commands) belong to package-management chapters; here is what’s unique to MySQL/MariaDB.

Package names and services

Common package names:

Service names (systemd):

Typical management commands: 

sudo systemctl enable --now mariadb
sudo systemctl status mariadb
sudo systemctl enable --now mysqld
sudo systemctl status mysqld

Initial secure setup

Both MySQL and MariaDB provide an interactive hardening script: 

sudo mysql_secure_installation

You’ll typically be asked:

On modern systems, root may authenticate via Unix socket by default (no password when running as root), which affects how you log in for administration.

Core Configuration Files and Layout

The exact paths can vary, but common patterns:

Server sections:

Example minimal override file: 

# /etc/my.cnf.d/10-custom.cnf
[mysqld]
bind-address = 127.0.0.1
max_connections = 200
innodb_buffer_pool_size = 2G

After changing configuration: 

sudo systemctl restart mariadb
# or
sudo systemctl restart mysqld

Basic Server Administration

Connecting to the server

Local (socket or TCP) as root: 

sudo mysql
# or explicitly
mysql -u root -p

Remote: 

mysql -h db.example.com -u appuser -p

Creating databases and users

Basic workflow:

  1. Create a database.
  2. Create a user.
  3. Grant privileges to that user on that database.

Example: 

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword!';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

Key points:

Checking databases and users

List databases: 

SHOW DATABASES;

List users (syntax varies by version): 

SELECT user, host FROM mysql.user;

Show grants for a user: 

SHOW GRANTS FOR 'appuser'@'localhost';

Storage Engines Overview

MySQL/MariaDB use pluggable storage engines. For server admins, the main concern is data durability, performance, and features.

Common engines:

Check default storage engine: 

SHOW ENGINES;
SHOW VARIABLES LIKE 'default_storage_engine';

Basic Performance-Related Settings

Deep performance tuning is covered elsewhere; here are MySQL/MariaDB-specific knobs you should recognize.

Commonly tuned options (in [mysqld]): 

    [mysqld]
    slow_query_log = 1
    slow_query_log_file = /var/log/mysql/slow.log
    long_query_time = 1

View current values: 

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';

Backup and Restore with mysqldump

General backup strategy is covered elsewhere; this section focuses on MySQL/MariaDB tools.

Logical backups with `mysqldump`

mysqldump exports SQL statements to recreate schema and data.

Example: dump a single database 

mysqldump -u root -p appdb > appdb.sql

Dump multiple databases: 

mysqldump -u root -p --databases appdb otherdb > multi.sql

Dump all databases: 

mysqldump -u root -p --all-databases > all.sql

Useful options:

Example consistent backup: 

mysqldump -u root -p \
  --single-transaction \
  --routines --triggers --events \
  appdb > appdb_full.sql

Restoring from `mysqldump`

Restore a single database dump: 

mysql -u root -p appdb < appdb.sql

For dumps created with --databases or --all-databases: 

mysql -u root -p < all.sql

Be aware:

Basic Security Practices (MySQL/MariaDB-specific)

General system security is covered elsewhere; here are DB-specific practices.

Restricting network exposure

In config ([mysqld]): 

  bind-address = 127.0.0.1
  bind-address = 0.0.0.0

If you allow remote access:

Principle of least privilege

  GRANT SELECT, INSERT, UPDATE, DELETE
  ON appdb.* TO 'appuser'@'10.0.0.%';

Authentication and passwords

Basic Monitoring and Diagnostics

Monitoring connections and status

See who is connected: 

SHOW PROCESSLIST;

Global status metrics: 

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Connections';

Example: QPS (queries per second), measured over time:

$$
\text{QPS} = \frac{\Delta \text{Questions}}{\Delta \text{time (seconds)}}
$$

Logs

Locations vary by distro, but common:

Check paths: 

SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'slow_query_log_file';

Schema and Table Basics for Admins

Application schema design is not the focus here; instead, what admins often need to do.

Creating and inspecting tables

Create a simple table: 

USE appdb;
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Inspect a table: 

DESCRIBE users;
SHOW CREATE TABLE users\G

Change table schema carefully, especially on large tables: 

ALTER TABLE users ADD COLUMN last_login DATETIME NULL AFTER email;

On busy systems, consider online DDL options where supported (ALGORITHM=INPLACE, LOCK=NONE) to minimize downtime.

Replication Overview (MySQL/MariaDB)

Detailed replication and clustering are broader topics, but you should know basic terminology.

Key replication features:

Core concepts:

Checking if binary logging is enabled: 

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'server_id';

MariaDB and MySQL implement replication similarly but not identically; never mix major versions arbitrarily in a replication topology without checking compatibility.

Practical Operational Tips

This chapter focused on what is specific to running MySQL/MariaDB as a Linux server admin. Broader backup strategies, security hardening, and performance tuning are handled in their dedicated chapters.

Previous
Up
Next

Views: 24

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro

Visitors: 1535 / 24h

Impressum / Imprint

Datenschutz / Data Policy