How to Change a Default MySQL Data Directory On Rocky Linux 8.6

To Change A Default MySQL Data Directory On Rocky Linux 8.6

Introduction :

The MySQL "data directory" is the area where the Retain database is been stored. If you have accurately located the data directory, you'll see a file called "ibdata1" and the parent directory will have a subdirectory called "retain".

Installation Procedure:

Step 1: Check the OS version by using the below command

[root@linuxhelp ~]# cat /etc/os-release 
NAME="Rocky Linux"
VERSION="8.6 (Green Obsidian)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="8.6"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Rocky Linux 8.6 (Green Obsidian)"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:rocky:rocky:8:GA"
HOME_URL="https://rockylinux.org/"
BUG_REPORT_URL="https://bugs.rockylinux.org/"
ROCKY_SUPPORT_PRODUCT="Rocky Linux"
ROCKY_SUPPORT_PRODUCT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="Rocky Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

Step 2: Install mysql by using the below command

[root@linuxhelp ~]# yum install mysql-server* -y
Last metadata expiration check: 0:25:21 ago on Saturday 01 October 2022 01:40:51 AM IST.
Dependencies resolved.
====================================================================================================
 Package                      Arch     Version                                    Repository   Size
====================================================================================================
Installing:
 mysql-server                 x86_64   8.0.26-1.module+el8.4.0+652+6de068a7       appstream    25 M
Installing dependencies:
 mariadb-connector-c-config   noarch   3.1.11-2.el8_3                             appstream    14 k
 mecab                        x86_64   0.996-1.module+el8.3.0+242+87d3366a.9      appstream   392 k
 mysql                        x86_64   8.0.26-1.module+el8.4.0+652+6de068a7       appstream    12 M
 mysql-common                 x86_64   8.0.26-1.module+el8.4.0+652+6de068a7       appstream   133 k
 mysql-errmsg                 x86_64   8.0.26-1.module+el8.4.0+652+6de068a7       appstream   597 k
 protobuf-lite                x86_64   3.5.0-13.el8                               appstream   148 k
Enabling module streams:
 mysql                                 8.0                                                         

Transaction Summary
====================================================================================================
Install  7 Packages

Total download size: 38 M
Installed size: 195 M
Downloading Packages:
(1/7): mariadb-connector-c-config-3.1.11-2.el8_3.noarch.rpm          90 kB/s |  14 kB     00:00    
(2/7): mysql-common-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64.rpm 1.3 MB/s | 133 kB     00:00    
(3/7): mecab-0.996-1.module+el8.3.0+242+87d3366a.9.x86_64.rpm       1.5 MB/s | 392 kB     00:00    
(4/7): mysql-errmsg-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64.rpm 4.6 MB/s | 597 kB     00:00    
(5/7): protobuf-lite-3.5.0-13.el8.x86_64.rpm                        365 kB/s | 148 kB     00:00    
(6/7): mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64.rpm         14 MB/s |  12 MB     00:00    
(7/7): mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64.rpm  28 MB/s |  25 MB     00:00    
----------------------------------------------------------------------------------------------------
Total                                                                20 MB/s |  38 MB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                            1/1 
  Installing       : mariadb-connector-c-config-3.1.11-2.el8_3.noarch                           1/7 
  Installing       : mysql-common-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64                   2/7 
Installed:
  mariadb-connector-c-config-3.1.11-2.el8_3.noarch                                                  
  mecab-0.996-1.module+el8.3.0+242+87d3366a.9.x86_64                                                
  mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64                                                 
  mysql-common-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64                                          
  mysql-errmsg-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64                                          
  mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64                                          
  protobuf-lite-3.5.0-13.el8.x86_64                                                                 

Complete!

Step 3: Now start the MySQL service by using the below command

[root@linuxhelp ~]# systemctl start mysqld.service 

Step 4: Identify Current MySQL Data Directory by using the below command

[root@linuxhelp ~]# mysql -u root -e "select @@datadir;"
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

