How to modify the location of data files in CentOS6.7 mysql5.6.33

Problem: The partition where MySQL stores data files has a small capacity and is currently full, causing MySQL to be unable to connect.

Solution:

1. Delete unnecessary data in the partition, such as log files, etc. (cannot solve the fundamental problem)

2. Expand the capacity of a disk

3. Modify the data storage location

To modify the data storage location:

1. View the mysql storage directory

#Installation directory --basedir=/usr
#Data storage location --datadir=/home/mysqlData/mysql
#User --user=mysql
#Log --log-error=/home/mysqlData/mysql/localhost.localdomain.err
#Port --port=3306

2. Stop MySQL service

service mysql stop

3. Create a directory

mkdir /home/mysql
chown -R mysql:mysql /home/mysqlData

4. Data file transfer (move the entire /var/lib/mysql to /home/mysqlData)

cp -afir /var/lib/mysql /home/mysqlData

5. Modify the MySql configuration document /etc/my.cnf configuration document

[client]
port = 3306
#Modified directory socket = /home/mysqlData/mysql/mysql.sock
[mysqld]
port = 3306
default-storage-engine = InnoDB #Default storage engine lower_case_table_names = 1 #Not case sensitive max-connections = 3000
character_set_server=utf8
#The modified directory datadir=/home/mysqlData/mysql
#The modified directory sock=/home/mysqlData/mysql/mysql.sock

6. Modify the MySQL startup script /etc/init.d/mysql

Change the datadir item in the /etc/init.d/mysql file to /home/mysqlData/mysql

vim /etc/init.d/mysql 

7. Start mysql service

service mysql start

The above is the method I introduced to you to modify the location of the data file in CentOS6.7 mysql5.6.33. 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 everyone for their support of the 123WORDPRESS.COM website!