What is INFORMATION_SCHEMA?

[james438]

Just wondering what INFORMATION_SCHEMA is and whether it is important to back it up. As I recall it has certain write/delete restrictions placed on it. I could check, but I wasn't sure how dangerous it would be to play around with it.

[djr33]

MySQL stores its own configuration internally (at least part of it). That's the INFORMATION_SCHEMA.

If you're moving servers, then just backup the database like you usually would. Then you can use the automatically generated "CREATE TABLE" queries to recreate the structure. In fact, you might not even have access to modify the INFORMATION_SCHEMA table.

But if you are staying on the same server, don't change it or it might erase (or modify) your tables.

Basically the INFORMATION_SCHEMA is the table that stores the information about your tables. Interesting circular logic (although I don't think it stores information about itself actually).

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

[james438]

When you say "backing up your database like you usually would" are you saying it is fine to back up the database without the INFORMATION_SCHEMA? That's how I normally do it.

[djr33]

Yeah, exactly. INFORMATION_SCHEMA stores the current configuration. When you back up your database it includes "CREATE TABLE" and other queries designed to re-create the old setup in the new database. Also, it won't conflict* with any of the existing content (so you could merge databases). If you did include INFORMATION_SCHEMA, then you would have to either replace the existing one or find a creative way to merge them.

(*Except if there are any shared names [eg, for tables] in the new and old databases you're merging.)

In short, INFORMATION_SCHEMA is part of your server (MySQL) configuration, not your data. Your new server will also need to be configured, but you don't need to do it (and probably shouldn't) by copying that table, just like you won't be copying a disc image of the operating system of your first server to your new server. (Of course you could do that if you want to identically copy the database, but it's not required.)

[james438]

That makes sense. Thanks, I was rather curious about this.