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


3 Answers 3


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

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

You must log in to answer this question.

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