Dump A MySQL Database To YAML Using PHP

The Problem

I was recently working on a project that is built on WordPress / BuddyPress. I built up some content using the CMS features of WordPress in my development server and then I transfered the WordPress content to a beta server using the export/import functionality. That kind of stinks having to do that every time I want to push updates to my beta server. So I looked into using Doctrine to generate data fixtures which I could repeatedly run against the different database. Doctrine supports YAML files, which are extremely easy to edit by hand, so it seemed like a good idea.

So what’s the problem? Well, I didn’t currently have any of my data in YAML files. I didn’t want to had edit them or do some sort of copy and paste madness. So, I wrote a script in PHP to dump my data into a YAML file.

The Solution

I’m not going to do much explanation because the code is fairly well commented. You can download the file here or you could just copy and paste it from below, but you may get some weird character conversions. The script can generate YAML for all the tables in your database, for a specific list of tables or you can specify an SQL query. Just execute the script without any arguments to see the usage statement (or just read it at the top of the file). Without further ado, here’s the script.

  • Nifty. I was just wondering why you chose to use array_shift($argv) rather than getopt()? In this case it would be getopt(‘h:u:p:d:t:q:n’,array(‘qn:’)); (Note the longopts array for the two-letter option qn, which, prior to PHP 5.3.0 is only available on a few systems.) Personally, when I am doing command-line scripts in PHP I tend to attempt to avoid longopts, and provide a help option (always ‘?’, sometimes ‘h’ as well if it is not already in use.)

  • I went with array_shift because, well I’ve done it that way in the past. No other reason, but I like the getopt() way. I may change it up to use that.

    One other note, the above doesn’t produce valid YAML for literals… oops. I’ve since fixed that and will probably post an updated version. Thanks!

  • Pingback: Easily port an existing database to Rails | Constant.co.za()

  • Used it for a quick import into a Django project, thanks!

  • Paula Andrea

    Only a suggestion… The file should be generate a yml codification in UTF-8

    • Nice point! I’ll try to update the code above to reflect that. Thanks for the feedback. 🙂