How do i change the mysql data directory to a new location in centos 8?

I found this step by step guide working for me.

You must install:

yum install policycoreutils-python

Guide:

View the SELinux context of the default database location for mysql:

~]# ls -lZ /var/lib/mysql
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql

This shows mysqld_db_t which is the default context element for the location of database files. This context will have to be manually applied to the new database location that will be used in this example in order for it to function properly.

Stop the mysqld daemon:

~]# systemctl stop mariadb.service

Create a new directory for the new location of the database(s). In this example, /mysql/ is used:

~]# mkdir -p /mysql

Copy the database files from the old location to the new location:

~]# cp -R /var/lib/mysql/* /mysql/

Change the ownership of this location to allow access by the mysql user and group. This sets the traditional Unix permissions which SELinux will still observe:

~]# chown -R mysql:mysql /mysql

Run the following command to see the initial context of the new directory:

~]# ls -lZ /mysql
drwxr-xr-x. mysql mysql unconfined_u:object_r:usr_t:s0   mysql

The context usr_t of this newly created directory is not currently suitable to SELinux as a location for MariaDB database files. Once the context has been changed, MariaDB will be able to function properly in this area.

Open the main MariaDB configuration file /etc/my.cnf with a text editor and modify the datadir option so that it refers to the new location. In this example the value that should be entered is /mysql:

[mysqld]
datadir=/mysql

Save this file and exit.

Start mysqld. The service should fail to start, and a denial message will be logged to the /var/log/messages file:

~]# systemctl start mariadb.service
Job for mariadb.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.

However, if the audit daemon is running and with him the setroubleshoot service, the denial will be logged to the /var/log/audit/audit.log file instead:

SELinux is preventing `/usr/libexec/mysqld` "write" access on /mysql. For complete SELinux messages. run `sealert -l b3f01aff-7fa6-4ebe-ad46-abaef6f8ad71`

The reason for this denial is that mysql is not labelled correctly for MariaDB data files. SELinux is stopping MariaDB from having access to the content labelled as usr_t. Perform the following steps to resolve this problem:

Run the following command to add a context mapping for mysql. Note that the semanageutility is not installed by default. If it missing on your system, install the policycoreutils-python package.

~]# semanage fcontext -a -t mysqld_db_t "/mysql(/.*)?"

This mapping is written to the /etc/selinux/targeted/contexts/files/file_contexts.local file:

~]# grep -i mysql /etc/selinux/targeted/contexts/files/file_contexts.local

/mysql(/.*)?    system_u:object_r:mysqld_db_t:s0

Now use the restorecon utility to apply this context mapping to the running system:

~]# restorecon -R -v /mysql

Now that the mysql location has been labelled with the correct context for MariaDB, mysqld starts:

~]# systemctl start mariadb.service

Confirm the context has changed for mysql:

~]$ ls -lZ /mysql
drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql

The location has been changed and labelled, and mysqld has started successfully. At this point all running services should be tested to confirm normal operation.

After installing the components of a LAMP stack on a CentOS/RHEL 7 server, there are a couple of things you may want to do.

Some of them have to do with increasing the security of the Apache and MySQL / MariaDB, while others may be applicable or not according to our setup or needs.

For example, based on the expected use of the database server, we may want to change the default data directory (/var/lib/mysql) to a different location. This is the case when such a directory is expected to grow due to high usage.

Otherwise, the filesystem where /var is stored may collapse at one point causing the entire system to fail. Another scenario where changing the default directory is when we have a dedicated network share that we want to use to store our actual data.

For this reason, in this article, we will explain how to change the default MySQL / MariaDB data directory to a different path on a CentOS/RHEL 7 server and Ubuntu/Debian distributions.

Although we will use MariaDB, the concepts explained and the steps taken in this article apply both to MySQL and to MariaDB unless noted otherwise.

Changing the default MySQL/MariaDB Data Directory

Note: We are going to assume that our new data directory is /mnt/mysql-data. It is important to note that this directory should be owned by mysql:mysql.

# mkdir /mnt/mysql-data
# chown -R mysql:mysql /mnt/mysql-data

For your convenience, we’ve divided the process into 5 easy-to-follow steps:

Step 1: Identify Current MySQL Data Directory

To begin, it is worthy and well to identify the current data directory using the following command. Do not just assume it is still /var/lib/mysql since it could have been changed in the past.

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

After you enter the MySQL password, the output should be similar to.

Step 2: Copy MySQL Data Directory to a New Location

To avoid data corruption, stop the service if it is currently running before proceeding. Use the systemd well-known commands to do so:

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

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

OR

# service mysql stop
# service mysql status

If the service has been brought down, the output of the last command should be as follows:

Then copy recursively the contents of /var/lib/mysql to /mnt/mysql-data preserving original permissions and timestamps:

# cp -R -p /var/lib/mysql/* /mnt/mysql-data

Step 3: Configure a New MySQL Data Directory

Edit the configuration file (my.cnf) to indicate the new data directory (/mnt/mysql-data in this case).

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

Locate the [mysqld] and [client] sections and make the following changes:

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

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

Save the changes and then proceed with the next step.

Step 4: Set SELinux Security Context to Data Directory

This step is only applicable to RHEL/CentOS and its derivatives.

Add the SELinux security context to /mnt/mysql-data before restarting MariaDB.

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

Next restart the MySQL service.

------------- 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, use the same command as in Step 1 to verify the location of the new data directory:

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

Step 5: Create MySQL Database to Confirm Data Directory

Login to MariaDB, create a new database and then check /mnt/mysql-data:

# mysql -u root -p -e "CREATE DATABASE tecmint;"

Congratulations! You have successfully changed the data directory for MySQL or MariaDB.

Summary

In this post, we have discussed how to change the data directory in a MySQL or MariaDB server running on CentOS/RHEL 7 and Ubuntu/Debian distributions.

Do you have any questions or comments about this article? Feel free to let us know using the form below – we are always glad to hear from you!

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

We are thankful for your never ending support.

How do I move a MySQL database to another directory?

How do I change the MariaDB data directory to a new location in CentOS 8?

How do I change the data directory in MySQL 8?

How do I move a MySQL database to another drive in Linux?