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

MySQL how to change the database data storage directory, in response to this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

1: Confirm MySQL database storage directory

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

2: Shut down MySQL service

MySQL service must be shut down before changing MySQL's data directory.

Mode 1:

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

Mode 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 MySQL data directory to new location

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

5: Modify the configuration file my.cnf

Not all versions include my.cnf configuration file, in MySQL version 5.5, I can not find my.cnf configuration file, and some MySQL versions of the file is located in/usr/my.cnf, if there is no my.cnf configuration file in/etc/directory, please find the *.cnf file in/usr/share/mysql/, copy one of them to/etc/and rename it my.cnf. The order reads as follows:

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

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

MySQL version 5.5

# 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

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

7: Start MySQL service and verify 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 question:

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

For mysql.sock files, a search for information: mysql.sock is a file used for socket connections. This file exists only if your daemon is started. But your mysql program (this program is client, server is mysqld) can choose whether to use mysql.sock file to connect (because this method is only suitable for connecting local mysqld on Unix hosts), for any type of host that is not local. Is this document necessary? This requires further clarification.

2: I see some netizens summary of the MySQL data path modification, some need to do some processing to the new directory permissions, and some do not need to authorize the directory permissions, I did not deal with, there is no problem. Do you want to license the new database directory or not?

3: When I tested MySQL_5.6.20, I did not modify my.cnf, 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, you will encounter some errors when starting MySQL after modifying MySQL's datapath. The explanation for this is that background services need to have corresponding permissions to the corresponding directory, and mysql default path/var/lib/mysql has added corresponding policies, after modifying the path because there is no corresponding policy, resulting in background processes reading files is blocked 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]#About MySQL how to change the database data storage directory questions to share the answer here, I hope the above content can have some help for everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.