Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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


share|improve this question

2 Answers 2

up vote 8 down vote accepted

Three(3) Options

OPTION 1 : From within mysql client

mysql -uroot -A -e"SHOW 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.

share|improve this answer
-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

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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