In this example, we are moving the data directory to the mount point /DBdata. You can also create and use a separate directory for this purpose.
Check the mount point free space and create new data directory
In this example we are considering /DBdata as new directory location.
[orahow@orahowdb ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/cl_clunode3-DBdata 500G 33M 500G 1% /DBdata
# mkdir directory_name
# chown -R mysql:mysql directory_name
[orahow@orahowdb ~]$ mysql -u root -p
Enter password:
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
STEP 2: Check the Current Status of MySQL Server:
[orahow@orahowdb ~]$ service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2018-05-23 19:33:55 IST; 764ms ago
Process: 13660 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Process: 13644 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 13659 (mysqld_safe)
Tasks: 12
CGroup: /system.slice/mysqld.service
├─13659 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─13825 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/run/...
STEP 3: To ensure data integrity, shut down the MySQL server before making changes to the data directory.
systemctl doesn’t display the outcome of all service management commands, so if you want to be sure you’ve succeeded, use the following command:
[orahow@orahowdb ~]$ sudo systemctl stop mysqld
[orahow@orahowdb ~]$ service mysql status
[orahow@orahowdb ~]$ sudo systemctl status mysqld
.......
.......
May 23 19:34:11 orahow.oradb systemd[1]: Stopping MySQL Community Server...
May 23 19:35:34 orahow.oradb systemd[1]: Stopped MySQL Community Server.
STEP 4: Copy and Synchronize Default Directory (/var/lib/mysql) to New Location.
To copy existing database directory to new location, we will use sync (Remote Sync) command which is the most commonly used command for copying and synchronizing files and directories remotely as well as locally in Linux/Unix systems.
[orahow@orahowdb ~]$ cd /var/lib/mysql
[orahow@orahowdb mysql]$ ls -lrt
total 116804
drwx------. 2 mysql mysql 4096 May 17 10:35 mysql
drwx------. 2 mysql mysql 4096 May 17 10:35 performance_schema
-rw-rw----. 1 mysql mysql 52 May 17 10:35 aria_log_control
-rw-rw----. 1 mysql mysql 16384 May 17 10:35 aria_log.00000001
-rw-r--r-- 1 mysql mysql 276 May 23 15:33 RPM_UPGRADE_MARKER-LAST
-rw-r--r-- 1 root root 276 May 23 15:33 RPM_UPGRADE_HISTORY
-rw-rw---- 1 mysql mysql 50331648 May 23 15:54 ib_logfile1
-rw-rw---- 1 mysql mysql 56 May 23 15:54 auto.cnf
srwxrwxrwx 1 mysql mysql 0 May 23 16:01 mysql.sock
-rw-rw----. 1 mysql mysql 18874368 May 23 16:01 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May 23 16:01 ib_logfile0
-rw-r----- 1 mysql mysql 0 May 23 16:13 binlog.index
We have already shut down the MySQL server, now we will copy the existing database directory to the new location with rsync command. For this we will use -a flag which will preserves the permissions and other directory properties, while -v provides verbose output so that you can follow the progress.
Note: Please don’t use trailing slash on the directory, otherwise rsync can dump the contents of the directory into the mount point instead of transferring into a containing mysql directory.
[orahow@orahowdb DBdata]$ sudo rsync -av /var/lib/mysql /DBdata
sending incremental file list
mysql/
mysql/RPM_UPGRADE_HISTORY
mysql/RPM_UPGRADE_MARKER-LAST
mysql/aria_log.00000001
mysql/aria_log_control
mysql/auto.cnf
mysql/performance_schema/threads.frm
sent 120,636,427 bytes received 1,962 bytes 80,425,592.67 bytes/sec
total size is 120,600,480 speedup is 1.00
Once the synchronization is complete, rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful.
[orahow@orahowdb ~]$ sudo mv /var/lib/mysql /var/lib/mysql_23rd_may_2018.bak
STEP 5: Configure and Point the Existing Data Directory to the New Location
By default, the datadir is set to /var/lib/mysql in the /etc/my.cnf file. Edit this configuration file to change the new data directory:
We have marked the necessary changes below. Please make an entry of client block[client], if it doesn’t exist and point socket file to the new location.
[orahow@orahowdb ~]$ sudo vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
datadir=/DBdata/mysql
socket=/DBdata/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
port=3306
socket=/DBdata/mysql/mysql.sock
[orahow@orahowdb ~]$ sudo systemctl start mysqld
[orahow@orahowdb ~]$ sudo systemctl status mysqld
[orahow@orahowdb ~]$ mysql -u root -p
Enter password:
mysql> select @@datadir;
Output
+----------------------------+
| @@datadir |
+----------------------------+
| /DBdata/mysql/ |
+----------------------------+