Step 5: Change the default MySQL/MariaDB Data Directory by using the below command

[root@linuxhelp ~]# mkdir /mnt/mysql-data
[root@linuxhelp ~]# cd /mnt/mysql-data/
[root@linuxhelp mysql-data]# chown -R mysql:mysql /mnt/mysql-data/

Step 6:Avoid data corruption, stop the service if it is currently running before proceeding by using the below command.

[root@linuxhelp mysql-data]# systemctl stop mysqld.service 

Step 7: Check the status for after service stop by using the below command

[root@linuxhelp mysql-data]# systemctl status mysqld.service 
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
Oct 01 02:07:28 linuxhelp systemd[1]: Starting MySQL 8.0 database server...
Oct 01 02:07:28 linuxhelp mysql-prepare-db-dir[85813]: Initializing MySQL database
Oct 01 02:07:34 linuxhelp systemd[1]: Started MySQL 8.0 database server.
Oct 01 02:09:31 linuxhelp systemd[1]: Stopping MySQL 8.0 database server...
Oct 01 02:09:32 linuxhelp systemd[1]: mysqld.service: Succeeded.
Oct 01 02:09:32 linuxhelp systemd[1]: Stopped MySQL 8.0 database server.

Step 8: Then copy recursively the contents of /var/lib/mysql to /mnt/mysql-data preserving original permissions and timestamps by using the below command.

