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