Mysql Backup with Mercurial

Question

is it possible to take ? I researched at Google, I found few articles, but in German and other langs, so didnt understand well.

It would be great if we could get mysql back-up from server to localhost with Mercurial [at localhost]. maybe with Remote Mysql Connection, etc. do you know any way of doing this? is it possible?

Thanks!! Regards...

Answer 1

Presuming you want to store a periodic backup in a version control repository there are three steps:

1. Setup the mercurial repository where you want to store the database snapshots.

   mkdir db-backup
   hg init db-backup
   

2. Get the database in a file format. The simplest way is via mysqldump. Just backing up the raw database table files won't work as they may be in an inconsistent state.

   cd db-backup
   mysqldump -u username -p -h dbhost databasename > databasename.sql
   

3. Commit the database dump into the version control repository, in your case mercurial.

   hg commit -A -m "committing database snapshot as at `date`"
   

The last 2 steps are what you'll probably want to automate.

Answer 2

I think this would be a pointless and dangerous exercise on a number of levels. But if you think about how a VCS system works, it makes the diff between the current version and the previous (or the benchmark) version and then if you revert to a previous revision it (the VCS) writes out the files to the directory. In the first instance if you did this with a database and then did a diff what would you see? The view you get of the data in a database is filtered through the DBMS so diffing raw files would be pointless. In the second instance if you restored a revision to a working database i don't think you would get much except a trashed database. Also what would happen to views, stored procedures, triggers etc.?

The only time i considered anything like this was to dump the database structure only, no data, and VCS it so i could diff to see what structural changes had been made. However ramping up the communications in the team solved this problem.

THe way to deal with databases is to use a proper set of backup programmes and procedures, not forgetting a set of restore programmes and procedures and a test regime to make sure your backups are all working.