Detailed explanation of examples of changing database data storage location in mysql 5.7

As the data stored in the MySQL database gradually becomes larger, the original space for storing data has been filled up, causing mysql to be unable to connect. Therefore, we need to change the place where the stored data is stored. The following editor will share with you the solution to changing the data storage location of the database in MySQL 5.7. Let’s take a look.

As the data stored in the MySQL database gradually becomes larger, it has been The original space for storing data is full, causing mysql to be unable to connect. Therefore, it is necessary to change the place where the data is stored. Here are some steps in the process. Make a note of it and review it later.

1. Modify the directory where mysql data is stored

There are two places to modify. One 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 /etc/init.d/mysqldFile, change datadir="$result" to:

datadir=”/data/mysql”

2. Stop the mysql service

service mysql stop

3. Create New data storage directory

mkdir /data/mysql

4. Move the data to the new database storage directory

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

A few additional points here Knowledge, the data files of the innoDB engine and the MyISAM engine are different.

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

For the innoDB engine, the data is stored in the ibdata1 file of "$innodb_data_home_dir" (generally), and the structure file exists in table_name.frm.

5. Modify the mysql database directory permissions and configuration file

chown mysql:mysql -R /data/mysql/

6. Modify the socket

Modify the 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

executes

service mysql restart

and starts reading and writing operations on the database. It is found that there is such a problem:

Reading and writing:

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

Create table ：

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

Solution:

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

Execute again

mysql> REPAIR TABLE ***;

For tables with relatively large data volume, this process may be slower. After the execution is completed, the tables of the MyISAM engine are also normal.

Mysql related topics, friends in need can refer to

Database Operations in MySQLKnowledge Summary

Mysql Getting StartedTutorial

Mysql error reporting and solutions

Mysql root password operation method

[Related recommendations]

1. Mysql free video tutorial

2. Detailed explanation of how to import mysql big data into Navicat and report an error

3. Detailed explanation of examples of unlocking and locking tables in MYSQL

4. How to improve the query speed of millions of databases

5. 5 common database design mistakes

The above is the detailed content of Detailed explanation of examples of changing database data storage location in mysql 5.7. For more information, please follow other related articles on the PHP Chinese website!