Shulou(Shulou.com)06/01 Report--

Conceptually, most relational database systems are similar: they consist of a series of databases, each containing 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 state files that provide server operation information. If you perform administrative tasks on an MySQl installation, 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 directory is / usr/local/var, / var/lib/mysql if installed from a RPM file, and / usr/local/mysql/data if installed from a binary distribution.

When you start the server, you can clearly specify the location of the data directory by using the-- datadir=/path/to/dir option. 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 should go 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. Look for the value of 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, and you can take turns to get data catalog information for each of them by providing the-port or-- socket option of the port or socket that the connection server is listening to:

% 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 connect to the server from a remote host, use a-- host=host_name option:

% mysqladmin-- host=host_name variables

On Windows, you can connect to a NT server that listens on a command pipeline by using-- pipe to force a command pipe connection and-- socket=pipe_name to specify a pipe name:

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 directory locations right away, but the disadvantage is that you have to run on the server, and there may be no useful information to generate, unless the-datadir option is explicitly specified 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, the location can be obtained from the top-level Makefile, but note that the location is the localstatedirvalue in Makefile, not datadir, and if the distribution is on a NFS-mounted file system and is used to build MySQL for multiple hosts, the configuration information reflects the distribution of the newly built host, which may not provide data directory information for the host you are interested in.

If the above fails, you can use find to find the database file, and the following command looks for the ".frm" file, which is part of any 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, which is directly implemented by using the hierarchical structure of the 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 files provide important information about server operations. It is valuable for management, especially when something goes wrong and tries to determine the cause of the problem. For example, if a particular query kills the server, you can identify the disruptive query by checking the log file.

2.1 how does the MySQL server provide access to data

Everything under the data directory is managed by a separate entity, the MySQL server mysqld, and the client never manipulates the data directly. Instead, the server provides an entry point for data access, which is an intermediary between client programs 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 and then performs the desired operation with a MySQL query transfer request. 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 executes one by one, the actual effect is to serialize the request so that two customers can never change the same record at the same time.

Under normal circumstances, having the server as the sole arbiter of database access provides a guarantee to avoid the destruction of multiple processes that can access database tables at the same time. 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 databases on the host, but it is possible to run multiple servers. If this completes providing access to multiple separate data directories, there is no problem of interacting with each other, 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 repair, as you might think, because these programs modify the contents of the table and allow them to operate on the table while the server is operating, which can lead to table corruption. It is important to understand how to limit this interaction so that you do not damage your watch.

2.2 data item representation

Each MySQL server manages a database with its own database table, which is a subdirectory under the data directory with 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 very simple in its implementation. CREATE DATABASE db_name creates an empty db_name directory in the data directory with an owner and schema that only allows MySQL server users (Unix users running the server), which is equivalent to the following manual creation of the database on the server host:

% mkdir DATADIR/db_name

Chmod 700 DADADIR/db_name

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

The implementation of the DROP DATABASE statement is equally simple. DROP DATABASE db_name deletes the db_name directory and all table files from the database, 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 other files in the database directory. 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 component 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 an 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. The extension of the data and 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 to define the structure of the table, the server creates a file called tbl_name.frm, which includes the internal coding of the structure, as well as an empty data and index file, initialized to contain information indicating no record and no index (if the CREATE TABLE statement includes index assignments, the index file reflects those 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 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 by the server to be equivalent to ALTER TABLE. DROP TABLE is achieved by deleting three files corresponding to the table.

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 database systems have a registry that enumerates all the tables contained in a database. MySQL is not, because it is unnecessary, the "registry" 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:

The name can consist of any alphanumeric character in the current character set, or an underscore and the dollar sign $.

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 impose additional 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 directory or table names. In fact, this is not a concern for Unix or Windows, and the biggest difficulty is to reference the name directly in shell when performing database administration. For example, if you name a database such as $my_db that contains a dollar character, any reference to that name from shell may be interpreted by shell 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 the length limited by your operating system, which is generally not a problem (although the old System V forces 14 characters). In this case, 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), the two names my_tbl and MY_TBL are different tables. If the file system is not case-sensitive (such as Windows), the two names refer to the same table. If you use a Unix server to develop databases, 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. The default names of most files are generated from the server hostname and are 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, when the system shuts down, the script that shuts down 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 redirection of the server's standard error output, which contains any messages that are evil to stderr. This means that the error file exists only if you start the server by calling safe_mysqld (in any case, 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 turn on only the log types you need, using the-- 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. The update 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 that can be provided to the mysql client for execution. If you encounter a crash and have to rewind the backup file, updating the log is useful because you can repeat the update since the crash, which allows you to restore the database to the state it was when the crash occurred by feeding the update log back to the server.

Here is a simple example where the information appears in the general log, which is a session that creates a table in the database test, inserts a row, 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, 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 when and when to issue each query, which uses more disk space, but if you want to know who is doing what, you do not have to compare the update log to the contents of the general log to find connection events.

For the above session, the extended update log produces the following 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 accessible to any user. Both general and update logs can contain 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. The instructions to make the data directory secure include the following commands:

Chmod 700 DATADIR

Run this command as the Unix user who owns the data directory. Make sure the server runs with this user, otherwise the command not only shuts 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 about whether these file names conflict with the database name or make errors (such as when the server executes SHOW DATABASES statements). The answer is no. The state and log file information is stored in a file, while the database is a directory, so the executable can distinguish between them with a simple stat () call. If you look at the data directory, you can distinguish between the state file and the database directory, use ls-l and check whether the first character of the schema is 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.

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 freedom to determine the location of the contents of the data directory. This section discusses why you may remove some of the data directories (or even the directories themselves), what you can remove, and how you can 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. 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 is one way to solve the problem of limiting each process file descriptor, especially if you cannot reconfigure the kernel to allow 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 files there for consistency in system operation.

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 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).

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 relocate the data directory itself, you have to make the default data directory readable so that you can place option files here to specify where the server should find the "real" data directory! It's a mess. If you want to use an options file to specify server options, you'd better 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. Get information about disk space with the help of the du, df, and ls-l commands, but it depends on you correctly understanding 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 / var

Filesystem 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/var

Filesystem 1k-blocks Used Avail Capacity Mounted on

/ dev/wd0s3f 1189359 1111924 162287 15% / var

That's weird. If we are requesting free space for a file system that contains / usr/local/var, why report 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 to the / var file system and replaced with a symbolic link that points there. Moving the data directory to / var unexpectedly frees up 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 and replace it with a symbolic link to the new location, or restart the server with an option that clearly indicates the new location. The following table lists the command line and options for the specified location.

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 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 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 as the owner of the data directory. For security reasons, the original database directory was renamed 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 do this by moving table files to a different place and creating symbolic links to these files in the data directory. However, if you issue an ALTER TABLE or OPTIMIZE TABLE statement, your changes will not be made.

Each statement is done by creating a temporary table in the database directory that you modify or optimize, then deleting the original table and renaming the temporary table to the original table, the result is that your symbolic link is deleted and the new table goes back to the database directory, which is the location of the original table file before you removed it. To make matters worse, you don't realize they're there, they continue to take up space, and the symbolic connection has been broken, so that later, when you realize what's going on, 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 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 and cannot be relocated (unless you edit safe_mysqld).

To write a state file in a different location, shut down the server and start it with the appropriate option to specify the location of the new state file. 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-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 names have 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. [@ more@]

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.