CentOS Linux changes the MySQL database directory location
- 2020-05-15 02:19:50
- OfStack
Quote:
Because the database of MySQL is too large, the default installed /var disk can no longer accommodate the newly added data, so there is no way but to find a way to transfer the data directory.
Below, I will sort out the specific operation of transferring MySQL from /var/lib/mysql directory to /home/mysql_data/mysql directory:
1. First, we need to close MySQL. The command is as follows:
service mysqld stop
2. Then, transfer the data. For the sake of security, we use the copy command cp to find the original directory of mysql
cd /var/lib
ls
When you run this command, you will see the mysql directory and execute the cp command
cp-a mysql /home/mysql_data/ # thus copies the database under /home/mysql_data
Note :(-a parameter 1 must be taken, otherwise the permissions copied will be incorrect.)
If the database is relatively large, the time will be relatively long, and the time may be timed out. How to set the method of ssh not to time out? Please find relevant materials by yourself.
3. Then we modify the configuration files. There are 3 of 1.
Modify the first file: backup cp /etc/ my.cnf /etc/ my.cnfbak before modifying
vi /etc/my.cnf
After opening, change the directory of datadir to /home/mysql_data/mysql
Changing socket to/home/mysql_data/mysql/mysql sock # to be on the safe side, you can put the original commented out, and then to add 1 line, change directory now.
==================================================================================
Modify the second file: backup before you modify cp/etc/init d mysqld/etc/init d/mysqldbak
vi /etc/init.d/mysqld
Note: the accurate location is/etc rc d/init d/mysqld, because here there are 1 / etc/init d to/etc/rc d/init d mapping,
So use the above command can, also simple.
Among them datadir = / var/lib/mysql1 line, the right hand side of the path to your current actual storage path: / home/mysql_data/mysql
=================================================================================
Modify the third file: backup cp /usr/bin/mysqld_safe /usr/bin/mysqld_safebak before modification
vi /usr/bin/mysqld_safe
Here, too, the directory of datadir has been changed to /home/mysql_data/mysql
==================================================================================
4. The following is a link for mysql.sock:
ln -s /home/mysql_data/mysql/mysql.sock /var/lib/mysql/mysql.sock
System operation and maintenance warm reminder: qihang01 original content all rights reserved, reproduced please indicate the source and the original link
Now that all the changes have been made, let's start mysql
service mysqld start
Or restart linux
reboot
If it starts normally, the modification is successful.
Because the database of MySQL is too large, the default installed /var disk can no longer accommodate the newly added data, so there is no way but to find a way to transfer the data directory.
Below, I will sort out the specific operation of transferring MySQL from /var/lib/mysql directory to /home/mysql_data/mysql directory:
1. First, we need to close MySQL. The command is as follows:
service mysqld stop
2. Then, transfer the data. For the sake of security, we use the copy command cp to find the original directory of mysql
cd /var/lib
ls
When you run this command, you will see the mysql directory and execute the cp command
cp-a mysql /home/mysql_data/ # thus copies the database under /home/mysql_data
Note :(-a parameter 1 must be taken, otherwise the permissions copied will be incorrect.)
If the database is relatively large, the time will be relatively long, and the time may be timed out. How to set the method of ssh not to time out? Please find relevant materials by yourself.
3. Then we modify the configuration files. There are 3 of 1.
Modify the first file: backup cp /etc/ my.cnf /etc/ my.cnfbak before modifying
vi /etc/my.cnf
After opening, change the directory of datadir to /home/mysql_data/mysql
Changing socket to/home/mysql_data/mysql/mysql sock # to be on the safe side, you can put the original commented out, and then to add 1 line, change directory now.
==================================================================================
Modify the second file: backup before you modify cp/etc/init d mysqld/etc/init d/mysqldbak
vi /etc/init.d/mysqld
Note: the accurate location is/etc rc d/init d/mysqld, because here there are 1 / etc/init d to/etc/rc d/init d mapping,
So use the above command can, also simple.
Among them datadir = / var/lib/mysql1 line, the right hand side of the path to your current actual storage path: / home/mysql_data/mysql
=================================================================================
Modify the third file: backup cp /usr/bin/mysqld_safe /usr/bin/mysqld_safebak before modification
vi /usr/bin/mysqld_safe
Here, too, the directory of datadir has been changed to /home/mysql_data/mysql
==================================================================================
4. The following is a link for mysql.sock:
ln -s /home/mysql_data/mysql/mysql.sock /var/lib/mysql/mysql.sock
System operation and maintenance warm reminder: qihang01 original content all rights reserved, reproduced please indicate the source and the original link
Now that all the changes have been made, let's start mysql
service mysqld start
Or restart linux
reboot
If it starts normally, the modification is successful.