We will be undergoing scheduled maintenance on May 20th, 2013 at 02:00 GMT.
MySQL 5 Development (Part 1) Tutorials
Database Basics / Databases in MySQL
Subtitles of the Movie (MySQL 5 Development (Part 1) / Database Basics / Databases in MySQL)
We introduced the general concept of a Database in the first lesson of this chapter but we now need to learn just a little bit more about how MySQL implements that concept. A MySQL installation can manage many databases; all database directories are contained in a directory named Data, kept in a location known to and accessible by the MySQL Server. These databases can be modules of a larger application or can contain the data for completely separate applications. It is the choice of the system designer and the database administrator as to how to arrange these things. So, a database in MySQL is nothing more than a directory in the file system of the computer on which the data directory is kept. This database directory contains the Table Structure Files for each table belonging to that database as well as the files that actually contain the data and index information for those tables in most cases. Different storage engines handle the storage of data and index information in different ways and with different file types, but most engines house all of this information within the database directory. We can think of a database as a project, different databases maintained on the same server are not necessarily related to one another; they are simply managed by the same MySQL Server. In fact, there's always at least one database, the MySQL database, that will not directly relate to the others. Let's look at what is set up when we install our MySQL Server. There is one issue about MySQL databases that we should note here, since a database is a construct of the file system, there is the possibility that the name of the database will be case sensitive when addressing it through the MySQL Server. This will be the case if the file system itself is case-sensitive, which is true for most file systems except those running on a Windows operating system. The names of tables have the same issue since MySQL also uses the file system to maintain uniqueness of table names by using the name of the tables format file. Since everything else in MySQL is not case-sensitive I find this to be an issue. I would rather not have to remember the exact case of each letter I use in initially naming my databases and tables. We have a remedy for this but I will leave that remedy as a mystery until we get to the chapter on configuring the MySQL Server, we'll take care of this there. There is one other implication to the fact that a database is simply a directory of the file system, if we were to drop a database, this causes the file system to remove that directory and you know what that means, yep the directory and all of its contents are suddenly deleted. There is no undo command for this action either like there might be with a word processor, the deletion is permanent, so be careful. With that happy news, let's peer into the future and see what databases we will have by default once we install our MySQL Server. There are three databases that exist on a new server, but only two of them actually appear in the file system, let's see what those are and what they're used for. The first and most important database for a MySQL Server is the MySQL Database. This database contains all the administrative tables for the server, this includes tables for user accounts and various types of privileges, tables for time zone information, tables for plug-ins and other things that help the server to run properly. This database is only accessible to properly privileged database administrators and should not even be touched by them except to look at some of this data occasionally, unless there is an emergency situation that requires some kind of surgical intervention. The contents of these tables are managed internally by certain administrative commands such as Create User. The next built-in database is the Test Database; this one is empty to begin with and is simply a useful sandbox to use for testing various things as we perform development work. But this is not the most secure database MySQL offers so many database administrators will remove it from their production servers for security reasons. We will be using this extensively during this and other courses for performing demonstrations outside of our example databases. The third database that we would see if we issued the Show Databases command when connected to a newly installed MySQL Server is the Information Schema Database. This is a virtual database that is stored primarily in the memory of the host computer for the server. It contains metadata about how the server and all of its connections are running, which can be very helpful information for a database administrator. This database is read-only though, so no one can make changes to it, its contents are updated as various commands are executed by the server. We don't spend much time with this database in this course either, but we do spend some quality time examining what it has to offer in the MySQL 5 Administration courses. Well that's enough background information for now, in the next chapter we will get some practice installing the MySQL Server and the basic client programs that come with it from the manufacturer. We will then have to configure the server to work the way we want it to for this course, so there is also another chapter on configuring the MySQL Server and that is followed by a chapter on how we operate the MySQL Interactive Command Line Client program and then we'll be able to get down to sending request to the server and seeing what results come back. So, a lot of work to do to get set up, let's get started.
Tutorial Information
| Course: | MySQL 5 Development (Part 1) |
| Author: | David Swain |
| SKU: | 34225 |
| ISBN: | 1-936334-93-3 |
| Release Date: | 2011-05-27 |
| Duration: | 11 hrs / 129 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