[root@linuxhelp mysql-data]# cp -rvf -p /var/lib/mysql/* /mnt/mysql-data/
'/var/lib/mysql/auto.cnf' -> '/mnt/mysql-data/auto.cnf'
'/var/lib/mysql/binlog.000001' -> '/mnt/mysql-data/binlog.000001'
'/var/lib/mysql/binlog.index' -> '/mnt/mysql-data/binlog.index'
'/var/lib/mysql/ca-key.pem' -> '/mnt/mysql-data/ca-key.pem'
'/var/lib/mysql/ca.pem' -> '/mnt/mysql-data/ca.pem'
'/var/lib/mysql/client-cert.pem' -> '/mnt/mysql-data/client-cert.pem'
'/var/lib/mysql/client-key.pem' -> '/mnt/mysql-data/client-key.pem'
'/var/lib/mysql/#ib_16384_0.dblwr' -> '/mnt/mysql-data/#ib_16384_0.dblwr'
'/var/lib/mysql/#ib_16384_1.dblwr' -> '/mnt/mysql-data/#ib_16384_1.dblwr'
'/var/lib/mysql/ib_buffer_pool' -> '/mnt/mysql-data/ib_buffer_pool'
'/var/lib/mysql/ibdata1' -> '/mnt/mysql-data/ibdata1'
'/var/lib/mysql/ib_logfile0' -> '/mnt/mysql-data/ib_logfile0'
'/var/lib/mysql/ib_logfile1' -> '/mnt/mysql-data/ib_logfile1'
'/var/lib/mysql/#innodb_temp' -> '/mnt/mysql-data/#innodb_temp'
'/var/lib/mysql/mysql' -> '/mnt/mysql-data/mysql'
'/var/lib/mysql/mysql/general_log_213.sdi' -> '/mnt/mysql-data/mysql/general_log_213.sdi'
'/var/lib/mysql/mysql/general_log.CSM' -> '/mnt/mysql-data/mysql/general_log.CSM'
'/var/lib/mysql/mysql/general_log.CSV' -> '/mnt/mysql-data/mysql/general_log.CSV'
'/var/lib/mysql/mysql/slow_log_214.sdi' -> '/mnt/mysql-data/mysql/slow_log_214.sdi'
'/var/lib/mysql/mysql/slow_log.CSM' -> '/mnt/mysql-data/mysql/slow_log.CSM'
'/var/lib/mysql/mysql/slow_log.CSV' -> '/mnt/mysql-data/mysql/slow_log.CSV'
'/var/lib/mysql/mysql.ibd' -> '/mnt/mysql-data/mysql.ibd'
'/var/lib/mysql/mysql_upgrade_info' -> '/mnt/mysql-data/mysql_upgrade_info'
'/var/lib/mysql/performance_schema' -> '/mnt/mysql-data/performance_schema'
'/var/lib/mysql/performance_schema/rwlock_instances_101.sdi' -> '/mnt/mysql-data/performance_schema/rwlock_instances_101.sdi'
'/var/lib/mysql/performance_schema/setup_actors_102.sdi' -> '/mnt/mysql-data/performance_schema/setup_actors_102.sdi'
'/var/lib/mysql/performance_schema/setup_consumers_103.sdi' -> '/mnt/mysql-data/performance_schema/setup_consumers_103.sdi'
'/var/lib/mysql/performance_schema/setup_instrument_104.sdi' -> '/mnt/mysql-data/performance_schema/setup_instrument_104.sdi'
'/var/lib/mysql/performance_schema/setup_objects_105.sdi' -> '/mnt/mysql-data/performance_schema/setup_objects_105.sdi'
'/var/lib/mysql/performance_schema/setup_threads_106.sdi' -> '/mnt/mysql-data/performance_schema/setup_threads_106.sdi'
'/var/lib/mysql/performance_schema/table_io_waits_s_107.sdi' -> '/mnt/mysql-data/performance_schema/table_io_waits_s_107.sdi'
'/var/lib/mysql/performance_schema/table_io_waits_s_108.sdi' -> '/mnt/mysql-data/performance_schema/table_io_waits_s_108.sdi'
'/var/lib/mysql/performance_schema/table_lock_waits_109.sdi' -> '/mnt/mysql-data/performance_schema/table_lock_waits_109.sdi'
'/var/lib/mysql/performance_schema/processlist_100.sdi' -> '/mnt/mysql-data/performance_schema/processlist_100.sdi'
'/var/lib/mysql/private_key.pem' -> '/mnt/mysql-data/private_key.pem'
'/var/lib/mysql/public_key.pem' -> '/mnt/mysql-data/public_key.pem'
'/var/lib/mysql/server-cert.pem' -> '/mnt/mysql-data/server-cert.pem'
'/var/lib/mysql/server-key.pem' -> '/mnt/mysql-data/server-key.pem'
'/var/lib/mysql/sys' -> '/mnt/mysql-data/sys'
'/var/lib/mysql/sys/sys_config.ibd' -> '/mnt/mysql-data/sys/sys_config.ibd'
'/var/lib/mysql/undo_001' -> '/mnt/mysql-data/undo_001'
'/var/lib/mysql/undo_002' -> '/mnt/mysql-data/undo_002'

Step 9:Configure a New MySQL Data Directory.Locate the [mysqld] and [client] sections and make the following changes by using the below command.

[root@linuxhelp mysql-data]# cd /etc/my.cnf.d/
[root@linuxhelp my.cnf.d]# ls -la
total 24
drwxr-xr-x.   2 root root   95 Oct  1 02:06 .
drwxr-xr-x. 142 root root 8192 Oct  1 02:07 ..
-rw-r--r--.   1 root root  295 May 17  2021 client.cnf
-rw-r--r--.   1 root root  565 Sep 22  2021 mysql-default-authentication-plugin.cnf
-rw-r--r--.   1 root root  612 Sep 22  2021 mysql-server.cnf

[root@linuxhelp my.cnf.d]# vim mysql-server.cnf 

[root@linuxhelp my.cnf.d]# ls -la
total 24
drwxr-xr-x.   2 root root   95 Oct  1 02:11 .
drwxr-xr-x. 142 root root 8192 Oct  1 02:07 ..
-rw-r--r--.   1 root root  295 May 17  2021 client.cnf
-rw-r--r--.   1 root root  565 Sep 22  2021 mysql-default-authentication-plugin.cnf
-rw-r--r--.   1 root root  616 Oct  1 02:11 mysql-server.cnf

