MySQL Data directory structure

mysql| Data | Data Directory
MySQL Data directory structure

Blue Forest http://www.lslnet.com May 28, 2000 10:40


Author: Yan Zi



Conceptually, most relational database systems are similar: They have a series of database components, each containing a list of database tables, but each system has its own managed data way, MySQL is no exception.

By default, all data managed by the MySQL server mysqld is stored in a directory called MySQL data, where all databases are stored, and state files that provide information about server operations. If you perform administrative tasks on a MySQL installation, 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 the server organizes and provides access to the database and the tables it manages. Where to find the state files generated by the server memory what they contain. How to change the organizational 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 install MySQL from a source code, the typical default directory is/usr/local/var, if the installation from the RPM file is/var/lib/mysql, if the installation from a binary distribution is/usr/local /mysql/data.

When you start the server, you can explicitly 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 of the other default locations.

As a MySQL administrator, you should know where your data directory is. If you run multiple servers, you should be going to all the data directories where, but if you don't know the exact location, find it by multiple methods: Use Mysqladmin variables to get the data directory pathname from your server directly. Find the value of the DataDir variable, on UNIX, whose output is similar to the following:
%mysqladmin variables

+----------------------+----------------------+| variable_name | Value |+----------------------+----------------------+| Back_log | 5 | | Connect_timeout | 5 | | Basedir | /var/local/| | DataDir | /usr/local/var/| ....

On Windows, the output might 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 more than one server running, they will be listening on different TCP/IP ports or sockets, and by providing the--port or--socket options for the ports or sockets that the server is listening to, you can take turns getting each of their data directory information:

%msqladmin--port=port_name variables

%mysqladmin--socket=/path/to/socket variables

The mysqladmin command can be run on any host you can connect to the server, if you want to connect to the server in a remote host, use a--host=host_name option:

%mysqladmin--host=host_name variables

On Windows, you can use--pipe to force a command pipe connection and--socket=pipe_name the specified pipe name to connect to an NT server that listens on a command pipe:

C:\mysqladmin--pipe--socket=pipe_name variables
You can use the PS command to view any command line that is running the mysqld process.
Try one of the following commands and look for--datadir:

%ps AXWW | grep MySQL BSD style

%ps-ef | grep mysqld System v Style

If your system is running multiple servers, the PS command can be especially useful because you can immediately discover multiple data directory locations, with the disadvantage that you must be running on the server and that you may not have useful information to produce unless you explicitly specify the--datadir option on the mysqld command line.
If MySQL is installed from a source code distribution, you can check its configuration information to determine the location of the data directory. For example, a location can be obtained from a top-level makefile, but note that the location is a Localstatedir value in makefile, not a datadir, and that if you distribute a file system located in an NFS mount and use it to build MySQL for multiple hosts, Configuration information reflects the most recently built hosts for distribution, which may not provide data directory information for the host you are interested in. If the above method fails, you can find the database file using search, and the following command looks for the ". frm" file, which is part of any MySQL installation:

% Find/-name ". frm"-print

In the following examples, the MySQL Data directory location is represented by DataDir. 2. Data directory structure
The MySQL data directory contains all the data directories managed by the server, which are organized into a tree-like structure, implemented directly by leveraging the hierarchy of UNIX or Windows file systems. Each database corresponds to a directory under the data directory. The tables in one database correspond to the files in the data directory.
The data directory also contains several state files generated by the server, such as log files. These files provide important information about server operations. It is valuable for management to try to identify the cause of the problem, especially when there is a problem. For example, if a particular query kills a server, you can check the log file to identify disruptive queries.
2.1 How the MySQL server provides access to the data
Everything under the data directory by a single entity-mysql Server MYSQLD Management, the client program does not directly manipulate the data. Instead, the server provides a pointcut for data accessibility, which is the intermediary between the client program and the data they want to use.

When the server is started, it opens the log file, if necessary, and then renders a network interface by listening to the network connection bit data directory. To access data, the client establishes a connection to the server and then executes the desired operation with the MySQL query transfer request. The server performs each operation and sends the results back to the user. The server is multi-threaded and can serve multiple simultaneous customer connections. However, because the modification operation is one execution, the actual effect is to order the request so that two customers must not change the same record at the same time.

