Useful sed / awk liners for MySQL

Listing some useful sed / awk liners to use with MySQL. I use these on occasion.

sed, awk & grep have many overlapping features. Some simple tasks can be performed by either. For example, stripping empty lines can be performed by either:

grep '.'
awk '/./'
sed '/./!d'
grep -v '^$'
awk '!/^$/'
sed '/^$/d'

It’s a matter of taste & convention which tool and variation to use. So for any script I suggest, there may be many variations, possibly cleaner, shorter; feel free to comment.

mysqldump

The output of mysqldump is in particular useful when one wishes to make transformation on data or metadata.

  • Convert MyISAM tables to InnoDB:
mysqldump | sed -e 's/^) ENGINE=MyISAM/) ENGINE=InnoDB/'

I’ve had several occasion when people said this type of conversion assumes no ‘ENGINE=MyISAM’ snippet exists within row data. This is not so. The ‘^) ENGINE=MyISAM/’ pattern strictly requires that this text is outside row data. No row data begins with a ‘)’. This is a safe conversion.

  • Convert InnoDB to InnoDB plugin, compressed tables:
mysqldump | sed -e 's/^) ENGINE=InnoDB/) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8/'
  • Slice out a specific database (assumes existence of the USE statement):
sed -n "/^USE \`employees\`/,/^USE \`/p"
  • Slice out a specific table:
sed -n "/^-- Table structure for table \`departments\`/,/^-- Table structure for table/p"
  • Combine the above two statements to slice a specific table from a specific database:
sed -n "/^USE \`employees\`/,/^USE \`/p" | sed -n "/^-- Table structure for table \`departments\`/,/^-- Table structure for table/p"

See also On restoring a single table from mysqldump.

my.cnf

Some my.cnf files are just a mess to read. Here’s some normalizing scripts:

  • Strip a my.cnf file from comments, remove blank lines, normalize spaces:
cat my.sandbox.cnf | sed '/^#/d' | sed '/^$/d' | sed -e 's/[ \t]\+//g'
  • Same, but only present [mysqld] section parameters:
cat my.sandbox.cnf | sed -n '/^\[mysqld\]/,/^\[/p' | sed '/^\[/d' | sed '/^#/d' | sed '/^$/d' | sed -e 's/[ \t]\+//g'
  • Only present [mysqld] section parameters, tab delimited (this is useful in exporting and comparing instance parameters):
cat my.sandbox.cnf | sed -n '/^\[mysqld\]/,/^\[/p' | sed '/^\[/d' | sed '/^#/d' | sed '/^$/d' | sed -e 's/[ \t]\+//g' | sed -e 's/=/\t/'
  • Multi-word parameters in my.cnf can be written with either hyphens or underscores. innodb_file_per_table is the same as innodb-file-per-table, as well as innodb_file-per_table. The following normalizes the parameter names to using underscores only, keeping from changing values (e.g. ‘mysql-bin’ parameter value should not change). It isn’t pretty!
cat my.sandbox.cnf | awk -F "=" 'NF < 2 {print} sub("=", "=~placeholder~=") {print}' | awk -F "=~placeholder~=" 'NF < 2 {gsub("-", "_", $0); print} NF==2 {gsub("-", "_", $1); print $1 "=" $2}'
grep “.”
awk ‘/./’
sed ‘/./!d’
grep -v ‘^$’
awk ‘!/^$/’
sed ‘/^$/d’

10 thoughts on “Useful sed / awk liners for MySQL

  1. Now I see why the use of cat and pipes, it could be rewrite as

    sed ‘/^#/d; /^$/d; s/[ \t]\+/ /g’ /etc/mysql/my.cnf

    or even

    sed ‘/^#|^$/d; s/[ \t]\+/ /g’ /etc/mysql/my.cnf.

  2. @Aneiros,

    Wow, it’s been years since my last visit to Useless Use of Cat. Thanks!

    Yes, technically the ‘cat’ is wasteful; but I actually like it the way it is: that way all the ‘sed’ invocations are more generic; they’re like real filters, whereas if the first ‘sed’ takes an input file, it’s less generic.
    It’s a programmatic point of view; though I will not argue ove any contradicting opinion…

  3. I think the last script is not correct: it will changes comment lines in the form of –help to __help. Let see if I can found a different way to do it.

  4. This is almost a solution:

    awk ‘/[^=]+\s*=\s*[^=]+/ { gsub(“-“, “_”, $1); print; next } { print }’ /etc/mysql/my.cnf

    Still failing in the case of a comment line like

    # ssl-ca=/etc/mysql/cacert.pem

    Pretty cool the power of RE, eh?

  5. Only with Perl but it’s too long:

    #!/usr/bin/perl

    while () {
    chomp;
    if (/[^=# \t]+\s*=/) {
    $l = $`;
    $m = $&;
    $r = $’;
    $m =~ s/-/_/g;
    print “$l$m$r\n”;
    } else {
    print “$_\n”;
    }
    }

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.