Setting up MySQL and MariaDB #

This page is for database administrators who prepare MySQL or MariaDB for an Exponential site. Both run on the same driver, eZMySQLiDB (lib/ezdb/classes/ezmysqlidb.php), on the PHP extension mysqli. The page answers: how to create the database and the account, which privileges are needed and why, what the settings look like and how to run the installer. Source: 9.3 MySQL and MariaDB.

Contents #

1. Requirements
2. Create the database and the account
3. Why these privileges
4. The settings
5. Install
6. MariaDB and MySQL: what differs
7. First checks
8. Checklist

Requirements #

• A MySQL or MariaDB release your vendor still supports with security fixes, with InnoDB and utf8mb4. The wizard's floor of 4.1.1 is historical. The project's CI runs MySQL 8.0; the development server runs MariaDB 10.5.
• The PHP extension mysqli (mysqlnd) in every PHP that uses the site.

Create the database and the account #

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

Use the web servers' host or network in place of localhost when the database is on another machine. None of the installers creates the database or the account.

Why these privileges #

• SELECT, INSERT, UPDATE, DELETE: the site's normal work.
• CREATE, DROP, INDEX, ALTER: the installer and every later update file create and change tables and indexes.
• LOCK TABLES: the driver's lock() uses LOCK TABLES.
• CREATE TEMPORARY TABLES: the kernel creates temporary tables for some operations (names such as ezsearch_tmp_12).

No global privilege, no SUPER and no right to list databases is needed: the wizard works with per-database privileges. If your policy separates a migration account from the run-time account, the run-time account still needs CREATE TEMPORARY TABLES and LOCK TABLES; test the split on staging, because the project does not document or test such a split (Database accounts and privileges).

The settings #

[DatabaseSettings]
DatabaseImplementation=ezmysqli
Server=localhost
# empty: 3306
Port=
User=exponential
Password=<the password>
Database=exponential
# a Unix socket instead of host and port, e.g. /var/lib/mysql/mysql.sock
Socket=disabled
Charset=utf-8
# enabled: mysqli persistent connections (the driver prefixes the host with p:)
UsePersistentConnection=disabled
ConnectRetries=0

ezmysqli, mysqli, ezmysql and mysql all name this driver.

Install #

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

--db=mariadb is the same. In the setup wizard choose "MySQL Improved"; it checks that the database's default character set is UTF-8 and refuses a latin1 database. The kickstarter takes [database_choice] Type=mysqli (mysql accepted). The installer creates InnoDB tables when the server offers InnoDB (kernel/setup/steps/ezstep_create_sites.php).

MariaDB and MySQL: what differs #

For Exponential there is no other difference: the same driver, the same settings, the same update files in update/database/mysql/.

First checks #

mysql -u exponential -p exponential -e "SELECT name, value FROM ezsite_data WHERE name LIKE 'ezpublish-%';"
mysql -u exponential -p exponential -e "SELECT ENGINE, COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='exponential' GROUP BY ENGINE;"

All tables should be InnoDB. Then open Setup > System information: the Database card shows the engine, the server version and the character set.

Checklist #

• To do: A supported MySQL or MariaDB release with InnoDB.
• To do: The database is utf8mb4.
• To do: The account has the privileges above on this database only, and is not root.
• To do: The account is restricted to the web servers' hosts.
• To do: Backups with --single-transaction are scheduled (Backups per engine).
• To do: AlwaysLog[]=warning was enabled while you set up the site, to catch charset warnings.

Related pages #

• MySQL and MariaDB: utf8mb4, the connection charset and InnoDB
• MySQL and MariaDB read replicas
• MySQL for hosts, MariaDB for hosts
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.