How to change data directory path in MySQL

 

How to change data directory path in MySQL.

 

Below steps has been performed to modify the datadir path:

 

1. To see the current data directory location:

 

mysql> show global variables like 'datadir';

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| datadir       | /var/lib/mysql/ |

+---------------+-----------------+

1 row in set (0.00 sec)

 

2. Create the physical path

# mkdir mysqldata

# pwd

/mysqldata

 

3. Stop mysql server

systemctl stop mysqld

 

4. Edit the path in file /etc/my.cnf as mentioned below

#datadir=/var/lib/mysql

datadir=/mysqldata   >>> New path mentioned here

 

5. Copy all the files from current data directory to new data directory:

 

cd /var/lib/mysql

cp -RH * /mysqldata/

6. Change the permission to mysql 

chown -R mysql:mysql mysqldata

 

7. Start the mysql server, and check the path now

 

systemctl start mysqld

   

    mysql> show global variables like 'datadir';

+---------------+-------------+

| Variable_name | Value       |

+---------------+-------------+

| datadir       | /mysqldata/ |

+---------------+-------------+

1 row in set (0.01 sec)

 

    You are done.