Set up a separate MySQL/MariaDB database server for your web servers

As a site grows, putting the database on its own server lets the web and database tiers use their full resources independently and makes scaling to several web servers possible. This guide sets up a separate MySQL/MariaDB database server that only your app servers can reach.

Before you start

• Two Linux servers: a web/app server (for example private IP 10.0.0.11) and a database server (10.0.0.21), ideally on a private network in the same data centre for low latency.
• MariaDB or MySQL installed on the database server.
• A recent backup of your current database.

Bind the database to the private IP

1. Edit the server config: on Ubuntu/Debian MariaDB uses /etc/mysql/mariadb.conf.d/50-server.cnf; on AlmaLinux /etc/my.cnf.d/mariadb-server.cnf. Under [mysqld] set:
   Copy

   bind-address = 10.0.0.21
   skip-name-resolve

2. Restart: sudo systemctl restart mariadb.
3. Confirm: ss -tlnp | grep 3306 shows 10.0.0.21:3306, not 0.0.0.0.

Avoid binding to a public IP. If you have no private network, use a VPN such as WireGuard between servers.

Create a database and a restricted user

Open the shell with sudo mariadb:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'10.0.0.11' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON appdb.* TO 'appuser'@'10.0.0.11';
FLUSH PRIVILEGES;

The @'10.0.0.11' host part means the account can only connect from the app server. Add another user line for each extra web server.

Firewall port 3306

• UFW: sudo ufw allow from 10.0.0.11 to any port 3306 proto tcp
• firewalld: sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.11" port port="3306" protocol="tcp" accept' && sudo firewall-cmd --reload

Enable TLS for connections (recommended)

1. Place a certificate and key readable by the mysql user, then add under [mysqld]:
   Copy

   ssl_cert = /etc/mysql/ssl/server-cert.pem
   ssl_key  = /etc/mysql/ssl/server-key.pem
   ssl_ca   = /etc/mysql/ssl/ca.pem

2. Require TLS for the app user: ALTER USER 'appuser'@'10.0.0.11' REQUIRE SSL;
3. Restart MariaDB and check with SHOW VARIABLES LIKE 'have_ssl';.

Move the data and connect the app

1. On the old server: mysqldump --single-transaction --routines appdb > appdb.sql.
2. Copy it over and import: mariadb appdb < appdb.sql.
3. Test from the app server: mariadb -h 10.0.0.21 -u appuser -p appdb.
4. Update the app's database host to 10.0.0.21 (for WordPress, DB_HOST in wp-config.php).

Tune the dedicated database server

Without a web server competing for memory, you can give InnoDB most of the RAM – commonly well over half on a database-only server – via innodb_buffer_pool_size. See the tuning guide for details.

Troubleshooting

• ERROR 2003 Can't connect: wrong bind address, firewall blocking 3306, or the service is down.
• ERROR 1045 Access denied: the user's host part doesn't match the app server's IP as seen by the database server.
• Slow queries after the move: high latency between servers; keep them in the same data centre and reduce round trips with caching.

FAQ: separate database server

When should I split the database onto its own server?

When the database and web server compete for RAM or CPU, or you need several web servers sharing one database.

Is it safe to expose MySQL to the internet?

Avoid it. Use a private network or VPN and IP-restricted users.

Does a separate database server make a single site faster?

Only if resources were the bottleneck. Network latency adds a little time to each query.

Next steps

• MySQL/MariaDB performance tuning
• Back up and restore with mysqldump
• MySQL vs MariaDB vs PostgreSQL