adplus-dvertising
4.5 C
Amsterdam
angeloma
angeloma
Senior Writer and partner

How to change the data directory of MariaDB / MySQL?

- Advertisement -

Hello friends. We already know that MariaDB / MySQL is a very popular database driver that is the basis of many applications on the Internet. However, managing it sometimes is not so easy and today we will explain to you how to change the data directory of MariaDB / MySQL. This is so that you can indicate where you want the data to be stored.

Why change the data directory of MariaDB / MySQL?

The need to do this depends on each sysadmin. In the case of a developer, it may be required to have more control over the data or to copy it to a portable device.

Also, if we have different volumes on the system it may be a good idea to change the location to another partition so that if there is a failure on the main hard disk it doesn’t affect our data.

So it really depends on your needs but it is always good to know this process.

Change the MariaDB / MySQL data directory

First, proceed to install MariaDB if you don’t have it.

sudo apt install mariadb-server

In CentOS, Fedora, RHEL

sudo dnf install mariadb

In any case, we need MariaDB / MySQL properly installed.

Next, create the folder that will be the new location of the data:

mkdir -p [path]

Replace [path] with the location of your folder. Then, make mysql the owner of it

sudo chown -R mysql:mysql [path]

Now check which is the location where the application data is located.

sudo mysql -u root -p -e "SELECT @@datadir;"

Output:

Enter password: 
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

Once this is done, you need to stop the MariaDB / MySQL service.

sudo systemctl stop mariadb

Now copy all the data that has been made in MariaDB to the new location including permissions and attributes.

sudo cp -R -p /var/lib/mysql/* [path]

Then you need to modify the MariaBD configuration file which on DEbian, Ubuntu and derivatives is /etc/mysql/mariadb.conf.d/50-server.cnf and on CentOS is /etc/my.cnf.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

And under the [mysqld] section add

[mysqld]
datadir=[path]
socket=[path]/mysql.sock

And under [client]

port=3306
socket=[path]/mysql.sock

Remember to replace [path] with the path to the folder you have created and that will be the new location of the data.

Now start the service

sudo systemctl start mariadb

And you can check again the location of the data to see if everything went well.

sudo mysql -u root -p -e "SELECT @@datadir;"

Output:

Enter password: 
+-----------------------+
| @@datadir             |
+-----------------------+
| /home/angelo/newdata/ |
+-----------------------+

So, enjoy it.

One more thing…

You can choose any folder to be the new location for the data. However, if this folder is in /root or /home there will be a permissions problem that you have to fix.

To do this you have to edit the MariaDB service file

sudo nano /usr/lib/systemd/system/mariadb.service

And inside the file locate the ProtectHome value and change it from :

ProtectHome=true

To

ProtectHome=false

Save the changes and close the file.

To apply the changes you have to refresh the daemon directory.

sudo systemctl daemon-reload

And now repeat the process.

Conclusion

Changing the MariaDB data directory can be quite useful in database administration. Especially when it is sensitive data that we need to have always at hand.

MariaDB documentation

- Advertisement -

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Donate to Osradar

Latest article