How to change the MySQL database directory location under Linux (CentOS) system

How to change the MySQL database directory location under CentOS system

1. First we need to shut down MySQL. The command is as follows:

service mysqld stop

2. Then transfer the data. For safety reasons, we use the copy command cp to find the original directory of mysql first.

cd /var/lib
ls

After running this command, you will see the mysql directory, and then execute the cp command

cp -a mysql /home/mysql_data/

This will copy the database to /home/mysql_data

Note: (The -a parameter must be included, otherwise the permissions copied will be incorrect.)

If the database is large, it will take a long time and may time out. Please find relevant information on how to set ssh to not time out.

3. Then we modify the configuration files. There are three in total. Let me explain them one by one:

Modify the first file: Back up before modifying

cp /etc/my.cnf /etc/my.cnfbak

vi /etc/my.cnf

After opening, change the directory of datadir to /home/mysql_data/mysql .

Change socket to /home/mysql_data/mysql/mysql.sock . To be safe, you can comment out the original one and then add a new line to change it to the current directory.

Modify the second file: Back up before modifying

cp /etc/init.d/mysqld /etc/init.d/mysqldbak

vi /etc/init.d/mysqld

Note: The exact location is /etc/rc.d/init.d/mysqld , because there is a mapping from /etc/init.d to /etc/rc.d/init.d .

So just use the above command, it is also simple.

In the line datadir=/var/lib/mysql , change the path on the right side of the equal sign to your actual storage path: /home/mysql_data/mysql

Modify the third file: Back up before modifying

cp /usr/bin/mysqld_safe /usr/bin/mysqld_safebak

vi /usr/bin/mysqld_safe

Here we also modify the datadir directory to /home/mysql_data/mysql

4. Next, you need to create a mysql.sock link:

ln -s /home/mysql_data/mysql/mysql.sock /var/lib/mysql/mysql.sock

At this point all modifications are complete, start mysql

service mysqld start

Or restart linux

reboot

Check if Mysql is running normally. If it can start normally, there is basically no problem.

For more basic tutorials on MySQL, please click on the following related articles