How can I cleanly output current mysql variables to a my.cnf?

Question

I'm trying to run the following command to generate a my.cnf:

{
  # Print version, user, host and time
  echo -e "# MYSQL VARIABLES {{{1\n##\n# MYSQL `
      mysql -V | sed 's,^.*\(V.*\)\, for.*,\1,'
      ` - By: `logname`@`hostname -f` on `date +%c`\n##"
  for l in {a..z}; do
      # Get mysql global variables starting with $l
      echo '#'; mysql -NBe "SHOW GLOBAL VARIABLES LIKE '${l}%'" |
      # Transorm it
      sed 's,\t,^= ,' |
      column -ts^ |
      tr "\n" '@' |
      eval $(echo "sed '" "s,@\("{a..u}{a..z}"\),\n\n\1,;" "'") |
      eval $(echo "sed '" "s,@\(innodb_"{a..z}{a..z}"\),\n\n\1,;" "'") |
      tr '@' "\n" |
      sed 's,^,# ,g'
  done
  echo -e "#\n##\n# MYSQL VARIABLES }}}1";
} | tee ~/mysql-variables.log

(original source for command: http://www.askapache.com/mysql/view-mysql-variables-my-cnf.html )

However sed keeps throwing an unterminated substitute pattern error:

sed: 3: " s,@\(aa\),\n\n\1,; s,@ ...": unterminated substitute pattern
sed: 2: " s,@\(innodb_aa\),\n\n\ ...": unterminated substitute pattern
#
sed: 3: " s,@\(aa\),\n\n\1,; s,@ ...": unterminated substitute pattern
sed: 2: " s,@\(innodb_aa\),\n\n\ ...": unterminated substitute pattern
#

How can I cleanly output my current mysql variables?

Since the issue seems to be with the regex only, I would extract just this part of your command if I were you. Your command line is not human readable, I am afraid. Divide and conquer. — RandomSeed, Jul 20 '14 at 15:02
The whole command works for me. Did you just run it from terminal, or launch it from something else (I'm thinking to something which could interpret quotes or `\n` before bash handles them)? Also, does `mysql -NBe "SHOW GLOBAL VARIABLES"` work as expected? — Qeole, Jul 20 '14 at 17:20
-_-" Well, if you look at post revisions history you'll see Evan hasn't done much on this one. I'll take those thanks for me if you don't mind :) — Qeole, Jul 20 '14 at 17:37
Ha. Thanks @Qeole!. And yes, I'm running it directly in terminal. And yes, `mysql -NBe "SHOW GLOBAL VARIABLES"` works as expected. Any idea how to handle the nested quotes better? — Ryan, Jul 20 '14 at 17:39
I get the same error either way (with or without the brackets). — Ryan, Jul 20 '14 at 17:44
I'm sorry I can't reproduce your `sed` error :/ Oh just to be sure, you're using bash, aren't you? — Qeole, Jul 20 '14 at 17:50
I ran the original single line without {} brackets and received: -bash: syntax error near unexpected token `|' — Ryan, Jul 20 '14 at 17:54
Posted a new question to focus on nested quotes within the sed regex here: http://stackoverflow.com/questions/24853398/how-can-i-reorganize-nested-quotes-within-sed-regex-in-a-bash-script-that-trigge Will report back here if I find anything helpful. — Ryan, Jul 20 '14 at 18:08

score 3 · Accepted Answer · edited May 23 '17 at 12:05

As discussed in this other post, FreeBSD sed seems to have a limit on the size of individual lines in a script, which is exceeded by the {a..u}{a..z} bash expansion.

Here is a sed script that should have exactly the same effect as the combined two eval … lines (and their surrounding tr commands):

sed 'N;P;s/^innodb_//;tl;/.*\ninnodb_/{x;p;x;D};:l /^\(.\)\(.\).*\n\(innodb_\)\?\1\2/D;{x;p;x;D}' | …

Edit:
Corrected version by mklement0, using FreeBSD sed syntax (see comment below):

sed 'N;P;s/^innodb_//;tl'$'\n''/.*\ninnodb_/{x;p;x;D;};:l'$'\n''/^\(.\)\(.\).*\n\(in‌nodb_\)\?\1\2/D;{x;p;x;D;}' | …
# Changes:              ^^^^^^^                     ^    ^^^^^^^                                            ^

(Should replace all of this:)

  tr "\n" '@' |
  eval $(echo "sed '" "s,@\("{a..u}{a..z}"\),\n\n\1,;" "'") |
  eval $(echo "sed '" "s,@\(innodb_"{a..z}{a..z}"\),\n\n\1,;" "'") |
  tr '@' "\n" |

Now with details: what we want to obtain with those two lines are kind of stanzas, the first one containing all lines starting with aa, the second one lines starting with ab, etc.

Bash expansion in OP's script should result in something like

sed ' s,@\(aa\),\n\n\1,;
      s,@\(ab\),\n\n\1,;
      ...
      s,@\(uz\),\n\n\1,;' | # without line breaks

which would then be evaluated. To get stanzas we have to exectute each subcommand exactly once (and in this order, as input is sorted alphabetically), so using

sed 's/@\([a-u][a-z]\)/\n\n\1/' | ...

instead is not a solution.

Back to my script:

sed 'N;                              # Append next input line to pattern space
                                     # Pattern space is "input1 \n input2"

     P;                              # Print first line of pattern space

     s/^innodb_//;                   # Delete (already printed) suffix if present

         tl;                         # If substitution succeeded goto label l

         /.*\ninnodb_/{x;p;x;D};     # Else: if we\'re on first ^innodb_ line,
                                     # then print a new line before (see below)

  :l /^\(.\)\(.\).*\n\(innodb_\)\?\1\2/D;   # If the two lines in pattern space
                                     # begins with the same two chars, remove 1st
                                     # line and go on with next input line

     x;                              # We have to print a blank line: swap space
                                     # and hold patterns

     p;                              # Print space pattern (now empty), i.e.
                                     # print a new line

     x;                              # Swap again patterns

     D                               # Delete "input1 \n" from space pattern and
                                     # loop 
  ' | ...

Unless I'm mistaken, it should contain no GNU extension.

+1 (a little mind-bending, though, as is typical of line-spanning `sed` solutions), but note that while you're not using GNU sed _extensions_, you're using GNU sed _syntax_ - _FreeBSD/OSX sed has stricter syntax requirements_. You'd have to use the somewhat cumbersome `sed 'N;P;s/^innodb_//;tl'$'\n''/.*\ninnodb_/{x;p;x;D;};:l'$'\n''/^$.$$.$.*\n$innodb_$\?\1\2/D;{x;p;x;D;}'` - the spliced-in newlines are crucial, as are the `;` instances before `}`. For a summary of differences between GNU sed and FreeBSD sed, see http://stackoverflow.com/a/24276470/45375. — mklement0, Jul 21 '14 at 01:41
Thank you @mklement0. I wasn't aware of syntax differences between `sed` versions, it's good to know. I'm gonna read the post you link. — Qeole, Jul 21 '14 at 10:23
Thanks @Qeole, your extensive write up gets me right up to the finish line. The only thing left is adding the = sign between variables and associated values. I added another escape to handle the error where t's were replaced (`\\t`), but I still can't get the `=` to show up. Can you help me get all the way through this? Thanks in advance. — Ryan, Jul 21 '14 at 17:26
@Ryan in your `sed 's,\t,^= ,' |` command, try replacing `\t` by either 1) a litteral tabulation character (just press `Tab`, so that it looks like this: `sed 's, ,^= ,' |`, or 2) an ANSI C-quoted string `$'\t'` (this way: `sed 's,'$'\t'',^= ,' |`). If you haven't already, you should have a look at post linked by mklement0, it's very interesting. — Qeole, Jul 21 '14 at 21:53

How can I cleanly output current mysql variables to a my.cnf?

1 Answers1

Linked