Shulou(Shulou.com)05/31 Report--

Mysql data directory structure is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Mysql data directory structure [@ more@] conceptually, most relational database systems are similar: they are made up of a series of databases, each database

Contains a series of database tables, but each system has its own way of organizing and managing data, and mysql is no exception.

By default, all data managed by the mysql server mysqld is stored in a place called the mysql data directory

Where all databases are stored, including status files that provide server operation information. If you are safe with a mysql

To perform administrative tasks, you should be familiar with the layout and purpose of the data directory.

This article introduces the following topics:

How to determine the location of data directories.

Conceptually, most relational database systems are similar: they consist of a series of databases, each of which

Contains a series of database tables, but each system has its own way of organizing and managing data, and mysql is no exception.

By default, all data managed by the mysql server mysqld is stored in a place called the mysql data directory

Where all databases are stored, including status files that provide server operation information. If you are safe with a mysql

To perform administrative tasks, you should be familiar with the layout and purpose of the data directory.

This article introduces the following topics:

How to determine the location of data directories.

How the server organizes and provides access to the database and the tables it manages.

Where to find the status files generated by the server to remember what they contain.

How to change the organizational structure of the default location or data directory or separate database.

1. The location of the data directory

A default data directory is compiled into the server, and if you install mysql from a source code distribution, the typical default

The provincial directory is / usr/local/var, if installed from a RPM file, / var/lib/mysql, if separated from a binary

Send and install / usr/local/mysql/data.

When you start the server, you can specify the data directory bits explicitly by using a-- datadir=/path/to/dir option

Buy. This is useful if you want to place the data directory somewhere other than the default location.

As a mysql administrator, you should know where your data directory is. If you are running multiple servers, you

It should be to where all the data directories are, but if you don't know the exact location, there are several ways to find it:

Use mysqladmin variables to get the data directory pathname directly from your server. Find the datadir variable

And on Unix, the output looks like this

% 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:mysqldata |.

If you have multiple servers running, they will listen on different TCP/IP ports or sockets by providing a connection

Follow the-port or-- socket options of the port or socket that the server is listening on, and you can take turns to get each of them

Data catalog information:

% msqladmin-- port=port_name variables

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

The mysqladmin command can be run on any host from which you can connect to the server, if you want to run it on a remote host

To connect to the server, use a-- host=host_name option:

% mysqladmin-- host=host_name variables

On Windows, you can force a command pipe connection and-- socket=pipe_name by using-- pipe

Set a pipe name to connect to the NT server that listens to a command pipeline:

C:mysqladmin-pipe-socket=pipe_name variables

You can use the ps command to view any command line that is running a 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 may be particularly useful because you can find multiple data directories immediately.

Location, the disadvantage is that it must be run on the server, and no useful information may be generated, except on the mysqld command line

The-- datadir option is explicitly specified.

If mysql is installed from a source code distribution, you can check its configuration information to determine the location of the data directory. Example

For example, the location can be obtained from the top-level Makefile, but note that the location is the localstatedirvalue in Makefile, no

Is datadir, and if you distribute a file system on a NFS mount and use it to build mysql for multiple hosts, match

The setting information reflects the distribution of newly built hosts, which may not provide data catalog information for the hosts you are interested in.

If the above method fails, you can use find to find the database file, and the following command looks for the ".frm" file, which is any

Part of he mysql installation:

% find /-name ".frm"-print

In the following examples, DATADIR is used to represent the location of the mysql data directory.

2. Data directory structure

The mysql data directory contains all the data directories managed by the server, and these files are organized into a tree structure

It is directly realized by making use of the hierarchical structure of Unix or Windows file system.

Each database corresponds to a directory under the data directory.

The tables in a database correspond to the files in the data directory.

The data directory also contains several status files generated by the server, such as log files. These documents provide information about the service

Important information about device operation. It is valuable for management, especially when something goes wrong and tries to determine the cause of the problem. For example, if some

A specific query kills the server, and you can identify disruptive queries by checking log files.

2.1 how does the mysql server provide access to data

Everything under the data directory is managed by a separate entity-mysql server mysqld, and clients never operate directly

To make data. Instead, the server provides an entry point for data access, which is between the client program and the data they want to use

Intermediary.

When the server starts, if necessary, it opens the log file and then presents the network connection bit data directory by listening

Now there is a network interface. To access the data, the client establishes a connection to the server and then transmits it with a mysql query

Request to perform the desired operation. The server performs each operation and sends the results back to the user. The server is multithreaded and can

Serve multiple simultaneous customer connections. However, because the modification operation is executed one by one, the actual effect is to serialize the request to

