So mysqldump can’t be used to dump only certain columns ?

One of those things was backup of only certain columns of a table. Why would you want to do that ? One reason would be creating read only tables with limited data so that you don’t hurt too much if they get hacked into. So I was about to add a mysqldump cronjob, browsing through the manual page of the tool, when it dawned on me : this was not built-in.

Google didn’t help me much : the best solution seemed to select into file, which is not as good as it can’t get data remotely, adds IOs to my otherwise busy databases, and, more importantly, gets me a new format of backups to handle. There is no way I’m going to cope with that, so behold, a dirty trick to make it happen with mysqldump :

COLUMNS=`mysql -N -u$USERNAME -p$PASSWORD $DBNAME -e $TABLE | \
grep -v -- '----' | awk '{print $1}' | sed -e 's/column1|column2/NULL/' | \
xargs echo | sed -e 's/ /,/g'` \
mysqldump -u$USERNAME -p$PASSWORD $DBNAME $TABLE \
-w "0=1 union select $COLUMNS from $TABLE" > /tmp/dump.sql

The shell part is ugly, but this is nothing compared to using SQL injection on your own command line tools. Oh and if somebody has a clue on how to format that kind of things in WordPress, I’m interested.

Next time I’m going to bitch about people explaining how to implement algorithms in useless languages, and failing miserably at implementing them.

Advertisements

4 thoughts on “So mysqldump can’t be used to dump only certain columns ?

  1. Hi,

    Thanks for the post. I am newbie to MySQL via commandline. If you could explain the script that you have posted here, it would be helpful for me to understand and then place the column names/table/dbnames appropriately.
    Many thanks in advance.

    Thanks,
    Yogalakshmi.

    • You can just use the second line, with something that looks like:

      mysqldump -u$USERNAME -p$PASSWORD $DBNAME $TABLE \
      -w "0=1 union select col1, col2, col3 from $TABLE" > /tmp/dump.sql

      This will produce a dump containing only columns col1, col2 and col3.

  2. Hi,

    Thanks for the info.

    I’m trying to follow what you suggest, but keep hitting this error:

    The used SELECT statements have a different number of columns (1222)

    Is this still possible in 2017 and MySQL 5.7?

    It kind of makes sense to me, since I’m running a union statement with only a few fields against a full list of fields from the existent table.

    1 way that this works for me is by creating a temp table with the fields I want and then dumping that new table, but that needs extra space in the OS and I don’t have it. The table I’m trying to dump this way is huge.

    Thanks,
    John.

    • That should still work. Maybe that script isn’t working anymore, but the trick is to select NULL instead of the columns you need to avoid dumping.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s