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



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.

  • -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 '11 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 ? – RolandoMySQLDBA Aug 12 '11 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 '11 at 21:18
  • Sorry for any confusion caused by the way I wrote my answer originally. – RolandoMySQLDBA Aug 13 '11 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 '11 at 4:19

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.


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

  • Some notes: 1. bsdmainutils need to be installed 2. Warning: it writes version of mysql client, not server – gadelat Jun 21 '16 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 '19 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.