Install MariaDB (MySQL) on Debian

MariaDB is a lightweight (compared to Oracle or PostgreSQL) database management system. It is designed to manage simple databases with a big number of records. MariaDB is mainly used to store websites Content Management Systems data. MariaDB is a free software fork of MySQL.

This howto is tested on:

• Debian 10.0 Buster

Settings

Choose a root password for the database server (optional, randomly generated if not provided):

command read -s -p 'Enter MySQL/MariaDB root account password (leave empty to generate random password):' 'mysqlPassword' \
&&   command echo \
&& if [ -n "${mysqlPassword}" ]; then
  command read -s -p 'Please confirm password:' 'confirmPassword'
  command echo
  if [ "${mysqlPassword}" != "${confirmPassword}" ]; then
    mysqlPassword=''
    command echo "Error: passwords do not match. The password will be randomly generated."
  fi
fi

Installation

Detect if sudo is available (“command” is used if not):

cmdProxy='command'
command type -f 'sudo' &>'/dev/null' && cmdProxy='sudo'

Environment setup

Detect if MariaDB is available:

serverDeb='mysql-server'
[ -n "$(command apt-cache pkgnames 'mariadb-server')" ] && serverDeb='mariadb-server'

Install the software:

callChain=("DEBIAN_FRONTEND=noninteractive" "${cmdProxy}")
test "${cmdProxy}"='sudo' && callChain=('sudo' "DEBIAN_FRONTEND=noninteractive")
${callChain[@]} apt install "${serverDeb}" 'expect' 'apg'

Generate a random “root” account password for the database server, if needed:

[ -z "${mysqlPassword}" ] && mysqlPassword="$(command apg -q -a 0 -n 1 -M NCL)"

command echo "The password for MySQL root account is: '${mysqlPassword}'."

Securing installation

The following actions do the work of the “mysql_secure_installation” command.

Set the root account password:

sudo mysqladmin --user='root' 'password' "${mysqlPassword}"
sudo mysqladmin 'flush-privileges'

Store MariaDB root account password in the .my.cnf file of the current user account and of the root account in order to ease management:

Storing the MariaDB root account password in a text file is convenient a great security risk. Do this at your own risk.

command read -n 1 -p 'Storing the MariaDB root account password in a ".my.cnf" file is convenient but a great security risk. Do you really want to store the plain text MariaDB root account password in a text file ? (Y/n) :' 'securityAknowledgement' \
&&   command echo \
&& if [ -z "${securityAknowledgement}" \
    -o "${securityAknowledgement//Y/y}" = "y"  ]; then
  if [ ! -e "${HOME}/.my.cnf" ]; then
    echo "[client]
host=localhost
user=root
password=${mysqlPassword}" \
      | ${cmdProxy} tee -a '/root/.my.cnf' \
      >> "${HOME}/.my.cnf"
    ${cmdProxy} chmod 500 '/root/.my.cnf' "${HOME}/.my.cnf" 
  fi
fi

Allow using the root account to log in PHPMyAdmin (allow network root access for localhost):

command mysql --user='root' --password="${mysqlPassword}" \
<<< "GRANT ALL PRIVILEGES on *.* to 'root'@'localhost'
  IDENTIFIED BY '${mysqlPassword//\'/\'\'}';
GRANT ALL PRIVILEGES on *.* to 'root'@'127.0.0.1'
  IDENTIFIED BY '${mysqlPassword//\'/\'\'}';
GRANT ALL PRIVILEGES on *.* to 'root'@'::1'
  IDENTIFIED BY '${mysqlPassword//\'/\'\'}';
FLUSH PRIVILEGES;"

Restrict root login to localhost (delete remote login permissions):

command mysql --user=root --password="${mysqlPassword}" \
  <<< "DELETE FROM mysql.user
  WHERE user='root'
    AND host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;"

Remove anonymous user:

command mysql --user=root --password="${mysqlPassword}" \
  <<< "DELETE FROM mysql.user WHERE user='';
FLUSH PRIVILEGES;"

Remove test database:

command mysql --user=root --password="${mysqlPassword}" \
  <<< "DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE db='test' OR db='test\\_%';"

Enable point-in-time restoration and slow queries log (and other enhancements):

${cmdProxy} wget 'https://raw.githubusercontent.com/biapy/howto.biapy.com/master/mysql/base-optimisations.cnf' \
    --quiet --no-check-certificate --output-document='/etc/mysql/conf.d/base-optimisations.cnf'

Restart the database server:

${cmdProxy} systemctl restart 'mysql'

The database server is ready.

Using one file per InnoDB tables

By default, InnoDB tables are all created in the same file (table space: /var/lib/mysql/ibdata1). This file can with time take huge proportions. If the database server is destined to do a lot of manipulations (records, tables or databases creation and deletion), storing InnoDB tables in dedicated files can limit disk space usage.

${cmdProxy} tee '/etc/mysql/conf.d/innodb-file-per-table.cnf' \
<<< "# Use one file by InnoDB table.
# Use Barracuda as InnoDB engine.
# Allow InnoDB large indexs.
# Use Dynamic row format.
# Reduce disk usage, ease disk space reclaiming.
[mysqld]
innodb_file_per_table = 1
innodb_file_format=Barracuda
innodb_large_prefix=1"

Reload the configuration:

${cmdProxy} systemctl restart 'mysql'

Allow remote access to the database server

Allowing remote access to a database server is a security risk. DO NOT allow remote access if the server is directly connected to Internet. Only use remote database access on a fire-walled and secure local network.

Unset the bind-address setting to allow the database server to listen for connections on all available networks:

${cmdProxy} grep --files-with-matches --recursive '^bind-address' '/etc/mysql' \
  | ${cmdProxy} xargs -iFILE sed -i -e 's/^bind-address.*/#\0/' 'FILE'

Enable compression for clients, in order to reduce network load for queries with large result sets:

echo "# Enable connection compression for clients.
# Reduce network load and enhance response time for remote clients.
[client]
compress" \
  | ${cmdProxy} tee '/etc/mysql/conf.d/client-compression.cnf'

Reload the configuration:

${cmdProxy} systemctl restart 'mysql'

Accessing a database over network slow down the connection. For better performances, setting up a local replication of the remote database is probably the best choice.

Next steps

This howto recommends:

• Ease MySQL / MariaDB server administration with mysql-tools.

Backups

Setup a backup of the database to a remote host by using one of theses solutions:

• Backup MariaDB (MySQL) databases with Backup Manager on Debian.

Bibliography

• MySQL Cookbook: Solutions for Database Developers and Administrators.
• High Performance MySQL: Optimization, Backups, and Replication.
• MySQL High Availability: Tools for Building Robust Data Centers.
• Learning MySQL: Get a Handle on Your Data.
• MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speed.
• MySQL 8 Administrator’s Guide: Effective guide to administering high-performance MySQL 8 solutions.
• Getting Started with MariaDB.
• Learning MySQL and MariaDB: Heading in the Right Direction with MySQL and MariaDB.
• MariaDB Cookbook.
• MariaDB High Performance.

Thanks

• Thanks to MariaDB (en) developers.
• Thanks to MySQL (en) developers.
• Thanks to MySQL Performance Blog (en) for Binary log file size matters (sometimes) (en).
• Thanks to Bert Van Vreckem (en) for Automating `mysql_secure_installation` (en).