ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL 移动数据目录后启动失败 [打印本页]
作者: 种地    时间: 2023-4-23 12:46
标题: MySQL 移动数据目录后启动失败
背景概述

由于安装数据库时将MySQL的数据目录放在了根目录下，现在存储空间不足，想通过mv将数据目录移动到其他目录下，但将数据目录移动到其他数据目录后，启动数据库失败。
问题复现

本次测试基于 MySQL 8.0.31
1.关闭数据库
  1. mysql> shutdown;
  2. Query OK, 0 rows affected (0.02 sec)
复制代码
2.查看当前数据目录所在位置
  1. shell> pwd
  2. /mysql80
复制代码
3.通过mv移动整个MySQL数据目录到其他目录
  1. shell>  mv /mysql80 /data
  2. shell>  cd /data/mysql80/svr
  3. shell>  ln -s mysql-8.0.31-linux-glibc2.12-x86_64 mysql
复制代码
4.修改属主属组
  1. shell> chown -R mysql.mysql /data
复制代码
5.修改配置文件中数据目录的地址
  1. shell> sed -i 's#/mysql80#/data/mysql80#g' my5001.cnf
复制代码
6.启动数据库
  1. shell> /data/mysql80/svr/mysql/bin/mysqld_safe \
  2. --defaults-file=/data/mysql80/conf/my5001.cnf --user=mysql &
复制代码
此时启动数据库失败，错误日志报错如下：
  1. mysqld: File '/mysql80/dbdata/data5001/log/binlog.000012' not found (OS errno 2 - No such file or directory)
  2. 2023-02-27T10:38:09.240576+08:00 0 [ERROR] [MY-010958] [Server] Could not open log file.
  3. 2023-02-27T10:38:09.240657+08:00 0 [ERROR] [MY-010041] [Server] Can't init tc log
  4. 2023-02-27T10:38:09.240718+08:00 0 [ERROR] [MY-010119] [Server] Aborting
  5. 2023-02-27T10:38:10.548605+08:00 0 [System] [MY-010910] [Server] /data/mysql80/svr/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
复制代码
这里报错显示找不到binlog文件，并且报错显示的binlog的目录还是之前的，但是配置文件中的目录已经修改
  1. shell> grep 'log-bin' my5001.cnf
  2. log-bin=/data/mysql80/dbdata/data5001/log/binlog
  3. log-bin-trust-function-creators
复制代码
7.问题解决

最后通过查找发现 binlog.index 文件中存放着每个binlog文件的绝对路径地址，这里的路径还是之前路径，内容如下：
  1. shell> cat binlog.index
  2. /mysql80/dbdata/data5001/log/binlog.000001
  3. /mysql80/dbdata/data5001/log/binlog.000002
  4. /mysql80/dbdata/data5001/log/binlog.000003
  5. /mysql80/dbdata/data5001/log/binlog.000004
  6. /mysql80/dbdata/data5001/log/binlog.000005
  7. /mysql80/dbdata/data5001/log/binlog.000006
  8. /mysql80/dbdata/data5001/log/binlog.000007
  9. /mysql80/dbdata/data5001/log/binlog.000008
  10. /mysql80/dbdata/data5001/log/binlog.000009
  11. /mysql80/dbdata/data5001/log/binlog.000010
  12. /mysql80/dbdata/data5001/log/binlog.000011
  13. /mysql80/dbdata/data5001/log/binlog.000012
复制代码
修改binlog.index文件中binlog的绝对路径：
  1. shell> sed -i 's#/mysql80#/data/mysql80#g' binlog.index
  2. shell> cat binlog.index
  3. /data/mysql80/dbdata/data5001/log/binlog.000001
  4. /data/mysql80/dbdata/data5001/log/binlog.000002
  5. /data/mysql80/dbdata/data5001/log/binlog.000003
  6. /data/mysql80/dbdata/data5001/log/binlog.000004
  7. /data/mysql80/dbdata/data5001/log/binlog.000005
  8. /data/mysql80/dbdata/data5001/log/binlog.000006
  9. /data/mysql80/dbdata/data5001/log/binlog.000007
  10. /data/mysql80/dbdata/data5001/log/binlog.000008
  11. /data/mysql80/dbdata/data5001/log/binlog.000009
  12. /data/mysql80/dbdata/data5001/log/binlog.000010
  13. /data/mysql80/dbdata/data5001/log/binlog.000011
  14. /data/mysql80/dbdata/data5001/log/binlog.000012
复制代码
8.启动数据库
  1. shell> /data/mysql80/svr/mysql/bin/mysqld_safe
  2. --defaults-file=/data/mysql80/conf/my5001.cnf --user=mysql &
复制代码
数据库启动成功。
9.作为从节点

需要注意的是，如果该实例还作为其他实例的从节点，还需要设置 relaylog.index 文件中relay log的绝对路径，否则会报如下错误： 错误日志报错：
  1. 2023-02-27T15:56:55.224372+08:00 0 [ERROR] [MY-010599] [Repl] log /mysql80/dbdata/data5002/log/relaylog.000002 listed in the index, but failed to stat.
  2. 2023-02-27T15:56:55.224422+08:00 0 [ERROR] [MY-011059] [Repl] Error counting relay log space.
  3. 2023-02-27T15:56:55.226571+08:00 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
  4. 2023-02-27T15:56:55.226622+08:00 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
复制代码
执行 start replica 时也会报错：
  1. # 客户端报错
  2. mysql> start replica;
  3. ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
  5. # error log报错
  6. 2023-02-27T15:57:53.858798+08:00 8 [ERROR] [MY-013124] [Repl] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: MY-013124
复制代码
10.问题解决

修改 relaylog.index 文件中relay log的绝对路径
  1. sed -i 's#/mysql80#/data/mysql80#g' relaylog.index
复制代码
重新启动数据库，并启动主从复制
  1. # 重启实例
  2. mysql> restart;
  4. # 启动主从复制
  5. mysql> start replica;
复制代码
此时主从复制恢复正常。
总结


Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4