Move MySQL or MariaDB Databases To A New Location

Introduction

This quick TIP covers moving Mysql’s / MariaDB’s database storage to a different location.  This example was done on CentOS7.

Moving the Databases

First we need to stop mysql if it is running. Type the following:
> service mysqld stop

Next copy the data to the new location and fix the ownership.
> cp -R /var/lib/mysql /home/
> mv /home/mysql /home/databases
> chown -R mysql:mysql /home/databases

Save the old databases directory.
> mv /var/lib/mysql/ /var/lib/mysql_old

Now we need to take care of the needs of some scripts that may not know the data has been moved.
> ln -s /home/databases/ /var/lib/mysql
> chown mysql:mysql /var/lib/mysql

Next edit /etc/my.cnf.d/server.cnf to look like the folloeing

[mysqld]
 #datadir=/var/lib/mysql
 #socket=/var/lib/mysql/mysql.sock
 datadir=/home/databases
 socket=/home/databases/mysql.sock
 user=mysql
 # Disabling symbolic-links is recommended to prevent assorted security risks
 symbolic-links=0

[mysqld_safe]
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid

Finally we can restart Mysql. Type the follwoing:
> systemctl restart mariadb

Conclusion

Normally this isn’t done much.  However there are those times when the databases have to be in a different place.

Subscribe
Notify of
0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments