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.
# sudo chcon -R systemu:objectr:mysqlddbt:s0 /mnt/volume-nyc1-01/mysql
and
systemctl -l status mariadb
Check if you have this ownership.