Mysql Backup with Mercurial

Question

Hi friends,

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

I don't see why one would take backups with Mercurial... it makes no sense. If port 3306 is open on the server, just type mysqldump -u USER -h HOST -p YOUR_DATABASE > backup.sql in a terminal to get a remote backup. You'll need to type in your database password.

If port 3306 is not open (which it probably shouldn't be) you could instead take your backup locally on the server and then send it to a secure location over scp, rsync, ftp etc

Answer 2

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 3

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.