Step 10: Also change the configuration setting for client.cnf.Save the changes and then proceed with the next step by using the below command.

[root@linuxhelp my.cnf.d]# vim client.cnf 

Step 11: Add the SELinux security context to /mnt/mysql-data before restarting MariaDB by using the below command.

[root@linuxhelp my.cnf.d]# semanage fcontext -a -t mysqld_db_t "/mnt/mysql-data(/.*)?"
[root@linuxhelp my.cnf.d]# restorecon -R /mnt/mysql-data/
[root@linuxhelp my.cnf.d]# systemctl restart mysqld.service 

Step 12: Now, use the same command as in verify the location of the new data directory by using the below command

[root@linuxhelp my.cnf.d]# mysql -u root -e "select @@datadir;"
+------------------+
| @@datadir        |
+------------------+
| /mnt/mysql-data/ |
+------------------+

Step 13: Login to MariaDB, create a new database and then check /mnt/mysql-data by using the below command

[root@linuxhelp my.cnf.d]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye

Step 14: Next check move to the mysql data directory location and you find the created datadirectory by using the below command

[root@linuxhelp my.cnf.d]# cd /mnt/mysql-data/
[root@linuxhelp mysql-data]# ls -la
total 188876
drwxr-xr-x. 7 mysql mysql     4096 Oct  1 02:15  .
drwxr-xr-x. 4 root  root        36 Oct  1 02:08  ..
-rw-r-----. 1 mysql mysql       56 Oct  1 02:07  auto.cnf
-rw-r-----. 1 mysql mysql      179 Oct  1 02:09  binlog.000001
-rw-r-----. 1 mysql mysql      341 Oct  1 02:15  binlog.000002
-rw-r-----. 1 mysql mysql       32 Oct  1 02:14  binlog.index
-rw-------. 1 mysql mysql     1680 Oct  1 02:07  ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Oct  1 02:07  ca.pem
-rw-r--r--. 1 mysql mysql     1112 Oct  1 02:07  client-cert.pem
-rw-------. 1 mysql mysql     1680 Oct  1 02:07  client-key.pem
-rw-r-----. 1 mysql mysql   196608 Oct  1 02:15 '#ib_16384_0.dblwr'
-rw-r-----. 1 mysql mysql  8585216 Oct  1 02:07 '#ib_16384_1.dblwr'
-rw-r-----. 1 mysql mysql     3520 Oct  1 02:09  ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct  1 02:15  ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct  1 02:15  ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct  1 02:07  ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Oct  1 02:14  ibtmp1
drwxr-x---. 2 mysql mysql      187 Oct  1 02:14 '#innodb_temp'
drwxr-x---. 2 mysql mysql      143 Oct  1 02:07  mysql
-rw-r-----. 1 mysql mysql 25165824 Oct  1 02:15  mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 Oct  1 02:14  mysql.sock
-rw-------. 1 mysql mysql        6 Oct  1 02:14  mysql.sock.lock
-rw-r--r--. 1 mysql mysql        7 Oct  1 02:07  mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Oct  1 02:07  performance_schema
-rw-------. 1 mysql mysql     1680 Oct  1 02:07  private_key.pem
-rw-r--r--. 1 mysql mysql      452 Oct  1 02:07  public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Oct  1 02:07  server-cert.pem
-rw-------. 1 mysql mysql     1676 Oct  1 02:07  server-key.pem
drwxr-x---. 2 mysql mysql       28 Oct  1 02:07  sys
drwxr-x---. 2 mysql mysql        6 Oct  1 02:15  test
-rw-r-----. 1 mysql mysql 16777216 Oct  1 02:14  undo_001
-rw-r-----. 1 mysql mysql 16777216 Oct  1 02:15  undo_002

Conclusion:

We have reached the end of this article. In this guide, we have walked you through the steps required to Change a Default MySQL/MariaDB Data Directory. Your feedback is much welcome.