In normal circumstances, having the server as the sole arbiter of database access provides a guarantee against the destruction of multiple processes that can access database tables concurrently. Administrators should know that sometimes the server has no authoritarian control over the data directory. When you run multiple servers on a single data directory. Generally Nuyun all databases on the new server management host, but it is possible to run multiple servers. If this completes providing access to multiple independent data directories, there is no interaction problem, but yo can also start multiple servers and point to the same directory. In general, this is not a good idea. If you try this, preferably your system provides good file locking functionality, otherwise the server will not collaborate properly. If you write multiple servers into a log file at the same time, you also risk your log file as the source of the confusion. When you run Isamchk and Myisamchk. Isamchk and Myisamchk utilities are used for table maintenance, diagnostics, and repairs, as you would think, because these programs can modify table content, allowing them to manipulate the table while the server is operating, which can cause the table to be corrupted. It is important to understand how to limit this interaction so that you do not damage your watch. 2.2 Data representation
Each MySQL server manages a database that has its own database table, a subdirectory under the data directory that has the same name 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 to be simple in their implementation. Create DATABASE db_name creates a db_name empty directory in the data directory that has the owner and mode of only the MySQL server user (the UNIX user running the server), which is equivalent to the following manual creation of databases on the server host:

%mkdir Datadir/db_name%chmod Dadadir/db_name

The simplest way to represent a new database with an empty directory is just the opposite of creating a large number of control files or system files for an empty database.

The DROP database statement is also simple to implement. The drop database db_name deletes the db_name directory and all table files in the databases, almost like the following commands:

%RM-RF Datadir/db_name

(The difference is that the server deletes only files that have known suffix names for the table.) If you create additional files in the database directory. The server retains them, and the directory itself is not deleted.

Show database basically doesn't do anything, just lists the directory names that are located in the data directory. Some database systems maintain a master table that maintains all databases, but does not have this widget in MySQL. Given the simplicity of the data directory structure, the list of databases is implied in the contents of the data directory, and such tables do not have to incur additional overhead.
2.3 Representation of database tables
Each database has 3 files in the database directory: a style (description file), a data file, and an index file. The base name for each file is the table name, and the filename extension represents the file type. The extension is the following table. The extension of the data and index file indicates that the table uses an older IASM index or a new MyISAM index.
Table MySQL file type file type filename extension file content Style file. frm describes the structure of the table (its columns, column types, indexes, and so on). Data files. ISD (ISAM)
Or. MyD (MyISAM) contains the index tree of all indexes on the data file. index files. ISM (ISAM)
Or. Myi (MyISAM) The index file relies on whether the table has an index.
When you emit a CREATE table tbl_name when the statement defines the structure of the table, the server creates a name called Tbl_ A name.frm file that includes an internal encoding of the structure, as well as an empty data and index file, initialized to include information that indicates no records and no indexes (indexed files reflect these indexes if the CREATE TABLE statement includes index designations). The owner and mode of the file corresponding to the table is set to allow only MySQL server users to access it.

When you issue an ALTER TABLE TBL_NAME statement, the server encodes the TBL_NAME.FRM and modifies the contents of the data and index files to reflect the structural changes specified by the statement. The same is true for CREATE index and drop index, as they are considered equivalent to alter TABLE by the server. The drop table is implemented by deleting three files corresponding to the table.

Although you can delete three files in the database directory that correspond to the tables, but you cannot create or modify a table manually, for example, if my_db is the current database, DROP table my_tbl is probably equivalent to the following command.

% RM-RF datadir/my_db/my_tbl.*

The output of Show TABLE my_db only lists the base file name of the. frm file in the my_db database directory. Some database systems have a registry that enumerates all the tables contained in a database, MySQL is not, because it is not necessary, the "registry" is implied in the structure of the data directory.
2.4 Restrictions on database and table naming by the operating system
MySQL has a principle for naming databases and tables: Names can consist of any alphanumeric character in the current character set, underlined and $ $. The first name is 64 characters long.
However, because the database and table names correspond to directories and file names, the operating system that the server is running may impose additional restrictions.

First, the database and table names are limited to characters that are valid for file names, such as $ is allowed in the MySQL principle, but you cannot use it in the directory or table name if your operating system is not allowed. In fact, this is not a concern for UNIX or Windows, and the biggest difficulty is referencing names directly in the shell when performing database management, for example, if you name a database such as $my_db, which contains a dollar character, Any reference to that name from the shell may be interpreted by the shell as a reference to a variable:

%ls $my _db
my_db:undefined variable

In this respect, you must escape the $ character or use quotation marks to prohibit its special meaning:

%LS \ $my _db
%ls ' $my _db '

If you use quotes, be sure to use single quotes, and double quotes do not prohibit variable interpretation.

Second, while MySQL allows database and table names to be up to 64 characters long, the length of the name is limited by the length of your operating system, generally this is not a problem (although the old system V Forces 14 characters). In this case, your database name is capped at 14 characters, and the table name has a maximum of 10 characters because the name of the table has a point (.) and a three-character extension.

