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’
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…
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.
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?
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”;
}
}