Skip to content

# Useful sed / awk liners for MySQL

MySQL Configuration InnoDB mysqldump scripts 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.

#### 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’

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
10
Leave a Reply

10 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
nadavkav

Very useful commands.
Thanks!

Please consider adding them to:
http://www.commandlinefu.com/commands/browse

Author
shlomi

@nadavkav,
thanks, have added some.

Log Buffer #228, A Carnival of the Vanities for DBAs | The Pythian Blog

[…] Shlomi Noach is listing some useful sed / awk liners to use with MySQL. […]

Guest
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 }’

Guest
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: http://partmaps.org/era/unix/award.html.

Guest
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.

Author
shlomi

@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…

Guest
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.

Guest
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?

Guest
J. E. Aneiros

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”; } }

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.