How to Set Up MariaDB for AMX Mod X (Local or Remote)

Stats, bans, shops and VIP plugins all want a MySQL-compatible database, and on Debian or Ubuntu the one to install is MariaDB. It is what the distribution ships, and it still creates users with the mysql_native_password login method, which the MySQL 5.5 client code built into the AMX Mod X module understands. MySQL 8 defaults to a newer login method (caching_sha2_password) that the older client code in the AMX Mod X module may not support, a common reason a fresh MySQL 8 box rejects every plugin. This page covers the database server. The plugin side (the module, sql.cfg, threaded queries) is in enabling the MySQL module in AMX Mod X.

1. Install MariaDB

<code>sudo apt update
sudo apt install -y mariadb-server
sudo systemctl enable --now mariadb
sudo mariadb -e "SELECT VERSION();"</code>

On Debian and Ubuntu the database root account logs in through the Unix socket: sudo mariadb works with no password because the operating system has already proved you are root. Keep it that way: there is no root password to leak or brute-force.

2. Secure the install

<code>sudo mariadb-secure-installation</code>

• Enter current password for root: press Enter.
• Switch to unix_socket authentication: n. The script itself tells you root is already protected.
• Change the root password: n, for the same reason.
• Remove anonymous users, Disallow root login remotely, Remove test database, Reload privilege tables: Y to all four.

3. Create the database and a least-privilege user

Generate a password that needs no quoting in sql.cfg:

<code>openssl rand -hex 20</code>

Then, for a database on the same machine as the game server:

<code>sudo mariadb
CREATE DATABASE amxx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'amxx'@'127.0.0.1' IDENTIFIED BY 'paste-the-generated-password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX ON amxx.* TO 'amxx'@'127.0.0.1';</code>

Granting to 127.0.0.1 rather than localhost matters: with amx_sql_host set to 127.0.0.1 AMXX connects over TCP, and MariaDB matches user and host together, so 'amxx'@'localhost' would only cover socket logins. No ALL PRIVILEGES, no root: a plugin you downloaded should not be able to drop the database. If a plugin's upgrade notes say it alters its own tables, grant ALTER for that upgrade and revoke it afterwards.

4. Same host or separate host

Same host (the default, and the best choice for one server)

Debian's config already binds MariaDB to loopback only. Confirm nothing listens on the network:

<code>sudo ss -ltnp | grep 3306</code>

You want 127.0.0.1:3306. Nothing outside the box can reach it, so port 3306 needs no firewall rule, and queries take well under a millisecond.

Separate host (several game servers sharing bans or stats)

Assume the database box is 10.0.0.5 and the game server is 10.0.0.10. On the database box, edit /etc/mysql/mariadb.conf.d/50-server.cnf and change the bind-address line to the address the game server will connect to, preferably a private network address:

<code>bind-address            = 10.0.0.5</code>

<code>sudo systemctl restart mariadb
sudo ss -ltnp | grep 3306</code>

Use 0.0.0.0 only if there is no private network, and then the firewall below is mandatory. Create the user for the game server's IP, as MariaDB will see it (behind NAT or over the internet, that is the game box's public IP):

<code>CREATE USER 'amxx'@'10.0.0.10' IDENTIFIED BY 'paste-the-generated-password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX ON amxx.* TO 'amxx'@'10.0.0.10';</code>

Each additional game server gets its own CREATE USER and GRANT for its IP. On the game server, amx_sql_host becomes 10.0.0.5.

Now allow 3306 from the game server only. With ufw (allow your SSH port before enabling it, or you lock yourself out):

<code>sudo ufw allow OpenSSH
sudo ufw allow from 10.0.0.10 to any port 3306 proto tcp
sudo ufw enable
sudo ufw status numbered</code>

If SSH listens on a non-standard port, allow that port number instead of OpenSSH. With plain iptables, the accept rule must come before the drop:

<code>sudo iptables -A INPUT -p tcp -s 10.0.0.10 --dport 3306 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP
sudo apt install -y iptables-persistent
sudo netfilter-persistent save</code>

Two warnings. If MariaDB runs in Docker with a published port, Docker's own rules bypass ufw, so publish it on a specific address (-p 10.0.0.5:3306:3306) instead of all of them. And AMXX's SQL connection has no TLS option, so queries cross the wire in plain text: if the two boxes talk over the public internet, put them on a private network or a WireGuard tunnel.

5. Tuning that matters for AMXX

