MySQL and MariaDB are the open-source relational DBMS. These databases store their data in a specified directory by default. Furthermore, there may be times when we need to change the default data directory. This might be due to a variety of factors including a requirement for greater storage space, organizational goals, or improved data management.
Let us discuss how to change the default MySQL/MariaDB data directory on our Linux operating system.
How to Install MySQL or MariaDB on Linux
Before beginning the steps to change the default data directory, we must ensure that our Linux system must have MySQL or MariaDB installed. Fortunately, Linux makes it simple to install and configure the various database systems. To install MySQL or MariaDB, we may use the distribution’s package manager such as APT for Debian-based systems.
At first, we need to use the following command to update our APT package:
Then, to install the MySQL database in our Linux system, we have to type the following command:
We can also install MariaDB by typing the following command:
After we run the installation command, we will be asked to confirm the installation. Then, we may proceed by pressing the “Y” and “ENTER” keys.
Steps to Change the Default Data Directory in Linux
On a Linux system, changing the default data directory for MySQL or MariaDB requires many steps. When we expect our existing data directory can run out of storage due to expanding data, we can change the default data location. Also, when we wish to use a separate network share for storage, this step is very important.
1. Find Out the Default Mysql/Mariadb Location
Before making any changes, it is essential to determine our current MySQL or MariaDB data directory. This step ensures us to know where the current directory is present. This can be done with the help of the following command:
Output:
+ ----------------------- +
| @@datadir |
+ ------------------------ +
| /var/lib/mysql/ |
+ ----------------------- +
2. Data Copy to a New Location
We must stop any operating MySQL or MariaDB services to avoid data damage during the changeover. We can stop the service using the commands such as “systemctl” for SystemD or service for “SysVInit”, depending on our system’s init system.
# service mysql status
After stopping the service, we can copy the contents of the current data directory to the new location which is “/new/mysql”. This step ensures that all data is correctly moved to the new directory.
3. Configure the New Data Directory
To make MySQL or MariaDB recognize the new data directory, we need to update the configuration file. Usually, these files are located at “/etc/my.cnf” or “/etc/mysql/my.cnf”.
Inside the configuration file, we have to locate the “[mysqld]” and “[client]” sections and make the required changes.
socket= /new/mysql/mysql.sock
We have to save the changes and proceed to the next step.
4. Confirm the Changes Made
To confirm that the changes are completed successfully, we have to use the “mysql -u root -p -e “SELECT @@datadir”” command to check the location of the new data directory.
Output:
| @@datadir |
+ ---------------------- +
| /new/mysql/ |
+ ---------------------- +
Conclusion
Changing the default data directory for MySQL or MariaDB is an important task that may handle the expanding data demands and improve the data management. We can smoothly transfer our database systems to a new directory by understanding the steps that are explained in this article. This adaptability guarantees that the database can continue to function efficiently as the data needs to grow. Furthermore, understanding this procedure is important for administrators and users who want to improve the efficiency and management of their databases.