Analysis of changing MySQL database storage directory on CentOS 7

I have summarized two articles before“ MySQL changes database data storage directory "And" Change MySQL database data store directory on Ubuntu ”, is a simple summary of relevant cases in the work. At the beginning, the experience was insufficient and the cognition was limited, so now it seems that the original blog really appeared to be simple and not comprehensive, and there was no way to do this. At that time, some environments or cases were not involved, so the article was very difficult to cover all aspects, slightly thin and simple, and this is normal. Blog is not a paper, but a summary of personal notes and knowledge points. And it is based on the experience summary and knowledge summary of cognition at that time. When I switched the database of DPA to MySQL a few days ago, I encountered this problem again, so I will summarize it.

 

System environment: CentOS Linux release 7.5.1804 (Core)

MySQL version: 8.0.18 MySQL Community Server - GPL

 

 

By default, MySQL's data directory is usually under / var/lib/mysql

 

1: First, find out the configuration information of MySQL

 

Find the data directory of MySQL and the location of the configuration file my.cnf and error log.

 

mysql> show variables like 'datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.03 sec)
mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.01 sec)
 
mysql> 

 

 

[root@KerryDB ~]# whereis my.cnf

my: /etc/my.cnf

 

 

2: Make a cold backup of the database in case of emergency. Of course, you can back it up in other ways.

 

 

[root@KerryDB ~]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
 
[root@KerryDB ~]# cp -rp /var/lib/mysql  /tmp/coldbackup

 

 

 

3: Move data directory to another directory

 

Let's assume that I need to migrate the data directory of the database to / mysql_data /.

 

[root@KerryDB ~]# chown -R mysql:mysql /mysql_data/
 
[root@KerryDB ~]# cd /var/lib
[root@KerryDB lib]# mv mysql  /mysql_data/

 

Note: it is recommended to use mv to move the database directory instead of copying (cp command), because SELinux context will be lost when copying, and you must set it manually later. If you use cp command to copy files, if you do not use the parameter - p, remember to modify the directory owner, otherwise you may encounter "MySQL error: 1017 Can't find" File: (errno: 13) "error.

 

The difference between mv and cp

 

 

4: Modify the configuration file my.cnf

 

In general, you only need to modify parameters such as datadir and socket, and judge which parameters need to be modified according to your actual situation.

 

#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
 
datadir=/mysql_data/mysql
socket=/mysql_data/mysql/mysql.sock

 

 

After modification, for CentOS or RHEL operating system, you may need to modify SELinux settings, while for Ubuntu or Debian, you need to modify AppArmor settings (this article Change MySQL database data store directory on Ubuntu It has already been introduced, and will not be repeated here). For example, for CentOS or RHEL, if SELinux is disabled, you can directly ignore this setting, because if SELinux is disabled, many miscellaneous problems will be avoided, but we do not want to avoid the problem, but we need to find out the root cause of the problem.

 

First you need to check if SELinux is turned on. As follows:

 

[root@KerryDB ~]# /usr/sbin/sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   enforcing
Mode from config file:          enforcing
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Max kernel policy version:      31
 
[root@KerryDB ~]# getenforce
Enforcing

 

If SELinux is on, if you do not make some configuration at this time, you will encounter an error like the following when starting MySQL service:

 

