{"id":3685,"date":"2011-07-06T08:41:00","date_gmt":"2011-07-06T06:41:00","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3685"},"modified":"2011-07-06T08:41:00","modified_gmt":"2011-07-06T06:41:00","slug":"useful-sed-awk-liners-for-mysql","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/useful-sed-awk-liners-for-mysql","title":{"rendered":"Useful sed \/ awk liners for MySQL"},"content":{"rendered":"<p>Listing some useful <strong>sed<\/strong> \/ <strong>awk<\/strong> liners to use with MySQL. I use these on occasion.<\/p>\n<p><strong>sed<\/strong>, <strong>awk<\/strong> &amp; <strong>grep<\/strong> have many overlapping features. Some simple tasks can be performed by either. For example, stripping empty lines can be performed by either:<\/p>\n<blockquote>\n<pre><strong>grep<\/strong> '.'\r\n<strong>awk<\/strong> '\/.\/'\r\n<strong>sed<\/strong> '\/.\/!d'\r\n<strong>grep<\/strong> -v '^$'\r\n<strong>awk<\/strong> '!\/^$\/'\r\n<strong>sed<\/strong> '\/^$\/d'<\/pre>\n<\/blockquote>\n<p>It&#8217;s a matter of taste &amp; 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.<\/p>\n<h4>mysqldump<\/h4>\n<p>The output of <em>mysqldump<\/em> is in particular useful when one wishes to make transformation on data or metadata.<!--more--><\/p>\n<ul>\n<li>Convert MyISAM tables to InnoDB:<\/li>\n<\/ul>\n<blockquote>\n<pre>mysqldump | sed -e 's\/^) ENGINE=MyISAM\/) ENGINE=InnoDB\/'<\/pre>\n<\/blockquote>\n<p>I&#8217;ve had several occasion when people said this type of conversion assumes no <strong>&#8216;ENGINE=MyISAM&#8217;<\/strong> snippet exists within row data. This is not so. The <strong>&#8216;^) ENGINE=MyISAM\/&#8217;<\/strong> pattern strictly requires that this text is outside row data. No row data begins with a <strong>&#8216;)&#8217;<\/strong>. This is a safe conversion.<\/p>\n<ul>\n<li>Convert InnoDB to InnoDB plugin, compressed tables:<\/li>\n<\/ul>\n<blockquote>\n<pre>mysqldump | sed -e 's\/^) ENGINE=InnoDB\/) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8\/'<\/pre>\n<\/blockquote>\n<ul>\n<li>Slice out a specific database (assumes existence of the <strong>USE<\/strong> statement):<\/li>\n<\/ul>\n<blockquote>\n<pre>sed -n \"\/^USE \\`employees\\`\/,\/^USE \\`\/p\"<\/pre>\n<\/blockquote>\n<ul>\n<li>Slice out a specific table:<\/li>\n<\/ul>\n<blockquote>\n<pre>sed -n \"\/^-- Table structure for table \\`departments\\`\/,\/^-- Table structure for table\/p\"<\/pre>\n<\/blockquote>\n<ul>\n<li>Combine the above two statements to slice a specific table from a specific database:<\/li>\n<\/ul>\n<blockquote>\n<pre>sed -n \"\/^USE \\`employees\\`\/,\/^USE \\`\/p\" | sed -n \"\/^-- Table structure for table \\`departments\\`\/,\/^-- Table structure for table\/p\"<\/pre>\n<\/blockquote>\n<p>See also <a rel=\"bookmark\" href=\"http:\/\/code.openark.org\/blog\/mysql\/on-restoring-a-single-table-from-mysqldump\">On restoring a single table from mysqldump<\/a>.<\/p>\n<h4>my.cnf<\/h4>\n<p>Some <em>my.cnf<\/em> files are just a mess to read. Here&#8217;s some normalizing scripts:<\/p>\n<ul>\n<li>Strip a <em>my.cnf<\/em> file from comments, remove blank lines, normalize spaces:<\/li>\n<\/ul>\n<blockquote>\n<pre>cat my.sandbox.cnf | sed '\/^#\/d' | sed '\/^$\/d' | sed -e 's\/[ \\t]\\+\/\/g'<\/pre>\n<\/blockquote>\n<ul>\n<li>Same, but only present <strong>[mysqld]<\/strong> section parameters:<\/li>\n<\/ul>\n<blockquote>\n<pre>cat my.sandbox.cnf | sed -n '\/^\\[mysqld\\]\/,\/^\\[\/p' | sed '\/^\\[\/d' | sed '\/^#\/d' | sed '\/^$\/d' | sed -e 's\/[ \\t]\\+\/\/g'<\/pre>\n<\/blockquote>\n<ul>\n<li>Only present <strong>[mysqld]<\/strong> section parameters, tab delimited (this is useful in exporting and comparing instance parameters):<\/li>\n<\/ul>\n<blockquote>\n<pre>cat my.sandbox.cnf | sed -n '\/^\\[mysqld\\]\/,\/^\\[\/p' | sed '\/^\\[\/d' | sed '\/^#\/d' | sed '\/^$\/d' | sed -e 's\/[ \\t]\\+\/\/g' | sed -e 's\/=\/\\t\/'<\/pre>\n<\/blockquote>\n<ul>\n<li>Multi-word parameters in <em>my.cnf<\/em> can be written with either hyphens or underscores. <strong>innodb_file_per_<\/strong>table is the same as <strong>innodb-file-per-table<\/strong>, as well as <strong>innodb_file-per_table<\/strong>. The following normalizes the parameter names to using underscores only, keeping from changing values (e.g. <strong>&#8216;mysql-bin&#8217; <\/strong>parameter value should not change). It isn&#8217;t pretty!<\/li>\n<\/ul>\n<blockquote>\n<pre>cat my.sandbox.cnf | awk -F \"=\" 'NF &lt; 2 {print} sub(\"=\", \"=~placeholder~=\") {print}' | awk -F \"=~placeholder~=\" 'NF &lt; 2 {gsub(\"-\", \"_\", $0); print} NF==2 {gsub(\"-\", \"_\", $1); print $1 \"=\" $2}'<\/pre>\n<\/blockquote>\n<div id=\"_mcePaste\" class=\"mcePaste\" style=\"position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow: hidden;\">grep &#8220;.&#8221;<br \/>\nawk &#8216;\/.\/&#8217;<br \/>\nsed &#8216;\/.\/!d&#8217;<br \/>\ngrep -v &#8216;^$&#8217;<br \/>\nawk &#8216;!\/^$\/&#8217;<br \/>\nsed &#8216;\/^$\/d&#8217;<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Listing some useful sed \/ awk liners to use with MySQL. I use these on occasion. sed, awk &amp; grep have many overlapping features. Some simple tasks can be performed by either. For example, stripping empty lines can be performed by either: grep &#8216;.&#8217; awk &#8216;\/.\/&#8217; sed &#8216;\/.\/!d&#8217; grep -v &#8216;^$&#8217; awk &#8216;!\/^$\/&#8217; sed &#8216;\/^$\/d&#8217; [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,14,9,50],"class_list":["post-3685","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-innodb","tag-mysqldump","tag-scripts"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Xr","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3685","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3685"}],"version-history":[{"count":47,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3685\/revisions"}],"predecessor-version":[{"id":3803,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3685\/revisions\/3803"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}