Dumping MySQL information schema

by Giuseppe Maxia

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.


2 Comments

Brian Aker
2006-04-09 08:29:18
Hi!


So it sounds like what you need is a flag to allow yourself to dump the INFORMATION_SCHEMA if you really want it from mysqldump.


Cheers,


-Brian

Ronald Bradford
2006-04-09 23:09:00
In your script the necessary backslashes for your SQL Command, and concatenated perl lines are not presented in your display. For an inexperienced developer, this may be confusing, In fact for a moment I was looking for the $EG variable when first reading your script.


I can see some merit for dumping of the Information Schema. In a more controlled enterprise solution they may be some statistical benefit in versioning information on tables, views, triggers and stored procedures. This can of course also be managed via appropiate mysqldumps, or correct version control of source files, however the ability to query easily via SQL versioned histories of the INFORMATION_SCHEMA may prove benefical. An example could be to determine schema changes in a software upgrade via queries to the INFORMATION_SCHEMA.