Saturday, April 20, 2019

Changing Data Directory for MySQL on CentOS7

I was recently asked to move the DEV and TEST environment of the application I am working on to  new servers.  The old servers were running CentOS6, new servers running on CentOS7 with SELinux enabled.  I realized after the fact that the server that is hosting MySQL had a separate /data volume that had much disk space on it, so I needed to change the data directory after several databases had already been instantiated.

Environment:
CentOS Linux release 7.6.1810 (Core) with SELinux enabled with enforcing
Server version: 5.7.25 MySQL Community Server (GPL)

1.  Login as a user that has root privileges, sudo to root

2.  Verify the current data directory
mysql -u root -p <enter in the root password>

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

3. Stop mysqld and verify it is stopped
systemctl stop mysqld
systemctl status mysqld

Apr 19 08:39:51 <servername> systemd[1]: Stopping MySQL Server...
Apr 19 08:39:55 <servername> systemd[1]: Stopped MySQL Server.

4.  Check if you have cp with the -a option
man cp

       -a, --archive
              same as -dR --preserve=all
       -d     same as --no-dereference --preserve=links
       --preserve[=ATTR_LIST]
              preserve the specified attributes (default: mode,ownership,timestamps), if possible  additional  attributes:  context, links, xattr, all
       -R, -r, --recursive
              copy directories recursively

Copies a directory exactly as it is (preserves ownership and groups), the files retain all their attributes, and symlinks are not dereferenced (-d).

5.  The new folder/volume I want to copy to is /data..  Copy the files from the source /var/lib/mysql to /data with -a option
cp -a /var/lib/mysql /data

6. Rename the current folder /var/lib/mysql to a different name to avoid confusion
mv /var/lib/mysql /var/lib/mysql-OLD

7.  Take a backup of the my.cnf file
cp /etc/my.cnf ~/my.cnf.ORIG

8.  Configure MySQL data directory to new folder location, add in port=3306, and configure datadir and socket to the new location. Also add a [client] group to the bottom of the file (after every options in the [mysqld] group) with port and socket matching the [mysqld] group.

vi /etc/my.cnf

[mysqld]
port=3306
datadir=/data/mysql
socket=/data/mysql/mysql.sock

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

9.  Add SELinux security context to the new folder.  semanageutility is not installed by default and was missing, so installed policycoreutils-python.  Perform a listing with security context.

yum -y install policycoreutils-python
semanage fcontext -a -t mysqld_db_t "/data(/.*)?"
restorecon -Rv /data

ls -lZ /data/mysql/
ls -lZ /var/lib/mysql-OLD

NOTE:  If you don't perform this step, you will see the following warnings/errors in the /var/log/mysqld.log file.

2019-04-19T13:31:39.698773Z 0 [Warning] Can't create test file /data/mysql/<servername>.lower-test
2019-04-19T13:31:39.837948Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.

9. Restart mysql
systemctl start mysqld
systemctl status mysqld

10.  Verify the new data directory
mysql -u root -p <enter in the root password>

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

These articles helped a lot:

1 comment:

I appreciate your time in leaving a comment!