Mysql: Mysql Database Directory Storage Location Change

After using VPS for some time, I found that the disk space is almost full. My VPS bought a 500gbd disk at the time of purchase, and the provider gave away a 20GB high-performance system disk. In this way, the system has two disk spaces. So when installing MySQL for the first time, it is time to install the database directory on the system disk. (The first disk) After using it for a period of time, the database storage capacity becomes larger, and the storage space of 20GB is almost full. Therefore, the data storage space must be changed. Hey, the following is a simple operation, and heroes please give pointers to unreasonable points.

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.

Steps:

1. Check mysql database storage directory

Mysql-uroot-prootadmin

Show variables like'%dir%';

Quit;

(Check the path indicated by the datadir line)

2. Stop mysql service

Service mysqld stop

3. Create a new database storage directory

Mkdir/data/mysql

4. Store the database directory file before moving/copying to the new database storage directory location

Cp-R/usr/local/mysql/data/*/data/mysql/# or mv/usr/local/mysql/data/*/data/mysql

5. Modify the permissions and configuration files of the mysql database directory

Chown mysql:mysql-R/data/mysql/

Vim/etc/my.cnf

datadir=/data/mysql (designated as a new data storage directory)

Vim/etc/init.d/mysqld

Datadir=/data/mysql

Socket=/data/mysql/mysql.sock

6. Start database service

Service mysqld start

Note: According to the above simple 6-step operation, the path of the database directory has been successfully changed.

Remarks: The above system is CentOS 6.2 x64 mysql-5.5.13
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:

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

mv /var/lib/mysql /home/data/
This moves the MySQL data files to /home/data/mysql

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

In order 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  (plus 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, 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. A
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 multiple mysql configuration files. 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