Backups

Backups & maintenance

Your players' accounts live in MySQL. If the disk dies or a bad query wipes a table and you have no backup, it's gone. This page covers keeping the data safe — it's about MySQL administration, not omp-MySQL specifically, but every server owner needs it.

Back up with mysqldump (the basics)

mysqldump writes your whole database to a single .sql text file you can restore later.

# back up one database to a file
mysqldump -u root -p --single-transaction --routines mydb > mydb-backup.sql

# restore it (into an existing, empty database)
mysql -u root -p mydb < mydb-backup.sql

• --single-transaction takes a consistent snapshot without locking players out (works for InnoDB, the default engine).
• --routines includes stored procedures/functions if you have any.

Compress big dumps:

mysqldump -u root -p --single-transaction mydb | gzip > mydb-$(date +%F).sql.gz

Automate it (do this!)

A backup you have to remember to run is a backup you won't have. Schedule it.

Linux (cron) — daily at 4am, keep 14 days:

# crontab -e
0 4 * * * mysqldump -u backup -pYOURPASS --single-transaction mydb | gzip > /backups/mydb-$(date +\%F).sql.gz && find /backups -name 'mydb-*.sql.gz' -mtime +14 -delete

Docker MySQL:

docker exec mysql mysqldump -uroot -prootpw --single-transaction mydb | gzip > mydb-$(date +%F).sql.gz

Store backups off the server too (another disk, another machine, cloud). A backup on the same disk that dies is no backup.

Test your restores

Untested backups fail when you need them. Every so often, restore your latest dump into a throwaway database and check it's intact:

mysql -u root -p -e "CREATE DATABASE restore_test"
gunzip < mydb-2025-01-01.sql.gz | mysql -u root -p restore_test
mysql -u root -p restore_test -e "SELECT COUNT(*) FROM accounts"

Before risky changes

About to run an ALTER TABLE, a mass UPDATE/DELETE, or a schema migration? Take a fresh dump first. And always write destructive statements with a WHERE:

-- terrifying:  UPDATE accounts SET money = 0;          (every player!)
-- correct:     UPDATE accounts SET money = 0 WHERE id = 5;

Routine maintenance

• Keep MySQL updated within your major version for security fixes (8.4.x → 8.4.y).
• Monitor disk space — a full disk corrupts databases and stops writes.
• Check the error log (mysqld.err / journalctl -u mysql) occasionally.
• For omp-MySQL specifically, watch mysql_pending_count and the [omp-MySQL] debug log if queries seem slow (see Performance).

Migrating to a new server

To move your database to another machine: mysqldump on the old, copy the file, mysql < file on the new, recreate the DB user (with REQUIRE SSL), point omp-MySQL's host at the new server. See Configuration.

Next: Security · Performance