On 16 Jan 18:50, Mark Rogers wrote:
Actually I "solved" the immediate problem: When I wrote that the remote server was FC4 it occurred to me that it probably had root logins enabled, which it did, and thus root@host:... was all I needed. I'd still like to know the solution to the question posed though.
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).
As to whether a mysqldump is better: if the database isn't running there's not a lot to choose, AIUI. If the database is running and can't be locked for the duration of the dump then rsync has the advantage of not causing problems for the live server, with the downside that the copied files might not be consistent.
And an inconsistant database is *bad*, but hey.
In my situation I'm trying to migrate some code from one server to another, so what I tend to do is rsync the databases initially and get everything working (bearing in mind that the live databases are still in use and therefore changing, so I just want a representative database to work with at this point); then when I've resolved any code issues arising from missing libraries, PHP version differences, etc, I can shutdown the live database and repeat the rsync, usually with fairly low live database downtime (in any case from this point on the old database will not be "live" anyway). For that matter I can also use your dump method at this point too.
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).
Note that I have a lot experience of MySQL but no training so the above works for me and I believe it to be "safe" but I could well be wrong!
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).
And finally.... this would only solve the problem for MySQL databases, not other files which I might also want to rsync (or scp) which are not readable by normal users.
We regularly sync databases in the region of 7G, but we're also using postgres, and the file store that goes with that is in the region of 14G for the smallest part we can get away with. Our backups are "somewhat" larger than that (with a .tar.gz of the full filesystem data being in the region of 23G at the moment, we sync just the 14G to the machines that need it).
ssh keys are most likely to be the best way to do this, they're not exactly difficult to set up, as long as you trust both end points to not be compromised.