Change a MySQL Data Directory to a New Location on CentOS 7

change-mysql-data-directroy-to-new-location.md

Change a MySQL Data Directory to a New Location on CentOS 7

mysql -u root -p

# Check current data directory
mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

# Exit from MySQL
mysql> exit

# Stop MySQL
sudo systemctl stop mysqld

# Check and confirm it stopped
sudo systemctl status mysqld

# Copy current data directory content into new location
sudo rsync -av /var/lib/mysql /data/mysql-data-directory

# Rename the old data directory as .bck
sudo mv /var/lib/mysql /var/lib/mysql.bak

# Change the data directory location in MySQL configuration file my.cnf
sudo vi /etc/my.cnf

# Change below lines with new location
datadir=/data/mysql-data-directory/mysql
socket=/data/mysql-data-directory/mysql/mysql.sock

# Add below three lines
[client]
port=3306
socket=/data/mysql-data-directory/mysql/mysql.sock

# Start the MySQL
sudo systemctl start mysqld
# Check the MySQL status
sudo systemctl status mysqld

# Login into MySQL and check the new location
mysql -u root -p
mysql> select @@datadir;
+-----------------------------------+
| @@datadir                         |
+-----------------------------------+
| /data/mysql-data-directory/mysql/ |
+-----------------------------------+
1 row in set (0.00 sec)

# Exit from MySQL
mysql> exit

References

1. How to Change a MySQL Data Directory to a New Location on CentOS 7