Mysql Export current configuration to a file

Question

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!

Answer 1

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.

Answer 2

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.

Answer 3

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