Mysql – What happens to thesql information_schema after switching to innodb_file_per_table

information-schemainnodbMySQL

We're switching our mysql dev deb instance to innodb_file_per_table and moving the data dir onto another mounted drive so we can free up space on our dev box.

We've backed all our dbs up and about to set the per_table_file flag drop all the dbs and re-import, but what will happen to the information_schema database in the original mysql data file, since this db wont be dropped and recreated, it wont be in the per table/file format, will we still free up space from our original huge datafile, or will this remain the same size?

Best Answer

The information_schema database is an all memory database made up of temporary tables. It is not stored in any of the InnoDB plumbing.

I wrote about this 5 years ago : How is INFORMATION_SCHEMA implemented in MySQL?

If you performed a mysqldump of the infomration_schema, reimporting it will do nothing.

mysqld will correct and protect any outside manual attempts to change it.

For example, suppose you wanted to create a database called mynewdb and your datadir is /var/lib/mysql. if you ran the following commands

cd /var/lib/mysql
mkdir mynewdb
chown mysql:mysql mynewdb

This will create a database in the eyes of mysqld.

mynewdb shows up in INFORMATION_SCHEMA.SCHEMATA

You can verify this by running the following:

mysql> SHOW DATABASES;
mysql> SELECT * FROM information_schema.schemata;

The database mynewdb will be present

As for your actual question, don't worry. You cannot break the INFORMATION_SCHEMA. mysqld won't let you.

Related Question