How to change Mysql or MariaDB Data Directory into New location

How to change Mysql or MariaDB Data Directory into New location

MariaDB is an alternative for MySQL which has more features and improved performance. Distributions and websites like Mozilla Corporation, ArchLinux, OpenSuse, Wikipedia, OLX, SlashGear, RHEL 7, Gentoohave changed to MariaDB. This tutorial will explain on how to change mysql or mariadb old location into new location.

Installation procedure

To start the installation process, first open the mysql shell and check the current data directory location of mysql.

[root@localhost ~]# mysql
MariaDB [(none)]>  select @@datadir 
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
MariaDB [(none)]>  exit

Now the directory location is listed. To stop the mariadb service, run the following command.

[root@localhost ~]# systemctl stop mariadb

The service is stopped successfully. Now create a new location for mysql data directory by executing a following command.

[root@localhost ~]# mkdir /mnt/new-mysql/

Transfer the old mysql data’ s into new location by creating a new mysql location and run the command as follows.

[root@localhost ~]# rsync -pavzxl /var/lib/mysql/ /mnt/new-mysql/

The data is moved to a new location successfully. Take the backup of old mysql location and move the contents.

[root@localhost ~]# mv /var/lib/mysql/ /var/lib/mysql.bkp/

The backup of the file is taken. Open the my.cnf file and make changes as shown below. Save and exit from the configuration file.

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/mnt/new-mysql/mysql/
socket=/mnt/new-mysql/mysql/mysql.sock
[client]
port=3306
socket=/mnt/new-mysql/mysql/mysql.sock

The changes are made successfully. Now start the mariadb service.

[root@localhost ~]# systemctl start mariadb.service

Then open the mysql shell and check the updated data directory location. The updated location is shown below.

[root@localhost ~]# mysql
MariaDB [(none)]>  select @@datadir 
+-----------------------+
| @@datadir             |
+-----------------------+
| /mnt/new-mysql/mysql/ |
+-----------------------+
1 row in set (0.00 sec)

Now create a database for testing.

MariaDB [(none)]>  create database test_db 
Query OK, 1 row affected (0.00 sec)

Open the newly created mysql data directory location by executing the following command.

[root@localhost ~]# cd /mnt/new-mysql/mysql/

List and check the newly created database presence by running the ls command.

[root@localhost mysql]# ls -l
total 29700
-rw-rw---- 1 mysql mysql    16384 Apr 10 13:11 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Apr 10 13:11 aria_log_control
.
.
.
-rw-rw---- 1 mysql mysql     4096 Apr 10 12:55 tables_priv.MYI
drwx------ 2 mysql mysql        6 Apr 10 13:11 test
drwx------ 2 mysql mysql       19 Apr 10 15:03 test_db

Wasn' t that an easy configuration procedure? MariaDB has more features and improved performance than MySQL.