So that two customers can never change the same record at the same time.

Under normal circumstances, letting the server act as the sole arbiter of database access provides a way to avoid simultaneous access to data from

The guarantee of the destruction of multiple processes of the library table. Administrators should know that sometimes the server does not have dictatorial control over the data directory.

When you run multiple servers on a single data directory. Generally speaking, Ni Yun's new server manages all the servers on the host.

Database, but it is possible to run multiple servers. If this completes providing access to multiple independent data directories, there is no

Problems with interaction, but you can also start multiple servers and point to the same directory. Generally speaking, this is not a good idea.

If you try to do this, it is best if your system provides good file locking, otherwise the server will not be able to cooperate correctly.

If you write multiple servers to log files at the same time, you also run the risk of calling your log files a source of confusion.

When you run isamchk and myisamchk. The isamchk and myisamchk utilities are used for table maintenance, diagnosis, and

Fix, as you might think, because these programs can modify the contents of the table to allow them to operate at the same time as the server

Operate on the table, which can lead to table corruption. It's important to understand how to limit this interaction so that you don't damage you.

Donovan's watch.

2.2 data item representation

Each database managed by the mysql server has its own database table, which is a subdirectory under the data directory, which

The name 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 to be very simple in its implementation. CREATE DATABASE db_name in

Create an empty db_name directory in the data directory with only mysql server users (Unix users running the server)

Which is equivalent to manually creating the database on the server host as follows

Mkdir DATADIR/db_name%chmod 700 DADADIR/db_name

The easiest way to represent a new database with an empty directory is to create a large database with other databases or even an empty database.

The amount of control files or system files is just the opposite.

The implementation of the DROP DATABASE statement is equally simple. DROP DATABASE db_name deletes the db_ name item in the database

Record and all table files, almost the same as the following command:

% rm-rf DATADIR/db_name

(the difference is that the server deletes only files with suffixes known to be used for tables. If you create it in the database directory

It's a file. The server retains them, and the directory itself is not deleted.

SHOW DATABASE basically does nothing but list the directory names that are located in the data directory. Some database systems

Maintain a master table for maintaining all databases, but there is no such artifact in mysql. Because of the simplicity given to the data directory structure, the database list is implicit in the contents of the data directory, and such tables do not have to have additional overhead.

2.3 representation of database tables

Each database has three files in the database directory: a style (description file), a data file, and a

Index file. The base name of each file is the table name, and the file name extension represents the file type. The extension is shown in the following table. Data and

The extension of the index file indicates that the table uses either the old IASM index or the new MyISAM index. Table mysql file type

File type file name extension file content

The style file .frm describes the structure of the table (its columns, column types, indexes, and so on).

Data file .ISD (ISAM)

Or .MYD (MyISAM) an index tree that contains all indexes on a data file.

Index file .ISM (ISAM)

Or .MYI (MyISAM) the index file depends on whether the table has an index.

When you issue a CREATE TABLE tbl_name statement that defines the structure of the table, the server creates a table named

The tbl_name.frm file, which includes the internal coding of the structure, and also creates an empty data and index file, the initial

To contain information indicating no record and no index (if the CREATE TABLE statement includes an index assignment, the index file reflects

Out of these indexes). The owner and mode of the file corresponding to the table is set to allow access only to mysql server users.

When you issue an ALTER TABLE tbl_name statement, the server re-encodes the tbl_name.frm and modifies the number

According to and index the contents of the file to reflect the structural changes specified by the statement. The same is true for CREATE INDEX and DROP INDEX

Because they are considered by the server to be equivalent to ALTER TABLE. DROP TABLE implements by deleting three files corresponding to the table

Now.

Although you can delete three files corresponding to tables in the database directory, you cannot manually create or modify a table

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 simply lists the base file name of the .frm file in the my_db database directory. Some numbers

According to a registry of the library system, enumerating all the tables contained in a database, mysql is not, because it is not necessary, "register."

The "table" is implicit in the structure of the data directory.

2.4 operating system restrictions on database and table naming

Mysql has a principle for naming databases and tables: names can consist of any alphanumeric characters in the current character set

An underscore and the dollar sign $are also fine. The maximum length of the name is 64 characters.

However, because the names of databases and tables correspond to directory and file names, the operating system the server is running may be forced to increase the amount

External restrictions.

First of all, database and table names are limited to characters that are legal for file names, such as $is allowed in mysql principles, but

If your operating system does not allow it, you cannot use it in a directory or table name. In fact, this is for Unix or Windows

Not to worry, the biggest difficulty is to reference names directly in shell when performing database administration, for example, if you

Name a database such as $my_db, which contains a dollar sign, and any reference to that name from shell may be shell

Interpreted as a reference to a variable:

Ls $my_db

My_db:undefined variable

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

Ls $my_db

% ls'$my_db'

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

Second, although mysql allows database and table names to be up to 64 characters long, the length of names is limited by your operating system

Generally speaking, this is not a problem with the limited length (although the old System V forces 14 characters). Under the circumstances,

Your database name is limited to 14 characters, while the table name is limited to 10 characters, because the file name that represents the table has a dot (.)

And a three-character extension.

Third, the case sensitivity of the file system affects how you name and reference databases and table names. If the file system

Is case-sensitive (such as Unix), and the two names my_tbl and MY_TBL are different tables. If the file system is not the size

Write sensitive (such as Windows), the two names refer to the same table. If you use a Unix server to develop data

Library, and if you have the possibility of moving to Windows, you should keep this in mind.

2.5 mysql status file

In addition to the database directory, the mysql data directory contains a number of status files, which are summarized in the following table. Mostly

The default name of the number file is generated from the server hostname and is represented as HOSTNAME in the following table. Table mysql status file

File type default name file content

Process ID HOSTNAME.pid the ID of the server process

Error log HOSTNAME.err startup and shutdown events and error conditions

General log HOSTNAME.log connection / disconnect events and query information

Update log HOSTNAME.nnn modifies all query text of table structure-level content

When the server starts, it writes its process ID to the process ID (PID) file, and when it shuts down, it deletes the file.

PID files are tools that allow the server itself to be found by other processes. For example, if you run mysql.server on the system

When off, the script that shuts down the mysql server examines 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 redirection of the server's standard error output, which contains any messages sent to stderr

Donovan's news. This means that the error file exists only if you start the server by calling safe_mysqld (anyway

It is the best way to start the server, because if it exits due to an error, safe_mysqld will restart the server. ).

