因为业务增长太快,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目录,需要修改:
- 移动data目录到新data2目录
- 移动log目录到新的log2目录
- 在log2目录找到bin-log.index文件,把里面的log目录改为logs2目录
- 修改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
几点注意:
- 折腾前先备份
- 注意文件目录的属主关系