How to Change default mysql data directory on Redhat/Centos 7.X Linux
Data is the most important part for the organization so, in this post, we will learn how we can save our mysql database even our system got crashed or some things happen which is not good for the organization.
Suppose, we have one server in which we are running mysql and we all know mysql default data storage location is /var/lib/mysql
Here, suppose due to some internal system issues our os got corrupted and we are not able to recover our system and also we do not have any native database backup then what would we the option to avoid this situation in the future.
The Answer is to move your mysql default data directory in additional data disk. So in this post, we will learn how to change the default mysql data directory on Redhat/Centos 7.X Linux.
You need to have the following prerequisites:
- Basic understanding of MySQL database
- Sudo or administrative level access to your server
Step 1. Create a data directory
First of all, we will create a data directory on which we will mount our additional data drive. Here, our data drive mounted on /datadrive which is also our data directory.
# mkdir /datadrive
Check additional data drive mount point
# df -h
[root@linuxfork ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 32G 2.7G 29G 9% / devtmpfs 2.0G 0 2.0G 0% /dev tmpfs 2.0G 0 2.0G 0% /dev/shm tmpfs 2.0G 9.1M 2.0G 1% /run tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup /dev/sda1 497M 127M 371M 26% /boot /dev/sdb1 7.8G 2.1G 5.3G 28% /mnt/resource tmpfs 394M 0 394M 0% /run/user/0 tmpfs 394M 0 394M 0% /run/user/1000 /dev/sdc1 7.8G 36M 7.3G 1% /datadrive [root@linuxfork ~]#
Change the Ownership of data directory
By default the owner and group of the directory is root so, here we need to change the owner and group of the directory to mysql using below command.
# chown -R mysql:mysql /datadrive/
Step 2. Check the current data directory of Mysql
By default mysql data directory is /var/lib/mysql/, we can check it using below command. it will prompt for the password of mysql put the password and press Enter.
# mysql -u root -p -e "SELECT @@datadir;"
[root@linuxfork ~]# mysql -u root -p -e "SELECT @@datadir;" Enter password: +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ [root@linuxfork ~]#
Step 3. Stop the mysql service
Before moving the Default Data Directory of MySQL to additional drive we need to stop the service of MySQL using the below command.
# systemctl stop mysqld
[root@linuxfork ~]# systemctl status mysqld ● mysqld.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Thu 2020-07-09 21:11:38 UTC; 1min 5s ago Process: 967 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS) Process: 966 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS) Process: 933 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 966 (code=exited, status=0/SUCCESS) Jul 09 19:52:42 vm-mysql systemd: Starting MySQL Community Server... Jul 09 19:52:42 vm-mysql mysqld_safe: 200709 19:52:42 mysqld_safe Logging to '/var/log/mysqld.log'. Jul 09 19:52:42 vm-mysql mysqld_safe: 200709 19:52:42 mysqld_safe Starting mysqld daemon with databases from ...mysql Jul 09 19:52:47 vm-mysql systemd: Started MySQL Community Server. Jul 09 21:11:37 linuxfork systemd: Stopping MySQL Community Server... Jul 09 21:11:38 linuxfork systemd: Stopped MySQL Community Server. Hint: Some lines were ellipsized, use -l to show in full. [root@linuxfork ~]#
Now we can recursively copy the content of mysql to our data drive which is /datadrive .
# cp -R -p /var/lib/mysql/* /datadrive/
Step 4. Change the Mysql Conf File
Now, we need to change default mysql configuration which located at /etc/my.cnf in the conf file we will change the data directory from /var/lib/mysql to /datadrive under mysqld section.
Step 5. Manage SElinux Context on Data Directory
Now, we have to manage SElinux context on /datadrive using below command.
Before running below command install below package otherwise, it will fetch error semanage: command not found
# yum install policycoreutils-python*
# semanage fcontext -a -t mysqld_db_t "/datadrive(/.*)?" # restorecon -R /datadrive
Step 6. Restart the mysql service and verify
Now, we need to restart the service of mysql to reflect the changes.
## Restart the mysql service # systemctl restart mysqld ## Check the status of mysql service # systemctl status mysqld ## Enable the mysql service on boot # systemctl enable mysqld
Verify the data directory
[root@linuxfork ~]# mysql -u root -p -e "SELECT @@datadir;" Enter password: +-------------+ | @@datadir | +-------------+ | /datadrive/ | +-------------+ [root@linuxfork ~]#
Now, as we can see that the data directory of mysql is /datadrive, so we have successfully changed the default mysql data directory. If you are facing any error please use our comment section we will definitely get back to you soon!
I am a professional Cloud Engineer and founder of LinuxFork. I am working on Infrastructure Deployment and focusing on the integration of open source technologies with the Cloud. I am an avid Linux lover and open source enthusiast. mostly, I am working with Redhat and Ubuntu Linux in the production environment. apart from this I always believe in knowledge sharing.