Third, the case sensitivity of file systems affects how you name and reference database and table names. If the file system is case sensitive (such as Unix), two names 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 are developing a database with a UNIX server, and if you are likely to move to Windows, you should keep this in mind.
2.5 MySQL State file
In addition to the database directory, the MySQL data directory contains a number of status files, which are summarized in the following table. The default name for most files is generated from the server hostname, which is represented in the following table as hostname.
Table MySQL state file type default name file content process IDHOSTNAME.PID server process ID error log Hostname.err startup and shutdown events and error conditions general log HOSTNAME.log Connection/ Disconnect event and query Information update log hostname.nnn All query text that modifies table structure-level content
When the server starts, it writes its process ID to the process ID (PID) file, and when it shuts down, deletes the file. A PID file is a tool that allows the server itself to be found by other processes. For example, if you run Mysql.server, when the system shuts down, the script that closes the MySQL server checks the PID file to determine which process it needs to send a termination signal to.

The error log is created by Safe_mysqld as a redirect to the server standard error output, which contains any messages that are evil to stderr. This means that only if you start the server by calling Safe_mysqld, the error file exists (anyway, it is the best way to start the server, because if it exits because of an error, SAFE_MYSQLD will reboot the server.) ）。

The general and update logs are optional. You can just turn on the type of log you need, using the--log and--log-update server options.
General logs provide general information on server operations: who connects servers from where and what queries they issue. The update log provides query information, but only queries that modify the contents of the database. The update log content is written as an SQL statement that can be delivered to the MySQL client for execution. If you are in a crash and you have to rewind the backup file, the update log is useful because you can repeat the update from the crash and feed the update log back to the server, which allows you to restore the database to the state when the crash occurred.

The following is a simple example where information appears in the general log, which is a session that creates a table in database test, inserts a row, and then deletes the table:

9905097:37:09492 connectpaul@localhost on test492 queryshow databases492 queryshow tables492 field List tbl_1492 field Lis T tbl_2...9905097:34:22492 querycreate TABLE my_tbl (Val INT) 9905097:34:34492 Queryinsert into my_tbl values (1) # 9,905,097:3 4:38492 Querydrop TABLE my_tbl9905097:34:40492 Quit

The general log contains the date and time, the server process ID, the event type, and the event information column.

The same session appears in the update log to look like this:

Use test; CREATE table My_tbl (val int), INSERT into My_tbl VALUES (1);D ROP table my_tbl;

For the update log, use the--log-long-format option to obtain an extended form of the log, which provides information about who will issue each query, which uses more disk space, but if you want to know who is doing it, instead of finding the connection event against the contents of the general log, update the log.

For the session above, 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 into My_tbl VALUES (1); # time:990507 7:32:43# User@host:paul [Paul] @ localhost []drop TABLE my_tbl;

It's a good idea to keep your log files safe and not to allow any users to read them. Both the general and update logs can contain sensitive information, such as passwords, because they contain the query text. Such as:

9905097:23:314 queryupdate user SET Password=password ("secret") WHERE user= "root"

For permissions to check and set the data directory, see the MySQL Security Guide. The instructions that make the data directory security include the following commands:

% chmod DataDir

Run this command with the UNIX user who owns the data directory. Make sure that the server is also running with this user, otherwise the command will not only shut out other people, it also prevents the server from accessing your database.

Status files appear in the top-level directory of the data directory, just like the database directory, so you may be concerned about whether the file names conflict with the database name (such as when a show databases statement is executed on the server). The answer is no. State and log file information is stored in a file, and the database is a directory, so executables can differentiate them with a simple stat () call. If you look at the data directory, you can distinguish between state files and database directories, using ls-l and checking that the first character of the pattern is an "_" or a "D".

You can also simply look at the name, all the status file names contain a point ("."), and the database directory does not (. is not a valid character in the database name).

3 Relocate Database Directory
The data directory structure discussed earlier is the default configuration, and all databases and state files are included, however, you have some freedom to determine the location of the contents of the data directory, and this section discusses why you can move some of the data directories (or even the directory itself), what you can remove, and how you do the changes

MySQL allows you to reposition a data directory or a member of it, for several reasons why you do this: you can place the data directory on a larger file system with the file system that you default to. If your data directory is on a busy hard drive, you might put it on a less busy disk to balance disk activity. You can place the database and log files on separate disks or across disks. You may want to run multiple servers, each with its own data directory, which is one way to resolve each process file descriptor constraint, especially if you cannot reconfigure the kernel to allow for higher limits. Some systems save part of the server's files, such as/var/run, and you might want to put MySQL's PID file there for consistency in system operations.
3.1 Relocation Method
There are two ways to reposition the contents of a data directory: You can specify options at the server startup, either on the command line or in [mysqld] in an option file. You can remove what you want to reposition and then make a symbolic connection to the new location in the original location.
Neither method solves everything you can reposition, and the following table summarizes what can be repositioned and which method to reposition. If you use the option file, it is possible to specify options in the Global options file/etc/my.cnf (c:\my.cnf on Windows). The current version of Windows also looks for the system directory (c:\Windows or C:\NT).
Reposition method for reposition method of table reposition method the entire Data directory startup option or symbol connection single database directory symbol connection single database table symbol connection PID file startup option general log startup options update log startup options
You can also use the option file My.cnf in the default data directory, but the file is not recommended for use. If you want to reposition the data directory itself, you have to make the default data directory readable so that you can place an option file here specifying where the server should find the "real" Data Directory! It's confusing. If you want to use an option file to specify server options, it is best to use/ETC/MY.CNF.
3.1 Inspection of the effect of relocation
Before attempting to reposition anything, it is a good idea to verify that the operation achieves the desired effect. Use the Du, DF, and LS-L commands to get information about disk space, but these rely on you to correctly understand the layout of your file system.

