MySQL Master-Slave replication for MU Online servers

MySQL Master-Slave replication is the most widely used technique for adding redundancy and distributing read load on MU Online servers that have moved past the initial stage and need more reliability. Unlike a static backup, the replica stays constantly synchronized with the primary database, providing both failure contingency and relief from heavy queries. This tutorial details the full setup using GTID (the currently recommended method), lag monitoring, and the slave promotion procedure.

What Master-Slave replication is and when it makes sense to implement

In Master-Slave replication, the master server receives all writes (everything the GameServer writes: login, character creation, item movement, leveling up) and logs each transaction to a binary log (binlog). The slave server continuously reads that binlog and applies the same changes to its own copy of the database. It makes sense to implement this once the server already has a stable population, once administrative queries/reports start impacting the main database's performance, or as the foundation for a more serious contingency plan than periodic backups alone.

Technical prerequisites

• Two MySQL/MariaDB instances on the same major version (avoids replication incompatibility).
• Reliable network between the two servers, ideally private/VPN.
• Disk space on the slave equal to or greater than the master.
• Root access to both instances for the initial setup.
• A low-traffic maintenance window for the initial dump.

Enabling GTID on the master server

GTID (Global Transaction Identifier) dramatically simplifies replication setup and recovery, eliminating the need to manually track the binlog file and position. In the master's my.cnf:

[mysqld]
server-id = 1
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW

Restart the MySQL service after making the change.

Creating the replication user

CREATE USER 'repl_mu'@'%' IDENTIFIED BY 'SenhaForteReplicacao456';
GRANT REPLICATION SLAVE ON *.* TO 'repl_mu'@'%';
FLUSH PRIVILEGES;

Restrict the % to the slave's specific IP whenever possible, reducing this privileged user's attack surface.

Taking the initial database dump

With the master still running, generate a consistent dump including the GTID position:

mysqldump --all-databases --single-transaction --gtid --master-data=2 \
  -u root -p > dump_inicial_mu.sql

The --single-transaction flag avoids prolonged locking on InnoDB tables (the default engine for account tables in most emulators), reducing the impact on the GameServer during the export.

Restoring the dump on the slave

Transfer the file to the slave server and import it:

scp dump_inicial_mu.sql usuario@ip_do_slave:/tmp/
mysql -u root -p < /tmp/dump_inicial_mu.sql

Configuring the slave with GTID

In the slave's my.cnf:

[mysqld]
server-id = 2
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON

Restart the slave's MySQL and set up the connection to the master:

CHANGE MASTER TO
  MASTER_HOST='ip_do_master',
  MASTER_USER='repl_mu',
  MASTER_PASSWORD='SenhaForteReplicacao456',
  MASTER_AUTO_POSITION = 1;
START SLAVE;

Validating that replication works

SHOW SLAVE STATUS\G

Confirm Slave_IO_Running: Yes, Slave_SQL_Running: Yes, and check Seconds_Behind_Master. A useful additional practical test is creating a test character on the GameServer connected to the master and checking, a few seconds later, whether the record shows up on the slave via a direct query.

Table of important configuration parameters

Continuously monitoring replication lag

Set up a simple script that runs every minute and alerts if Seconds_Behind_Master exceeds an acceptable threshold (recommendation: 5-10 seconds for MU servers, where player experience depends on quickly updated data):

#!/bin/bash
LAG=$(mysql -u root -p"senha" -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master | awk '{print $2}')
if [ "$LAG" -gt 10 ]; then
  echo "ALERTA: lag de replicação em ${LAG}s" | ./notify_discord.sh
fi

Using the slave to offload read traffic

Once replication is validated, redirect non-critical read queries to the slave: administrative panel reports, rankings, audit queries, and statistics. Keep all writes and time-critical reads (login, item verification during trades) pointed at the master, since the slave may lag by a few seconds.

Slave promotion procedure in case of master failure

When the master fails, the slave needs to be promoted manually (absent an automated orchestration tool):

1. Confirm the master is really unavailable (not just network slowness).
2. On the slave, run STOP SLAVE; followed by RESET SLAVE ALL; to take it out of replica mode.
3. Disable read_only (SET GLOBAL read_only = OFF;).
4. Repoint the GameServer's and web panel's connection string to the IP of the (now former) slave.
5. Once the old master comes back, reconfigure it as a slave of the new master before reintroducing it — never the other way around.

Common errors and fixes

MySQL Master-Slave replication checklist

• GTID enabled on master and slave (gtid_mode = ON).
• Replication user created with the minimum required permission.
• Initial dump taken with --single-transaction --gtid and restored on the slave.
• SHOW SLAVE STATUS confirming IO and SQL threads are running.
• read_only enabled on the slave to prevent accidental writes.
• Lag monitoring configured with automatic alerts.
• Slave promotion procedure documented and tested.
• Part of the administrative reads redirected to the slave.

With replication running, the next step is integrating this into an overall infrastructure contingency plan, also covering the GameServer and ConnectServer — the MU Online server setup tutorial provides the complete foundation for anyone structuring or reviewing this environment.

Frequently asked questions