Backing Up Your Database

Warning

This page is a draft and isn't to be treated as guidance of best practice.

It's also not complete.

Backing up a database where user data is involved is a complicated business and there's no such thing as a one-size-fits-all approach. This warning can be removed once this page has been filled in more completely.

Note

This page needs people with more knowledge to answer these questions:

• TODO: How can atomicity be enforced? i.e., it would be good to know what commands can miss writers that come in after the command starts.
• To ensure atomicity, you must use --single-transaction.https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_single-transaction
• TODO: Does this pause the server?
  • No
  • Preliminary responses indicate it may while some people aren't sure. This needs more clarification/testing.
• @Ripley says "to my knowledge, the world doesn't exhibit any unusual lag during the online backup.. just the typical lag"
• TODO: If done when the server is up, does the game world need to be empty?
• TODO: How long can this take?
  • @Ripley says it takes ~20min to mysqldump shard+auth Coldeve, and it takes the same time whether online or offline.
• TODO: Investigate the "single transaction option". Does it slow down other reads or writes? Does it preserve atomicity? More info at https://stackoverflow.com/questions/41683158/mysqldump-single-transaction-option
• Relevant thread in Discord: https://discord.com/channels/261242462972936192/261242462972936192/1187967838904918097
• Relevant SO post: https://stackoverflow.com/questions/41683158/mysqldump-single-transaction-option

Using MySQL Workbench

1. Open MySQL Workbench

2. Connect to your database

3. In the Server menu, select Data Export

   

4. In the "Administration - Data Export" pane that appears,

   • Under Tables to Export, select ace_auth, ace_shard, and ace_world.
   • Under Export Options, switch to "Export to Self-Contained File"
   • Check "Include Create Schema"
   • Click "Start Export"
   

5. You should see something like the following:

   

This prints the following

18:14:34 Dumping ace_auth (all tables)
Running: mysqldump.exe --defaults-file="C:\Users\User\AppData\Local\Temp\tmph0efqnz9.cnf"  --host=localhost --port=3306 --default-character-set=utf8 --user=root --protocol=tcp --skip-triggers "ace_auth"
18:14:34 Dumping ace_world (all tables)
Running: mysqldump.exe --defaults-file="C:\Users\User\AppData\Local\Temp\tmphsxcn7zm.cnf"  --host=localhost --port=3306 --default-character-set=utf8 --user=root --protocol=tcp --skip-triggers "ace_world"
18:14:37 Dumping ace_shard (all tables)
Running: mysqldump.exe --defaults-file="C:\Users\User\AppData\Local\Temp\tmphi6q_30e.cnf"  --host=localhost --port=3306 --default-character-set=utf8 --user=root --protocol=tcp --skip-triggers "ace_shard"

Command Line

Browse to your wamp/bin/mysql/VERSION/bin folder, then run the following (changing the username (-u) and/or password (-p) as needed:

mysqldump -u root -p ace_auth > ace_auth.sql
mysqldump -u root -p ace_shard > ace_shard.sql

And if you have customized weenies (.sql files) or modified anything in ace_world, also run:

mysqldump -u root -p ace_world > ace_world.sql

How Coldeve does running backups

Notes

• They use --single-transaction in their backup script (via @gmriggs)