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.