General logs and update logs are optional. You can only open the type of log you need, using-- log and

-- log-update server options.

General logs provide general information about server operations: who connects to the server from where and what queries they issue. Update

The log provides query information, but only queries that modify the contents of the database. The contents of the update log are written as SQL statements, and you can set the

They are provided to the mysql client to execute. If you encounter a crash and have to rewind the backup file, update the log

It's useful because you can repeat updates since the crash, by feeding back the update log to the server, which allows you to count

According to the state of the library at the time of the crash.

Here is a simple example where the information appears in the general log, which creates a table in the database test

Insert a row and delete the session of 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, server process ID, event type, and event information columns.

The same session appears in the update log and looks like this:

Use test;CREATE TABLE my_tbl (val int); INSERT INTO my_tbl VALUES (1)

DROP TABLE my_tbl

For update logs, use the-- log-long-format option to get an extended log that provides information about

Who issues each query uses more disk space, but if you want to know who is doing what, you don't have to be more

The new log finds the connection event against the contents of the general log.

For the above session, the extended update log produces the following information:

# Time: 990507 7-paul 32-3-3 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

User@Host: paul [paul] @ localhost [] DROP TABLE my_tbl;

It's a good idea to keep your log files safe and not accessible to any user. General logs and update logs can be packaged

Contains 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 permissions to check and set data directories, see the mysql Security Guide. Instructions to make data catalogs secure

Contains the following commands:

Chmod 700 DATADIR

Run this command as the Unix user who owns the data directory. Make sure that the server also runs with this user, otherwise the command will not only

By shutting out others, 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 worried that these file names are

No conflict or error with the database name (such as when the server executes a SHOW DATABASES statement). The answer is no. Status and day

The log file information is stored in the file, and the database is a directory, so the executable program can use a simple stat () call area

Divide them. If you look at the data directory, you can distinguish between the state file and the database directory, use ls-l and check the schema

Is the first character of a "_" or a "d"

You can also simply look at the name. All status file names contain a dot ("."), while the database directory does not.

(. Is an invalid character in the database name.

MySQL data directory structure (2)

3 relocate the database directory

The data directory structure discussed earlier is the default configuration, and all databases and state files are included. However, you have

Some are free to determine the location of the contents of the data directory. This section discusses why you may remove some data directories (or even directories).

Itself), what you can remove and how you make these changes.

MySQL allows you to relocate data directories or members of them for several reasons why you should do so:

You can put the data directory on your default file system, a larger file system.

If your data directory is on a busy hard disk, you may put it on a less busy disk to balance disk activity

Move. You can put the database and log files on separate disks or distribute them across disks.

You may want to run multiple servers, each with its own data directory, which addresses each process file descriptor limit

One way to solve the problem, especially if you can't reconfigure the kernel to allow for higher limits.

