How to Change a MySQL Data Directory to a New Location on CentOS 7

Prerequisites

A CentOS 7 server with a non-root user with sudo privileges and MySQL installed.


In this example, we’re moving the data to a block storage device mounted at /data/mysql-data


Step 1 : Moving the MySQL Data Directory


To prepare for moving MySQL’s data directory, let’s verify the current location by starting an interactive MySQL session using the administrative credentials.

$ mysql -u root -p

mysql > select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

To ensure the integrity of the data, we’ll shut down MySQL before we actually make changes to the data directory:

$ sudo systemctl stop mysqld

Now that the server is shut down, we’ll copy the existing database directory to the new location with rsync. Using the -a flag preserves the permissions and other directory properties, while-v provides verbose output so you can follow the progress.

$ sudo rsync -av /var/lib/mysql /data/mysql-data

Once the rsync is complete, rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful.

$ sudo mv /var/lib/mysql /var/lib/mysql.bak

Step 2 :  Pointing to the New Data Location

MySQL has several ways to override configuration values. By default, the datadir is set to /var/lib/mysql in the /etc/my.cnf file. Edit this file to reflect the new data directory:

$ sudo vi /etc/my.cnf

Find the line in the [mysqld] block that begins with datadir=, which is separated from the block heading with several comments. Change the path which follows to reflect the new location. In addition, since the socket was previously located in the data directory, we’ll need to update it to the new location:

/etc/my.cnf

[mysqld]
. . .
datadir=/data/mysql-data/mysql
socket=/data/mysql-data/mysql/mysql.sock

After updating the existing lines, we’ll need to add configuration for the mysql client. Insert the following settings at the bottom of the file so it won’t split up directives in the [mysqld] block:

[client]
port=3306
socket=/data/mysql-data/mysql/mysql.sock


Restarting MySQL

$ sudo systemctl start mysqld
$ 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:

mysql > select @@datadir;

+----------------------------+
| @@datadir                  |
+----------------------------+
| /data/mysql-data/mysql/    |
+----------------------------+
1 row in set (0.01 sec)

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.


Fixing issues due to change in SElinux policy.

SELinux is aware of the MySQL file types (or contexts if you prefer) and will deny access to non MySQL (labeled context) files anyway.

You may face below error due SELINUX policy :

May 19 11:40:44 crummyworld3 mysqld[14010]: 2017-05-19 11:40:44 139833240783104 [ERROR] Plugin 'InnoDB' init function returned error.
May 19 11:40:44 crummyworld3 mysqld[14010]: 2017-05-19 11:40:44 139833240783104 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
May 19 11:40:44 crummyworld3 mysqld[14010]: 2017-05-19 11:40:44 139833240783104 [Note] Plugin 'FEEDBACK' is disabled.
May 19 11:40:44 crummyworld3 mysqld[14010]: 2017-05-19 11:40:44 139833240783104 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
May 19 11:40:44 crummyworld3 mysqld[14010]: 2017-05-19 11:40:44 139833240783104 [ERROR] Unknown/unsupported storage engine: InnoDB
May 19 11:40:44 crummyworld3 mysqld[14010]: 2017-05-19 11:40:44 139833240783104 [ERROR] Aborting

130321 11:50:51 mysqld_safe Starting mysqld daemon with databases from /data/mysql-data
...
2013-03-21 11:50:52 2119 [Warning] Can't create test file /data/mysql-data/mysql.lower-test
2013-03-21 11:50:52 2119 [Warning] Can't create test file /data/mysql-data/mysql.lower-test
...
2013-03-21 11:50:52 2119 [ERROR] /usr/sbin/mysqld: Can't create/write to file 
    '/data/mysql-data/mysql.pid' (Errcode: 13 - Permission denied)
2013-03-21 11:50:52 2119 [ERROR] Can't start server: can't create PID file: 
    Permission denied


Troubleshoot Method 1 :


Set Selinux to permissive 

$ setenforce 0

$ getenforce
Permissive


Set in config file

vi  /etc/selinux/config 

SELINUX=Permissive


Then Start the Mysql services 


The difference:

  • enforcing 

Blocks operations that SELinux does not allow

  • permissive 

Does not block the operations, but logs them (to /var/log/audit/audit.log )

  • disabled

Switches off SELinux entirely, to the extent that you cannot use



Troubleshoot Method 2 :

$ cd /var/lib/mysql

$ ls -lZ /var/lib/mysql

Output :

-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 auto.cnf
-rw-rw----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ibdata1
-rw-rw----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ib_logfile0
-rw-rw----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ib_logfile1
drwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysql
drwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 performance_schema
-rw-r--r--. root  root  unconfined_u:object_r:mysqld_db_t:s0 RPM_UPGRADE_HISTORY
-rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 RPM_UPGRADE_MARKER-LAST
drwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 test


Execute the following steps :

$ semanage fcontext -a -t mysqld_db_t "/data/mysql-data(/.*)?"  

$ grep -i mysql /etc/selinux/targeted/contexts/files/file_contexts.local

$ restorecon -R -v /data/mysql-data
 
$ ls -lZ /data/mysql

drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql

Start the Mysql process

$ systemctl start mysql


In this example semanage is adding the type  mysqld_db_t to the file context map (fcontext) for anything in the /data/mysql directory and subdirectories (“/data/mysql(/.*)?”, a regular expression). 

File mappings such as this are contained in the file /etc/selinux/targeted/contexts/files/file_contexts.local ; that file must subsequently be read in order to set the appropriate type on the file itself. 

That’s done by the restorecon utility, and at system reboot.  

If you want to change a file context immediately, but don’t need it to survive a reboot, there’s a chcon utility that performs that task.

Note :

Configure SELinux to enable MySQL’s use of port other than 3306 

$ semanage port -a -t mysqld_port_t -p tcp 3307 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: