MariaDB

MariaDB runs once on the host and serves every site. Each site gets one database, one user limited to that database, and a per-site my.cnf the application can apply at connect time.

Server-wide config

Lives in /etc/mysql/mariadb.conf.d/99-tulixhost.cnf. Numbered 99- so it loads last and overrides any earlier file:

[mysqld]
bind-address              = 127.0.0.1
skip-name-resolve         = 1
local-infile              = 0
max_allowed_packet        = 64M
max_connections           = 200
thread_cache_size         = 32
table_open_cache          = 4000
innodb_file_per_table     = 1
innodb_buffer_pool_size   = 256M
innodb_log_file_size      = 128M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method       = O_DIRECT
slow_query_log            = 1
slow_query_log_file       = /var/log/mysql/slow.log
long_query_time           = 2
log_error                 = /var/log/mysql/error.log

Tune innodb_buffer_pool_size for your host: aim for 50-70% of available RAM on a DB-dedicated box; on a shared host like this one, leave it at ~256M and grow only as needed. Reload with sudo systemctl restart mariadb after editing.

Root authentication

setup.sh forces root to authenticate via unix_socket. There is no root password — anything trying to log in as root over the network is rejected, and root only works from a process running as the actual unix root user. The DBA workflow is:

sudo mariadb           # root shell — no password prompt

If you want a password-based DBA account on top of socket root (e.g. for remote admin via SSH tunnel), create one explicitly:

sudo mariadb <<'SQL'
CREATE USER 'admin'@'127.0.0.1' IDENTIFIED BY 'a-strong-password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SQL

Per-site user grants

create_vhost.sh creates one database and one user per site, both named after the site's unix user (e.g. web_example_com). The grant scope is exactly that one database:

CREATE DATABASE IF NOT EXISTS `web_example_com` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS 'web_example_com'@'127.0.0.1' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE,
      CREATE, ALTER, INDEX, DROP, REFERENCES,
      CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE,
      CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE,
      TRIGGER, EVENT
   ON `web_example_com`.*
   TO 'web_example_com'@'127.0.0.1';

The user can only connect from 127.0.0.1. The user has no GRANT OPTION, no RELOAD, no SUPER, and no FILE. They can fully manage their own schema and routines but cannot see information_schema rows for other databases.

Per-site my.cnf

The per-site file at conf/my.cnf has three sections:

[client], [mysql], [mysqldump]

These are read by the CLI tools when invoked with --defaults-extra-file=/data/web/<site>/conf/my.cnf. Convenient for ad-hoc queries — the prompt shows the site name, the charset is set, single-transaction dumps are enabled.

mariadb --defaults-extra-file=/data/web/example.com/conf/my.cnf

[tulixhost_session]

Informational. These are values your application code should apply with SET SESSION on every new connection:

SET SESSION max_execution_time   = 30000;   -- ms — kills runaway queries
SET SESSION wait_timeout         = 600;
SET SESSION interactive_timeout  = 600;
SET SESSION net_read_timeout     = 30;
SET SESSION net_write_timeout    = 60;
SET SESSION group_concat_max_len = 1048576;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION transaction_isolation = 'READ-COMMITTED';

Why session-level and not server-wide? Because two apps on the same host may need different sql_modes or isolation levels. READ-COMMITTED is a common choice for web apps (less locking than REPEATABLE-READ at the cost of phantom reads, which web apps rarely care about). max_execution_time at 30s is a brute-force way to kill a runaway report query — your app should set this higher for known long-running queries.

[tulixhost_pool]

Connection pool guidance for the app's pool config (PHP-PDO doesn't pool by default; Laravel + persistent PDO, ProxySQL, or a Go app's database/sql all do):

pool_min_idle           = 1
pool_max_open           = 16
pool_max_lifetime_sec   = 1800
pool_max_idle_time_sec  = 300

Sized so that max_open × number_of_FPM_workers stays comfortably below the server-wide max_connections = 200. With 20 workers per site and three sites, you'd already be at 240 connections worst-case — bump max_open down to 8 or max_connections up to 400.

How the app uses all of this

The site's .envtulix contains a ready-to-use DSN:

DB_DSN="mysql:host=127.0.0.1;port=3306;dbname=web_example_com;charset=utf8mb4"
DB_USER="web_example_com"
DB_PASS="..."

A typical bootstrap in PHP:

$env = parse_ini_file('/data/web/example.com/conf/.envtulix');
$pdo = new PDO($env['DB_DSN'], $env['DB_USER'], $env['DB_PASS'], [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::ATTR_PERSISTENT         => false,
]);
// Apply per-site session caps from my.cnf [tulixhost_session].
$pdo->exec("SET SESSION sql_mode='...', transaction_isolation='READ-COMMITTED', ...");

Useful queries

What's slow right now?

SELECT id, user, host, db, command, time, state, LEFT(info, 200) AS query
  FROM information_schema.processlist
 WHERE command != 'Sleep' AND time > 1
 ORDER BY time DESC;

Tables in this DB by size

SELECT table_name,
       engine,
       table_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS mb
  FROM information_schema.tables
 WHERE table_schema = DATABASE()
 ORDER BY data_length + index_length DESC;

Find tables without a primary key (warning sign)

SELECT table_name
  FROM information_schema.tables t
 WHERE t.table_schema = DATABASE()
   AND t.table_type = 'BASE TABLE'
   AND NOT EXISTS (
       SELECT 1 FROM information_schema.statistics s
        WHERE s.table_schema = t.table_schema
          AND s.table_name   = t.table_name
          AND s.index_name   = 'PRIMARY');

Backups

backup_site.sh runs mysqldump --single-transaction --routines --triggers --events --hex-blob against the site's DB using the credentials from .envtulix — so backups don't require root and work even if the root socket access breaks. Restores use mysql_root to recreate the user (so it can set the password back to whatever was in .envtulix) and then loads the dump as that user. See Cron & backups for the full restore flow.