AJ ONeal November 12, 2021

How to install MySQL (MariaDB), correctly

TL;DR Here’s the short of it:

MariaDB is MySQL’s new name (due to trademark issues)

Install MySQL using the Official MariaDB Installer

MYSQL_VERSION="10.6"
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup |
    sudo bash -s -- --mariadb-server-version="mariadb-${MYSQL_VERSION}"

sudo apt install -y mariadb-server mariadb-client

Secure the installation (delete the test accounts and data, create your own)

DB_PASS=''

sudo mysql -u root << SQL_EOF

UPDATE mysql.user SET Password=PASSWORD('${DB_PASS}') WHERE User='root';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

SQL_EOF

(this is the automated form of mysql_secure_installation)

Enable encrypted remote access (see below)
Don’t use MySQL, use PostgreSQL instead

Explained

Here’s the 3 things you need to know to save yourself a lot of pain and heartache:

1. MySQL has been renamed to MariaDB

The original trademark owner was MySQL AB.

MySQL AB (the MySQL company) sold to Sun Microsystems.

Sun Microsystems (the Java company) was bought by Oracle.

Oracle has a enterprise database product of the same name which they still sell to companies that, for whatever reason, haven’t switched to PostgreSQL.

The MySQL trademark is now owned by Oracle and they have no incentive to continue working with the original authors - it just doesn’t make financial sense to create a superior, lower-priced competitor that to undercuts your own legacy (unless you’ve read The Innovator’s Solution, that is).

(on top of that Oracle has a bad rap for operated essentially as a patent troll - a holdings company that acquires smaller companies for their patents and trademarks and, rather than focusing on innovation, then creating dubious legal battles as a form of generating income)

In fine, MySQL is dead.

Or, more accurately, the author’s future developments can’t use that name due to the trademark sale.

Thus, the author of MySQL established new trademarks MariaDB and MaxScale for the currently maintained version of the project.

Sidenote: Both databases are named after the author’s children (My, Maria, and Max).

2. Use the Official MariaDB repositories

Important: NEVER use apt, dnf, or any other 3rd party tools to install MySQL from their default repositories.

Doing so will result in a version that is several years old, unpatched, under-performant, and quite probably no longer supported or maintained by the author.

Instead, you should use the Official MariaDB Installer, as per MariaDB’s Installation Documentation:

MYSQL_VERSION="10.6"
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup |
    sudo bash -s -- --mariadb-server-version="mariadb-${MYSQL_VERSION}"

Assuming that the repositories are updated to include the official 1st party sources, you can then use apt (or whatever else) to install the correct version:

sudo apt install -y mariadb-server mariadb-client

See https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/ for more info.

3. Harden (Secure) the installation

This consists of 3 parts:

Delete the test users and data
Create a user for your app
(optional) Enable secure remote access

“mysql_secure_installation”

You should probably delete the anonymous test users and test database.

You can choose to leave the root password empty for single-user instances (i.e. Docker, VPS):

DB_ROOT_PASS=''

Or you can prepare to set it to a secure random string if you need to guard root access:

DB_ROOT_PASS="$(openssl rand -base64 12 | tr + - | tr / _)"
echo "The secure password is '${DB_PASS}'"

Then run these automated versions of the mysql_secure_installation SQL queries:

sudo mysql -u root << SQL_EOF

UPDATE mysql.user SET Password=PASSWORD('${DB_ROOT_PASS}') WHERE User='root';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

SQL_EOF

This approach improves upon mysql_secure_installation, as described here:

Create a user for your app

Again, you can have a dummy password empty for single-user instances, or create a secure password.

DB_NAME=my_database
DB_USER=my_user
DB_PASS=my_pass

sudo mysql -u root << SQL_EOF

-- Create app database
CREATE DATABASE IF NOT EXISTS ${DB_NAME};
USE ${DB_NAME};

-- Grant Read Access
CREATE USER IF NOT EXISTS '${DB_USER}'@'localhost' IDENTIFIED BY '${DB_PASS}';
GRANT SELECT ON ${DB_NAME}.* to '${DB_USER}'@'localhost';

-- Grant Write Access
GRANT ALL PRIVILEGES ON my_database.* to '${DB_USER}'@'localhost';
FLUSH PRIVILEGES;

SQL_EOF

(optional) Enable encrypted remote access

TLS_SUBJECT='/C=US/ST=Utah/L=Salt Lake City/CN=example.com'
DB_REMOTE_USER=''

Note: The new remote user’s password will be printed as the final output, if successful.

#!/bin/bash
set -e
set -u

