15

We lost our my.cnf file and were wondering if there is a way to export a copy from the currently running mysql instance.

Thanks!

4 Answers 4

20

Three(3) Options

OPTION 1 : From within mysql client

mysql -uroot -A -e"SHOW GLOBAL VARIABLES;" > MySQLCurrentSettings.txt

This will capture all options into the text file.

OPTION 2 : From the Linux command line

ps -ef | grep mysqld | grep -v grep

This will show options mysqld started with as set from mysqld_safe

OPTION 3: Ask the Server Directly

mysqld --help --verbose

At the bottom of the 'mysqld --help --verbose' display, you will see the current settings mysqld loaded from my.cnf or defaulted to.

7
  • -1 Sorry, but this makes no sense. Why are you dumping SHOW VARIABLES to a text file, then greping the processlist and doing nothing with its results, then saying read the help flag to mysqld?
    – Coops
    Aug 12, 2011 at 20:53
  • @Coops - They are three separate things. The first captures the SHOW VARIABLES in a text file. The second lets you see what options are specified by the /etc/init.d/mysql on the startup of mysql. The third lets you see in the bottom half of the display what mysqld is currently operating with. It is up to samspot to do the leg work of setting those options in /etc/my.cnf from these displays. Should I have written up something to automate and regenerate /etc/my.cnf ? Aug 12, 2011 at 21:03
  • Your opening line is "do all of the following" suggesting your providing a step-by-step guide to achieve an answer to the question. You also don't explain what each step does, or how it answers the question, adding to the confusion! :-)
    – Coops
    Aug 12, 2011 at 21:18
  • Sorry for any confusion caused by the way I wrote my answer originally. Aug 13, 2011 at 0:06
  • Even though regenerating my.cnf would be the best case (I had hoped mysql could do this), this is really great info for us to recover. I had tried googling for this information but all I could find were articles about backing the database. Thanks!
    – samspot
    Aug 14, 2011 at 4:19
4

These instructions are for the stock mariadb on centos 7.1.

Here follows how to backup or replicate the current settings of a machine to a new installation, present or future.

On the machine from which we want to copy the settings, we can run:

/usr/libexec/mysqld --help --verbose > mysql_current_settings.txt

On another machine, we can install mariadb-server and run:

/usr/libexec/mysqld --help --verbose > mysql_default_settings.txt

Then we put both files into one directory, which in this example is "/a/".

Then we run:

comm -3 <(sort /a/mysql_current_settings.txt) <(sort /a/mysql_default_settings.txt)

If there is no output, then the two files are identical. Which means that all the settings, on both machines, are at their default.

If there is some output, then some lines will be not indented, while some lines will be indented.

The non-indentented lines are present only in the first file, which here is /a/mysql_current_settings.txt.

The indentend lines are present only in the second file, which here is /a/mysql_default_settings.txt.

