Tuesday, 30 January 2018

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 


0 comments:

Post a Comment