Some systems save part of the server's files in / var/run, for example, and you may want to put MySQL's PID file in

There, for the consistency of the operation of the system.

3.1 relocation method

There are two ways to relocate the contents of the data directory:

You can specify options when the server starts, either on the command line or in [mysqld] in an options file.

You can remove what you want to relocate and make a symbolic link in the original location that points to the new location.

Neither method can solve everything you can relocate. the following table summarizes what can be relocated and which method to relocate.

If you use an options file, it is possible to specify it in the global options file / etc/my.cnf (c:my.cnf on Windows)

Options. The current version of Windows also looks for the system directory (c:Windows or c:NT). Table relocation method

Relocation method suitable for relocation method

Start options or symbolic connections for the entire data directory

Single database directory symbolic connection

Symbolic connection of a single database table

PID file startup options

General log startup options

Update log startup options

You can also use the option file my.cnf in the default data directory, but it is not recommended. If you want to reorder

Bit data directory itself, you have to make the default data directory readable so that you can place option files here to specify the server

Where to find a "real" data directory! It's a mess. If you want to use an options file to specify server options

It is best to use / etc/my.cnf.

3.1 verify the effect of relocation

Before trying to reposition anything, it's a good idea to verify that the operation works as expected. With the help of du, df and

The ls-l command gets information about disk space, but this depends on your correct understanding of the layout of your file system.

The following is a demonstration of a design trap when you verify the relocation of a generic directory. Suppose your data directory is

/ usr/local/var, and you want to move it to / var/mysql, because df shows that the / 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

1189359 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 under 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 request free space for a file system that contains / usr/local/var, why report it?

What about the space on the var? Here ls-l provides the answer:

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

The output shows that / usr/local/var is a symbolic link to / var/mysql, in other words, the data directory has been relocated

Located in the / var file system and replaced with a symbolic link that points there. Unexpectedly release by moving the data directory to / var

There is so much space on usr!

3.2 relocate the data directory

To relocate the data directory, shut down the server and move the data directory to a new location, then you should delete the member data directory

Record and replace it with a symbolic connection to the new location, or restart the server with an option that clearly indicates the new location. The following table lists the references to

Locate the command line and options. Table data catalog relocation syntax

Option source syntax

Command line-- data-dir=/path/to/dir

Options file [mysqld]

Datadir=/path/to/dir

3.3 relocate the database

The database can be removed by symbolic connection. To relocate a database, shut down the server and remove the database directory

Record and delete the original database directory, replace it with a symbolic connection to the new location, and then restart 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 reasons, the original database directory was renamed to

Bigdb.orig . After you have verified that the server is working properly, you can delete the original data directory.

% rm-rf bigdb.orig

3.4 relocate database tables

It's not a good idea to relocate a separate watch. You can move the table file to a different place and in the data

Directory to create symbolic links to these files. However, if you send out a message

ALTER TABLE or OPTIMIZE TABLE statements will not be modified by you.

Each statement deletes the original table by creating a temporary table in the database directory that you modify or optimize

And rename the temporary table to the original table, the result is that your symbolic connection is deleted, and the new table goes back to the database.

Record, this is the location of the original table file before you removed it. To make matters worse, you don't realize they're there and continue to occupy them.

Space, and symbolic connections have been broken, so that later, when you realize what's happening, if you forget that you put them

If you move somewhere, you may not have a good way to track the file.

Because it is difficult to ensure that people with table access will not modify or optimize the table, it is best to leave the table in the database directory.

3.5 relocate the status file

You can relocate PID files, general logs, and update logs. The error log is created by safe_mysqld with the startup option

Build, but cannot be relocated (unless you edit safe_mysqld).

To write a state file in a different location, shut down the server, and then specify the appropriate option for the new state file location

Activate it. The following table lists the command line and options file syntax for each file. Table status file relocation syntax

Option source syntax

Command line-- pid-file=pidfile

-- log=lodfile

-- log-update=updatefile

Options file [mysqld]

Pid-file=pidfile

Log=lodfile

Log-update=updatefile

If you specify a status file with an absolute path name, create a file with that path, otherwise the file is created in the data directory.

For example, if you specify-- the pid-file=/var/run/mysqld.pid,PID file is / var/run/mysqld.pid. If

You specify that the-pid-file=mysqld.pid,PID file is DATADIR/mysqld.pid.

If you specify an update log file without an extension, MySQL generates a sequential name each time it opens the update log. These.

The name has an extension .nnn, where .nnn is the first number not used by the existing update log (such as update.000

Update.001 etc.). You can override the sequence name by explicitly specifying the extension, and the server will only use the specified name.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.