The following shows a design trap when you examine a generic directory relocation. Suppose your data directory is/usr/local/var and you want to move it to/var/mysql because DF shows/var file system has a lot of free space:


%df/usr/varfilesystem 1k-blocks Used Avail Capacity mounted on/dev/wd0s3e 396895 292126 73018 80%/usr/dev/wd0s3f 118935 9 1111924 162287 15%/var

How much free space does the relocated data directory have on the/usr file system? To know it, use du-s to find out how much space the directory uses.

%cd/usr/local/var%du-s. 133426

This is about 130MB, is it true? Try df in the Data directory:

%df/usr/local/varfilesystem 1k-blocks Used Avail Capacity mounted on/dev/wd0s3f 1189359 1111924 162287 15%/var

That's weird. If we apply for free space for the file system that contains/usr/local/var, why do we report the space on Var? Here Ls-l provides the answer:

%ls-l/usr/local....lrwxrwxrwx 1 root wheel Dec 23:33 var->/var/mysql ....

The output shows that the/usr/local/var is a symbolic connection to the/var/mysql, in other words, the data directory has been relocated to the/var file system and replaced with a symbolic connection pointing there. By moving the data directory to/var, there is so much room on/usr freed!

3.2 Relocation Data Directory
To reposition the data directory, shut down the server and move the data directory to a new location, then you should remove the data directory and replace it with a symbolic connection to the new location, or restart the server with the option to explicitly indicate the new location. The following table lists the command lines and options at the specified location.
Table Data Directory Relocation syntax option source syntax command line--data-dir=/path/to/dir option file [mysqld]
datadir=/path/to/dir3.3 Relocation Database
The database can be removed by means of a symbolic connection. To relocate a database, shut down the server and remove the database directory and delete the original database directory, replace it with a symbolic connection to the new location, and then reboot the server.

The following example shows how you can move a database bigdb to a different location:

%mysqladmin-u root-p shutdownenter Password: ******%cd datadir%tar cf-bigdb | (cd/var/db, Tar XF-)%mv bigdb bigdb.origln-s/var/db/bigdb.%safe_mysqld

You should execute these commands as the owner of the data directory. For security purposes, the original database directory is renamed Bigdb.orig. After you verify that the server is working properly, you can delete the original data directory.

%RM-RF Bigdb.orig

3.4 Reposition Database tables
Relocating a separate table is not a good idea. You can do this by moving the table files to a different place and creating a symbolic connection to those files in the data directory. However, if you issue an ALTER TABLE or a optimize table statement, your modifications will not be made.

Each statement creates a temporary table in the database directory that implements your modification or optimization. Then delete the original table and rename the temporary table to the original table, as a result, your symbolic connection is deleted, and the new table is returned to the database directory, which is the location of the original table file before you remove it. What's worse, you don't realize that they're there, they continue to occupy space, and the symbolic connections have been broken so that when you realize what's happening, if you forget where you moved them, you may not have a good way to track the files.
Because it is difficult to ensure that people with table access do not modify or optimize tables, it is a good idea to leave the table in the database directory.

3.5 Reposition State File
You can reposition the PID file, the general log, and the update log. The error log is created by Safe_mysqld with the startup option and cannot be relocated (unless you edit safe_mysqld).

To write a status file in a different location, shut down the server, and then start it by specifying the appropriate option for the new status file location. The following table lists the syntax for the command line and option files for each file.
Table state File Relocation syntax option source Syntax command line--pid-file=pidfile
--log=lodfile
--log-update=updatefile option file [mysqld]
Pid-file=pidfile
Log=lodfile
Log-update=updatefile
If you specify a status file with an absolute path name, create the file with that path, otherwise the file is created in the data directory. For example, if you specify--pid-file=/var/run/mysqld.pid,pid file is/var/run/mysqld.pid. If you specify-pid-file=mysqld.pid,pid file is datadir/mysqld.pid.

If you specify an update log file without an extension, MySQL produces a sequential name each time it opens the update log. These names are in an extension. nnn, here. NNN is the first number (such as update.000,update.001, etc.) that has not been used by the existing update log. You can override the order name by explicitly specifying the extension, at which point the server will use only the specified name.