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}'
awk ‘/./’
sed ‘/./!d’
grep -v ‘^$’
awk ‘!/^$/’
sed ‘/^$/d’
Very useful commands.
Thanks!
Please consider adding them to:
http://www.commandlinefu.com/commands/browse
@nadavkav,
thanks, have added some.
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 }’
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.