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...

Console

I'm not completely clear on this question. Do you want to dump your MySQL database every so often into a Mercurial repository? – Farnese

to take mysql backup of my online websites, I always login to phpmyadmin on server, writing login pass, etc. than select tables, backup- saveas file, bla bla... very long process. ps, I have websites at different servers, hosts. so I was just thinking, if it is possible to take mysql backups from servers with mercurial somehow, and into mercurial rep. – Console

What advantage is hg bringing here? Wouldn't you just create a backup schedule and timestamp the file. Disk space isn't really a limiting factor these days. – Crespi

It can be a limiting factor depending on the size of database! If you want a daily backup of your database for the last 7 days, you're going to use 7 times the size of one backup, I would imagine that there could be significant savings by using an differences efficiently. – Yoohoo

To BenM, see my answer below, how would a VCS make a meaningful snapshot of the DB? And if you are already backing the db up then there is no advantage. As CurtainDog say's write a cron that backs the DB up then tar and gzip it then rpc it to your local server. All in about 4 lines of a bash script. Alternatively set up a local machine with MySQL and make it a slave of the remote DB ands set up master slave replication then back up the local DB – Lassalle

It's an old thread but I think interesting idea. I did a test, that was commit 92 dump files in mercurial, one by one. The size of dump files sums 1.2GB. The hg repository has 98M!! I compared md5sum from revisions with original dumps and is ok. – Actinotherapy

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.

Yoohoo

By storing snapshots of the data over time you can compare and contrast the state of the database at different points in time. The advantage over storing timestamped backup files would include use of less disk space due to efficiently storing differences, the ability to share the full history of the database through repository cloning. – Yoohoo

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.

Lassalle