Useful sed / awk liners for MySQL

July 6, 2011

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.


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:
  • 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.


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'
  • Very useful commands.

    Please consider adding them to:

  • @nadavkav,
    thanks, have added some.

  • Pingback: Log Buffer #228, A Carnival of the Vanities for DBAs | The Pythian Blog()

  • varokism

    tx shlomi ,

    I found this one in my mailbox to found out updates in the slow log

    cat dumslow.log | grep -i -n1 "update" | awk '{ print $2 }' | grep -v "update" | awk '{ toto += $1 } END { print toto }'

  • J. E. Aneiros

    Hi, thanks for the examples by I noticed that some of them could be rewrite without the useless cat command, avoiding Useless Use Of Cat:

  • J. E. Aneiros

    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.

  • @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...

  • J. E. Aneiros

    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.

  • J. E. Aneiros

    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?

  • J. E. Aneiros

    Only with Perl but it's too long:


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

Powered by Wordpress and MySQL. Theme by