如何将当前的mysql变量干净地输出到my.cnf? [英] How can I cleanly output current mysql variables to a my.cnf?

问题描述

我正在尝试运行以下命令来生成my.cnf:

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

(命令的原始来源: http://www. askapache.com/mysql/view-mysql-variables-my-cnf.html )

但是sed不断抛出unterminated substitute pattern错误:

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
#

如何整洁地输出当前的mysql变量?

How can I cleanly output my current mysql variables?

推荐答案

如此其他文章 中所述，FreeBSD sed似乎有一个限制脚本中每行的大小，而{a..u}{a..z} bash扩展超出了该大小.

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.

这是一个 sed脚本，其效果应与合并的两个eval …行(及其周围的tr命令)完全一样效果:

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}' | …

---

修改:
mklement0使用 FreeBSD sed语法纠正了版本(请参见下面的评论):

---

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:              ^^^^^^^                     ^    ^^^^^^^                                            ^

---

(应替换所有这些:)

  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" |

现在有详细信息:我们希望通过这两行获得的是 节 ，第一个包含所有以开头的行aa，第二行以ab开头，等等.

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扩展应该会导致类似的情况

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/' | ...

相反，不是一种解决方案.

返回我的脚本:

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

除非我弄错了，否则它不应包含GNU扩展名.

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

这篇关于如何将当前的mysql变量干净地输出到my.cnf?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！