MySql迁移历史日志表_数据库

使用表空间方法迁移历史日志数据到其它挂载目录

需求分析：

系统要求保留近12个月的日志数据。由于/DATA2磁盘空间接近满载，需要将部分历史日志数据迁移到扩展磁盘/DATA1。

磁盘空间情况：

# 磁盘使用情况[root@server /]# df -ThFilesystem         Type      Size  Used Avail Use% Mounted on/dev/sda5          xfs       2.4T  662G  1.8T  28% /DATA/dev/sdb1          xfs       5.3T  5.1T  286G  94% /DATA2/dev/sdb6          xfs       5.0T  161M  5.0T   1% /DATA1

迁移方案：

通过扩展磁盘并新建存储目录，将历史日志表迁移至新的磁盘目录，并释放旧磁盘空间。

迁移步骤：

1. 创建新磁盘路径

在 /DATA1 目录下创建数据目录并调整权限：

mkdir -p /DATA1/mysql/datachown -R mysql.mysql /DATA1/mysql/data/

2. 修改数据库配置

编辑 MySQL 配置文件 /etc/my.cnf，添加以下参数：

[mysqld]innodb_file_per_table = 1innodb_directories = "/DATA2/mysqldata/;/DATA1/mysql/data/"

注意：innodb_directories变量，该变量只读，需修改配置文件并重启数据库服务；

3. 创建表空间

在MySQL中创建指向新磁盘路径的表空间：

CREATE  TABLESPACE new_ts ADD  DATAFILE '/DATA1/mysql/data/new_ts.ibd'  ENGINE=InnoDB;

4. 创建新表并指向新表空间

创建指向新表空间的新表（以work.t_work_msg_log_2023_09为例）：

USE  work;CREATE  TABLE `t_work_msg_log_2023_09_bak` (  `RID` varchar(100) NOT NULL,  `ORG_ID` varchar(50),  `MESSAGE_ID` varchar(50),  -- 其他列省略 --  PRIMARY KEY (`RID`)) ENGINE=InnoDB  TABLESPACE=new_ts;

5. 迁移数据到新表

将历史数据插入到新表中：

INSERT  INTO work.t_work_msg_log_2023_09_bakSELECT * FROM work.t_work_msg_log_2023_09;

6. 验证数据迁移

查询新表和旧表的数据行数，确保一致：

SELECT  COUNT(*) FROM work.t_work_msg_log_2023_09_bak;SELECT  COUNT(*) FROM work.t_work_msg_log_2023_09;

7. 删除旧表并释放磁盘空间

确认数据迁移成功后，删除旧表以释放空间：

DROP  TABLE work.t_work_msg_log_2023_09;

8. 重命名新表

将新表重命名为原表名：

RENAME  TABLE work.t_work_msg_log_2023_09_bak TO work.t_work_msg_log_2023_09;

总结：

通过此迁移方案，日志数据得以成功迁移至新的磁盘空间，解决了原磁盘空间不足的问题，同时保留了完整的历史数据。

from:公众号【DBA小强】

MySql迁移历史日志表