[root@KerryDB ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2020-03-09 15:43:06 +08; 2min 54s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 14903 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 14879 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 14903 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"
    Error: 13 (Permission denied)
 
Mar 09 15:43:06 KerryDB systemd[1]: Starting MySQL Server...
Mar 09 15:43:06 KerryDB systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Mar 09 15:43:06 KerryDB systemd[1]: Failed to start MySQL Server.
Mar 09 15:43:06 KerryDB systemd[1]: Unit mysqld.service entered failed state.
Mar 09 15:43:06 KerryDB systemd[1]: mysqld.service failed.
 
 
 
 
[root@KerryDB ~]# journalctl -xe
Mar 09 15:39:05 KerryDB polkitd[731]: Registered Authentication Agent for unix-process:14836:224127979 (system bus name :1.1355 [/usr/bin/pkttyagent --notify
Mar 09 15:39:05 KerryDB systemd[1]: Stopping MySQL Server...
-- Subject: Unit mysqld.service has begun shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has begun shutting down.
Mar 09 15:39:07 KerryDB systemd[1]: Stopped MySQL Server.
-- Subject: Unit mysqld.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has finished shutting down.
Mar 09 15:39:07 KerryDB polkitd[731]: Unregistered Authentication Agent for unix-process:14836:224127979 (system bus name :1.1355, object path /org/freedeskt
Mar 09 15:43:06 KerryDB polkitd[731]: Registered Authentication Agent for unix-process:14863:224152052 (system bus name :1.1356 [/usr/bin/pkttyagent --notify
Mar 09 15:43:06 KerryDB systemd[1]: Starting MySQL Server...
-- Subject: Unit mysqld.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has begun starting up.
Mar 09 15:43:06 KerryDB systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Mar 09 15:43:06 KerryDB systemd[1]: Failed to start MySQL Server.
-- Subject: Unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has failed.
-- 
-- The result is failed.
Mar 09 15:43:06 KerryDB systemd[1]: Unit mysqld.service entered failed state.
Mar 09 15:43:06 KerryDB systemd[1]: mysqld.service failed.
Mar 09 15:43:06 KerryDB polkitd[731]: Unregistered Authentication Agent for unix-process:14863:224152052 (system bus name :1.1356, object path /org/freedeskt
lines 4369-4401/4401 (END)

 

 

No detailed error information can be seen in the above log. At this time, you should check the error log / var/log/mysqld.log, as shown below. You will see errors like "OS errno 13 - Permission denied" and "Can't create test file xxx"

 

 

mysqld: File './binlog.~rec~' not found (OS errno 13 - Permission denied)

2020-03-09T07:43:06.927360Z 0 [Warning] [MY-010091] [Server] Can't create test file /mysql_data/mysql/mysqld_tmp_file_case_insensitive_test.lower-test

2020-03-09T07:43:06.927453Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 14903

2020-03-09T07:43:06.929669Z 0 [Warning] [MY-010091] [Server] Can't create test file /mysql_data/mysql/mysqld_tmp_file_case_insensitive_test.lower-test

2020-03-09T07:43:06.929681Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /mysql_data/mysql/ is case insensitive

2020-03-09T07:43:06.930542Z 0 [ERROR] [MY-010846] [Server] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.

2020-03-09T07:43:06.930594Z 0 [ERROR] [MY-010817] [Server] MYSQL_BIN_LOG::open_index_file failed to sync the index file.

2020-03-09T07:43:06.930657Z 0 [ERROR] [MY-010119] [Server] Aborting

2020-03-09T07:43:06.930830Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.

 

Note: by default, the time in the log is UTC time, not local time. Unless you set it up. For details, please refer to my blog "MySQL 5.7 parameter log ﹣ timestamps".

 

At this point, if you use the chcon command to change the SELinux context type in the new directory, I think some blog introductions will be OK. But in my environment, the same error will still be reported after such processing

 

 

[root@KerryDB ~]# chcon -R -t mysqld_db_t /mysql_data/

[root@KerryDB ~]#

 

After the toss test, it is found that you need to modify mysqld dB in file context as follows:

 

 

[root@KerryDB ~]# cd /etc/selinux/targeted/contexts/files/

[root@KerryDB files]# ls -lrt

 

 

Before amendment:

 

/var/lib/mysql(-files|-keyring)?(/.*)? system_u:object_r:mysqld_db_t:s0

 

 

After amendment:

 

/mysql_data/mysql(-files|-keyring)?(/.*)? system_u:object_r:mysqld_db_t:s0

 

In fact, using chcon to modify the security context of objects (files) is quite difficult to master and a little difficult. Unless you are a Linux expert, you usually use the tool semanage to query and modify the security context of the default directory

 

 

In general, policycoreutils Python may not be installed by default, and the corresponding components need to be installed

 

#yum -y install policycoreutils-python

 

If you want to view the settings of MySQL related files, you can use semanage fcontext -l | grep -i mysql to view them.

 

root@KerryDB ~]# semanage fcontext -l | grep -i mysql
/usr/lib(64)?/nagios/plugins/check_mysql           regular file       system_u:object_r:nagios_services_plugin_exec_t:s0 
/usr/lib(64)?/nagios/plugins/check_mysql_query     regular file       system_u:object_r:nagios_services_plugin_exec_t:s0 
/etc/mysql(/.*)?                                   all files          system_u:object_r:mysqld_etc_t:s0 
/etc/my\.cnf\.d(/.*)?                              all files          system_u:object_r:mysqld_etc_t:s0 
/var/log/mysql.*                                   regular file       system_u:object_r:mysqld_log_t:s0 
/var/lib/mysql(-files|-keyring)?(/.*)?             all files          system_u:object_r:mysqld_db_t:s0 
/var/run/mysqld(/.*)?                              all files          system_u:object_r:mysqld_var_run_t:s0 
/var/log/mariadb(/.*)?                             all files          system_u:object_r:mysqld_log_t:s0 
/var/run/mariadb(/.*)?                             all files          system_u:object_r:mysqld_var_run_t:s0 
/usr/sbin/mysqld(-max)?                            regular file       system_u:object_r:mysqld_exec_t:s0 
/var/run/mysqld/mysqlmanager.*                     regular file       system_u:object_r:mysqlmanagerd_var_run_t:s0 
/usr/lib/systemd/system/mysqld.*                   regular file       system_u:object_r:mysqld_unit_file_t:s0 
/usr/share/munin/plugins/mysql_.*                  regular file       system_u:object_r:services_munin_plugin_exec_t:s0 
/usr/lib/systemd/system/mariadb.*                  regular file       system_u:object_r:mysqld_unit_file_t:s0 
/etc/my\.cnf                                       regular file       system_u:object_r:mysqld_etc_t:s0 
/root/\.my\.cnf                                    regular file       system_u:object_r:mysqld_home_t:s0 
/usr/sbin/ndbd                                     regular file       system_u:object_r:mysqld_exec_t:s0 
/usr/libexec/mysqld                                regular file       system_u:object_r:mysqld_exec_t:s0 
/usr/bin/mysqld_safe                               regular file       system_u:object_r:mysqld_safe_exec_t:s0 
/usr/bin/mysql_upgrade                             regular file       system_u:object_r:mysqld_exec_t:s0 
/usr/sbin/mysqlmanager                             regular file       system_u:object_r:mysqlmanagerd_exec_t:s0 
/etc/rc\.d/init\.d/mysqld                          regular file       system_u:object_r:mysqld_initrc_exec_t:s0 
/var/lib/mysql/mysql\.sock                         socket             system_u:object_r:mysqld_var_run_t:s0 
/usr/bin/mysqld_safe_helper                        regular file       system_u:object_r:mysqld_exec_t:s0 
/usr/sbin/zabbix_proxy_mysql                       regular file       system_u:object_r:zabbix_exec_t:s0 
/etc/rc\.d/init\.d/mysqlmanager                    regular file       system_u:object_r:mysqlmanagerd_initrc_exec_t:s0 
/usr/sbin/zabbix_server_mysql                      regular file       system_u:object_r:zabbix_exec_t:s0 
/usr/libexec/mysqld_safe-scl-helper                regular file       system_u:object_r:mysqld_safe_exec_t:s0 
/home/[^/]+/\.my\.cnf

 

 

 

 

[root@KerryDB  ~]# semanage fcontext -a -t mysqld_db_t "/mysql_data/mysql(/.*)?"
[root@KerryDB  ~]# restorecon -Rv /mysql_data/mysql
restorecon reset /mysql_data/mysql context system_u:object_r:default_t:s0->system_u:object_r:mysqld_db_t:s0

 

At this time, it's normal to start MySQL instance. For more information about semanage modifying directory or file security context, please refer to the resources for further learning.

 

# service mysqld start

Redirecting to /bin/systemctl start mysqld.service

 

 

 

 

At this time, if you log in to the database with MySQL client tool, you will encounter "can't connect to local MySQL server through socket '/ var / lib / MySQL / MySQL. Socket'"

 

[root@KerryDB mysql]# mysql -u root -p
 
Enter password: 
 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

 

 

How to solve the following errors:

 

 

1: When connecting to MySQL, specify the value of the parameter -- socket

 

[root@KerryDB mysql]# mysql -u root -p   --socket=/mysql_data/mysql/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL
 
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> 

 

2: Modify the configuration file my.cnf and add the client parameter socket

 

[client]

socket=/mysql_data/mysql/mysql.sock

 

Note that the socket parameter is different from the parameter socket under mysqld.

 

[client]
socket=/mysql_data/mysql/mysql.sock
[mysqld]
datadir=/mysql_data/mysql
socket=/mysql_data/mysql/mysql.sock

 

 

In fact, this method is the simplest and most effective one.

 

 

3: You can establish the soft connection of mysql.sock file

 

 

In addition, I tried to solve this problem by modifying the UNIX domain socket context, but I didn't solve it. I don't know whether it was due to my misunderstanding or other reasons. Here are some of the tests:

 

 

# semanage fcontext -a -t mysqld_var_run_t "/mysql_data/mysql/mysql\.sock"

# restorecon -Rv /mysql_data/mysql/mysql.sock

 

# mysql -u root -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

 

Still, error is reported. Check semanage fcontext -l | grep -i mysql as shown below. Because the knowledge of SELinux is not deep enough, we are still learning. Temporarily record it first and leave it to be solved later.

 

#  semanage fcontext -l | grep -i mysql

 

 

 

reference material:

 

 

https://dev.mysql.com/doc/refman/5.6/en/can-not-connect-to-server.html

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/selinux_users_and_administrators_guide/sect-security-enhanced_linux-working_with_selinux-selinux_contexts_labeling_files

Tags: MySQL socket SELinux Database

Posted on Tue, 10 Mar 2020 20:11:15 -0700 by jburbage