Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to list the defaults for the global variables that are innodb specific.

The problem is that the following commands doesn't list any innodb variables:

/usr/sbin/mysqld --no-defaults --verbose --help
/usr/sbin/mysqld --verbose --help

However I can see the runtime ones with:

mysql -e "show global variables"
share|improve this question
up vote 2 down vote accepted


mysql --table -Ae "SHOW GLOBAL VARIABLES LIKE 'innodb%'"


SQLSTMT="SELECT * FROM information_schema.global_variables"
SQLSTMT="${SQLSTMT} WHERE variable_name LIKE 'innodb%'"
mysql -Ae"${SQLSTMT}"

my.cnf Settings

cat /etc/my.cnf | grep innodb

MySQL Documentation

As for the runtime settings and /etc/my.cnf diff, here it is

mysql -uroot --table -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBRuntime.txt
cat /etc/my.cnf | grep innodb | sed 's/=/ /g' | awk '{print $1}' > InnoDBConfigs.txt
for X in `cat InnoDBConfigs.txt`
    grep "${X}" InnoDBRuntime.txt

As for the runtime settings and actual defaults, it's a little different

STEP 01) Install mysql on a DevServer with no /etc/my.cnf

STEP 02) Run mysql -uroot -hDevServerIP -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBDefault.txt

STEP 03) Run mysql -uroot -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBRuntime.txt

STEP 04) Run the Diff

diff InnoDBDefault.txt InnoDBRuntime.txt

Give it a Try !!!

share|improve this answer
I would like to list the default values using mysql or mysqld. I know they are in the documentation of the engine, but I can not use this in an automation script. – Mircea Vutcovici Jul 5 '13 at 20:59
I want to see which variables are changed in the runtime compared to the defaults, a kind of diff between "show global variables" and "/usr/sbin/mysqld --no-defaults --verbose --help" – Mircea Vutcovici Jul 5 '13 at 21:00
Updated my answer with the diff code... – RolandoMySQLDBA Jul 5 '13 at 21:13
The problem is that the code does not makes the diff with the MySQL defaults. – Mircea Vutcovici Jul 5 '13 at 21:24
It seems that there are variable that exists only at runtime and can not be set in my.cnf or from command line. Like autocommit. – Mircea Vutcovici Jul 5 '13 at 21:27

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.