Mysql Database Location: Change Data Storage Directory

The default data file storage directory of MYSQL is /var/lib/mysql. If you want to move the directory to /home/data, you need to perform the following steps.

Alibaba Cloud Simple Application Server:  Anti COVID-19 SME Enablement Program
$300 coupon package for all new SMEs and a $500 coupon for paying customers.

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 directory of /var/lib/mysql to /home/data

This moves the MySQL data files to /home/data/mysql

mv /var/lib/mysql /home/data/

4. Find the my.cnf configuration file
If there is no my.cnf configuration file in the /etc/ directory, please go to /usr/share/mysql/ to find the *.cnf file, copy one of them to /etc/ and rename it to my.cnf). The command is as follows:

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

5. Edit the MySQL configuration file /etc/my.cnf
To ensure that MySQL can work properly, you need to specify the location of the mysql.sock file. Modify the value of socket=/var/lib/mysql/mysql.sock to the right of the medium sign: /home/mysql/mysql.sock. The operation is as follows:
vi  my.cnf  (Use the vi tool to edit the my.cnf file and find the following data to modify it)

# The MySQL server[mysqld] 
port = 3306
#socket  = /var/lib/mysql/mysql.sock
socket  = /home/data/mysql/mysql.sock

6. Modify the MySQL startup script /etc/init.d/mysql
  Finally, you need to modify the MySQL startup script /etc/init.d/mysql, and change the path to the right of the equal sign in the line of datadir=/var/lib/mysql to your actual storage path: home/data/mysql.

vi /etc/init.d/mysql#datadir=/var/lib/mysql (comment this line)

datadir=/home/data/mysql (plus this line)

If it is CentOS, change the location of /usr/bin/mysqld_safe related files;
Finally, make a mysql.sock link:
in -s /home/data/mysql/mysql.sock /var/lib/mysql/mysql.sock

7. Restart the MySQL service

/etc/init.d/mysqld start
Or restart Linux with reboot command
If the work moves normally, it is successful, otherwise check it again according to the previous 7 steps. Also pay attention to the owner and permissions of the directory.
Copy content to clipboard

Code:

[root@sample ~]# chown -R mysql:mysql /home/data/mysql/  ← Change the ownership of the database to mysql
[root@sample ~]# chmod 700 /home/data/mysql/test/  ← Change the database directory attribute to 700
[root@sample ~]# chmod 660 /home/data/mysql/test/*  ← Change the attribute of the data in the database to 660

Error solution:

1. On CentOS, if mysql is installed via yum, then the above method may not be fully effective.

 Reason: There are many places in the mysql configuration file. In addition to changing the /etc/my.cnf file, you also need to change /usr/lib64/mysql/mysql_config

There is a line "ldata='/var/lib/mysql'" and "socket=/var/lib/mysql/mysql.sock" in this file, which need to be changed here

2. In addition, there are permissions issues, check /var/log/mysqld.log, and find Can't create test file /xxx/mysql/centos5.lower-test

This is because there is no permission to create or read files.

 The solution is to use the setenforce 0 command to allow the system to turn off permission verification, and then run the service mysqld start command to find OK.

 If you want to completely disable SELinux, you need to set the parameter selinux=0 in /etc/sysconfig/selinux

 Another way is to restorecon -FRv /home/data/mysql, or chcon -R -t mysqld_db_t /home/data/mysql