How to change MySQL Default datadir in CentOS

How to change MySQL Default datadir in CentOS

1. Create a data directory under the home Directory

Cd/home

Mkdir data

2. Stop the MySQL service process:

Mysqladmin-u root-p shutdown

3. Move the entire/var/lib/mysql directory to/home/data

Mv/var/lib/mysql/home/data/

In this way, the MySQL data file is moved to/home/data/mysql.

4. Find my. cnf configuration file.

Assume that my is not in the/etc/directory. for cnf configuration documents, go to/usr/share/mysql/and find *. cnf file, copy one of them to/etc/and change it to my. cnf. The command is as follows:

[Root @ test1 mysql] # cp/usr/share/mysql/my-medium.cnf/etc/my. cnf

5. Edit the MySQL configuration file/etc/my. cnf.

To ensure that MySQL works properly, you must specify the location where the mysql. sock document is generated. Change socket =/var/lib/mysql. sock to/home/mysql. sock. The procedure is as follows:

Vi my. cnf (use the vi tool to edit the my. cnf document and find the following data to modify)

# The MySQL server

[Mysqld]

Port = 3306

# Socket =/var/lib/mysql. sock)

Socket =/home/data/mysql. sock (add this line)

6. Modify the MySQL STARTUP script/etc/init. d/mysql

Finally, you need to modify the MySQL STARTUP script/etc/init. d/mysql: change the path on the Right of datadir =/var/lib/mysql to your actual storage path: home/data/mysql.

[Root @ test1 etc] # vi/etc/init. d/mysql

# Datadir =/var/lib/mysql (comment this row)

Datadir =/home/data/mysql (add this row)

If it is CentOS, you need to change the location of the/usr/bin/mysqld_safe file;

Finally, create a mysql. sock link:
Ln-s/home/data/mysql. sock/var/lib/mysql. sock

7. Restart the MySQL service.

/Etc/init. d/mysql start

Or use the reboot command to restart Linux.

If it works properly, it will succeed. Otherwise, check again against the previous seven steps. Pay attention to the owner and permissions of the directory.

Copy content to clipboard
Code:
[Root @ sample ~] # Chown-R mysql: mysql/home/data/mysql/logs change the database to mysql

[Root @ sample ~] # Chmod 700/home/data/mysql/test/CATALOG change the database Directory attribute to 700

[Root @ sample ~] # Chmod 660/home/data/mysql/test/* modify the attribute of data in the database to 660

In Windows:

1. Change the master path of the database in the MySQL configuration file My. ini.

Open the default MySQL installation folder C: \ Program Files \ MySQL Server 5.1 In my. INI file, click "edit" and "Search" at the top of the notepad ", enter datadir in the search content and click "find next" to go to "Path to the database root database storage master Path" parameter settings, find datadir = "C: /Documents and Settings/All Users/Application Data/MySQL Server 5.1/Data/"is the default database storage master path setting, which is now changed to C: \ mysql \ data (what you want) folder. The correct setting is datadir = "C:/mysql/data /".

After the changes are completed, click "file" in the menu bar and then click "save ".

2. Copy the database files and folders in the master path of the old database storage to the new master path.

Copy All the files and folders in the C:/Documents and Settings/All Users/Application Data/MySQL Server 5.1/Data/folder to the folder you created.

3. Restart the MySQL service.

4. Verify that the operation to change the primary path of the database is successful.

To verify whether the change is successful, we can create a new database named xunmeinet in MySQL. After the establishment, observe C: whether the \ mysql \ data folder contains a folder with the same name as the database name.