* WHAT TO DO AFTER UPGRADES
===========================

Please see the MariaDB package changelog to learn what is new in the latest
Debian revision. It can be read by e.g.

    zcat /usr/share/doc/mariadb-server/changelog.Debian.gz | more


* MARIADB WON'T START OR STOP?
============================

The most common reasons the server does not start are:
- AppArmor is enforced and something is wrong with the confinement profile.
- Process supervisor scripts (init, systemd etc) fail to execute normally.
- The configuration in /etc/mysql/... is wrong and prevents server from running.

Typically this is debugged by attempting to restart the server several times,
and looking in the system logs what the server is reporting.

Example commands when running systemd:

    systemctl restart mariadb
    systemctl status mariadb
    journalctl -u mariadb

Example commands when running rsyslogd or syslog-ng:

    /etc/init.d/mariadb restart
    /etc/init.d/mariadb status
    grep mysql /var/log/syslog

If the system does not seem to have any logs visible by default, such when
running inside a container, these commands can be used to install and activate
rsyslog:

    apt install --yes rsyslog
    /usr/sbin/rsyslogd -n -iNONE &


* NEW SERVICE NAME, PROCESS AND BINARY NAMES IN MARIADB 10.5
============================================================

Starting from MariaDB 10.5, the default SysV init service name is 'mariadb',
and can be accessed at path /etc/init.d/mariadb. The alias 'mysql' is only
created on upgrades.

On systemd services both 'mariadb' and alias 'mysql' are available all the time.

Note that the new daemon name is 'mariadbd' instead of 'mysqld' and also most of
the binaries have been renamed to mariadb-something, yet the old mysql-something
name has been kept as a symbolic link to the new name for backwards
compatibility.


* NATIVE SYSTEMD SERVICE INTRODUCED IN MARIADB 10.1
===================================================

From MariaDB 10.1 onward the upstream mariadb.service and mariadb@.service are
used to provide the full systemd experience. Some features available in
traditional /etc/init.d/mysql have been changed. For details see
https://mariadb.com/kb/en/mariadb/systemd/


* MIXING PACKAGES FROM MARIADB.ORG AND OFFICIAL DEBIAN REPOSITORIES
==================================================================

Please note that the MariaDB packaging in official Debian repositories is not
identical to that in the MariaDB.org repositories, and the packages might not be
fully interoperable. To avoid issues, don't mix and match MariaDB packages from
official Debian (or Ubuntu) repositories with packages from MariaDB.org
repositories. Packages from the MariaDB.org repositories include the revision
string '+maria'.

If a MariaDB.org repository is enabled, learn to use apt pinning properly.

Please do not file bugs in Debian regarding packages with '+maria' in the
revision string.


* DOWNGRADING MAJOR VERSIONS (E.G. 11.8 -> 11.4)
================================================

Due to the nature of databases with persistent data, major version upgrades that
alter the on-disk format will render downgrading impossible. Downgrading a major
version in-place, such as swapping the binaries of 11.8 to 11.4 with the same
data directory, is not possible.

The only way to execute a major version downgrade is to, take a
mariadb-dump/mariadb-backup consistent backup using the current version and
reload after downgrading and purging existing databases.

This may however not always work. It is always recommended to take a backup
**before** attempting an upgrade, so that in case of an emergency one can
restore to the backup.


* DOWNGRADING MINOR VERSIONS (E.G. 11.8.2 -> 11.8.1)
====================================================

Minor versions of MariaDB do not alter the on-disk data format, and thus allow
downgrades. However, once a new MariaDB version is available in the
Debian/Ubuntu repositories, it is not straightforward to `apt install` the old
version. Previous Debian packages are however available from
https://snapshot.debian.org/ but require learning how apt pinning works to use
correctly.


* DEFAULT MARIADB SERVER SETTINGS ARE SECURE
============================================

For reference, these are the default users, grants and databases in a fresh
MariaDB installation:

    SELECT User,Host FROM mysql.user;
    +-------------+-----------+
    | User        | Host      |
    +-------------+-----------+
    | mariadb.sys | localhost |
    | mysql       | localhost |
    | root        | localhost |
    +-------------+-----------+

    SHOW GRANTS FOR 'mariadb.sys'@'localhost';
    +----------------------------------------------------------------------------+
    | Grants for mariadb.sys@localhost                                           |
    +----------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`                            |
    | GRANT SELECT, DELETE ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost` |
    +----------------------------------------------------------------------------+

    SHOW GRANTS FOR 'mysql'@'localhost';
    +------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for mysql@localhost                                                                                                               |
    +------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO `mysql`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
    | GRANT PROXY ON ''@'%' TO 'mysql'@'localhost' WITH GRANT OPTION                                                                           |
    +------------------------------------------------------------------------------------------------------------------------------------------+

    SHOW GRANTS FOR 'root'@'localhost';
    +-----------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for root@localhost                                                                                                               |
    +-----------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
    | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
    +-----------------------------------------------------------------------------------------------------------------------------------------+

    SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+

These are secure. All users are bound to localhost only. The `root` user is used
by the Linux root user (or somebody running `sudo`) accessing MariaDB. The user
`mysql` is used by some system automation scripts that don't need full database
root access. The user `mariadb.sys` cannot be used to login at all, and exists
only as an internal construct for system table access.

There is absolutely no need to run the script `mariadb-secure-installation` or
(`mysql_secure_installation`) after installing MariaDB with `apt install
mariadb-server`. The script is useless, and very misleading with reporting
"Success!" after every step even if it did nothing.

