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

Today, I will talk to you about how to relocate the contents of the database directory in MySQL, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

◆ MySQL allows you to relocate data directories or elements in it. There are several reasons for this:

◆ can place data directories in a file system with more capacity than the default location.

◆ if the data directory is on a busy disk, you can place it on a less used drive to balance disk activity between physical devices. For similar reasons, you can put the database and log files on different drives, or redistribute the database between drives.

◆ you can run multiple servers, and each server has its own data directory. This is a way to solve the problem of total process file descriptor restrictions, especially if the core of the system cannot be reconfigured to get higher limit values.

◆ some systems save PID files in directories such as / var/run. For consistency in the operation of the system, you can put the MySQL PID file there as well.

Relocation method

There are two ways to relocate a data directory:

◆ can specify an option at server startup time either on the command line or on the [mysqld] group of an options file.

◆ can move the content to be relocated, and then make a symlink (symbolic link, symbolic link) pointing to the new location in the original location.

Neither of the two methods can do all the relocation work for you. Table 5-3 combines the content that can be relocated and the methods that can be used for relocation.

If you use an options file, you can specify the options in the global options file / etc/my.cnf (c:my.cnf in Windows). The current version of Windows also accesses the system directory (c: or c:WINNT).

You can also use the option file my.cnf of the default data directory, which is compiled on the server. The author does not recommend using this file. If you want to relocate the data directory itself, you must maintain the integrity of the default data directory in order to place an options file in the data directory, which will indicate where the server should find the "real" data directory! What a mess. If you want to use an options file to specify the options for the server, you'd better use / etc/my.cnf.

Relocate the data directory

Now explain the methods and steps to relocate the directory.

1. Shut down the server, for example:

$admin-u root-p shutdown

2. Move the database directory to a new location

3. Then delete the original database

4. If you use the symlink method (on Unix platform), for example:

$ln-s NEWDIR DATADIR

DATADIR is the location of the standard database directory and the original database directory path

.

If you use the start option, you can start it like this:

$safe_mysqld-- basedir=/path/to/dir/ & (Unix)

$mysqld-basedir=x:datadir (Windows)

If you use the options file method:

Add to / etc/my.cnf (Unix) or c:my.cnf (Windows):

[mysqld]

Datadir=/path/to/dir/

5. Restart the database

Relocate the database

The database can only be relocated through the symlink method, so the windows platform cannot relocate the database. In order to relocate the database, close the database and move the directory of the database. Delete the original database directory, replace it with a symlink pointing to the new location, and then start the server.

Here is a practical example, backup bigdb--, to illustrate this process:

$mysqladmin-u root-p

$cd DATADIR

$tar cf bigdb | (cd / var/db;tar xf)

$mv bigdb bigdb.old

$ln-s var/db/bigdb.

$safe_mysqld &

These commands should be executed as the owner of the database directory. After the server is working properly, you can delete the backup directory bigdb.old:

$rm-rf bigdb.old

Relocate database table

Relocating a single database table is not particularly useful and can sometimes cause a lot of trouble. You can do this by moving the files of the table to another location and no longer creating a symlink that points to those files in the library directory. Method is similar to the method of relocating a database.

However, if you have ever issued an ALTER TABLE or OPTIMIZE TABLE statement, these relocations will be cancelled. The reasons will not be introduced.

Relocate status file

The PID file in the status file and the regular log brown update log can be relocated by using the startup option or the option file. The error log is created by safe_mysqld and cannot be relocated unless you edit the safe_mysqld script.

In order to write the status file in another location, shut down the server and start it with the appropriate option to set the location of the new state file.

Startup options:

-- pid-file=pidfile PID file

-- log=logfile regular log

-- log-update=updatefile update log

Options file

[mysqld]

Pid-file=pidfile

Log=logfile

Log-update=updatefile

Naming rules for status files:

1. If you specify the name of a state file with an absolute path, create a change file with that path.

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

2. If you only give the file name, the file is created in the database directory.

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

3. If no file is given, the status file uses the default name.

Doing so tells the server to enable the state file, which doesn't make sense for the PID file, because the server always uses it. For example:

$safe_mysqld-- log--log-update &

Alternatively, use the options file:

[mysqld]

Log

Log-update

For regular logs, the DATADIR/hostname.log file is generated, and hostname is the hostname of the running server. For update logs, generate sequential files for DATADIR/hostname.nnn.

4. For the update log, if you specify an update log without an extension, MySQL will generate the name of the data when opening the update log. These names are created with the .nnn extension, where .nnn violates the first number used by the existing update log file (for example, hostname.001, hostname.002, and so on). You can ignore the generation of sequential names by specifying an explicit extension, and then the server will use only the name you specify.

Summary

In this section you will learn how to relocate database directories, status files, etc. The things you may need to relocate are:

1. Database directory

2. Database

3. Status files, including regular log, update log, etc.

The methods of relocation are as follows:

1. Symbolic links

2. Specify command line parameters

3. Use options file

After reading the above, do you have any further understanding of how to relocate the contents of the database directory in MySQL? If you want to know more knowledge or related content, 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.