How To Move a MariaDB Data Directory to a New Location on Linux

How To Move a MariaDB Data Directory to a New Location on Linux

1) Check the current Location of Data Directory:

Connect to MariaDB by using root or with your user and run below statement.
MariaDB [(none)]> select @@datadir;

you will see below output
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec) 
You can see that the current data directory path is /var/lib/mysql.

2) Stop the MariaDB Service 

Stop the MariaDB service by using below command
# sudo systemctl stop mysql
OR
# sudo service mysql stop

3) Check the MariaDB service status by using below command

# sudo systemctl status mysql
 You will see many messange line and also one line should be "Status: "MariaDB server is down". This confirms that MariaDB service is stopped successfully.

4) Copy Entire mysql to new location

use below command to copy entire mysql directory to new location. In my case I am copying to /Cassandra1
cp -R -p /var/lib/mysql/* /Cassandra1/mysql

5) Change the Config file

The location of configuration file is /etc/my.cnf
In configuration file add the below lines. 
[mysqld]
datadir=/Cassandra1/mysql
socket=/Cassandra1/mysql/mysql.sock
[client]
port=3306
socket=/Cassandra1/mysql/mysql.sock

6) Start MariaDB Service :
After Eidting the configuration, I tried to start the mysql service ( MariaDB) but kept getting below error.
Starting mysql (via systemctl):  Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
                                                           [FAILED]
Once I added the permission to mysql service to /Cassandra1/mysql directory and to my.cnf. It worked.

# chown mysql:mysql /etc/my.cnf
# chown mysql:mysql /Cassandra/mysql 

7) Create a database to test new location is working fine:

# mysql -uroot -p
Provide the password for root or any user you are using. After that create test database.
MariaDB > Create database TechBrothers;
Exit MariaDB and then check if the file is created in new location
# ls /Cassandra1/mysql 
You should see TechBrothers Directory in mysql directory. 


4 comments:

  1. Thanks for this...however if you have SELinux enabled, you will have problems starting mariadb service due to change in permissions. Because this is a test environment, I ended up disabling SELINUX to allow me proceed. Well done for the tutorials, they are very useful.

    ReplyDelete
  2. If you need to connect to MySQl database, you can use devart mydac.

    ReplyDelete
  3. I am trying the same in Redhat 8.8 Ootpa and mariadb not starting showing error https://stackoverflow.com/questions/77222970/mariadb10-5-datadir-change-gives-error-on-redhat-ootpa-8-8
    I posted this issue on the link. Please help me

    ReplyDelete