cps广告联盟网站,网站定制开发注意事项,网页搭建服务,wordpress解封目录 迁移脚本删除数据以及备份数据 解决方法OPTIMIZE TABLE二进制日志按月生成数据 最近某个项目虽说用户量不大#xff0c;但是#xff0c;单表的数据量越来越大#xff0c;mysql一般单表超过千万级别后#xff0c;性能直线下降#xff0c;所以利用shardingphere按月做了… 目录 迁移脚本删除数据以及备份数据 解决方法OPTIMIZE TABLE二进制日志按月生成数据 最近某个项目虽说用户量不大但是单表的数据量越来越大mysql一般单表超过千万级别后性能直线下降所以利用shardingphere按月做了一个分表所以得把历史数据迁移到各个分月的表中。 迁移脚本
直接开始迁移执行如下脚本
insert into cern_202301
select * from cern_bak
where report_time2023-01-01 00:00:00
and report_time2023-01-31 23:59:59report_time是作为分表字段的依据这里是迁移1月份的数据到cern_202301中因为已经迁移最近半年的数据到分表中保证在不影响业务的前提下做迁移操作迁移到一半发现磁盘空间不够
如果是你你的解决方案是
可以留言一起交流一下学习一下
删除数据以及备份数据
####一次导出所有数据
mysqldump -uroot -p123456 iopace cern /mnt/datadisk1/cern.sql###按月导出数据--不用执行,了解即可mysqldump -uroot -p123456 iopace cern_bak --where report_time2023-04-01 00:00:00 and report_time2023-04-30 23:59:59 /mnt/datadisk1/cern2304.sql###导入数据--不用执行,了解即可
mysql -u root -p123456 iopacecern2307.sql
root为用户名123456为密码iopace为数据库名cern为需要导出的表名/mnt/datadisk1/cern.sql 表示导出到哪里因为我们有三个磁盘这是另外一个磁盘暂时未使用所以数据导出到这里 因为我已经把业务已经切换到分表来了所以cern表是不会存在写入操作的一点要注意防止丢失数据
数据已备份那我们就可以执行delete操作
delete from cern
where report_time2024-02-01 00:00:00
and report_time2024-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左右被我删除到88purge binary logs to ‘binlog.000088’; 表示删除88之前的所有二进制文件
这个时候我们再去df -h查看磁盘磁盘空间已释放
按月生成数据
如果二进制腾出来一部分空间后还不够历史数据迁移到各个月分表的还有一种方式。 就是把cern表的数据按月导出成sql脚本按月命名 找一个测试环境把几个月的sql脚本导入后测试一下总记录数是否有丢失 如果没有丢失就可以把cern表删除再通过dump导入sql的方式一个月一个月的导入数据
###按月导出数据--不用执行,了解即可
mysql -u root -p123456 iopacecern2307.sql