The script claims to activate unix socket authentication for root, but
Debian/Ubuntu has already been using it by default for over a decade. The script
also claims to remove remote access for the root user, but the default root user
is already bound to localhost only by default. Additionally the script claims to
drop anonymous users and remove test databases, but there are none. The script
itself hasn't really been maintained for over a decade, and it is best to not
trust it in any way.


* ROOT USER AUTHENTICATION VIA UNIX SOCKET
==========================================

On new installs no root password is set and no debian-sys-maint user is
created anymore. Instead the MariaDB root account is set to be authenticated
using the Unix socket, e.g. any mysqld invocation by root or via sudo will
let the user see the mariadbd prompt.

You may never ever delete the MariaDB user "root". Although it has no password,
the unix_auth plugin ensures that it can be accessed by a locally connecting
superuser (Linux root or sudo user).

The credentials in /etc/mysql/debian.cnf specify the user which is used by the
init scripts to stop the server and perform log rotation. This used to be the
debian-sys-maint user which is no longer used as root can run directly.

If you have start/stop problems make sure that the /etc/mysql/debian.cnf file
specifies the root user and no password. In the long run please stop using that
file as is has been obsoleted.

If you need to do a restore of a previous `mysql_dump`/`mariadb-dump` that
includes the `mysql.user` table from before the passwordless transition, that
will revert you to a non-passwordless root setup (and its various drawbacks
illustrated above and below). If you'd like to restore the now-standard
passwordless-root setup, you can do so with:

  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION

And then drop the debian-sys-maint user with:

  DROP USER 'debian-sys-maint'@'localhost'

And then:

  FLUSH PRIVILEGES

NOTE: Before modifying root permissions to the MariaDB instance, you should
ensure you keep a mysql root connection open until you're sure the changes
work and you can still create new root connections to your server.


* ROOT USER WITH REMOTE ACCESS
==============================

This is not recommended in general, but if you specifically want the user 'root'
to be able to access MariaDB remotely, run this to add a second 'root' entry in
the users table that can access from any remote host and authenticate using
password:

    -- Configure root@% for remote connections using password
    CREATE OR REPLACE USER 'root'@'%' IDENTIFIED BY 'your_secure_password';

    -- Grant full privileges with permission to grant more
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
    GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION;

    -- Reload privilege tables
    FLUSH PRIVILEGES;


* CREATING DATABASES AND USERS FOR APPS
=======================================

On Debian/MariaDB running `apt install mariadb-server` will set up a sane and
secure MariaDB server by default. The commands `mariadb-install-db` and
`mariadb-upgrade` run automatically when needed.

The primary tasks for the administrator is to create new users and databases,
and add custom settings in `/etc/mysql/mariadb.conf.d/`.

To add a custom database for an app, and create a custom password-authenticated
user that has full access to that database over the network, run the following
commands using the `mariadb` client (e.g., via `sudo mariadb`):

    -- Create the database
    CREATE DATABASE app_db;

    -- Create the user 'app_user' with a secure password, allowing connections from any IP address
    CREATE USER 'app_user'@'%' IDENTIFIED BY 'your_secure_password';

    -- Grant full privileges to the 'app_user' user on the 'app_db' database
    GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'%';

    -- Reload the privilege tables to apply changes
    FLUSH PRIVILEGES;

Remember to replace 'your_secure_password' with a strong, unique password.
Using '%' for the host allows connections from any IP address, which is less
secure than restricting connections to a specific IP address or subnet if
possible.


* REMOTE CONNECTIONS OVER THE NETWORK AND TLS
=============================================

For security reasons, the Debian package has enabled networking only on the
loop-back device using `bind-address` in
`/etc/mysql/mariadb.conf.d/50-server.cnf`. Check current setting by running:

    SHOW VARIABLES LIKE 'bind_address';

Alternatively, check with `sudo netstat -tlnp` on what ports services are
listening. If your connection is aborted immediately, check your firewall rules
or network routes.

To allow remote connections from the network, the easiest way is to create a new
configuration file and allow the MariaDB Server to listen for remote
connections. To ensure passwords don't leak on the network, enforce that
connections must use TLS encryption.

Create file `/etc/mysql/mariadb.conf.d/99-server-customizations.cnf`
with contents:

    [mariadbd]
    # Listen for connections from anywhere
    bind-address = 0.0.0.0
    # Only allow TLS encrypted connections
    require-secure-transport = on

For settings to take effect, restart the server:

    systemctl restart mariadb

Note that in MariaDB 11.8 the TLS certificates are generated automatically, so
there is no need to create them manually.

Test connections with e.g.

    mariadb --user=app_user --password=your_secure_password  --host=192.168.1.66 -e '\s'
    mariadb --user=root --password=your_secure_password  --host=192.168.1.66 --ssl


* WARNING OF REPLICATION WITH TMPDIR
====================================

If the MariaDB server is acting as a replication slave, you should not
set --tmpdir to point to a directory on a memory-based file system or to
a directory that is cleared when the server host restarts. A replication
slave needs some of its temporary files to survive a machine restart so
that it can replicate temporary tables or LOAD DATA INFILE operations. If
files in the temporary file directory are lost when the server restarts,
replication fails.


* BACKUPS
=========

Backups save jobs. Don't get caught without one.


* WHERE IS THE DOCUMENTATION?
=============================

Read more at https://mariadb.com/kb