Mark Ridley wrote:
Are you intending to backup an entire database? If so, why not dump, like this:
/usr/bin/mysqldump -all-databases -u root -ppassword > mydump.sql
I appreciate that doesn't fix your rsync permissions problem, but I was under the impression that's not a good way to backup MySQL.
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.
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.
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.
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!
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.
Thanks for the suggestion though!