1

I have an apache server (machine #1) holding a database which I want to rsync distantly (the server is actually owncloud, for those who know). My data is maintained by the server and its rights are rwx------ www-data:www-data or so.

Now I have another distant machine (machine #2) which I would like to use in order to backup the first machine's database. Machine #2 will run rsync over ssh and via a cron job. I am not sure which account to use in order to log to machine #1 via ssh.

  • I understand the only 2 accounts which can read/copy the database on machine #1 are www-data and root. As I cannot login as www-data (this is a system account and I do not know the password), does that mean I need to login as root via ssh?
  • Is that possible? Is that safe?
  • Another way would be to create another account, add it to the www-data group and give the www-data group read rights.
  • Unfortunately this is not possible as it is the server (owncloud) and not me which gives the access rights to the database, and the rights given do not allow the www-data group to read the files (at least not on all files): see rwx------

I am not sure how to get the accounts and rights organized so as to be able to rsync distantly. Any idea?

Thanks,

Antoine.

Improve this question
5
  • If this is a proper database server like MySQL or PostgreSQL, you should use its inbuilt sync services and avoid permission problems with rsync altogether, as well as a host of other potential problems. Or can you only get to these machines through ssh?
    – Jos
    Aug 4, 2014 at 13:11
  • Hi Jos, in the current situation I access both machines via SSH only. I am not sure using the inbuilt sync services is an option to me (but I am not a MySQL expert...).
    – arennuit
    Aug 4, 2014 at 14:41
  • OK. Some questions: do you really need a fully operational database server at machine #2, or just a place to send your backups to (which would be easier)? Does database #2 receive updates independently from #1? Can you access the database on machine #1 directly from a local mysql client? Is the MySQL port (default 3306) open on that machine?
    – Jos
    Aug 4, 2014 at 15:39
  • Jos, here are the answers to your questions. No need to backup the full database on machine #2, only the folders and files kept by owncloud (the database sync is a "nice to have", not a "must"). The updates on #2 are timely independent from the ones on #1: this is because #1 is updated any time during the day (I cannot enforce this, the users decide when they like to modify the files on #1) but #2 is only updated once a day at night (via a cron job). I can access the db on #1 from a local client and I can open the MySQL 3306 on machine #1 if needed.
    – arennuit
    Aug 4, 2014 at 20:21
  • Thank you. I collected all my recommendations into the answer below.
    – Jos
    Aug 4, 2014 at 20:55

1 Answer 1

Reset to default
0

I would run mysqldump periodically on machine #1; this gives you one large file. This file can then be transferred to machine #2 using rsync (with compression, as it will contain only ASCII text, SQL statements really) without bothering about database permissions. On machine #2, the dump file is unreadable for anyone but the owner. And as your database on #1 can be reached from a local machine, you can even do the mysqldump locally, which is useful for testing the procedure.

If anything goes wrong on machine #1, you need to reload the database from the last good dump file. Basically, that is a one-line command that erases the whole database and loads it from the dump file. Backup and restore procedures are well documented and described e.g. here and more tips and tricks can be found by googling mysqldump.

Using this standard tool will be much more safe and straightforward than trying to transfer the data files yourself.

Improve this answer
3
  • Hum, this is an interesting approach indeed... Though the data pointed to by my database is 100's of Gb large. If I have to transfer this on a daily or weekly basis, I am in big trouble. The idea of rsyncing the db directly was to only transfer the updated files, which are much lighter. From what I understand mysqldump is not able to handle such a case, or is it?
    – arennuit
    Aug 5, 2014 at 9:24
  • In that case, no, mysqldump is not the right tool. It can't do incremental dumps. I see two options: 1) shutting down the server #1 and sending the data directory through rsync to machine #2 (to be stored as files, not as a live database); or 2) making a proper incremental backup on machine #1 with mysqlbackup and sending that over to the other end. You need either to stop the server or use the built-in backup facilities, in order to prevent inconsistencies. Keep in mind though that you can only make so many incremental backups before you need a full backup.
    – Jos
    Aug 5, 2014 at 10:00
  • Hi Jos, thanks for your clear answers, they really help! I will probably use a mix solution #2 and #1 (daily incremental updates and monthly full backup). Thanks!
    – arennuit
    Aug 5, 2014 at 13:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .