Export a single row from a database as MySQL

Posted on 11 July 2011 in
mysql tip programming

Nathan Rambeck has a great simple snippet of how to export a single row from a table however it didn't QUITE do what I needed it to. I needed to export a variable from {variable} as I was working on an update script which pulled data from a variable and into a database schema. I got fed up of re-populating the variable data each time.

Here is my version of Nathan's suggestion:

<pre language=”bash> mysqldump -uUSER -pPASSWORD –compact –no-create-info –where=”COLUMN_NAME=’FILTER_VALUE’” DB_NAME TABLE_NAME </pre>

Values in capitals are variables which you should alter with your own values, for example:

mysqldump -udrupal -pdrupal --compact --no-create-info --where="name='theme_settings'" drupal_d6 variable

This produced a line like this:

INSERT INTO `variable` VALUES ('theme_settings','a:1:{s:21:\"toggle_node_info_page\";b:0;}');

The --compact produces a less verbose output (it doesn't drop or lock the table and skips comments). The --no-create-info stops the dump including SQL to recreate the table.