mysql变更数据文件位置

June 24, 2021

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

1. 关闭 mysql
   sudo systemctl stop mysqld

2. 拷贝 mysql 数据文件夹到新的目录
   sudo rsync -av /var/lib/mysql /mnt/volume-nyc1-01

3. 修改 mysql 服务器数据目录配置，到新的目录
   ~~
   sudo vi /etc/my.cnf
   ~~~

1
2
3
4

[mysqld]
. . .
datadir=/mnt/volume-nyc1-01/mysql
socket=/mnt/volume-nyc1-01/mysql/mysql.sock

添加或修改 mysql 命令行配置

1
2
3

[client]
port=3306
socket=/mnt/volume-nyc1-01/mysql/mysql.sock

1. 重启mysql
   sudo systemctl start mysqld

2. 如果启动失败，查看下相关日志： /var/log/mysqld.log
3. 如果启动mysql报错：Can't change dir to 新的目录 (Errcode: 13)，可能是selinux的问题，解决方法参见下面附录

附录

查看数据文件的位置

登陆 mysql 执行 select @@datadir;

1
2
3
4
5
6

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

问题： 修改数据目录配置后，启动mysql报错：Can't change dir to 新的目录 (Errcode: 13)

1
2
3
4
5
6
7

210624 12:14:11 mysqld_safe Starting mysqld daemon with databases from /Volumes/mysql-data/mysql
210624 12:14:11 [Warning] Can't create test file /Volumes/mysql-data/mysql/vbcent69x64.lower-test
210624 12:14:11 [Warning] Can't create test file /Volumes/mysql-data/mysql/vbcent69x64.lower-test
/usr/libexec/mysqld: Can't change dir to '/Volumes/mysql-data/mysql/' (Errcode: 13)
210624 12:14:11 [ERROR] Aborting

210624 12:14:11 [Note] /usr/libexec/mysqld: Shutdown complete

• 原因分析： 可能是selinux的问题，将setenforce 0 临时关闭后，就可以正常启动了

解决办法：

方法一、关闭 selinux

方法二、设置 selinux

• 参考：
  • How to Move MySQL Data Directory to New Location on CentOS and Ubuntu
  • MySQL permission denied error even after setting security context for SELinux on VM
    
  • MySQL + SELinux: Can’t change dir (Errcode: 13)
  • /usr/libexec/mysqld: Can’t change dir to ‘/xxx’ (Errcode: 13) 解决

vim /etc/selinux/targeted/contexts/files/file_contexts.local

添加 /data/mysql(/.*)? system_u:object_r:mysqld_db_t:s0