In this article we will explain how to change the default MySQL data directory to a different mount point or location on a CentOS/RHEL 7 server.
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.
Sometimes there is a requirement to change the default mysql data directory (/var/lib/mysql) to a new location based on the expected use of the database server. The default /var location might not be feasible to hold the incoming data and over some period of time databases can run into I/O contention or space crunch.
Thus it is a good practice to relocate default MySQL’s data directory to the new location.
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.
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
STEP 1: Check the current MySQL Data Directory Location
To identify the current data directory, login into the mysql server and fire the below command.
[orahow@orahowdb ~]$ mysql -u root -p Enter password: mysql> select @@datadir; +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+
1 row in set (0.00 sec)
You can also fire the below command to get the current directory location.
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
Once done, save the configuration file using escape :wq! and exit.
STEP 6 — Finally Restart the MySQL server
We have updated the configuration file to use new location, now we are ready to start the MySQL server and verify the new directory location.
[orahow@orahowdb ~]$ sudo systemctl start mysqld [orahow@orahowdb ~]$ sudo systemctl status mysqld
To make sure that the new data directory is indeed in use, start the MySQL monitor. Look at the value for the data directory again:
[orahow@orahowdb ~]$ mysql -u root -p Enter password: mysql> select @@datadir; Output +----------------------------+ | @@datadir | +----------------------------+ | /DBdata/mysql/ | +----------------------------+
1 row in set (0.01 sec)
Now that you’ve restarted MySQL and confirmed that it’s using the new location, take the opportunity to ensure that your database is fully functional. Once you’ve verified the integrity of any existing data, you can remove the backup data directory with sudo rm -Rf /var/lib/mysql.bak.
Conclusion:
In this tutorial, we’ve moved and changed the MySQL’s data directory to the new location. Although we have updated and restarted the services, sometimes their might be a port conflict. So check if MySQL port is already in use or not. If port is already in use then check the process who is listening the port, kill that process and restart the MySQL server again.
