MySQL reposition the contents of the Data directory

mysql| Data | Data Directory 10. Section 2 discusses the structure of the data directory in its default configuration. All databases and status files are included. However, administrators have certain responsibilities in the layout that determines the content of the data directory. This section discusses why you move the various parts of the data directory (even the dictionary itself), what you can move, and how to move it.
MySQL allows you to reposition data directories or elements within them. There are several reasons for doing this:
You can place the data directory in the file system with a larger capacity than the default location file system.
If your 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 place the database and log files on separate drives, or distribute the databases between drives.
You can run multiple servers, and each server has its own data directory. This is a way to resolve the problem of total process file descriptor limitations, especially if the core of the system cannot be reconfigured to obtain a higher limit value.
Some systems store PID files in a directory such as/var/run. For consistency of system operation, you can also put the Mysqlpid file there.

Reposition method

There are two ways to reposition a data directory:
You can specify an option at the server startup time on the command line or on the [mysqld] group of an option file.
You can move the content you want to reposition, and then in the original position, do a new position of S y m l n K (symbolic link, symbolic links).
Neither of these methods can do all the relocation work for you. Table 10-4 combines the relocatable content with the methods available for relocation. If you use an option file, you can specify options in the global options File/E t c/my.cnf (c:\my.cnf in Windows). The current version of Windows also accesses the system directory (C:\Windows or c:\n T).
You can also use the default data directory's option file MY.CNF (which is compiled in the server). I do not recommend the use of this file. If you want to reposition the data directory itself, you must maintain the integrity of the default data directory so that you can place an option file in the data directory that shows where the server should find the "real" Data Directory! It's a mess. If you want to use an option file to specify the server's options, it is best to use/E t c/my.cnf.


Estimating the effect of relocation

It is a good idea to verify that the operation will have the desired effect before attempting to reposition anything. The author prefers the D u, DF and Ls-l commands to obtain disk space information, but all commands depend on the correct understanding of the file system layout.
The following example shows a mysterious interruption to keep a close eye on when to estimate the relocation of the data directory. Suppose the data directory is/US r/l o c a l/v a r and would like to move it to/V a r/mysql, because DF indicates that the/var file system has more free space (as shown in the following example):

How much space in the/usr file system can be freed by the relocated data directory? To find the amount of space, you can use Du-s to see how much space the directory uses:

This is approximately 0 MB and should have a significant change to/usr. But is it true? You can try the DF command in this data directory/US R:

It's strange. We are asking for the free space that contains the/usr/local/var system files, but why DF reports the space of V a r? The following Ls-l responded:

The output indicates that the/usr/local/var is an S y m l n K for the/var/mysql. In other words, the data directory has been relocated to the/var file system and replaced with symlink pointing to the/var file system. This is the end of the work on freeing up a lot of/usr space by moving the data directory to/var!
Lesson: Taking a few minutes to estimate the effect of relocation is a worthwhile investment. It doesn't take a long time to find that you may not be able to meet your expectations, so you can avoid wasting a lot of time moving data catalogs.

Relocate Data Directory

To reposition the data directory, you should shut down the server and move the data directory to a new location. You should then either delete the original data directory and replace it with a symlink that points to the new location, or restart the server using an option that directly indicates the new location. Table 10-5 lists the syntax for the command line and option files that specify this location.


Relocate database

The database can only be moved through the Symlink method. In order to reposition the database, you should shut down the server and move the database directory. Delete the original database directory, replace it with the symlink that points to the new location, and then start the server.
The following example shows how to move a database bigdb to another location:

Preventive measures for reposition
You should shut down the server before performing any relocation operation, and then restart it. For some types of relocation, such as moving the database directory, it is possible to keep the server running (although this is not recommended). If you want to do this, you must make sure that the server does not have access to the database that will be moved. You should also make sure that the Flush table statement is published before you move the database to ensure that all open table files are closed by the server. The non-performance of these precautions may result in the destruction of the table.
These commands should be executed as the data directory owner. For security purposes, rename the original database directory to B i g db. o r i G. After verifying that the server and the relocation server are working correctly, you can delete the original directory:
% RM-RF Bigdb.orig

Relocate database tables

It's not a good idea to reposition a single table. You can do this by moving the table's files to another location and creating a symlink in the database directory that points to those files. However, if you have previously published ALTER TABLE or optimize TABLE statements, these modifications will be canceled.
Each statement renames the temporary table to its original name by creating an implementation change and optimized temporary table in the database directory, and then deleting the original table. The result is that Symlink is deleted and the new table is returned to the database directory, which is the location of the original table before you moved the table. As a result, old table files that move out of the database directory are still in their original location-you don't even remember them, but they still occupy space. Again, Symlink has disappeared, so when you realize what's happening, if you don't remember where you moved it, there's no good way to capture those files.
It is difficult to ensure that anyone accessing the table does not change or optimize the table (and thus undo any attempt to relocate), so it is best to keep these tables in the database directory.

Relocate a state file

You can reposition PID files, regular logs, and update logs with the startup option. The error log is created by Safe_mysqld and cannot be relocated (unless edited s a f e _ mysqld)
To write a status file in another location, shut down the server and restart it with the appropriate option to specify the location of the new state file. Table 10-6 lists the syntax for the command line and options for each file.
To delete a relocated database
You can delete a database with the drop DB statement, but an older version of MySQL is difficult to delete a database that has been relocated. The tables in the database are deleted correctly, but errors occur when the server attempts to delete the database directory because the directory is a symlink rather than a true directory. The MySQL administrator must manually delete the database directory and point to it s y m l i n K. The problem has been solved since MySQL3.23.

If you specify the name of a state file with an absolute path name, the file is created with that pathname. Otherwise, the file is created in the data directory. For example, if you specify--
Pid-file =/var/run/mysqld.pid, then the PID file is/V a r/r un/mysqld. P i d. If you specify--Pid-file = Mysqld.pid, the PID file is Data D I r/mysqld. P i d.
If you specify an update log without an extension, MySQL will generate the order name when the update log is opened. These names are used. N-N-o extension created, where. n n n is the first number that has not been used by an existing update log file (for example, up date. 0 0 1, up date. 0 0 2 etc.). You can omit a build that has a sequential name by specifying an explicit extension, and then the server will use only the name you specify.