Solution to changing the data storage location of the database in MySQL 5.7

As the data stored in the MySQL database gradually increases, the original storage space has been filled up, resulting in MySQL being unable to connect. Therefore, it is necessary to change the location where the data is stored. Here are some steps in the process. Make a note to check back later.

1. Modify the directory where mysql data is stored

There are two places to modify, one of which is to modify the datadir in the /etc/my.cnf file. By default:

datadir=/var/lib/mysql

Because my /data/ directory is relatively large, I changed it to:

datadir=/data/mysql/

Also modify the /etc/init.d/mysqld file and change datadir=”$result” to:

datadir="/data/mysql"

2. Stop the mysql service

service mysql stop

3. Create a new data storage directory

mkdir /data/mysql

4. Move the data to the new database storage directory

mv /usr/local/mysql/data/* /data/mysql

Here is a little additional knowledge: the data files of the innoDB engine and the MyISAM engine are different.

For the MyISAM engine, data files are stored in the corresponding database folders under the "/var/lib/mysql" directory as three files: "*.frm", "*.MYD", and "*.MYI". Simply move these files to the new data storage directory.

For the innoDB engine, data is stored in the $innodb_data_home_dir” 的ibdata1 (usually), and the structure file exists in table_name.frm.

5. Modify the MySQL database directory permissions and configuration files

chown mysql:mysql -R /data/mysql/

6. Modify the socket

Modify socket in two places, /etc/my.cnf

socket=/data/mysql/mysql.sock

Create a connection file to /var/lib/mysql/mysql.sock

ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock

7. Restart mysql service

implement

service mysql restart

When I started reading and writing the database, I found the following problem:

Read and Write:

ERROR 1146 (42S02): Table ** doesn't exist

Create the table:

ERROR 1005 (HY000): Can't create table 'runoob_tbl' (errno: 13)

Solution:

In the /data/mysql directory, delete the ib_logfile* files so that the innoDB engine table will be normal.

Re-execute

mysql> REPAIR TABLE ***;

For tables with large amounts of data, this process may be slow. After the execution is completed, the MyISAM engine table is also normal.

MySQL related topics, friends who need it can refer to the following

Summary of database operation knowledge in MySQL

MySQL Getting Started Tutorial

Mysql error and solution

Mysql root password operation method

The above is the solution for changing the data storage location of the database in MySQL 5.7 that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank you very much for your support of the 123WORDPRESS.COM website!