Brett Parker wrote:
The mysql user may not have a password, but you could have added an ssh key in, and used the mysql user (assuming that it's got a shell, which you could check with a su - mysql on the remote machine).
Good solution, I'll try that next time.
with the downside that the copied files might not be consistent.
And an inconsistant database is *bad*, but hey.
It's not *good* but whether it is bad depends on what you're doing with the data. *Not knowing* it might be inconsistent would be a bad thing, certainly. For our purposes it allows us to sync databases pretty quickly, without taking the live database offline. If the copy has problems the database can be repaired or resynced, neither cause a problem for what I'm using it for.
So use a dump, a one of dump for testing new servers isn't going to take a lot of time. (unless you're running huge databases, but then, rsync won't be that good for it).
In one case, fairly huge (multi-GB); in others moderate sizes or miniscule. rsync isn't brilliant for huge databases but it's not that bad either, compared with the alternatives. For example I have one database (the multi-GB one) which has data logged to it every minute from sites around the world. Taking it offline (or locking it) to mysqldump it isn't an option. An rsync takes ages even when nothing has changed due to the sheer volume of checksum data it has to pass around, but the database itself runs normally in the meantime. I hate to think how long a dump would take!
It's not, for migrating data you should always use the dump, because you may be migrating data between different architectures and have issues with the on disk format. A dump at least gives you an architecture safe format to restore from (and, in theory at least, a snapshot of the database).
MySQL database files are binary compatible between architectures (I'm sure I'm right on that but I just spent 10mins looking for a reference to prove it and failed...)
I would not recommend rsync as a method for backing up a database for most of the reasons you mention, but for my specific purpose it does serve me well.
We regularly sync databases in the region of 7G, but we're also using postgres,
Ah, you're using a "real" database so we already have different criteria :-)
NB: I noticed just now that MySQL has been bought by Sun. Could be interesting.