How to change the database data storage directory in MySQL

Preface

The default database file of the MySQL database is located in /var/lib/mysql. Sometimes, due to storage planning and other reasons, it is necessary to change the data storage directory of the MySQL database. The following is a summary of the steps in the practical process. Without further ado, let’s take a look.

Here’s how:

1: Confirm the MySQL database storage directory

[root@DB-Server tmp]# mysqladmin -u root -p variables | grep datadir
 
Enter password: 
 
| datadir | /var/lib/mysql/

2: Shut down the MySQL service

Before changing the MySQL data directory, the MySQL service must be shut down.

Method 1:

[root@DB-Server ~]# service mysql status
 
MySQL running (9411) [ OK ]
 
[root@DB-Server ~]# service mysql stop
 
Shutting down MySQL..[ OK ]
 
[root@DB-Server ~]# 

Method 2:

[root@DB-Server ~]# /etc/rc.d/init.d/mysql status
 
MySQL running (8900) [ OK ]
 
[root@DB-Server ~]# /etc/rc.d/init.d/mysql stop
 
Shutting down MySQL..[ OK ]
 
[root@DB-Server ~]#

3: Create a new database storage directory

[root@DB-Server ~]# cd /u01

[root@DB-Server u01]# mkdir mysqldata

4: Move the MySQL data directory to a new location

[root@DB-Server ~]# mv /var/lib/mysql /u01/mysqldata/

5: Modify the configuration file my.cnf

Not all versions include the my.cnf configuration file. In MySQL 5.5, I cannot find the my.cnf configuration file. Some MySQL versions have this file located in /usr/my.cnf. If there is no my.cnf configuration file in the /etc/ directory, please find the *.cnf file in /usr/share/mysql/, copy one of them to /etc/ and rename it to my.cnf. The command is as follows:

[root@DB-Server mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

Edit the /etc/my.cnf file and modify the socket parameter

MySQL 5.5 version

# The following options will 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 the startup script /etc/init.d/mysql

Change the parameter datadir to datadir=/u01/mysqldata/mysql/

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

[root@DB-Server ~]# service mysql start
Starting MySQL..[ OK ]
[root@DB-Server ~]# mysqladmin -u root -p variables | grep datadir
Enter password: 
| datadir | /u01/mysqldata/mysql/

My questions:

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

Regarding the mysql.sock file, I searched for information: mysql.sock is a file used for socket connection. That is, this file only exists when your daemon is started. However, your mysql program (this program is the client, the server is mysqld) can choose whether to use the mysql.sock file to connect (because this method is only suitable for connecting to the local mysqld on a Unix host), for any type of non-local host. Is this file absolutely necessary? This needs further clarification.

2: I saw some netizens summarize how to modify the MySQL data path online. Some of them require some processing of the permissions of the newly created directory, while some do not require authorization of directory permissions. I did not process them and there was no problem. Should I authorize the new database directory?

3: When I tested the MySQL_5.6.20 version, I did not modify my.cnf, but only modified the startup script /etc/init.d/mysql, and there was no problem at all. There is also no myssql.sock file generated.

4: Note that if selinux is not disabled, some errors may occur when starting the MySQL service after modifying the MySQL data path. The explanation for this is that background services need to have corresponding permissions to the corresponding directories, and the default path of mysql /var/lib/mysql has added corresponding policies. After the path is modified, due to the lack of corresponding policies, the background process is blocked from reading files by selinux, resulting in permission errors. So either turn off Selinux or modify the file security context.

[root@DB-Server mysql]# /etc/init.d/mysql start
 
Starting MySQL....The server quit without updating PID file (/u01/mysqldata/mysql//DB-Server.localdomain.pid).[FAILED]
 
[root@DB-Server mysql]# 
 
[root@DB-Server mysql]# chcon -R -t mysqld_db_t /u01/mysqldata/mysql/
 
[root@DB-Server mysql]# /etc/init.d/mysql start
 
Starting MySQL. [ OK ]
 
[root@DB-Server mysql]#

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

References:

http://database.ctocio.com.cn/tips/449/7566949.shtml

https://www.jb51.net/article/150089.htm

https://www.jb51.net/article/150090.htm