Mysql手动迁移数据和日志目录的实验

因为业务增长太快，mysql服务器遇到目录空间不足问题，这里测试下手动修改data和log目录能不能成功
实验证明可以移动成功，跳到最后看结论

目的

测试手动把mysql的data目录和log目录mv到其他路径能不能成功
mysql配置现状：

port=7006
socket=/data/mysql7006/mysql.sock
pid-file=/data/mysql7006/mysql.pid
basedir=/apps/mysql
datadir=/data/mysql7006/data
log-error=/logs/mysql7006/error-log
log-bin=/logs/mysql7006/bin-log

准备data和log修改到新目录：

socket=/data2/mysql7006/mysql.sock
pid-file=/data2/mysql7006/mysql.pid
datadir=/data2/mysql7006/data
log-error=/logs2/mysql7006/error-log
log-bin=/logs2/mysql7006/bin-log

步骤

# 停止mysql
/apps/mysql/bin/mysqladmin -uroot --port=7006 --socket=/data/mysql7006/mysql.sock -p shutdown
# 新建目录
mkdir /data2
mkdir /logs2
# mv原mysql目录
mv /data/mysql7006/ /data2
mv /logs/mysql7006/ /logs2
# 修改配置文件my.cnf为（修改前先备份）：
socket=/data2/mysql7006/mysql.sock
pid-file=/data2/mysql7006/mysql.pid
datadir=/data2/mysql7006/data
log-error=/logs2/mysql7006/error-log
log-bin=/logs2/mysql7006/bin-log

启动mysql

/apps/mysql/bin/mysqld_safe --defaults-file=/apps/mysql/my.cnf &

失败了，日志：

/apps/mysql/bin/mysqld: File '/logs/mysql7006/bin-log.000025' not found (Errcode: 2 - No such file or directory)
2018-11-03 07:16:13 8163 [ERROR] Failed to open log (file '/logs/mysql7006/bin-log.000025', errno 2)
2018-11-03 07:16:13 8163 [ERROR] Could not open log file
2018-11-03 07:16:13 8163 [ERROR] Can't init tc log
2018-11-03 07:16:13 8163 [ERROR] Aborting

2018-11-03 07:16:13 8163 [Note] Binlog end

奇怪，我明明把logs目录换成了logs2，为什么还去找logs下面的日志呢？
哦想起来日志目录下的bin-log.index文件，它记录了有效的binglog日志，打开如下：

/logs/mysql7006/bin-log.000013
/logs/mysql7006/bin-log.000014
/logs/mysql7006/bin-log.000015
/logs/mysql7006/bin-log.000016
/logs/mysql7006/bin-log.000017
/logs/mysql7006/bin-log.000018
/logs/mysql7006/bin-log.000019
/logs/mysql7006/bin-log.000020
/logs/mysql7006/bin-log.000021
/logs/mysql7006/bin-log.000022
/logs/mysql7006/bin-log.000023
/logs/mysql7006/bin-log.000024
/logs/mysql7006/bin-log.000025

我们把他改成：

/logs2/mysql7006/bin-log.000013
/logs2/mysql7006/bin-log.000014
/logs2/mysql7006/bin-log.000015
/logs2/mysql7006/bin-log.000016
/logs2/mysql7006/bin-log.000017
/logs2/mysql7006/bin-log.000018
/logs2/mysql7006/bin-log.000019
/logs2/mysql7006/bin-log.000020
/logs2/mysql7006/bin-log.000021
/logs2/mysql7006/bin-log.000022
/logs2/mysql7006/bin-log.000023
/logs2/mysql7006/bin-log.000024
/logs2/mysql7006/bin-log.000025

重新启动mysql：/apps/mysql/bin/mysqld_safe --defaults-file=/apps/mysql/my.cnf &
登录：

/apps/mysql/bin/mysql -u root --port=7006 --socket=/data2/mysql7006/mysql.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> 

结论

mysql直接手动移动data和log目录能不能成功？
yes可以的！
如果修改data和log目录，需要修改：

1. 移动data目录到新data2目录
2. 移动log目录到新的log2目录
3. 在log2目录找到bin-log.index文件，把里面的log目录改为logs2目录
4. 修改my.cnf文件，对应配置改为新的data2目录、log2目录：

socket=/data2/mysql7006/mysql.sock
pid-file=/data2/mysql7006/mysql.pid
datadir=/data2/mysql7006/data
log-error=/logs2/mysql7006/error-log
log-bin=/logs2/mysql7006/bin-log

几点注意：

• 折腾前先备份
• 注意文件目录的属主关系