Mysql – List of variable defaults for InnoDB engine

innodbMySQL

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"

Best Answer

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

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

Mysql – innodb completely crashed after interruption

it might be not as bad. InnoDB dictionary is corrupt, but data may be still good. As long as MySQL start with innodb_force_recovery=6 dump as many tables tables as it allows:

    for d in `mysql -NBe "select distinct TABLE_SCHEMA  from information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')"`
    do
        mkdir -p "dumps/$d"
        for t in `mysql -NBe "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$d' AND TABLE_TYPE = 'BASE TABLE' AND  ENGINE='InnoDB';"`
        do
            mysqldump --skip-lock-tables $d $t > "dumps/$d/$t.sql"
            # let MySQL start if it crashed
            if [ "$?" -ne 0 ]; then sleep 30; else echo "$d/$t" >> crashed fi
        done
     done

Then take list of "crashed" tables and recover them with data recovery toolkit.

You need to parse InnoDB tables space:

 ./stream_parser -f /path/to/table.ibd

and fetch records from it:

 ./c_parser -5f pages-XXXX/FIL_PAGE_INDEX/0-<minimal id>.pages -t table.sql

Then load the dump back into MySQL(as well as dumps taken with mysqldump)

P.S. I should probably write a blog post as I answered this question hundred times here.