MySQL 5 Administration-Part 1 Tutorials
Architecture / Information Schema
Subtitles of the Movie (MySQL 5 Administration-Part 1 / Architecture / Information Schema)
As part of this server subsystems overview I'd like to spend a few minutes here on the information schema. I just think it's important to mention it is part of this process. So the information schema is our primary metadata repository these days. It's where we're most likely to get the metadata that we're looking for because we have such flexible access to this metadata. So what do we mean by metadata? For those of you who weren't in the developer course. Well, it's information about our system. It's information about the server, how it's running, about the tables, about the users, about all kinds of things having to do with the database management system. That's metadata. Now it draws this information from the various tables in the MySQL database as well as from variables inside the server itself. And it's a virtual database. It's not something that we create, we don't see format files for it anywhere, we don't see the files for it anywhere. It's generated when the server launches and then it's dynamically updated all the time. And it's made up of tables that if you were to list them with a View Tables Command, View Full Tables Command so you can see the type of table, you'll see that they're all listed as system views as different from a regular view. A view is based upon a database, a system view is a very interesting table like construct that allows us to reach into the server and reach into the database management system itself and get some information out of it but still query it as though it were a database table. Very interesting. Now it's also a read only database, we can't update the information except by performing other actions that would cause the server to operate differently. But we can't write to the information schema, we can't delete things, we can't add new tables to it. It's mainly held in RAM, there are a few tables that are stored on disk but not many. Most of them are in RAM and it's dynamically updated. So when we're querying the information schema database or any of it's tables, we know that we're looking at the most recent information. Although some information about the server can change so fast that by the time we get our query results back it maybe slightly different. But at least we were getting it at that moment, dynamically updated. It, it has exactly the truth at that moment. It is more flexible to use than the traditional Show Commands that MySQL offers us but just typing information underscore schema which is the name of the database to access one of it's tables without making that the current database is a lot more effort than most of the Show Commands require. That's a very long name for the database in the first place. And then we have to write complete queries but on the other hand we can write complete queries and we can decide what columns come back to us. Whereas with the Show Commands they're all prepackaged and so we don't really have too much control over much of anything with those. We have a few token options where we can have Where Clause and Like Clauses but with the information schema, we can perform aggregation queries and joins with other information schema tables or for that matter any table we want to. So it's a much more flexible system and one that I think as a DBA you'll find much more useful than just the Show Commands. One more topic I want to cover and that is about deciding on the environment where on MySQL server is going to run. We need to talk about hardware and the operating system running on the hardware and the implications that, that can have on things. So that's the subject of the next and final lesson in this chapter.
Tutorial Information
| Course: | MySQL 5 Administration-Part 1 |
| Author: | David Swain |
| SKU: | 34307 |
| ISBN: | 978-1-61866-086-2 |
| Release Date: | 2012-12-31 |
| Duration: | 16 hrs / 171 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
