MySQL Change database data store directory

The default database file for MySQL database is located under/var/lib/mysql, and sometimes the data storage directory of the MySQL database needs to be changed due to storage planning and other reasons. The following summarizes the steps of the practice process.

1: Confirm MySQL Database storage directory

[Email protected] tmp]# mysqladmin-u root-p Variables | grep datadir

| DataDir | /var/lib/mysql/

2: Turn off MySQL service

The MySQL service must be turned off before changing the MySQL data directory.

Way 1 :

[[Email protected] ~]# service MySQL status

MySQL running (9411) [OK]

[[Email protected] ~]# service MySQL stop

Shutting down MySQL. [OK]

Way 2 :

[Email protected] ~]#/etc/rc.d/init.d/mysql status

MySQL running (8900) [OK]

[[email protected] ~]#/etc/rc.d/init.d/mysql stop

Shutting down MySQL. [OK]

3: Create a new database storage directory

[Email protected] ~]# cd/u01

[Email protected] u01]# mkdir mysqldata

4: Move MySQL data directory to a new location

[Email protected] ~]# mv/var/lib/mysql/u01/mysqldata/

5 : Modifying a configuration file my.cnf

Not all versions contain my.cnf This configuration file, in the MySQL 5.5 version, I can not find my.cnf this profile, and some MySQL version of the file is located in/usr/my.cnf, if there is no MY.CNF configuration file in the/etc/directory, go to/usr /share/mysql/Find the *.cnf file, copy one of them to/etc/and rename it to MY.CNF. The command is as follows:

[Email protected] mysql]# CP/USR/SHARE/MYSQL/MY-MEDIUM.CNF/ETC/MY.CNF

Edit the/etc/my.cnf file, modify the parameters socket

MySQL version 5.5

# The following options would be passed to all MySQL clients

[Client]

#password = Your_password

Port = 3306

Socket =/u01/mysqldata/mysql/mysql.sock

# here follows entries for some specific programs

# The MySQL server

[Mysqld]

Port = 3306

Socket =/u01/mysqldata/mysql/mysql.sock

Skip-external-locking

Key_buffer_size = 16M

Max_allowed_packet = 1M

Table_open_cache = 64

Sort_buffer_size = 512K

Net_buffer_length = 8K

Read_buffer_size = 256K

Read_rnd_buffer_size = 512K

Myisam_sort_buffer_size = 8M

6: Modify startup script/etc/init.d/mysql

Modify the parameter DataDir to datadir=/u01/mysqldata/mysql/

7: Start the MySQL service and verify the MySQL database path

[[Email protected] ~]# service MySQL start

Starting MySQL. [OK]

[Email protected] ~]# mysqladmin-u root-p Variables | grep datadir

My question:

1: Before modifying the storage directory of the database, there is no Mysql.sock file at all in the/var/lib/mysql/directory, after installing the above configuration, the Mysql.sock file will be generated.

about the Mysql.sock file, search for information: Mysql.sock is the file used for the socket connection. That is, only your daemon is up and the file exists. However, your MySQL program (this program is the client, the server side is mysqld) can choose whether to use the Mysql.sock file to connect (because this method is only suitable for the UNIX host to connect the local mysqld), for non-local any type of host. So does this file necessarily need to be? This needs to be understood more clearly.

2: I see some users on the Internet to summarize the changes to the MySQL data path, some need to create a new directory permissions to do some processing, and some do not have to authorize the directory permissions, I did not deal with, there is no problem. Do you want to authorize the new database directory?

3: I mysql_5.6.20 This version of the test, do not modify the MY.CNF, only to modify the startup script/etc/init.d/mysql, there is no problem at all. There are no Myssql.sock file generation.

4: Note If SELinux is not disabled, starting the MySQL service after modifying the MySQL data path will encounter some errors. The explanation of this is that the background service needs to have corresponding permissions to the corresponding directory, and the default path of MySQL/var/lib/mysql has been added to the corresponding policy, modify the path because there is no corresponding policy, causing the background process to read files by SELinux blocked, resulting in a permissions error. So either turn SELinux off or modify the file security context.

[[email protected] mysql]#/etc/init.d/mysql start

Starting MySQL .... The server quit without updating PID file (/u01/mysqldata/mysql//db-server.localdomain.pid). [FAILED]

[Email protected] mysql]# Chcon-r-t mysqld_db_t/u01/mysqldata/mysql/

[[email protected] mysql]#/etc/init.d/mysql start

Starting MySQL. [OK]

MySQL Change database data store directory