Using INFORMATION_SCHEMA instead of shell scripting

INFORMATION_SCHEMA, in particular by favorite TABLES table is not only helpful to understand tables you have on the system, but I have also found it to be very helpful as a scripting language for variety of database administration tasks. It can be more straightforward compared to using shell or Perl when the operation is database specific.

For example if you would like to MySQLDump only Innodb table in one file per database you can do the following:

As you can see we’re just getting the set of commands to run. How to make it easily runable ? Well just use INTO OUTFILE to create very simple shell script:

In other case I needed to restore Innodb tables from mysqldump because of corrupted Innodb tablespace – to do this I had to clean all .frm files which correspond to innodb tables (as well as ibdata1 and innodb log files). With shell you could so it by looking for .frm files which do not have corresponding .MYI files…. but this will also get all MEMORY tables which we want to leave in tact. Using similar approach we can do:

Do not get me wrong, this is far from replacement for shell in all cases but it is rather handy for some niche tasks, in particular which involve unix commands driven by MySQL meta data.

Share this post

Comments (7)

  • Todd

    Another fun trick is to use INFORMATION_SCHEMA tables to generate SQL statements, and pipe them back into MySQL:

    mysql -uroot -e”SELECT concat(‘ALTER TABLE ‘,table_schema,’.’,table_name, ‘ ENGINE = MyISAM;’) #sql FROM information_schema.tables WHERE engine=’innodb’ AND table_schema = ‘test’;” | mysql -uroot

    November 24, 2008 at 10:19 pm
  • peter


    Yes good note. You can pipe it too which is better in many cases. I just used 2 different boxes in this case so outfile was easier.

    November 24, 2008 at 10:33 pm
  • nmike

    ниодного индекса в таблице TABLES – ну ппц просто.
    запросы на ней просто ужос.

    а так, мускул в этом отношении не только с этими таблицами использовать можно – для разный мелких задач такой путь тоже подходит. ну или сформировать csv например из какойто таблицы, причем можно сразу сделать некоторую обработку.

    November 25, 2008 at 3:46 am
  • Kay Roepke

    Metadata, baby, metadata 🙂 Powerful stuff…

    November 25, 2008 at 4:35 am
  • peter


    Information Schema is not really the table – MySQL scans the databases finding all the tables and putting their stats in the temporaty table which is later used for queries. This is why it takes so long time.

    November 25, 2008 at 9:09 am
  • Mike Myles

    This is a great post. It opened my eyes to a whole new way of controlling my DB. Funny how sometimes you just need a little ‘inspiration’ and your mind will open up to a plethora of new possibilities. THANK YOU FOR THIS POST!

    December 9, 2008 at 10:44 am
  • nik

    Todd, the great script. Very useful.

    July 22, 2009 at 12:03 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.