Using INFORMATION_SCHEMA instead of shell scripting

November 25, 2008

Author

Peter Zaitsev

Insight for DBAs

Share this Post:

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:

<br>mysql> select concat("mysqldump ",table_schema," ",table_name, " >> ",table_schema,".sql") from tables where engine='innodb' limit 5;<br>+------------------------------------------------------------------------------+<br>| concat("mysqldump ",table_schema," ",table_name, " >> ",table_schema,".sql") |<br>+------------------------------------------------------------------------------+<br>| mysqldump art73 article73 >> art73.sql                                       |<br>| mysqldump art73 author73 >> art73.sql                                        |<br>| mysqldump art73 forum73 >> art73.sql                                         |<br>| mysqldump art73 forum_stats73 >> art73.sql                                   |<br>| mysqldump art73 img_out73 >> art73.sql                                       |<br>+------------------------------------------------------------------------------+<br>5 rows in set (48.69 sec)<br>

1

mysql> select concat("mysqldump ",table_schema," ",table_name, " >> ",table_schema,".sql") from tables where engine='innodb' limit 5; +------------------------------------------------------------------------------+ | concat("mysqldump ",table_schema," ",table_name, " >> ",table_schema,".sql") | +------------------------------------------------------------------------------+ | mysqldump art73 article73 >> art73.sql | | mysqldump art73 author73 >> art73.sql | | mysqldump art73 forum73 >> art73.sql | | mysqldump art73 forum_stats73 >> art73.sql | | mysqldump art73 img_out73 >> art73.sql | +------------------------------------------------------------------------------+ 5 rows in set (48.69 sec)

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:

<br>mysql> select concat("mysqldump ",table_schema," ",table_name, " >> ",table_schema,".sql") from tables where engine='innodb' into outfile '/tmp/dump.sh';<br>Query OK, 328 rows affected (46.88 sec)<br>

1	<br>mysql> select concat("mysqldump ",table_schema," ",table_name, " >> ",table_schema,".sql") from tables where engine='innodb' into outfile '/tmp/dump.sh';<br>Query OK, 328 rows affected (46.88 sec)<br>

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:

<br>mysql> select concat("rm -f /var/lib/mysql/",table_schema,"/",table_name, ".frm") from tables where engine='innodb' limit 5;<br>+---------------------------------------------------------------------+<br>| concat("rm -f /var/lib/mysql/",table_schema,"/",table_name, ".frm") |<br>+---------------------------------------------------------------------+<br>| rm -f /var/lib/mysql/art73/article73.frm                            |<br>| rm -f /var/lib/mysql/art73/author73.frm                             |<br>| rm -f /var/lib/mysql/art73/forum73.frm                              |<br>| rm -f /var/lib/mysql/art73/forum_stats73.frm                        |<br>| rm -f /var/lib/mysql/art73/img_out73.frm                            |<br>+---------------------------------------------------------------------+<br>5 rows in set (44.68 sec<br>

1

mysql> select concat("rm -f /var/lib/mysql/",table_schema,"/",table_name, ".frm") from tables where engine='innodb' limit 5; +---------------------------------------------------------------------+ | concat("rm -f /var/lib/mysql/",table_schema,"/",table_name, ".frm") | +---------------------------------------------------------------------+ | rm -f /var/lib/mysql/art73/article73.frm | | rm -f /var/lib/mysql/art73/author73.frm | | rm -f /var/lib/mysql/art73/forum73.frm | | rm -f /var/lib/mysql/art73/forum_stats73.frm | | rm -f /var/lib/mysql/art73/img_out73.frm | +---------------------------------------------------------------------+ 5 rows in set (44.68 sec

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.