目录
- 迁移脚本
- 删除数据以及备份数据
- 解决方法
- OPTIMIZE TABLE
- 二进制日志
- 按月生成数据
最近某个项目虽说用户量不大，但是，单表的数据量越来越大，mysql一般单表超过千万级别后，性能直线下降，所以利用shardingphere按月做了一个分表，所以得把历史数据迁移到各个分月的表中。
迁移脚本
直接开始迁移，执行如下脚本
insert into cern_202301
select * from cern_bak
where report_time>='2023-01-01 00:00:00'
and report_time<='2023-01-31 23:59:59'
- report_time是作为分表字段的依据，这里是迁移1月份的数据到cern_202301中
- 因为已经迁移最近半年的数据到分表中，保证在不影响业务的前提下，做迁移操作
- 迁移到一半发现磁盘空间不够
如果是你，你的解决方案是？
- 可以留言一起，交流一下学习一下
删除数据以及备份数据
####一次导出所有数据
mysqldump -uroot -p123456 iopace cern > /mnt/datadisk1/cern.sql
###按月导出数据--不用执行,了解即可
mysqldump -uroot -p123456 iopace cern_bak --where=" report_time>='2023-04-01 00:00:00' and report_time<='2023-04-30 23:59:59'" > /mnt/datadisk1/cern2304.sql
###导入数据--不用执行,了解即可
mysql -u root -p123456 iopace<cern2307.sql
- root为用户名
- 123456为密码
- iopace为数据库名
- cern为需要导出的表名
- /mnt/datadisk1/cern.sql 表示导出到哪里，因为我们有三个磁盘，这是另外一个磁盘，暂时未使用，所以数据导出到这里
- 因为我已经把业务已经切换到分表来了，所以cern表是不会存在写入操作的，一点要注意，防止丢失数据
数据已备份，那我们就可以执行delete操作
delete from cern
where report_time>='2024-02-01 00:00:00'
and report_time<='2024-02-10 23:59:59'
- 之前已经迁移半年的数据，那我就delete最近半年的数据
- 因为表数据量有点大，所以，我都是10天删除一次，防止花费时间过长
- 清理大半年的数据后，发现磁盘空间还是没有任何的变化？
按我们正常的理解，是不是删除数据后，磁盘大小就会变大？
使用delete删除数据，不会把数据文件删除，而是将数据文件的标识位删除，因此会留下数据碎片，当有新数据写入的时候，mysql会利用这些已删除的空间再写入。如果碎片空间满，就不能插入
解决方法
OPTIMIZE TABLE
- 会优化表，会锁表，建议1个月处理一次，如果不存在大量删除的情况，不太建议使用
- 实现原理，实际上就是把当前表的数据复制到临时表，再删除当前表，再把临时表重命名为当前表，所以对空间会有要求
OPTIMIZE TABLE cern;
- cern为需要优化的表名
- cern表20G左右，1个月的数据大约2.5G左右，当前磁盘可用空间2.5G左右，所以肯定是没有办法通过该方式来优化的
- 空间足够，可以通过他来处理大量delete的情况
二进制日志
所有的修改、删除都会存放在二进制文件中，有时候误删除后，我们也可以通过二进制日志还还原数据，所以这个文件会有点庞大
###259200秒====30天，所以二进制文件保存的周期为30天
show variables like '%expire%';
###当前二进制历史文件存放目录
show BINARY logs;
###当前正在运行的二进制日志，注意，这个文件是不能动的
show master status;
- File 表示当前正在运行的二进制日志文件是那个
- position表示位置，例如主从复制的时候，就会用到
- 下面有很多日志文件，这是我删除一部分后的，当时，大约有20多个文件，每个文件1.5G左右，被我删除到88
- purge binary logs to ‘binlog.000088’; 表示删除88之前的所有二进制文件
这个时候，我们再去df -h查看磁盘，磁盘空间已释放，
按月生成数据
如果二进制腾出来一部分空间后，还不够，历史数据迁移到各个月分表的，还有一种方式。
就是把cern表的数据，按月导出成sql脚本，按月命名
找一个测试环境，把几个月的sql脚本导入后，测试一下总记录数是否有丢失
如果没有丢失，就可以把cern表删除，再通过dump导入sql的方式，一个月一个月的导入数据
###按月导出数据--不用执行,了解即可
mysql -u root -p123456 iopace<cern2307.sql
