Give a website its own database

MySQL is the default. Most people arrive with a database they already have and theirs is usually MySQL, and a dump MySQL wrote imports into MySQL without being edited first. Pass --database-engine mariadb at install to build the server on MariaDB instead.

The two cannot be installed side by side: each declares a conflict with the other and they want the same port and the same data directory. So this is chosen once, when the server is built. A server that already runs one keeps it and tells you so rather than replacing it, because switching means migrating every database on the machine and that is a job with a backup behind it.

Nearly everything in the panel is the same either way. Two differences are worth knowing:

• A dump written by MySQL 8 names a collation that older MariaDB does not have, so importing one into MariaDB fails until it is edited. On a MySQL server it simply imports.
• Moodle needs MySQL 8.4, and Ubuntu carries 8.0, so a server built on MySQL refuses to install Moodle and says why. A server built on MariaDB runs it. Every other application in the catalogue installs on either.

Open the website, then the engine you want under Databases. A server is built on MySQL or on MariaDB, and the tab is headed with whichever yours has; PostgreSQL appears beside it when the server has that too. Only the engines your server has are listed. Then New database.

The only thing to decide is the name. That name is yours, for telling one database from another in the panel; the database server never sees it. The real database is called hpd_ followed by an internal identifier, and the panel shows you that name wherever you need it.

A database cannot move to the other engine afterwards, because the two do not read each other’s data. Pick PostgreSQL when the application asks for it; otherwise use the one your server was built on.

Then Create account, which is what your application signs in as. The account reaches that one database and nothing else, and its password is shown once and never stored, so copy it before closing the dialogue.

Four values, all shown when you create the account. Nothing on the internet can reach your database whatever the password: neither engine listens on a network address.

Use the host the panel gives you, not localhost. It is localhost for a PHP or Node website on MySQL or MariaDB, because those run on the server itself. It is a socket path for everything else, and inside a container localhost is the container rather than the server, so an application given it reports that the database is not running.

Host      localhost                       PHP or Node, MySQL or MariaDB
          /run/mysqld/mysqld.sock         a container, MySQL or MariaDB
          /var/run/postgresql             PostgreSQL
Database  hpd_4f2c8a1b9e0d7c36
Username  hpu_8b3e1f0a7c92d546
Password  shown once when you create the account

For WordPress that is wp-config.php:

define( 'DB_HOST', 'localhost' );
define( 'DB_NAME', 'hpd_4f2c8a1b9e0d7c36' );
define( 'DB_USER', 'hpu_8b3e1f0a7c92d546' );
define( 'DB_PASSWORD', 'the password you copied' );

For Laravel and anything else reading a .env file:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=hpd_4f2c8a1b9e0d7c36
DB_USERNAME=hpu_8b3e1f0a7c92d546
DB_PASSWORD=the password you copied

Put the file outside the folder the web server serves, or make sure the server will not hand it out. A .env inside public_html is readable by anybody who guesses its name.

If you would rather work in a familiar tool, phpMyAdmin opens from a MySQL or MariaDB database’s page, already signed in as that website’s own account. The link is good for a couple of minutes and once only, so open it from the panel each time rather than bookmarking it.

Creating a database, issuing accounts, size limits and backups all work the same way. Four things do not.

Over its size limit, PostgreSQL refuses writes and keeps reads working, the same promise as the other engine by a different route: the website’s accounts are moved to a role that may only read. Deleting rows does not return the space on its own, and VACUUM FULL does.

This is MySQL and MariaDB only for now. Export from the old server, create the database here, then Import SQL into it. Import runs your file as it stands, so what it does depends on what is in it. A dump from mysqldump or mariadb-dump normally carries a DROP TABLE IF EXISTS before each table, which means it replaces the tables it contains and leaves anything else in the database untouched. Importing into a database you have just made avoids having to think about it.

mysqldump -u olduser -p --single-transaction \
  --default-character-set=utf8mb4 oldname > oldname.sql

A dump from phpMyAdmin or another panel works just as well. If yours is over the 64 MiB limit, the usual reason is a log or session table you do not need; drop it before dumping. If it is genuinely that large, moving the website over SSH has no such limit and brings the files too.

A database over its size limit does not break and is not deleted. Its account keeps the privileges it needs to get back under the limit and loses the ones that would make things worse: it can still read, delete rows, drop tables, lock tables and run routines, but it cannot insert or update.

So an application will usually report that a write failed while its pages still load, and clearing out whatever grew will put it back. The quota is enforced by the database’s own grant rather than by the panel asking nicely, which is why it holds even for a script connecting directly.

The size shown is the database’s files on disk, measured by the server rather than read out of information_schema. Those statistics lag badly: a directory really holding 9.4 MB reported 16 KB before an ANALYZE and 1.6 MB after one. If a tool shows you a smaller number than the panel does, the panel is the one measuring the disk the limit protects.

A database backup is this one thing and is quick both ways: Restore backup puts it back without touching the website’s files. That is what you want when the mistake was a bad query rather than a bad release.

A restore drops and recreates each table the backup holds. A table created after the backup was taken, and so not in it, survives the restore. If you want the database exactly as it was, delete it and restore into a fresh one.

A recovery point is the whole website, databases first and then files, so the database is never newer than the code expecting it. While a backup runs, the database is briefly held read-only so the copy is consistent; the window is bounded at an hour and normally lasts seconds. See backups and recovery points.

Databases count against the hosting package’s allowance. See customers and hosting packages.