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, changedatadir="$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 ibdata1file of "$innodb_data_home_dir" (generally), and the structure file exists in table_name.frm.
5. Modify the mysql database directory permissions andconfiguration 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]
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!