MySQL for hosts #

This page is for system administrators at a hosting company who provide MySQL databases for Exponential sites. It answers: where MySQL stands, how to create a site's database and user, what the driver needs from the server, how to use replicas, and how to back it up. MySQL is sixth in the official order: supported and tested in the project's CI, last only because the project recommends the others first for new sites. Sources: Database support and chapter 9.3.

Contents #

1. Where MySQL stands
2. Versions and extension
3. Creating a database per site
4. Settings and install
5. Replicas and persistent connections
6. Backups
7. Problems and their causes
8. Checklist
9. Related pages

Where MySQL stands #

• The driver eZMySQLiDB on mysqli is the one most third-party extensions were originally written against, so MySQL is the safe choice for a customer with older extensions that contain SQL of their own.
• It shares the driver with MariaDB (MariaDB for hosts); for a host, the two are provisioned the same way.
• The project's CI runs its database tests against MySQL 8.0 on every push (test coverage).

Versions and extension #

Creating a database per site #

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';

No global privilege is needed. The installer creates InnoDB tables when the server offers InnoDB, and transactions need it. The MySQL update files of an upgrade start with SET default_storage_engine=InnoDB; (chapter 9.3).

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, e.g. /var/lib/mysql/mysql.sock
Socket=disabled
Charset=utf-8
UsePersistentConnection=disabled
ConnectRetries=0

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

Replicas and persistent connections #

• Read replicas. UseSlaveServer=enabled with SlaveServerArray[], SlaveServerPort[], SlaverServerUser[], SlaverServerPassword[] and SlaverServerDatabase[] (the keys really are spelled Slaver...) sends reads to a replica chosen at random per request; writes go to Server. Replication lag means a page may show content a few seconds old.
• Persistent connections. UsePersistentConnection=enabled keeps one connection per PHP process (the driver prefixes the host with p:). Under Velocity's persistent workers each worker keeps its connection anyway. Size the server's connection limit for the number of PHP processes of every site that uses it.

Backups #

mysqldump --single-transaction -u <customer> -p <customer> > <customer>.sql
mysql -u <customer> -p <customer> < <customer>.sql

--single-transaction gives a consistent dump of InnoDB tables without locking them. A complete backup also contains var/storage, settings/override and settings/siteaccess (Backup and restore).

Problems and their causes #

Checklist #

• To do: A supported MySQL 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: The server's connection limit sized for the PHP processes of every site on the server.
• To do: mysqldump --single-transaction backups scheduled and a restore tested.

Related pages #

• Databases in the official order and MariaDB for hosts
• Database support: MySQL
• 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.