2

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"
2

SHOW COMMAND

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

INFORMATION_SCHEMA (MySQL 5.1+)

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`
do
    grep "${X}" InnoDBRuntime.txt
done

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

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