change default mysql data directory

How to Change Default MySQL Data Directory in Linux

MySQL is a popular database system that is used by millions of websites & organizations all over the world. Since it is very popular, it is also a target of many malicious attacks. Typically, system administrators install MySQL along with a web server such as Apache/NGINX, and start building their websites. It is advisable to secure your MySQL/MariaDB installation as much as possible. One of the simple tricks to do this is to change the default data directory of MySQL. By default, /var/lib/mysql is the data directory of MySQL installation. In this article, we will learn how to change default MySQL data directory in Linux.


How to Change Default MySQL Data Directory in Linux

There are several reasons for changing default MySQL data directory, primary one being increased security. Another reason is because it may grow due to high usage and crash causing the entire database to collapse. This is also required if you are using dedicated network shared folders to store underlying data.


1. Create New Directory

We will assume that our MySQL data will be moved to /home/ubuntu/mysql-data. So we will create a new directory for this purpose, using the following commands. We need to ensure that the owner of this directory is mysql:mysql, the default user of MySQL database.

# mkdir /home/ubuntu/mysql-data
# chown -R mysql:mysql /home/ubuntu/mysql-data


2. Identify Current MySQL Directory

Although, the default data directory of MySQL is located at /var/lib/mysql, it is better to check it using the following MySQL query. Open terminal and execute the following query. You will be prompted for MySQL password.

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

Once you enter the correct password, you will see the following type of screen.


3. Copy MySQL Data Directory

You need to stop your MySQL service before you copy its data directory. Run the following commands to stop MySQL server.

------------- On SystemD ------------- 
# systemctl stop mariadb
# systemctl is-active mariadb

------------- On SysVInit ------------- 
# service mysqld stop
# service mysqld status

OR

# service mysql stop
# service mysql status

Then run the following command to copy MySQL data directory to new location.

# cp -R -p /var/lib/mysql/* /home/ubuntu/mysql-data


4. Update MySQL Configuration

The MySQL configuration file my.ini (Linux)/my.cnf(Windows) stores the location of data directory. We need to point it to new location. Run the following command to open it.

# vi /etc/my.cnf
OR
# vi /etc/mysql/my.cnf

Locate [mysqld] and [client] sections & make the following changes.

Under [mysqld]:
datadir=/home/ubuntu/mysql-data
socket=/home/ubuntu/mysql-data/mysql.sock

Under [client]:
port=3306
socket=/home/ubuntu/mysql-data/mysql.sock

Save and close the file.


5. Set SELinux Security Context (For RHEL/CentOS)

If you are running RHEL/CentOS system, then add SELinux security context to /home/ubuntu/mysql-data before restarting MySQL server.

# semanage fcontext -a -t mysqld_db_t "/home/ubuntu/mysql-data(/.*)?"
# restorecon -R /mnt/mysql-data


6. Restart MySQL Server

Then run the following command to restart MySQL server to apply changes.

------------- On SystemD ------------- 
# systemctl stop mariadb
# systemctl is-active mariadb

------------- On SysVInit ------------- 
# service mysqld stop
# service mysqld status

OR

# service mysql stop
# service mysql status

Now run the same command to view the data directory location. The output will be new data directory’s location.

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

In this article, we have learnt how to change default MySQL directory. By changing the default data directory in MySQL, you can prevent attackers from injecting malicious code into your system to access this directory and steal your data.

Also read:

How to Fix ‘firewall-cmd:command not found’ error
How to Protect Hard and Symbolic Links
How to Run Shell Script on Another Server
How to Manage Systemd Services on Remote Linux Systems
How to Synchronize Time With NTP in Linux

Leave a Reply

Your email address will not be published.