Really Versioned Database Backup...
...Or abusing a version control system

I've always liked to keep my /etc configuration files in a source control system, since it both helps me document changes, as well as quickly roll back to an earlier date.

Then, I implemented some systems, such as web hosting and email configurations to reside inside a MySQL database, not to mention also some TikiWiki? sites.

Now, I wanted to have a versioned backup of the database... something that I could just dial to an arbitrary date, as well as see the changes.

How about mysqldump combined with p4 (Perforce) — Yes, I know subversion, cvs and bitkeeper might be polically more correct — but while I use all of them, I'm still fastest with p4, and it is available for free for up to 2 users and 5 clients.

So, here's a really simple script (use …

Really Versioned Database Backup...
...Or abusing a version control system

I've always liked to keep my /etc configuration files in a source control system, since it both helps me document changes, as well as quickly roll back to an earlier date.

Then, I implemented some systems, such as web hosting and email configurations to reside inside a MySQL database, not to mention also some TikiWiki? sites.

Now, I wanted to have a versioned backup of the database... something that I could just dial to an arbitrary date, as well as see the changes.

How about mysqldump combined with p4 (Perforce) — Yes, I know subversion, cvs and bitkeeper might be polically more correct — but while I use all of them, I'm still fastest with p4, and it is available for free for up to 2 users and 5 clients.

So, here's a really simple script (use …

Really Versioned Database Backup...
...Or abusing a version control system

I've always liked to keep my /etc configuration files in a source control system, since it both helps me document changes, as well as quickly roll back to an earlier date.

Then, I implemented some systems, such as web hosting and email configurations to reside inside a MySQL database, not to mention also some TikiWiki? sites.

Now, I wanted to have a versioned backup of the database... something that I could just dial to an arbitrary date, as well as see the changes.

How about mysqldump combined with p4 (Perforce) — Yes, I know subversion, cvs and bitkeeper might be polically more correct — but while I use all of them, I'm still fastest with p4, and it is available for free for up to 2 users and 5 clients.

…

Try to evaluate this yourself first

I think Kai wins this one, hands down.

I bought a new toy yesterday (and about time I did). A Logitech presentation clicker thingy:

It has the laser pointer, the forward and backwards slide buttons and, arguably most interestingly, a built-in timer with vibrate alert.

What’s annoying is that the forward/back is done by page up and page down - and this doesn’t work for the “appear on click” thing for OO.org. Luckily for me, I just about never use that “feature” as the in version of OOo that Ubuntu ships in their stable release (5.04) is just too darn buggy in that area. I do sometimes wonder if people use the stable release of their product for any real work.

But it’s a nice little device and seems to be an improvement of the using the remote control feature of my phone to do the same thing (if you didn’t do …

SELECT CONCAT(CONV(536,8,20), LPAD(EXPORT_SET(1,'Y','N','',1), 3, "P"), CHAR(32), HEX(234), UNHEX(594939469*POW(3,2)))

Then ask your server if you fail :-)

One of the features that distinguishes MySQL 5 from it's predecessors is the increased ability to control how the server interprets SQL and how strict or relaxed it should behave towards errors. This is done by setting the server SQL mode.

Choosing an appropriate Server SQL Mode can be an important tool to ease the task of porting from database products like Oracle or Microsoft Sequal Server. Despite the terrific documentation on the MySQL Reference manual, I really felt the need to have some kind of quick reference around to make effective use of the server sql modes. So, I made up one myself:



Enjoy!

After implementing feedback from the 0.7 version and fleshing out the documentation some more, I present the 0.8 version of the Sakila sample database. Feedback is always appreciated, especially regarding the documentation and the data itself regarding appropriateness and consistency.

Changes

• Added actor_info view.
• Changed error handler for inventory_held_by_customer function. Function now has an exit handler for NOT FOUND instead of the more cryptic 1329.
• Added template for new BSD license to schema and data files.
• Added READS SQL DATA to the procedures and functions where appropriate to support loading on MySQL 5.1.
• Fixed date range issue in rewards_report procedure (thanks Goplat).

DOWNLOAD

Hi everyone,

I know it's next to nothing, but in case you happen to need it, I've got some XML-related MySQL utilities.

• 
  
• A documented XML Schema of the mysql command line utility xml format
  
• A little windows batch file that exports schema-related metadata in the ordinary mysql command line utility xml format
  

It's all as-is of course but this time, I explicitly included a couple of lines saying that the usage is all under GPL license.

Regarding the information_schema dump, there are some more tools in the works, among which is a xslt stylesheet that …

As demonstrated in Gripe: MySQL 5 - a VIEW could replace a MERGE table, MySQL 5 falls a little short when it comes to using indexes in a VIEW containing UNION. So I've tried PostgreSQL out, and much to my chagrin (being a happy little MySQL developer from way back), Postgres handles indexes in this scenario perfectly. Doh!

Yet ultimately, I'm still up the proverbial creek. What I really want is the PostgreSQL query designer (equiv of MySQL query optimizer) coupled with MySQL's modular storage engine architecture, so FEDERATED tables could be used with a VIEW and a UNION as a form of distributed partitioning. Oh well. Back to the drawing board.