mysql| Data | Data directory The MySQL data directory contains all the databases and tables managed by the server. They are organized into a tree-like structure that is implemented in a simple way through a hierarchy of UNIX or Windows file systems:
Each database pair should be in a directory under the data directory.
The tables in the database correspond to the files in the database directory.
The data directory also contains several state files generated by the server, such as log files. These files provide important information about the operation of the server and are useful to administrators, especially when problems arise and try to identify the cause of the problem. For example, if a particular 檠 , , duo , Christie-Ü, 觳-covered, Jin-gilt-covered Cape chiselled
How the MySQL server provides access to the data
Everything in the data directory is managed by a single entity, the mysqld of the MySQL server. The client program cannot manipulate the data directly. The server provides the only connection point to access the database, which acts as a medium between the client program and the data required (see Figure 10-1)
When you start the server, if there is any request, it will open the log file, and then show the network interface to the data directory by listening to the network connection. To access the data, the client establishes a connection to the server and then communicates the request as an SQL query to complete the desired action (for example, creating a table, selecting a record, updating a record). The server performs each operation and sends the results back to the client. The server is multi-threaded and can serve connections from multiple concurrent clients. However, because the update operation can only execute one at a time, the requests are actually sequential, and two clients will never be able to modify the same record at the same time.
Under normal conditions, the only arbitrator who makes the server a database access will provide guarantees against various corruption that can cause multiple processes to access the database's tables at the same time. corruption However, an administrator should know that there are periods when the server does not have exclusive control over the data directory:
When to run multiple servers in a single data directory. It is common to run a single server to manage all databases in the host, but it is also possible to run multiple servers. If this can provide access to multiple separate data directories, there is no interaction problem. However, it is possible to start multiple servers and point to them in the same data directory. This is a good idea, if you want to try it, it is best to ensure that the system provides good file locking performance, otherwise the server will not work in harmony. If multiple servers are written to the log file at the same time, it will make the log file a source of confusion (rather than a source of useful information).
When to run Isamchk and my i s a M c h K. The Isamchk and Myisamchk utilities are used for table maintenance, troubleshooting, and repair. As you might guess, because these utilities can change the contents of the table, it will cause the table to be corrupted if the utility is allowed to operate on the table while the server is functioning. It is important to understand how to limit this type of interaction to avoid table destruction. For instructions on proper use of these programs, see Chapter 13th, "Database maintenance and repair."
Representation of the database
Each database managed by MySQL has its own database directory, which is a subdirectory of the data directory, with the same name as the database represented. For example, the database my_db corresponds to the database directory data D I r/my _ db.
This notation makes the implementation of several database-level statements almost negligible. Create Data B a S E db _ name uses only the ownership and manner of access to the MySQL server user (the UNIX user running the server) and creates an empty directory db _ name in the data directory. This is equivalent to creating a database manually by executing the following commands as a server user in the server host:
% mkdir datadir/db_name CREATE database directory
% chmod Datadir/db_name makes it accessible only to MySQL server users
The method of representing the new database through an empty directory is completely different from the other database systems, which even create many control files or system files for the "empty" database.
DROP DATABASE statements are also easy to implement. Drop DATABASE _ name deletes the db _ name directory in the data directory and all of its table files. This statement is similar to the following command:
%RM-RF DATA D I r/db _ name
The difference is that the server deletes only files with the extension of the table. If you have created additional files in the database directory, the server will keep them intact and do not delete the directory itself.
Show DATABASE is simply a list of subdirectory names that are located in the data directory. Some database systems need to keep a primary table listing all the databases that need to be maintained, but there is no such structure in MySQL. Because of the simplicity of the data directory structure, the list of databases is implied in the contents of the data directory, such as the main table
Tables can cause unnecessary overhead.
notation for database tables
Each table in the database exists as three files in the database directory: a format (description) file, a data file, and an index file. The base name of each file is the table name, and the extension indicates the type of the file. The extension is shown in table 10-1. The extension of the data and index file indicates whether the table uses an older ISAM index or a newer MyISAM index.
When you publish a CREATE TABLE Tbl_name statement that defines a table structure, the server creates a tbl_name. F R m file, which contains the internal encoding of the structure. The statement also creates an empty data file and index file with initial information indicating that there are no records and indexes (if the Create TABLE statement contains an index description, the index file will reflect those indexes). The ownership and manner of the file describing the table is set to allow access only to MySQL server users.
When the ALTER TABLE statement is published, the server tbl_name.frm and modifies the contents of the data file and index file to reflect the structural changes indicated by the statement. This is also true for create and drop INDEX, because the server considers them equivalent to alter TABLE statements. Drop table deletes the three files that represent the table.
Although you can delete the table by deleting three files from the corresponding table in the database directory, you cannot create or change the table manually. For example, if my_db is the current database, the DROP TABLE my_tbl is roughly equivalent to the following commands:
% rm-f datadir/my_db_tbl.*
The output from show TABLES my_db is a list of the base names of the. frm file in the my_db database directory. Some database systems maintain a registration that lists all the tables in the database. But MySQL does not, because it is not necessary, this "registration" is implied in the structure of the data directory.
Operating system constraints in database and table naming
MySQL has general rules for naming databases and tables:
Names can consist of alphanumeric characters in the current character set, as well as underscores and dollar signs (' _ ' and ' $ ').
The name can be up to 64 characters long.
However, because the database and table names correspond to directories and file names, the operating system running against the database can impose additional constraints.
First, limit the database and table names to legitimate characters in the file name. For example, according to the rules of MySQL, ' $ ' is allowed in the name, but it cannot be used in a directory name or table name if the operating system does not allow it to be used. In fact, this has nothing to do with UNIX or Windows. The most difficult problem you may encounter is when you are managing a database from the shell
Named directly in. For example, if a database is defined with a name such as $my_db, and the name includes a dollar sign, any reference to that name from the shell command line can be interpreted by the shell as a variable reference:
% ls $my _db
my_db:undefined variable
If this happens, you must change the ' $ ' character, or use quotation marks to remove its special meaning:
% ls \ $my _db
% ls ' $my _db '
If you want to use quotation marks, you should use single quotes. Double quotes cannot undo the explanation of a variable.
Second, although MySQL allows databases and tables to have a maximum length of 6 4 characters, the length of the name is limited by the length allowed by the operating system. Typically, this is not a problem, although in Unix you may enter an older version of System v-ish that has a 14 character limit. In this case, the database name is effectively limited to 14 characters and the table name is limited to 10 characters because the file name representing the table can be terminated with a period and a three-character extension.
Third, the case sensitivity of the underlying file system affects the naming and referencing of databases and tables. If the file system is case-sensitive (such as Unix), then the two names My_tbl and my_tbl involve different tables. If the file system is not case-sensitive (such as Windows), then My_tbl and MY_TBL are the same table. You should be aware that you are using a UNIX server to develop the database and, if possible, move the database to a Windows server at some point.
The meaning of the data directory structure of system performance
The structure of the data directory is easy to understand because it uses the way the hierarchy of the file system is structured. At the same time, the structure has specific performance implications, especially with regard to opening an operation that represents a database table file.
One consequence of this data directory structure is that because tables are represented by multiple files, each open table requires multiple file descriptors, not one. The server intelligently caches these descriptors, but a busy server can easily deplete the descriptor resource if the server is simultaneously serving many concurrent clients or running a primer
A complex query with multiple tables. File descriptors are scarce resources in many systems, especially systems that have a fairly low default total process (Per-p R o C s) limit set. The 11th chapter, "Conventional MySQL management", provides information on estimating the number of descriptors required, and reconfiguring the server or operating system if needed.
Another consequence of representing each table by the table's own files is that the table's opening time increases with the number of tables. The operation that opens the table is mapped to a file open operation provided by the operating system and is therefore affected by the system's Directory finder (d i r e C-R y-lookup routine) efficiency. This is usually not a problem, but it is a problem to consider if you need a large number of tables in your database.
For example, if you want to get 10 000 tables, the database directory should contain 30 000 files. For so many files, it will cause the running speed to decrease due to the time spent on the file open operation (Linux ext2 and S o L a r i s file systems have this problem). If this is a concern, then the table structure should be reconsidered, based on the needs of the application, so that the tables can be organized. You should check to see if you really need so many tables, because sometimes the application will unnecessarily breed many tables. An application that creates a single table for each user will result in many tables, but all of these tables have the same structure. If you want to combine these tables into one table, you can achieve the goal by adding another column to identify the users used for each row. If this makes the number of tables significantly less, the performance of the application is increased accordingly.
At the database design stage, you must consider whether this particular phase is worthwhile for a given application. The reason for merging tables without following the method described above is as follows:
The need for increased disk space. Merging tables is to reduce the number of tables you need (reduce the time that the table is open), but add another column (increasing the need for disk space). This is a typical tradeoff between space and time, and you need to decide which factor is more important. If you think speed is extremely important, you might be willing to sacrifice a little extra disk space. If the space is too tense, you can only tolerate the time when multiple tables are used.
Security considerations. These may constrain your ability or desire to merge with the table. One reason each user uses separate tables is that only users with table-level permissions can access each table. If a table is merged, all user data will appear in the same table.
MySQL does not restrict the access of a known user to a particular row, so you cannot merge tables without compromising access control. On the other hand, if all data access is controlled by the application (the user cannot directly connect to the server), you can merge the tables and use the application's logic to force the merged row-level access.
MySQL has its own internal limitations on the size of the table, but because it represents the table as a file, MySQL will also be limited by the maximum size of the file, which is given by the operating system. Therefore, a valid table size maximum is less than the internal limits of MySQL and the size of the system file.
In general, constraints on size will ease over time. For example, IBM AIX4.1 has a 2GB file size limit, but in AIX4.2 the limit is about 6 4 G B. Table size limits within MySQL also increase as the latest version appears. Prior to the 3.23 series, the internal limit was 4 G B. Since 3. 2 3 Series, the limit value is approximately 9 000 000 bytes. Table 10-2 illustrates how the table size limits within MySQL and the Aix file size limits interact to determine the maximum size of a valid table. Similar interactions can also be applied to other operating systems.
MySQL's status file
In addition to the database directory, the MySQL data directory also contains a number of status files. Table 10-3 provides an overview of these files. The default name for most state files is generated from the server host name, represented in this table as H O S T N A M E.
Table 10-3 MySQL State file
file contents
| file type |
default name |
| process ID |
hostname.pid |
server process ID |
| error log | TD style= "BORDER:0;PADDING:0;" >hostname.err
start and Shutdown event and error status |
| General log |
hostname.log |
connection/Disconnect event and query information |
| update log |
hostname.nnn |
|
The server writes its process ID (PID) to the PID file at startup and deletes the file when it is closed. A PID file is a method by which other processes can find the server. For example, if you run Mysql.server to shut down the MySQL server when the system shuts down, the script checks the PID file to determine which process it needs to send a termination signal.
The error log is generated by SAFE_MYSQLD and is redirected as a result of the server standard error output, which contains all messages that the server writes to STDERR. This means that the error log only exists when the server is started by calling Safe_mysqld (in short, this is the preferred method to start the server because, if an error log is present,
S A f E _ mysqld will reboot the server)
Regular and update logs are optional, and you can open the required log type with the--log and--log-update server options.
The general process provides general information about the operation of the server: Who from where to connect, and what queries they have published. The update log also provides query information, but only the query information for the modified database contents. The contents of the update log are SQL statements that can be run by entering them into the MySQL client program. If there is a collapse
When you must go to the backup file, the update log will be useful because you can repeat the update operations that have been done since the crash by entering the update log to the server. This will bring the database back to the state it was in when the crash occurred.
The following is an instance of the result of a short client session that appears in the regular log, which creates a table in the test database, inserts a row into the table, and deletes the table:
The regular log contains the date and time, the server thread ID, the event type, and the columns for the specific event information.
The same session appears in the update log as follows:
For update logs, the extended format of the log is available, even with the--log-long-format option. The extended log provides information about who will publish the query. Of course, this will use more disk space, but if you do not associate the contents of the update log with the connection events in the regular log, you want to know who is doing what, the extended log may be available.
For the session just shown, the extended log produces the following information:
It is a good note to ensure that the log files are secure and are not read by the user arbitrarily. Both the regular and update logs contain sensitive information such as passwords because they contain the text of the query. The following is a log entry that you do not want anyone to be able to read because it displays the password of the root user:
For information on checking to set permissions for data directories, see Chapter 12th. The short instructions for Data Directory security consist of the following commands:
% chmod DataDir
Run this command with the identity of the UNIX user who owns the data directory. Also make sure that the server is running as that user, otherwise this command will not only exclude other users from the data directory (you want), but will also prevent the server from accessing your database (you do not).
Status files appear at the highest level of the data directory, just like the database directory, so you might think about whether the names of those files are confusing or mistaken for database names (for example, when the server is executing the s H O W database statement). The answer is: No. State and log information is stored in a file, and the database is a directory, so the executable program can distinguish them from a simple stat (the server tells them how to differentiate). If you are monitoring the data directory, you can distinguish the state file from the database directory by using LS-L, and check the first character of the schema information to see if it is '-' or ' d ':
You can also simply tell by looking at the name: All status filenames contain a period, but the database directory name has no period (the period is not the legal character of the database name).
For information about log file maintenance and looping techniques, see the contents of chapter 11th.
