0

Maybe I'm wrong, but I was sure that some time ago when I was looking for a way to sync my MySQL databases using Dropbox, I was told that this was not possible because of the way MySQL works.

But I just recently came across this answer, which seems to indicate that one can sync a MySQL database on Dropbox, simply by using symlinks. It seems too easy to be true, given than I thought I had been advised against it before.

So my first question is, does the answer linked above apply to my situation? In my situation, I only want to sync some MySQL databases across two computers, my desktop and laptop, and I will be the only one accessing them. The databases are for locally tested websites.

My next question is, what happens if my laptop explodes, and I get a new one? Can I just symlink to the databases on Dropbox and be on my way? Does this method work as an effective backup tool? If for some reason I wanted to add a third computer, could I just symlink it and go?

Community
  • 1
Questioner
  • 6,839

1 Answers1

2

Sorry... It's not that easy.

What you have to understand to see why this doesn't work is that when MySQL Server is running, it assumes (with some limited exceptions that aren't important, here) that nothing else will be tinkering with its files, and that it maintains some internal concepts, in memory, about the state of the files, that it assumes can't become inconsistent with the files. If the files were to become inconsistent, through some kind of synching, then you'd have what was essentially just a corrupt database on your hands.

When MySQL Server shuts down cleanly, it leaves the files in an appropriate state that itself -- or another copy of MySQL Server with a sufficiently-identical configuration -- can open the files and start running... but after a graceful server shutdown is the only time that this is true.

The only way to synchronize two instances of MySQL Server like that is to copy all of the files, in one direction only, with both servers in a stopped state.

The same caveat applies to MariaDB and Percona Server.

Michael - sqlbot
  • 375
  • Thank you for your explanation. If I may, one further question: what does the person in the other answer achieve by syncing to Dropbox in the way they explained? Is it just a backup? – Questioner Sep 01 '14 at 03:17
  • What they are most likely achieving is a false sense of security. Yes, I suspect a backup is the intended outcome, but unless the server has very low traffic and Dropbox's algorithm is very aggressive, it's unlikely that the backups will be viable. Even then, the server, during startup from such a backup, is likely to need some time to recover from what it sees as evidence (in the files) of a crash occurring... best case. – Michael - sqlbot Sep 01 '14 at 12:38
  • Okay. Thank you for the additional explanation. – Questioner Sep 01 '14 at 15:05