Version control techniques for databases
Permalink
Hey all,
My partner and I are trying to come up with the best way to version (using Github) the databases of our site. The most obvious way seems to dump the databases along with pushes, but that seems cumbersome and prone to ugly merge conflicts. We've heard that Concrete5 provides some kind of dashboard function that exports database updates to an xml file, but have had little luck finding it.
Recommendations?
Thanks,
Mark
My partner and I are trying to come up with the best way to version (using Github) the databases of our site. The most obvious way seems to dump the databases along with pushes, but that seems cumbersome and prone to ugly merge conflicts. We've heard that Concrete5 provides some kind of dashboard function that exports database updates to an xml file, but have had little luck finding it.
Recommendations?
Thanks,
Mark
Hi there,
I'm actually going through the same since we recently had an Aussie host company get utterly hacked and lost everything for about 4K customers, thank fully I wasn't one of them. Here's what I'm working on.
1. Creating a dir `__backups` under the web root and added a RewriteRule to hide it from browsers.
2. Created a cron script that does temp database dump (using mysqldump --compact and ignores the Jobs and JobsLogs tables since they change every time you run the scheduled jobs), then does an md5 on it and compares that to the same md5 value of the previous backup.
3. If they've changed it does a full dump to `__backups/automated_backup.sql` and store the checksum (computed in step 2) to `__backups/automated_backup.checksum`.
4. It then runs a hg addremove, hg commit, hg push. This way we only do a push when the database changes, and we should only send text based diffs (hopefully, I'm assuming I know how hg sends diffs).
So far I'm up to step 4.
We're not exposing this in the UI in anyway, since it's purely for me to sync my local environment and to provide a completely separate backup of the site.
I'm not sure if not encrypting it is going to be an issue... given that C5's encryption is based on the password salt I suspect we're ok (since anyone who can get to __backup can probably also get to config/site.php and use the salt to decrypt it), but don't trust me on that one, I'm not an expert.
I'm just about to do the same for the concrete files dir as well but need to see what should be in version control and what shouldn't.
I'm also putting this in a cron job that also runs the scheduled jobs by auto computing the job hash.
Anyway, the bad news is I'm writing the script in ruby (for various reasons, probably none of them good (c:).
Cheers
EDIT:
I'm now ignoring JobsLog, Jobs, Logs, PageStatistics & Users from the checksum. Users gets updated everytime someone logs in as admin... All tables are included in the backup, just changes to these won't trigger one.
I'm also automatically generating a `deny from all` .htaccess file in __backups instead of the rewrite rule
I'm actually going through the same since we recently had an Aussie host company get utterly hacked and lost everything for about 4K customers, thank fully I wasn't one of them. Here's what I'm working on.
1. Creating a dir `__backups` under the web root and added a RewriteRule to hide it from browsers.
2. Created a cron script that does temp database dump (using mysqldump --compact and ignores the Jobs and JobsLogs tables since they change every time you run the scheduled jobs), then does an md5 on it and compares that to the same md5 value of the previous backup.
3. If they've changed it does a full dump to `__backups/automated_backup.sql` and store the checksum (computed in step 2) to `__backups/automated_backup.checksum`.
4. It then runs a hg addremove, hg commit, hg push. This way we only do a push when the database changes, and we should only send text based diffs (hopefully, I'm assuming I know how hg sends diffs).
So far I'm up to step 4.
We're not exposing this in the UI in anyway, since it's purely for me to sync my local environment and to provide a completely separate backup of the site.
I'm not sure if not encrypting it is going to be an issue... given that C5's encryption is based on the password salt I suspect we're ok (since anyone who can get to __backup can probably also get to config/site.php and use the salt to decrypt it), but don't trust me on that one, I'm not an expert.
I'm just about to do the same for the concrete files dir as well but need to see what should be in version control and what shouldn't.
I'm also putting this in a cron job that also runs the scheduled jobs by auto computing the job hash.
Anyway, the bad news is I'm writing the script in ruby (for various reasons, probably none of them good (c:).
Cheers
EDIT:
I'm now ignoring JobsLog, Jobs, Logs, PageStatistics & Users from the checksum. Users gets updated everytime someone logs in as admin... All tables are included in the backup, just changes to these won't trigger one.
I'm also automatically generating a `deny from all` .htaccess file in __backups instead of the rewrite rule
Are you looking for this one:
http://www.concrete5.org/marketplace/addons/database_backup/...
cheers,
Vincent