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 a source code, a typical default directory is / usr / local / var, if you installed from the RPM file for / var / lib / mysql, if A binary distribution is installed / 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 be where all the data directories are, but if you don't know the exact location, find it by a variety of ways:
Use mysqladmin variables to get the data directory path name 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 / |
.... In Windows, output may look like this: c: mysqladmin variables -------------------- ------- ---------------
| variable_name | Value |
--------------------------------------------
| Back_LOG | 5 |
| Connect_timeout | 5 |
| Basedir | C: MySQL |
| DATADIR | C: MySQLDATA |
.... If you have multiple servers running, they will listen on different TCP / IP ports or sockets, by providing the port or socket of the connection server is listening --port or - Socket option You can get through the data directory information of each of them:% msqladmin --port = port_name variables% mysqladmin - Socket = / path / to / socket variables mysqladmin command can run on any of the host you can connect to the server, If you want to connect 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 specified Pipe name to connect NT servers for monitoring a command pipe: c: mysqladmin --pipe - Socket = PIPE_NAME 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 is looking for ".frm" file, it is part of any mysql installation:% FIND / -NAME .FRM -PRINT In each case, use DATADIR to represent mysql data The directory location. 2, data directory structure
The MySQL data directory contains all the data directories managed by the server, which are organized into a tree structure that is directly implemented by 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 to provide access to data
Everything in the data directory is managed by a separate entity-Mysql server mysqld, 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 purpose
The database managed by each MySQL server 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 Creates a DB_NAME empty directory in the data directory, with only the primary and modes of the MySQL server user (running server's UNIX users), which is equivalent to create a database on the server host:
% MKDIR DATADIR / DB_NAME
% Chmod 700 Dadadir / DB_Name
The easiest way to use a null directory representing a new database is the opposite of the other database or even create a large number of control files or system files for an empty database.
The DROP DATABASE statement is equally simple. DROP DATABASE DB_NAME Deletes DB_NAME directories in the database and all table files, which is almost the same as the following commands:
% RM-RF DATADIR / DB_NAME
(Differences are files that only delete the suffix names with known use to the table. If you create other files in the database directory. 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 maintain a primary table for maintaining all databases, but no this component in MySQL. 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 overhead.
2.3 Representation of the database table
Each database has 3 files in the database directory: 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. The extension is as follows. The extension of the data and index files indicates that the table uses an old IASM index or a new MyISAM index.
Table MySQL file type file type file name extension file content style file. The structure of the FRM Description Table (its column, column type, index, etc.). Data file .ISD (isam) or .myd (MyISAM) contains indexeds of all indexes on the data file. Index files .ism (isam) or .myi (MyISAM) The index file exists if there is an index. When you issue a CREATE TABLE TBL_NAME, the server creates a file called TBL_NAME.FRM, which includes internal encoding of the structure, and also creates an empty data and index files, which initializes the inclusion indicating that there is no record And no indexed information (if the CREATE TABLE 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 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 because they are treated by the server as equivalent to Alter Table. DROP TABLE is implemented by deleting three files corresponding to the table.
Although you can delete the 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 the following commands.
% rm -rf datadir / my_db / my_tbl. *
The output of Show Table My_DB 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 has a principle for named databases and tables:
Names can be composed of any alphanumeric characters in the current character set, and the underscore and dollar values can be available. 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 the shell as a reference to a variable:
% ls $ my_dbmy_db: undefined variable
In this regard, you must escape $ characters or use quotation marks to ban its special meaning:
% LS $ MY_DB% LS $ MY_DB
If you use quotation marks, you must use single quotes, and double quotes don't disable the interpretation of variables.
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 file
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 IDHostName.pid server process ID error log Hostname.err Start and close events and error conditions General log Hostname.log connection / disconnection event and query information Update log Hostname.nnn modification All query texts for table structure level content
When the server starts, it writes its process ID into the process ID (PID) file, and the file is deleted when it is closed. 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. The information appears in the general log. It is a session that creates a table in the database Test, inserts a line, and then deletes the table:
990509 7:37:09 492 Connect Paul @ localhost on test
492 Query Show Databases
492 Query Show Tables
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
The general log contains the date and time, the server process ID, the event type, and the event information section.
The same session appears in the update log looks like this:
Use test;
CREATE TABLE MY_TBL (VAL INT);
INSERT INTO MY_TBL VALUES (1);
DROP TABLE MY_TBL;
For updated logs, use -log-long-format options to get an extension form, extended log provides whom to send each query, this uses more disk space, but if you want to know what to do, And don't use the update log to find the connection event to the content of the general log. For the above session, the extension 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;
Make sure your log file is safe and not allowing any user to 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
For the permissions for checking and setting up the data directory, see the Mysql Security Guide. Make the data directory security instructions contain the following commands:
% Chmod 700 DataDir
Run this command with UNIX users with a 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, using ls -l and check the first character of the mode is a _ or a D.
You can also simply look at the name, all status file names contain a point (.), And the database directory is not (. In the database name is invalid).
3 relocation database directory
The data directory structure discussed earlier is the default configuration. All databases and status files contain, however, you have some freely determines the content of the data directory, this section discusses why you may remove some data directories (or even the directory itself ), What you can remove and how you do these changes.
MySQL allows you to relocate the data directory or the members, why are you doing this:
You can put the data directory on the file system that you default, a larger capacity of the file system. If your data catalog is on a busy hard disk, you may put it on a less busy disk to equalize disk activity. You can put the database and log files on separate disks or distributed across disk. You may want to run multiple servers, each has its own data directory, which is a way to solve the problem of each process file descriptor, especially you can't reconfigure the kernel to allow higher limits. Some systems saved some files such as / var / run, you might want to put the mysql's PID files in that, for system operation consistency.
3.1 Relocation Method
There are two ways to relocate the contents of the data directory:
You can specify an option when you start, or in the command line or in [MySQLD] of an option file. You can remove something you want to relocate, and then make a symbolic connection pointing to the new location in the original location.
Both methods can solve everything you can relocate, the following table summarizes what can relocate and use which method relocation. If you use an option file, it is possible to specify an option in global options file /etc/my.cnf (c: my.cnf on Windows). The current Windows version is also looking for system directories (C: Windows or C: NT). Table relocation Method relocation method Applicable relocation method The entire data directory start option or symbol connection single database directory symbol connection single database table symbol connection PID file launch option General log boot option update log boot option
You can also use the options file my.cnf in the default data directory, but not recommended to use this file. If you want to relocate the data directory itself, you have to let the default data directory can be read to make you place an option file here to specify the server where to find the "real" data directory! This is very confusing. If you want to use an option file to specify the server option, it is best to use /etc/my.cnf.
3.1 Effect of checking heavy positioning
It is a good idea to reach the expected effect before trying to relocate anything. Get information about disk space by means of DU, DF and LS -L commands, but these dependencies you correctly understand your file system layout.
The following demonstrates a design trap when you verify a directory. Suppose your data directory is / usr / local / var, and you want to move it to / var / mysql because the DF display / var file system has a lot of free space:
% DF / USR / VAR
FileSystem 1k-blocks Used Avail Capacity Mounted ON
/ DEV / WD0S3E 396895 292126 73018 80% / usr
/ DEV / WD0S3F 1189359 1111924 162287 15% / var
How many free space is there in / usr file system? To know it, use du -s to find out how much space used.
% CD / USR / Local / Var
% du -s.
133426
This is about 130MB, is it true? Try a DF in the data directory:
% DF / USR / LOCAL / VAR
FileSystem 1k-blocks Used Avail Capacity Mounted ON
/ DEV / WD0S3F 1189359 1111924 162287 15% / var
This is strange. If we apply for an idle space for a file system containing / usr / local / var, why report the space on the VAR? Here LS -L provides an answer:
% LS -L / USR / LOCAL
....
LRWXRWXRWX 1 root wheel 10 dec 11 23:33 var -> / var / mysql
....
Output display / usr / local / var is a symbolic connection to / var / mysql, in other words, the data directory has been relocated in the / var file system and replaced with a symbolic connection to there. It is so much space to release the data directory to / var.
3.2 Reling data catalog
To relocate the data directory, turn off the server and move the data directory to a new location, and then you should delete the data directory and replace it with a symbolic connection pointing to the new location, or use the option to clear the new location to restart the server. The following table lists the command lines and options for the specified location.
Table Data Directory Location Syntax Option Source Syntax Command Line - Data-Dir = / Path / To / DIR Options File [mysqld] DataDir = / path / to / dir
3.3 Relocation Database
The database can be removed by the method of symbolic connection. To relocate a database, turn off 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 restart the server. The next example shows how you move a database BigDB to a different place:
% mysqladmin -u root -p shutdown
ENTER password: ******
% CD DATADIR
% TAR CF - BIGDB | (CD / VAR / DB; TAR XF -)
% MV BigDB Bigdb.orig
Ln -s / var / db / bigdb.
% SAFE_MYSQLD
You should execute these commands with the owner of this data directory. For safety, the original database directory is named BIGDB.ORIG. After you verify that the server is working properly, you can delete the original data directory.
% RM -RF BIGDB.ORIG
3.4 Relocation Database Table
Relocating a separate table is not a good idea. You can move the table file to a different place and create a symbolic connection to these files in the data directory. However, if you send an ALTER TABLE or OPTIMIZE TABLE statement, you will not make your changes.
Each statement is done by creating a temporary table that implements you modify or optimize in the database directory and then deletes the original table and rename the temporary table. The result is that your symbol connection is deleted, and the new table is back. To the database directory, this is the original table file location before you remove. Worse, you haven't realized that they are there, continue to occupy space, and symbolic connection has been destroyed, so when you realize what happens, if you forget you to move them, you may There is no good way to track files. Because it is difficult to ensure that people with table access do not modify or optimize the table, the table is best to stay in the database directory.
3.5 Relocation status file
You can focus on positioning the PID file, the general log, and update the log. The error log is created by SAFE_MYSQLD with the boot option, and cannot be relocated (unless you edit the Safe_Mysqld).
To write status files in a different location, turn off the server, then start it by the appropriate options that specify the new status file location. The following table lists the syntax of the command lines and options files for each file.
Table status file relocation syntax option Source Symptory command line - pid-file = pidfile - log = lodfile - log-update = updatefile option file [mysqld] PID-file = pidfilelog = LodFilelog-update = updatefile
If you specify a status file with an absolute path name, create a file with the path, otherwise the file is created under the data directory. For example, if you specify - pid-file = / var / run / mysqld.pid, the PID file is /var/run/mysqld.pid. If you specify -PID-file = mysqld.pid, the PID file is DATADIR / mysqld.pid.
If you specify an update log file without an extension, MySQL generates a sequence name when it opens the update log. These names use an extension .nnn, here .nnn is the first number that has not been used by existing update logs (such as Update.000, Update.001, etc.). You can override the order name by clearly specifying the extension, and the server will only use the specified name.