Now we know all settings, except for some settings which are set in the command line which started mysqld. These settings may come from /etc/my.cnf, or /etc/my.cnf.d/* files, or a custom script, or an alias, etc.. In any case, we can see them with the following command:

ps -ef | grep mysqld

Now we know the very few settings that we have to change on a new installation to configure it as the old one.


Here follow some other details.

On centos 7.1, the following command shows all current settings, except for some settings which are set in the command line which started mysqld:

/usr/libexec/mysqld --help --verbose

In total, it shows:

in the first part, the settings that we can use as first parameter after "mysqld" when we start it;

in the second part, the settings set at compile time;

in the third part, the current settings.

Even if the last line of its output says: to see what values a running MySQL server is using, type:

    mysqladmin variables -uroot -p

that command does not show, f.e., bind-address, even if we change it in /etc/my.cnf and restart mysql.

Also the following command shows many settings but not "bind-address":

mysql -uroot -p -e"SHOW VARIABLES;"

Note that, on centos 7.1, mysqld is not in $PATH.

2

Here's my favorite way to generate a current my.cnf:

{ 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 echo '#'; mysql -NBe "SHOW GLOBAL VARIABLES LIKE '${l}%'" | 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

However, this does not work reliably for Mac OS X.

This will output a clean variables log, commented out, ready to import into your my.cnf.

Original source: http://www.askapache.com/mysql/view-mysql-variables-my-cnf.html

2
  • Some notes: 1. bsdmainutils need to be installed 2. Warning: it writes version of mysql client, not server
    – gadelat
    Jun 21, 2016 at 12:31
  • For server version the command is mysqld -V|sed 's,^.*\(V.*\)\ for.*,\1,'. Not sure it matters since I think both client and server are installed together. What is the purpose of {{{1 and 1}}}?
    – beppe9000
    Nov 17, 2019 at 18:06
0

A summary of my.cnf

In order to configure my own mysql-client programmatically, I was interested in the settings that mysql (rather than mysqld) was reading from the various my.cnf files. First, I wanted to parse them myself, but then I realized they are not only in ~/.my.cnf and /etc/my.cnf, but they may be in different paths altogether and includes are also possible.

Since both, mysqld and mysql support the --help --verbose parameters equally, I came up with the following snippets based on the other answers (especially by rolandomysqldba and ryan). They all work with either of the mysql commands. Just use the one you need at the beginning of the respective snippet.

Simple value list

To show mysql's values from the my.cnf files, I used

mysql --help --verbose \
| sed -n -E '/^---+/,/^$/{/^---+/d;/^$/d;s/^([^[:space:]]+)[[:space:]]+(\(No default value\)|(.*)$)/\1=\3/;p}' \
| sort
  1. The mysql --help --verbose command prints the current configuration, as explained in the other answers.
  2. The added sed command basically changes mysql's output and
    • only prints the part with the current settings (the lines between the one with ------ and the next empty line),
    • removes the (No default value) part of the value field, and
    • formats it to a key=value format (the separator = is specified in this part: \1=\3, where \1 represents the key, and \3 the value). Like so
      -----8<----------
      column-names=TRUE
      column-type-info=FALSE
      comments=FALSE
      compress=FALSE
      connect-expired-password=FALSE
      connect-timeout=0
      database=
      debug-check=FALSE
      debug-info=FALSE
      default-auth=
      default-character-set=auto
      delimiter=;
      force=FALSE
      host=127.0.0.1
      -----8<----------
      
  3. and the sort command sorts the values alphabetically :-)

Simple table

If you prefer a table output, append the following to the above command: | column -t -s= -o' = ', where '=' is the separator of the input (-s) and ' = ' the separator of the output (-o):

mysql --help --verbose \
| sed -n -E '/^---+/,/^$/{/^---+/d;/^$/d;s/^([^[:space:]]+)[[:space:]]+(\(No default value\)|(.*)$)/\1=\3/;p}' \
| sort \
| column -t -s= -o' = '

It will then look similar to this:

-----8<----------
column-names             = TRUE
column-type-info         = FALSE
comments                 = FALSE
compress                 = FALSE
connect-expired-password = FALSE
connect-timeout          = 0
database                 =
debug-check              = FALSE
debug-info               = FALSE
default-auth             =
default-character-set    = auto
delimiter                = ;
force                    = FALSE
host                     = 127.0.0.1
-----8<----------

Simple JSON list

Of of you need JSON to parse it, use | column -t -s= --json -N key,value -n my.cnf instead and you'll get:

{
   "my.cnf": [
      {
         "key": "abort-source-on-error",
         "value": "FALSE"
      },{
         "key": "auto-rehash",
         "value": "TRUE"
      },{
  -----8<----------
      },{
         "key": "vertical",
         "value": "FALSE"
      },{
         "key": "xml",
         "value": "FALSE"
      }
   ]
}

JSON Object with jq

If you have jq available, you can format it to one object like so:
| jq -Rn ' reduce ( inputs / "=" ) as [$key,$value] ({}; .[$key] |= $value )'

mysql --help --verbose \
| sed -n -E '/^---+/,/^$/{/^---+/d;/^$/d;s/^([^[:space:]]+)[[:space:]]+(\(No default value\)|(.*)$)/\1=\3/;p}' \
| sort \
| jq -Rn ' reduce ( inputs / "=" ) as [$key,$value] ({}; .[$key] |= $value )'
{
  "abort-source-on-error": "FALSE",
  "auto-rehash": "TRUE",
  "auto-vertical-output": "FALSE",
  "binary-as-hex": "FALSE",
  "binary-mode": "FALSE",
  "character-sets-dir": "",
  -----8<----------
  "socket": "/run/mysqld/mysqld.sock",
  "ssl": "FALSE",
  "table": "FALSE",
  "tls-version": "",
  "unbuffered": "FALSE",
  "vertical": "FALSE",
  "xml": "FALSE"
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .