Thứ Ba, 18 tháng 7, 2017

How to Change a Default MySQL/MariaDB Data Directory in Linux



How to Change a Default MySQL/MariaDB Data Directory in Linux


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 datadirectory (/var/lib/mysql) to a different location. This is the case when such 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 MySQLand 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.
Identify MySQL Data Directory
Identify MySQL Data Directory
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:
Stop MySQL Service
Stop MySQL Service
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
Copy MySQL Data Directory to New Location
Copy MySQL Data Directory to New Location
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.conf
OR
# vi /etc/mysql/my.conf
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.
Configure New MySQL Data Directory
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;"

Verify MySQL New Data Directory
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;"

Check MySQL New Data Directory
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!
 Source: https://www.tecmint.com/change-default-mysql-mariadb-data-directory-in-linux/

Share This!


Không có nhận xét nào:

Đăng nhận xét