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

Editor to share with you the MySQL change database data storage directory of the case study, I believe that most people do not know much, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

The methods are as follows:

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

The MySQL service must be turned off before you can change the data directory of MySQL.

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 ~] #

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 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 version 5.5, I can't find the my.cnf configuration file, while some MySQL versions are located in / usr/my.cnf. If there is no my.cnf configuration file in the / etc/ directory, please find the * .cnf file under / 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 / etc/my.cnf file, modify parameter socket

MySQL version 5.5

Undefined

# The following options will be passed to all MySQL clients [client] # password = your_passwordport = 3306socket = / u01/mysqldata/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306socket = / u01/mysqldata/mysql/mysql.sockskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8m

6: modify 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 startStarting MySQL.. [OK] [root@DB-Server ~] # mysqladmin-u root-p variables | grep datadirEnter 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.

With regard to the mysql.sock file, I searched for some information: mysql.sock is the file used for socket connections. That is, this file exists only when your daemon starts. But your mysql program (this program is the client and the server is mysqld) can choose whether or not to use the mysql.sock file to connect (because this method is only suitable for connecting to the local mysqld on the Unix host), for any type of host that is not local. So is this document necessary? This needs to be further understood.

2: I read on the Internet that some netizens summarized the modification of the MySQL data path, some need to do some processing to the permissions of the new directory, while others do not need to authorize the permissions of the directory, I did not deal with it, and there is no problem. Do you want to authorize the new database directory or not?

3: when I was testing this version of MySQL_5.6.20, I didn't modify the my.cnf, only the startup script / etc/init.d/mysql, and there was no problem at all. There are no myssql.sock files generated.

4: note that if selinux is not disabled, you will encounter some errors when starting the MySQL service after modifying the data path of MySQL. The explanation for this is that the backend service needs to have the corresponding permission to the corresponding directory, and the default path / var/lib/mysql of mysql has added the corresponding policy. After modifying the path, the background process is blocked from reading files by selinux because there is no corresponding policy, resulting in a permission error. So either close 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] # these are all the contents of the article "case study of MySQL changing the database data storage directory" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.