An AMXX database is tiny and often shares the machine with HLDS, so most MySQL tuning advice does not apply. Put this in /etc/mysql/mariadb.conf.d/60-amxx.cnf:

<code>[mysqld]
skip-name-resolve
innodb_buffer_pool_size = 128M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 0.5</code>

<code>sudo mkdir -p -m 2750 /var/log/mysql
sudo chown mysql /var/log/mysql
sudo systemctl restart mariadb</code>

• skip-name-resolve stops MariaDB doing a reverse DNS lookup on every new connection. With slow DNS that lookup stalls the connect, and a plugin that connects without threading freezes the game while it waits. Grants by IP, as above, keep working.
• innodb_buffer_pool_size: 128M holds an entire AMXX database many times over. Ignore guides that say 70 to 80 percent of RAM; that RAM belongs to HLDS here.
• The slow query log names the plugin query that takes half a second, which is exactly the query that makes players stutter. Usually a missing index on a SteamID column.

Also check the table engines. Old plugin SQL files sometimes create MyISAM tables, which lock the whole table on every write and are not covered by a consistent backup:

<code>SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'amxx';
ALTER TABLE some_table ENGINE=InnoDB;</code>

6. Backups with mariadb-dump and cron

Create /usr/local/sbin/amxx-db-backup:

<code>#!/bin/bash
set -euo pipefail
DST=/var/backups/amxx
mkdir -p -m 700 "$DST"
mariadb-dump --single-transaction --routines --triggers amxx \
  | gzip > "$DST/amxx-$(date +%F_%H%M).sql.gz"
find "$DST" -name 'amxx-*.sql.gz' -mtime +14 -delete</code>

<code>sudo chmod 700 /usr/local/sbin/amxx-db-backup
sudo crontab -e</code>

<code>15 5 * * * /usr/local/sbin/amxx-db-backup</code>

Running from root's crontab uses socket login, so no password is stored anywhere. --single-transaction takes a consistent snapshot of InnoDB tables without locking out the game server, and pipefail makes the job fail loudly if the dump fails instead of writing an empty archive. Keeping date +%F inside the script also dodges cron, which treats a bare % in a crontab line as a newline. Prove a backup restores before you need it. As root (sudo -i):

<code>LATEST=$(ls -1t /var/backups/amxx/*.sql.gz | head -n 1)
mariadb -e "CREATE DATABASE amxx_restoretest"
zcat "$LATEST" | mariadb amxx_restoretest
mariadb -e "SHOW TABLES FROM amxx_restoretest"
mariadb -e "DROP DATABASE amxx_restoretest"</code>

Copying the archives off the machine is covered in backing up configs, bans and stats.

7. Verify from the game box

Test the path the plugin will use, from the game server, with the plugin's credentials:

<code>sudo apt install -y mariadb-client
mariadb -h 10.0.0.5 -u amxx -p amxx -e "SELECT CURRENT_USER(); SHOW GRANTS;"</code>

CURRENT_USER() should print [email protected], followed by your GRANT line. If this works and the plugin still fails, the problem is in sql.cfg, not the database. A newer client talking to an older server may refuse with an SSL error; add --skip-ssl for this test, since the AMXX module does not use TLS anyway.

Common errors

• Access denied for user 'amxx'@'10.0.0.10' (using password: YES): wrong password, or the user exists for a different host than the one in the message. The host in the message is the one to grant. See Access denied from an AMXX plugin.
• Host '10.0.0.10' is not allowed to connect to this MariaDB server: no user exists for that host at all. Create one for the IP shown.
• Can't connect to MySQL server on '10.0.0.5' (111): connection refused. MariaDB is still bound to 127.0.0.1, not running, or a firewall rejects. See errno 111.
• The connection hangs until it times out: a firewall is silently dropping 3306, often the cloud provider's firewall rather than the one on the box.
• The game freezes for seconds when the database is remote: a plugin uses blocking queries, which a network round trip makes visible. See server freezes on MySQL query.
• Backup files are a few bytes: the dump failed. Run the script by hand and read the error.

Verification checklist

1. ss -ltnp shows 3306 on 127.0.0.1 (same host) or on the private IP (separate host), never unfiltered on 0.0.0.0.
2. SHOW GRANTS FOR each amxx user lists only the amxx database and no ALL PRIVILEGES.
3. From any IP other than the game server, port 3306 does not answer.
4. The mariadb client test from the game box prints the expected CURRENT_USER().
5. A fresh .sql.gz appears each morning and restores into a test database.