Installing and Configuring MySQL

Most self-hosted web applications need a relational database. MySQL is the standard choice, though Debian ships MariaDB as its default MySQL-compatible server. The installation and hardening steps differ between distributions, but the end result is the same: a running database server with a secured root account, no anonymous users, no test database, and no remote root access.

Choosing Between MySQL and MariaDB

On Gentoo, you install MySQL directly from the dev-db/mysql package. On Debian, the default MySQL-compatible server is MariaDB (mariadb-server). MariaDB is a fork of MySQL that maintains wire-level compatibility — applications that work with MySQL work with MariaDB without changes.

Alpine Linux can run either. This post covers Gentoo (MySQL) and Debian (MariaDB). The SQL commands and client tools are identical between the two.

Installing the Database Server

On Gentoo

emerge -av dev-db/mysql

This installs the MySQL server and client. Gentoo's MySQL package includes the mysql command-line client, mysqld server binary, and init scripts.

On Debian

apt install -y mariadb-server

Debian's package includes the server, client, and systemd unit files. MariaDB starts automatically after installation.

On Alpine

apk add mysql mysql-client

Alpine's MySQL package is actually MariaDB under the hood.

Initializing the Data Directory

On Gentoo

After installing MySQL on Gentoo, the data directory at /var/lib/mysql needs to be initialized before the server can start. This creates the system tables that MySQL needs internally.

mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql

The --initialize-insecure flag creates the root account without a password. This is intentional — you'll set a password in the next step. The alternative --initialize generates a random root password and prints it to the error log, which is harder to automate.

If the data directory already contains files (from a previous installation), this command does nothing.

On Debian

Debian's MariaDB package initializes the data directory automatically during installation. No manual step is needed.

On Alpine

mysql_install_db --user=mysql --datadir=/var/lib/mysql

Starting and Enabling the Service

On Gentoo

rc-update add mysql default
rc-service mysql start

On Debian

systemctl enable mariadb
systemctl start mariadb

MariaDB may already be running after installation. systemctl start is a no-op if it's already active.

On Alpine

rc-update add mariadb default
rc-service mariadb start

Verify the service is listening:

ss -lnt sport = :3306

You should see MySQL/MariaDB listening on port 3306:

State    Recv-Q   Send-Q   Local Address:Port   Peer Address:Port
LISTEN   0        80       0.0.0.0:3306          0.0.0.0:*

Setting the Root Password

On a fresh installation, the MySQL root account has no password. Set one immediately.

On Gentoo

Connect to MySQL via the unix socket (no password needed on a fresh install):

mysql -u root

Set the root password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your-secure-password-here';
FLUSH PRIVILEGES;
EXIT;

On Debian

Debian's MariaDB uses unix socket authentication for root by default — the root OS user can connect without a password via the socket. To set a traditional password:

mysql -u root

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your-secure-password-here';
FLUSH PRIVILEGES;
EXIT;

Alternatively, if you want to keep unix socket auth (root OS user connects without a password, but non-root users need the password):

mysql -u root -e "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your-secure-password-here');"

Creating the Root Credential File

To avoid typing the root password on every mysql command, create a credentials file at /root/.my.cnf:

cat > /root/.my.cnf << 'EOF'
[client]
user=root
password=your-secure-password-here
EOF
chmod 600 /root/.my.cnf

The chmod 600 is critical — this file contains the root database password in plaintext. Only the root user should be able to read it.

Test that it works:

mysql -e "SELECT 1"

If this returns a result without prompting for a password, the credential file is working.

Securing the Installation

A default MySQL installation includes components that should be removed on a production server. The mysql_secure_installation script handles this interactively, but you can do it manually with SQL statements for reproducibility.

Removing Anonymous Users

Anonymous users allow anyone to connect to MySQL without a username. Remove them:

mysql -e "DELETE FROM mysql.user WHERE User='';"
mysql -e "FLUSH PRIVILEGES;"

Disabling Remote Root Login

The root account should only be accessible from localhost. Remove any root entries that allow remote connections:

mysql -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
mysql -e "FLUSH PRIVILEGES;"

Removing the Test Database

MySQL ships with a test database that any user can access. Remove it:

mysql -e "DROP DATABASE IF EXISTS test;"
mysql -e "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';"
mysql -e "FLUSH PRIVILEGES;"

All-in-One Script

For convenience, here's the full secure-installation equivalent as a single script:

mysql << 'EOF'
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
FLUSH PRIVILEGES;
EOF

Creating Application Databases and Users

With the server secured, create databases and users for your applications. The pattern is always the same: create the database, create a user with a strong password, and grant privileges only on that specific database.

mysql << 'EOF'
CREATE DATABASE IF NOT EXISTS myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS 'myapp'@'localhost' IDENTIFIED BY 'app-password-here';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;
EOF

Key points:

• utf8mb4 — use this instead of utf8. MySQL's utf8 encoding only supports 3-byte characters, which breaks on emoji and some CJK characters. utf8mb4 is the real UTF-8.
• 'myapp'@'localhost' — restricts the user to local connections only. If the application connects from a different host, replace localhost with that IP address.
• GRANT ALL ON myapp.* — grants full access to the myapp database only, not to other databases. Never grant ALL ON *.* to application users.

Checking the Socket Path

Different distributions put the MySQL socket in different locations. Knowing where it is matters for application configuration files that need to specify the socket path.

mysql -e "SHOW VARIABLES LIKE 'socket';"

Typical paths:

Applications like PHP, Python, and Ruby can connect via this socket instead of TCP when running on the same host. Socket connections are faster and skip the TCP stack entirely.

Verifying the Installation

Run through these checks to confirm everything is working:

# Service is running
ss -lnt sport = :3306

# Root can connect
mysql -e "SELECT 1"

# No anonymous users
mysql -e "SELECT User, Host FROM mysql.user WHERE User='';"
# Should return an empty set

# No remote root
mysql -e "SELECT User, Host FROM mysql.user WHERE User='root';"
# Should only show localhost entries

# No test database
mysql -e "SHOW DATABASES;"
# Should not include 'test'

# Character set is correct
mysql -e "SHOW VARIABLES LIKE 'character_set_server';"
# Should show utf8mb4 (or latin1 on older defaults)

Setting the Default Character Set

If the server defaults to latin1 instead of utf8mb4, set the default globally. On Gentoo, create /etc/mysql/conf.d/charset.cnf:

mkdir -p /etc/mysql/conf.d
cat > /etc/mysql/conf.d/charset.cnf << 'EOF'
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4
EOF

On Debian, create /etc/mysql/mariadb.conf.d/99-charset.cnf:

cat > /etc/mysql/mariadb.conf.d/99-charset.cnf << 'EOF'
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4
EOF

Restart the service after changing the character set:

# Gentoo/Alpine
rc-service mysql restart

# Debian
systemctl restart mariadb

Summary

After completing these steps:

• MySQL (Gentoo) or MariaDB (Debian) is installed and running
• The data directory is initialized
• Root has a strong password stored in /root/.my.cnf
• Anonymous users, remote root access, and the test database are removed
• Application databases use utf8mb4 for proper Unicode support
• The server listens on port 3306 and accepts connections via unix socket

This database server is now ready for applications like PostfixAdmin, Roundcube, Nextcloud, and Matomo.