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 use 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.
• TODO: Does this pause the server?
  • 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

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)