In default MySQL installation, Data Directory pointed to “/var/lib/mysql/” . As a best practice, it’s recommended to move Data directory to new location
which contains more disk space than default root partition. This tutorial guides you how to Move MySQL data directory to new location on CentOS or RHEL. Even data directory contains data, you can still move it to another location, but you have to be careful if you try this on production environment. Let’s go through it quickly 🙂
1) Prepare new location
|
1 |
#mkdir /opt/newmysql_datadir |
2) Find current Data Directory location
you can get it from /etc/my.cnf if it’s defined on. To verify it or it’s not mentioned on configuration file, most probably use default location.
let’s find that out
log into mysql server and run following command
|
1 2 3 4 5 6 7 |
select @@datadir; +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec) |
As per above current location is “/var/lib/mysql/”
3) Shut down MySQL server
|
1 |
#systemctl stop mysqld |
4) Copy MySQL data directory to new location
|
1 2 |
#cd /opt/newmysql_datadir #cp -rp /var/lib/mysql . |
now new location is /opt/newmysql_datadir/mysql
5) Modify SELinux to allow MySQL to use the different (non default) path
This step is mandatory if your system enabled with SELinux, otherwise you can ignore this step
|
1 2 |
#semanage fcontext -a -s system_u -t mysqld_db_t "/opt/newmysql_datadir/mysql(/.*)?" #restorecon -Rv /opt/newmysql_datadir/mysql |
6) Update new settings to my.cnf
Find the line in the [mysqld] block that begins with datadir=. Change the path which follows to reflect the new location. In addition to that, socket was previously located in the data directory, we’ll need to update it to the new location
It should like below after updating.
|
1 2 3 4 5 |
[mysqld] . . . datadir=/opt/newmysql_datadir/mysql socket=/opt/newmysql_datadir/mysql/mysql.sock . . . |
Apart from that we’ll need to add configuration for the MySQL client. Insert the following settings at [client] block which is at the bottom of the file.
|
1 2 3 |
[client] port=3306 socket=/opt/newmysql_datadir/mysql/mysql.sock |
7) Start MySQL server
|
1 |
#systemctl start mysqld |
If it won’t start , you may need to troubleshoot by checking MySQL error log. You can make comments any issues if you have faced here, I’m always happy to assist you !!