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.

Tag : MariaDB
Comment
cnccomputing
Jul 25 2021
mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)
Add a comment
FAQ
Q
Is SQL a programming language?
A
SQL (Structured Query Language) is a database management language for relational databases. SQL itself is not a programming language, but its standard allows creating procedural extensions for it, which extend it to the functionality of a mature programming language.
Q
What is MariaDB?
A
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.
Q
how to set SELinux Context for that directory for MYSQL?
A
Set Selinux Context for that directory
# sudo chcon -R systemu:objectr:mysqlddbt:s0 /mnt/volume-nyc1-01/mysql
Q
Is SQL Server the same as mysql?
A
Both MySQL and MS SQL Server are widely used enterprise database systems. MySQL is an open source RDBMS, whereas SQL Server is a Microsoft product. Microsoft allows enterprises to choose from several editions of SQL Server according to their needs and budget.
and
systemctl -l status mariadb
Q
After changing in my database are missing. But they present in the newly created directory. What can I do?
A
Check the permission, The ownership must be mysql:mysql
Check if you have this ownership.