#
# Create TLS Certificates
# (can be done by unprivileged user)
#

mkdir -p ./mysql-ssl/
pushd ./mysql-ssl/

# Note: ignore warnings about "Can't load /root/.rnd into RNG"

echo "gen key"
openssl genrsa 2048 > ca-key.pem

echo "gen req"
openssl req -new -x509 -nodes -sha256 -days 36500 \
    -key ca-key.pem -out ca-cert.pem \
    -subj "${TLS_SUBJECT}"

#echo "rsa decrypt"
#openssl rsa -in server-key.pem -out server-key.pem

echo "self req"
openssl req -newkey rsa:2048 -days 3650 -nodes \
    -keyout server-key.pem -out server-req.pem \
    -subj "${TLS_SUBJECT}"

echo "sign"
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
openssl rehash ca-*

popd

#
# Install TLS Certificate
#

sudo chown -R mysql:mysql mysql-ssl/
sudo chmod 0700 mysql-ssl/*-key.pem
sudo chmod 0700 mysql-ssl/
sudo rm -rf /etc/mysql-ssl
sudo mv mysql-ssl/ /etc/

#
# Enable TLS in MySQL
#

sudo sed -i 's:# ssl-ca=.*:ssl-ca=/etc/mysql-ssl/ca-cert.pem:g' /etc/mysql/my.cnf
sudo sed -i 's:# ssl-cert=.*:ssl-cert=/etc/mysql-ssl/server-cert.pem:g' /etc/mysql/my.cnf
sudo sed -i 's:# ssl-key=.*:ssl-key=/etc/mysql-ssl/server-key.pem:g' /etc/mysql/my.cnf
# TODO add 'tls_version = TLSv1.2,TLSv1.3'

sudo systemctl restart mariadb

#
# Failsafe check 
#

mysql -u "${DB_USER} -p"${DB_PASS}" "${DB_NAME}" -e "SHOW VARIABLES LIKE '%ssl%';" |
    grep "have_ssl.*YES" || (echo "MariaDB TLS Failure" && exit 1)

#
# Enable remote access
#
sudo sed -i 's:bind-address.*:bind-address            = 0.0.0.0:g' /etc/mysql/my.cnf
sudo systemctl restart mariadb

#
# Create a remote user
#
my_pass="$(openssl rand -base64 12 | tr + - | tr / _)"
sudo mysql -u root "${DB_NAME}" -e << SQL_EOF

CREATE USER '${DB_REMOTE_USER}'@'%' IDENTIFIED BY '$my_pass';
GRANT SELECT ON ${DB_NAME}.* to '${DB_REMOTE_USER}'@'%';
GRANT ALL PRIVILEGES ON ${DB_NAME}.* to '${DB_REMOTE_USER}'@'%';

SQL_EOF

echo "${my_pass}" > mysql_password
echo "New remote user's password saved to '$(pwd)/mysql_password'"

Important: Check that there are no unexpected users

#
# Check for insecure users
# (should be none on a new installation)
#

sudo mysql -u root "${DB_NAME}" -e "SELECT user,password,host,password_expired FROM mysql.user;"

Important: Check that TLS (ssl) has been enabled

mysql -u "${DB_USER} -p "${DB_NAME}" -e "SHOW VARIABLES LIKE '%ssl%';"

The output should look like this:

+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| have_openssl        | YES                            |
| **have_ssl**        | **YES**                        |
| ssl_ca              | /etc/mysql-ssl/ca-cert.pem     |
| ssl_capath          |                                |
| ssl_cert            | /etc/mysql-ssl/server-cert.pem |
| ssl_cipher          |                                |
| ssl_crl             |                                |
| ssl_crlpath         |                                |
| ssl_key             | /etc/mysql-ssl/server-key.pem  |
| version_ssl_library | OpenSSL 1.1.1  11 Sep 2018     |
+---------------------+--------------------------------+

Now check that you can login from another system as the remote user:

MYSQL_VERSION="10.6"
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup |
    sudo bash -s -- --mariadb-server-version="mariadb-${MYSQL_VERSION}"

sudo apt install -y mariadb-client

sudo mysql -h "${DB_HOST}" -u "${DB_REMOTE_USER}" -p "${DB_NAME}" -e "SELECT 'hello, world';"

Important: You may want to check your ~/.bash_history (or ~/.zsh_history) to make sure your password hasn’t been saved to disk during this process.

4. Don’t use MySQL, use PostgreSQL

PostgreSQL is the leading database used by professionals. It’s easier to learn, easier to use, faster, and all around better.

(off-by-one error, I’m aware)

Categories:

Web Development