个人之前总结过两篇文章“mysql更改数据库数据存储目录”和“ubuntu上更改mysql数据库数据存储目录”，都是在工作中遇到相关案例后的一个简单总结。当初的经验不足，认知有限，所以现在来看来，当初的博文确实显得浅析和不够全面，这个也没有办法，当时有些环境或案例没有涉及过，所以文章很难面面俱到，略显单薄和浅显，这个也很正常。博客不是论文，只是个人的笔记和知识点的总结。而且是基于当时的认知的经验总结和知识概括。前阵子将监控工具dpa的数据库切换为mysql时，又遇到了这个问题，遂总结一下。
系统环境 ： centos linux release 7.5.1804 (core)
mysql版本 ： 8.0.18 mysql community server - gpl
默认情况下，mysql的数据目录一般位于/var/lib/mysql下
1：首先，弄清楚mysql的相关配置信息
找到mysql的数据目录，以及配置文件my.cnf、错误日志的位置。
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：对数据库做一个冷备份，以备不时之需。当然，你以可以用其它方式备份。
[root@kerrydb ~]# service mysqld stop
redirecting to /bin/systemctl stop mysqld.service
[root@kerrydb ~]# cp -rp /var/lib/mysql /tmp/coldbackup
3：移动数据目录到其它目录
这里假设，我需要将数据库的数据目录迁移到/mysql_data/下。
[root@kerrydb ~]# chown -r mysql:mysql /mysql_data/
[root@kerrydb ~]# cd /var/lib
[root@kerrydb lib]# mv mysql /mysql_data/
注意：这里建议使用mv移动数据库目录，而是不是复制（cp命令），因为执行复制时，selinux上下文将丢失，并且您稍后必须手动进行设置，如果你使用cp命令复制文件，如果没有使用参数-p，那么要记得修改目录的属主，否则你有可能遇到“mysql error: 1017 can’t find file: (errno: 13)”这样的错误。
mv 与cp的区别
4：修改配置文件my.cnf
一般情况下，只需修改datadir和socket等参数，具体根据你实际情况来判断那些参数需要修改。
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/mysql_data/mysql
socket=/mysql_data/mysql/mysql.sock
修改过后，对于centos或rhel操作系统而言，你可能需要修改selinux的设置，而对于ubuntu或debian，你需要修改apparmor的设置（这篇ubuntu上更改mysql数据库数据存储目已经有介绍了，这里就不重复了）。当如，对于centos或rhel，如果你禁用了selinux的话，那么就可以直接忽略这个设置，因为禁用selinux的话，就会避免很多杂七杂八的问题，但是我们不是要回避问题，而是要弄清楚问题产生的根本原因。
首先你要检查，是否开启了selinux。如下所示：
[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
如果selinux是开启状态，那么此时如果不做一些配置，那么启动mysql服务，就会遇到类似下面这样的错误：
[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)
上面日志看不到细节错误信息，此时，应该检查错误日志/var/log/mysqld.log， 如下所示，你会看到“os errno 13 - permission denied”和“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.
注意：默认情况下，日志里面的时间是utc时间，而不是本地时间。除非你设置过。具体参考我的博客“mysql5.7参数log_timestamps”。
此时如果，你用chcon命令来在新目录中更改selinux上下文类型，我看有些博客介绍就ok了。但是在我这个环境中，这样处理过后依然报同样的错误
[root@kerrydb ~]# chcon -r -t mysqld_db_t /mysql_data/
[root@kerrydb ~]#
折腾测试后，发现此时需要在file_contexts里面修改mysqld_db_t，如下所示：
[root@kerrydb ~]# cd /etc/selinux/targeted/contexts/files/
[root@kerrydb files]# ls -lrt
修改前：
/var/lib/mysql(-files|-keyring)?(/.*)? system_u:object_r:mysqld_db_t:s0
修改后：
/mysql_data/mysql(-files|-keyring)?(/.*)? system_u:object_r:mysqld_db_t:s0
其实使用chcon修改对象（文件）的安全上下文，相当难掌握，有点难度，除非你是linux高手，一般用工具semanage对默认目录的安全上下文查询与修改
一般默认可能没有安装policycoreutils-python，需要安装对应组件
#yum -y install policycoreutils-python
如果你要查看mysql相关文件的设置，那么可以用semanage fcontext -l | grep -i mysql查看。
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
此时启动mysql实例就正常了，关于semanage修改目录或文件安全上下文的更多信息，参考资料有，可以移步那里去学习。
# service mysqld start
redirecting to /bin/systemctl start mysqld.service
此时，你如果用mysql客户端工具登录数据库，就会遇到"can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock'"
[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)
解决下面错误的方法：
1：在连接mysql时，指定参数--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：修改配置文件my.cnf，增加客户端参数socket
[client]
socket=/mysql_data/mysql/mysql.sock
注意这个socket参数，跟mysqld下的参数socket是有所区别的。
[client]
socket=/mysql_data/mysql/mysql.sock
[mysqld]
datadir=/mysql_data/mysql
socket=/mysql_data/mysql/mysql.sock
其实这种方法是最简单，最有效的一个方法。
3：可以通过建立mysql.sock文件的软连接
另外，我尝试通过修改unix-domain socket 上下文来解决这个问题，但是没有解决，不知是我理解有误，还是其它方面原因。 下面是部分测试内容：
# 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)
依然报错，检查semanage fcontext -l | grep -i mysql 如下所示， 由于对selinux的知识了解不够深入，暂时还在学习中。临时先记录一下，留待以后解决。
# semanage fcontext -l | grep -i mysql
参考资料：
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
如对本文有疑问， 点击进行留言回复！！
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火，普罗米修斯的崛起
我要评论
网友评论