Consider: rsync -avz user@host:/var/lib/mysql /var/lib/mysql
Assuming the files I want to transfer are owned by mysql (both locally and remotely), and are not world-readable, how do I achieve this?
I can use sudo at the local end: sudo rsync -avz user@host:/var/lib/mysql /var/lib/mysql .. to give me the necessary write access to /var/lib/locally, but how do I give rsync read access to the files at the remote host? The mysql user does not have login rights and therefore does not have a password, so I can't just use mysql@host, and there is no root user at the remote host either; if I wanted read access at that end I'd use sudo to get it.
Local end is Ubuntu, remote host is FC4, if that's relevant.
On Wed, 16 January, 2008 5:49 pm, Mark Rogers wrote:
Consider: rsync -avz user@host:/var/lib/mysql /var/lib/mysql
Assuming the files I want to transfer are owned by mysql (both locally and remotely), and are not world-readable, how do I achieve this?
I can use sudo at the local end: sudo rsync -avz user@host:/var/lib/mysql /var/lib/mysql .. to give me the necessary write access to /var/lib/locally, but how do I give rsync read access to the files at the remote host? The mysql user does not have login rights and therefore does not have a password, so I can't just use mysql@host, and there is no root user at the remote host either; if I wanted read access at that end I'd use sudo to get it.
Local end is Ubuntu, remote host is FC4, if that's relevant.
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.
Cheers.
-Mark
----------------------------------------------------------- This message may contain confidential and/or privileged information. This information is intended to be read only by the individual or entity to whom it is addressed. If you are not the intended recipient, you are on notice that any review, disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error, please notify the sender immediately and delete or destroy any copy of this message.
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!
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.
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.
-
Brett Parker -
Mark Ridley -
Mark Rogers