MySQL data directory structure http://www.fanqiang.com (2001-05-14 03:28:00)
Conceptual speaking, most relational database systems are similar: they have a series of databases, each database contains a series of database tables, but each system has the data of their own organizations, MySQL is no exception. By default, all data managed by MySQL server MySQLD is stored in a place called the MySQL data directory, where all databases are stored, and include status files that provide server operation information. If you install a mysql installation management task, you should be familiar with the layout and use of the data directory. This article describes the following topics: How to determine the location of the data directory. How to organize and provide access to the database and the table it managed. Where can I find what content generated by the status file generated by the server. How to change the organization structure of the default location or data directory or individual database. 1. The location of the data directory A default data directory is compiled into the server. If you distribute mysql from one source code, the typical default directory is / usr / local / var, if you installed from the RPM file / var / LIB / MySQL, if the installation is installed from a binary distribution is / usr / local / mysql / data. In you start the server, you can specify the data directory location by using a -DATADIR = / path / to / dir option. This is useful if you want to place the data directory somewhere outside the other default location. As a mysql administrator, you should know where your data directory is. If you run multiple servers, you should go to all the data directories, but if you don't know the exact location, find it from a variety of ways: Use MySQLADMIN VARIABLES to get the data directory path from your server.
Find the value of the DATADIR variable, on UNIX, its output is similar to:% mysqladmin variables -------------------- -------- -------------- | Variable_name | Value | ---------------------- ------ ---------------- | Back_LOG | 5 | | CONNECT_TIMEOUT | 5 | | BASEDIR | / VAR / LOCAL / | | DATADIR | / USR / local / var / | ... On Windows, output may look like this: C: / mysqladmin variables ------------------------------- ------------- | Variable_name | Value | --------------------------- --------------- | Back_LOG | 5 | | CONNECT_TIMEOUT | 5 | | BASEDIR | C: / MySQL / | | DATADIR | C: / MYSQL / DATA / | .... If you have multiple servers running, they will listen on different TCP / IP ports or sockets, by providing a port or socket of the connection server is listening to -port or --socket option, you can turn Get every data directory information:% msqladmin --port = port_name variables% mysqladmin --socket = / path / to / socket variables mysqladmin command can run on any you can connect to the server from which you can connect, if you want to A remote host connection server, use a --host = host_name option:% mysqladmin --host = host_name variables on Windows, you can connect a command pipe connection with -pipe to force a command pipe connection and - Socket = PIPE_NAME specifies pipe names Monitor the NT server of a command pipe: c: / mysqladmin --Pipe - Socket = PIPE_NA ME Variables You can use the PS command to see any command line that is running the MySQLD process. Try one of the following commands - DataDir:% PS AXWW | GREP MYSQL BSD Stylish% PS-EF | GREP MYSQLD SYSTEM V style If your system runs multiple servers, the PS command may be particularly useful, because you can discover immediately Multiple data directory locations, the disadvantage is that you must run on the server, and may not be useful information, unless the -datadir option is explicitly specified in the MySQLD command line. If mysql is installed from one source code, you can check its configuration information to determine the data directory location. For example, the location can be obtained from the top Makefile, but attention is that the location is the localstatedir value in the makefile, not DATADIR, and if distributing the file system located in an NFS mounted and used to build mysql for multiple hosts, configuration information reflects distribution The most constructed host, which may not provide the data directory information of the host you interested.
If the above method fails, you can find a database file with find, the following command looks for ".frm" file, it is part of any mysql installation:% FIND / -NAME ".frm" -print In each case, use DATADIR MySQL data directory location. 2, the data directory structure The mysql data directory contains all the data directories managed by the server, which is organized into a tree structure, which is directly implemented by using the hierarchy of UNIX or Windows file systems. Each database corresponds to a directory in the data directory. The table in a database corresponds to the file in the data directory. The data directory also contains several status files generated by the server, such as log files. These files provide important information about server operations. Very valuable to manage the problem in trial of the problem. For example, if a particular query kills the server, you can check the log file to discriminate the query. 2.1 How does the mysql server provide access to data to everything in the data directory by a separate entity-Mysql server mysqld manner, the client program is never directly operating. Instead, the server provides data accessible entry points, which is the intermediary between the client and the data they want to use. When the server starts, it opens the log file if necessary, and then presents a network interface by listening to the network connection bit data directory. To access the data, the client establishes a connection to the server, then performs the desired operation with a MySQL query transfer request. The server performs each operation and sends the result back the user. The server is multi-threaded and can serve multiple simultaneous client connections. However, because the modification operation is performed, the actual effect is the sequential request so that the two customers will never change the same record at the same time. In normal cases, the unique arbitrator for the server as a database is provided to avoid the failure of the destruction of multiple processes that can access the database table simultaneously. Administrators should know that the server does not have dictatorships for the data directory. When you run multiple servers on a single data directory. General Ni Yunfeng's new server management host, but it is possible to run multiple servers. If this completes access to multiple independent data directories, there is no mutual impact, but you can start multiple servers and point to the same directory. Generally, this is not a good idea. If you try to do this, it is best that your system provides a good file lock function, otherwise the server will not work correctly. If you write multiple servers to log files, you also brave your log files are called the risk of chaos. When you run Isamchk and Myisamchk. Isamchk and Myisamchk utilities are used for table maintenance, diagnosis, and fixes, think you think, because these programs can modify the table content, allowing them to operate while being operated while being operated, which can cause table damage. It is important to understand how to limit this mutual impact so you will not damage your table. 2.2 Data Objects Represents the database of each MySQL server management has its own database table, which is a subdirectory in the data directory, which is the same as the database it represents. For example, the database my_db corresponds to the database directory DATADIR / MY_DB. This representation allows multiple database-level statements that are very simple in its implementation. CREATE DATABASE DB_NAME Create a DB_Name empty directory in the data directory, with only the primary and modes that allow the mysql server user (Unix users running server), which is equivalent to create a database in the server host:% mkdir dataDir / db_name % ChMOD 700 Dadadir / DB_NAME uses an empty directory to represent a new database for a new database and even create a large amount of control file or system file for an empty database. The DROP DATABASE statement is equally simple.
DROP DATABASE DB_NAME Deletes DB_NAME directories in the database, which is almost like the following commands:% rm -rf datadir / db_name (Difference server only deletes files with a suffix name that are known to the table. If you are in the database Directory creates other files. The server retains them, and the directory itself is not deleted. SHOW Database is basically nothing, just lists the directory names located in the data directory. Some database systems keep a primary table for maintaining all databases However, in MySQL no this component. Due to the simplicity of the data directory structure, the database list is implicit in the contents of the data directory, and such a table does not have to have additional overhead. 2.3 Database table representation of each database in the database directory There are 3 files: a style (description file), a data file, and an index file. The basic name of each file is the table name, the file name extension represents the file type. Extension is as follows. Data and index file extension It is pointed out that the table uses old IASM indexes or new Myisam indexes. Table mysql file type file type file name extension file content style file. FRM Description Table structure (its column, column type, index, etc.). Data file .ism (isam) Or .MYD (MyISAM) contains indexeds of all indexes on the data file. Index file .ism (isam) or .myi (MyISAM) The index file exists if there is an index. When you send a CREATE TABLE TBL_NAME statement When defining the structure of the table, the server creates a file named TBL_NAME.FRM, which includes internal encoding of the structure, and also creates an empty data and index files, which initializes information that contains no recorded and no indexes (if Create Table The statement includes index designation, the index file reflects these indexes). The primary and mode corresponding to the table's files are set to only allow MySQL server users to access. When you send an ALTER TABLE TBL_NAME statement, the server re-encodes TBL_NAME.FRM, And modify the contents of the data and index files to reflect the structural changes specified by the statement. It is also the same for Create INDEX and DROP INDEX because they are treated as three files corresponding to the ALTER TABLE Equivalent .drop Table. Although you can delete three files corresponding to the table in the database directory, you cannot manually create or modify a table, if MY_DB is the current database, Drop Table my_TBL is about to equivalent to the following command.% RM -RF DATADIR / MY_DB / MY_TBL. * SHOW TABLE MY_DB Output is just a foundation name of the .frm file in the MY_DB database directory. Some database systems have a registry, which lists all tables contained in a database, mysql is not because unnecessary, "Registry" is implicit in the structure of the data directory. 2.4 Operating System Limits for Databases and Table Names Mysql There is a principle for named databases and tables: Names can be composed of any alphanumeric characters in the current character set, and the underscore and US dollars can also. The name is up to 64 characters. However, because the names of the database and table correspond to the directory and file name, the server running the server can impose additional restrictions. First, the database and table names are limited to the characters legal for file names, such as allowed in the principle of MySQL, but if your operating system is not allowed, you cannot use it in the directory or table name.
In fact, this is not worried about UNIX or Windows, the biggest difficulty is to reference the name directly in the shell when performing the database management, for example, if you name a database such as $ my_db, contain a dollar, any from the shell The reference to this name may be interpreted by Shell to a reference to a variable:% ls $ my_db my_db: undefined variable, you must escape $ characters or use quotation marks from special meanings:% ls / $ my_db% ls $ my_db if You use quotation marks, you must use single quotes, and double quotes do not disable the variable interpretation. Second, although MySQL allows the database and table name to 64 characters, the length of the name is limited to the length of your operating system, which is generally not a problem (although the old SYSTEM V mandates 14 characters). In this case, your database name is 14 characters, and the table name is 10 characters, because the file name indicating the table has a point (.) And three characters extension. Third, the case sensitivity of the file system affects how you name and reference the database and table name. If the file system is case sensitive (such as UNIX), two name my_tbl and my_tbl are different tables. If the file system is not case sensitive (such as Windows), these two names refer to the same table. If you develop a database with a UNIX server and you should remember this if you have it possible to transfer to Windows. 2.5 MySQL status files In addition to the database directory, the mysql data directory also contains a lot of status files, which are summarized in the table below. The default name of most files is generated from the server hostname and is represented in the following table as HostName. Table mysql status file file type Default name file content process ID hostname.pid server process ID error log Hostname.err launch and closing events and errors General log Hostname.log connection / disconnect events and query information Update log Hostname.nnn Modify all query texts of the table Structure level When the server is started, it is written to the process ID (PID) file when it is turned off, and the file is deleted. The PID file is a tool that allows the server to be found by other processes. For example, if you run mysql.server, turn off the script of the MySQL server check the PID file when the system is turned off to determine which process it needs to issue a termination signal. The error log is created by SAFE_MYSQLD as the redirection of the server standard error output, which contains any evil to stderr messages. This means that only you start the server by calling the SAFE_MYSQLD, the error file exists (anyway, it is a best way to start the server, because if it exits due to an error, Safe_Mysqld will restart the server.).). The general logs and update logs are optional. You can only turn on the log type you need, with -log and --log-update server options. General logs provide general information for server operations: Who is connecting to the server and what query they sent. The update log provides query information, but only the query of the modification of the database content. The update log content is written as a SQL statement, which can be performed to the MySQL client. If you have a collapse, and you must reverse the backup file, the update log is useful, because you can repeat the update of self-crash, by feedback the update log to the server, this allows you to restore the database to crash when you crash .
Here is a simple example, information appears in the general log, it is a session created in the database TEST, inserts a line, then delete the table: 99050 7:37:09 492 Connect Paul @ localhost on test 492 query show Database 492 Field List TBL_1 492 Field List TBL_2 ... 990509 7:34:22 492 Query Create Table My_TBL (VAL INT) 990509 7:34:34 492 Query Insert Into My_TBL Values (1) 990509 7:34 : 38 492 Query Drop Table My_TBL 990509 7:34:40 492 Quit General log contains date and time, server process ID, event type, and event information section. The same session appears in the update log looks like this: use test; crete Table my_tbl (val int); INSERT INTO MY_TBL VALUES (1); DROP TABLE MY_TBL; for update logs, use -log-long-format options An extended form of logs, extended logs provide anyone who issues every query, use more disk space, but if you want to know what to do, don't use the update log to find the connection event to the content of the general log. For the above session, the extended update log produces such information: # Time: 990507 7:32:42 # User @ host: paul [paul] @ localhost [] use test; create table my_tbl (val int); # user @ host : Paul [paul] @ localhost [] insert @ Time: 990507 7:32:43 # user @ host: paul [paul] @ localhost [] drop table my_tbl; guarantee your log file security and Don't let any user read is a good idea. The general logs and update logs can include sensitive information such as passwords because they contain query text. Such as: 990509 7:23:31 4 Query Update User Set Password = password ("secret") where user = "root" See the "MySQL Security Guide" for checking and setting the permissions of the data directory. Make the data directory security instructions contain the following commands:% chmod 700 DataDir run this command with UNIX users with the data directory. Make sure the server also runs in this user, otherwise the command not only refuses to other people, but also prevents the server from accessing your database. The status file appears in the top-level directory of the data directory, just like the database directory, so you may worry about whether these file names are conflict with the database name or an error (when executing the Show Databases statement in the server). The answer is not. Status and log file information is stored in the file, and the database is a directory, so the executable can distinguish them with a simple STAT () call. If you look at the data directory, you can distinguish the status file and the database directory, the first character of the LS -L and check the mode is a "_" or a "D".
