MariaDB for hosts #

This page is for system administrators and support staff at a hosting company who provide MariaDB databases for Exponential sites, which is the usual database a hosting panel creates. It answers: how MariaDB is driven, how to create a site's database and user, what to watch with character sets, and how to back it up. MariaDB is fourth in the official order and the MySQL-compatible server the project prefers. Sources: Database support and chapter 9.3.

Contents #

1. One driver for MariaDB and MySQL
2. Versions
3. Creating a database per site
4. Settings and install
5. Character sets
6. Backups
7. Problems and their causes
8. Checklist
9. Related pages

One driver for MariaDB and MySQL #

MariaDB runs on the same driver as MySQL, eZMySQLiDB (lib/ezdb/classes/ezmysqlidb.php), on the PHP extension mysqli. Everything on MySQL for hosts applies; this page covers what a host needs to do, and what differs.

• The setup wizard lists it as "MySQL Improved".
• exp:install --db=mariadb is the same as --db=mysql.
• The kickstarter takes [database_choice] Type=mysqli (mysql accepted).
• MariaDB is not in the project's CI; the MySQL 8.0 job exercises the same driver, and MariaDB 10.5 runs on the project's development server (test coverage).

Versions #

The wizard's floor is 4.1.1 (the MySQL number in eZSetupDatabaseMap()), which is not a recommendation. Run a release your vendor still supports with security fixes, with utf8mb4 and InnoDB (versions).

Creating a database per site #

The installers do not create databases or users. No global privilege is needed:

CREATE DATABASE <customer> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER '<customer>'@'localhost' IDENTIFIED BY '<a strong password>';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
      LOCK TABLES, CREATE TEMPORARY TABLES
   ON <customer>.* TO '<customer>'@'localhost';

LOCK TABLES and CREATE TEMPORARY TABLES are listed because the driver's lock() uses LOCK TABLES and the kernel creates temporary tables for some operations. No SUPER and no right to list databases is needed.

Settings and install #

[DatabaseSettings]
DatabaseImplementation=ezmysqli
Server=localhost
# empty: 3306
Port=
User=<customer>
Password=<the password>
Database=<customer>
# a Unix socket instead of host and port
Socket=disabled
Charset=utf-8
UsePersistentConnection=disabled

EXP_INSTALL_DB_PASSWORD='<the password>' php bin/php/console exp:install --apply --db=mariadb \
    --db-host=127.0.0.1 --db-name=<customer> --db-user=<customer> --allow-root-user

The installer creates InnoDB tables when the server offers InnoDB; transactions need it. For an older installation, php bin/php/ezconvertmysqltabletype.php --list lists the table types and --newtype=InnoDB converts them.

Read replicas. UseSlaveServer=enabled with SlaveServerArray[] and the related keys sends reads to a replica chosen at random per request; writes go to Server. MySQL and MariaDB are the only engines with built-in read splitting (chapter 9.3, Replicas).

Character sets #

• Create the database in utf8mb4. The installer creates its tables without a character set of their own, so they take the database's default.
• The connection: Charset=utf-8 (the default) is sent to the server as utf8, which MariaDB treats as the three-byte utf8mb3. Characters outside the Basic Multilingual Plane, such as emoji, cannot pass through the connection even when the tables are utf8mb4. Keep the database in utf8mb4 anyway.
• If the connection character set cannot be set, the driver keeps running and logs "could not set the connection charset", but only when warnings are logged (site.ini [DebugSettings] AlwaysLog[]=warning). Text then comes back with ? in place of characters.

The System information page warns when the connection's character set is not UTF-8.

Backups #

mariadb-dump --single-transaction -u <customer> -p <customer> > <customer>.sql
mariadb -u <customer> -p <customer> < <customer>.sql

--single-transaction gives a consistent dump of InnoDB tables without locking them; the older name mysqldump works too. Commands with a password on the command line show it to other users of the machine; prefer the prompt or an option file. A complete backup also contains var/storage, settings/override and settings/siteaccess.

Problems and their causes #

Checklist #

• To do: A supported MariaDB release with InnoDB.
• To do: One database per site in utf8mb4, one user with the grants above.
• To do: mysqli in the web and the command line PHP.
• To do: Panel defaults checked for character set and grants.
• To do: mariadb-dump --single-transaction backups scheduled and a restore tested.

Related pages #

• Databases in the official order and MySQL for hosts
• Database support: MariaDB and character sets
• Chapter 9.3: MySQL and MariaDB
• Database backups per engine
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.