Mysql 5.7, mysql5.7

Mysql 5.7, mysql5.7

As the data stored in the MySQL database grows, the original storage space has been fully occupied, resulting in the connection failure of mysql. Therefore, you must place the stored data in another place. The following are some steps in the operation process. Write it down and check it later.

1. Modify the directory where mysql data is stored

You need to modify the datadir in the/etc/my. cnf file. By default:

datadir=/var/lib/mysql

Because my/data/directory is large, change it:

datadir=/data/mysql/

Also modify/etc/init.d/mysqldFiledatadir=”$result”Changed:

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 bit of knowledge. The innoDB engine and MyISAM engine have different data files.

For the MyISAM engine, the data file is named "*. frm ","*. MYD ","*. MYI files are stored in the corresponding database folder under the "/var/lib/mysql" directory. You can directly move these files to the new data storage directory.

For the innoDB engine, the data is stored in"$ Innodb_data_home_dir "ibdata1The structure file is stored in table_name.frm.

5. Modify mysql database directory permissions and configuration files

chown mysql:mysql -R /data/mysql/

6. Modify the socket

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

socket=/data/mysql/mysql.sock

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

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

7. Restart the mysql Service

Run

service mysql restart 

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

Read/write:

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

Create a table:

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

Solution:

Delete the ib_logfile * file in the/data/mysql directory, so that the innoDB Engine table is normal.

Run again

mysql> REPAIR TABLE ***; 

This process may be slow for tables with large data volumes. After the execution is complete, the MyISAM engine table is also normal.

For more information about mysql, see

Database Operation Knowledge in MySQL

Mysql getting started

Mysql errors and Solutions

Mysql root Password Operation Method

The above section describes how to change the data storage location of a database in mysql 5.7. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner, and I would like to thank you for your support for the help House website!