* 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