One of the recent MySQL enhancements was the INFORMATION_SCHEMA data dictionary, which gives a more coherent view on the DBMS meta-data.

Useful as it is, the information schema is becoming widely used, and many DBAs are learning how to use it as an administration and debugging tool. So useful, in fact, that it would be convenient, from time to time, to export its contents and pass it to a colleague to have a second opinion when a problem arises.

Unfortunately, there is no built-in tool to achieve this result. The standard mysqldump utility will skip the information schema on purpose. Since they are tables that are generated on-the-fly at server start-up, dumping them would not make sense to the normal user. However, the DBA is not a normal user, and such a dump would be highly desirable.

Here is a quick workaround that will produce a dump suitable for transferring the data to a new database.

This bash script will make a composite dump, similar to the one you can get by running mysqldump –tab=directory.

#!/bin/bash
if [ "$2" == "" ]
then
    echo "syntax $0 username password"
   exit
fi
USERNAME=$1
PASSWORD=$2
TABLE_LIST=`mysql -u $USERNAME -p$PASSWORD -NB -e "show tables from information_schema"`

echo "-- information_schema dump " > load_information_schema.mysql
echo "-- `date` " >> load_information_schema.mysql
echo "create database if not exists dumped_information_schema;" >> load_information_schema.mysql
echo "use dumped_information_schema;" >> load_information_schema.mysql

for E in $TABLE_LIST
do
    echo $E
    mysql -u $USERNAME -p$PASSWORD -e "show create table information_schema.$E\G" \
        | perl -p -e 's/^\s*Table:.*//;' \
            -e 's/^Create Table: //;' \
            -e 's/^\*+.*//;' \
            -e 's/(CREATE) (TEMPORARY) (TABLE)/$1 $3/;' \
            -e 's/engine=memory/ENGINE=MyISAM/i;' > $E.mysql
    mysql -u $USERNAME -p$PASSWORD -BN  -e "select * from information_schema.$E" > $E.txt
    echo "source $E.mysql;" >> load_information_schema.mysql
    echo "LOAD DATA LOCAL INFILE '$E.txt' into table $E;" >> load_information_schema.mysql
done

Run this script inside a empty directory, and it will create for each table one file .mysql containing the table DDL, and a .txt containing the data. At the same time, it will also compose a load_information_schema.mysql, containing all the commands you need to load the information into a new database.

To load the data, it’s enough to run

$ mysql -u USERNAME -pPASSWORD < load_information_schema.mysql

To the readers of O'Reilly network. Hi. This is my first post in this blog. I wrote something for onLamp a few years ago and now I usually write in my own blog as the data charmer. More info about me at MySQL guilds