一则清理MySQL大表以释放磁盘空间的案例

一、基本情况：

1.dbtest库554G，先清理st_online_time_away_ds(37G)表的数据，保留半年的数据:

           1)删除的数据：select count(1),tdate from dbtest.st_online_time_away_ds where tdate < '2017-08-01';(记录数为：462171894)

           2)保留的数据：select count(1),tdate from dbtest.st_online_time_away_ds where tdate >= '2017-08-01';(记录数为：264870859)

2.59.38.194.32机器磁盘剩余空间120G。

3.业务是离线的定时任务，不是实时的。

4.delete部分数据需要带where条件，此操作不会释放磁盘空间,truncate会释放空间。

二、处理方案：

1.方案一：评估先delete操作然后再使用optimize优化表可能耗时过长，并且释放的磁盘空间可能不理想。不推荐使用。

2.方案二：先delete半年前的数据，然后dump整个表(给表加锁，防止写入操作)，再truncate表释放磁盘空间，然后load数据。数据量不大的时候可以使用。

以下是各个部分操作的预估时间：

    1)删除半年以前的数据(大概需要10分钟)：

           mysql>delete from dbtest.st_online_time_away_ds where tdate < '2017-08-01';

           注意：数据量有462171894条记录，需要确保where条件中tdate字段有索引，否则删除操作时间很长而且扫描大表很耗资源。

    2)dump数据(以50M/s的速度来计算，大概需要10分钟)：

           /data/app/mysql/bin/mysqldump -S /data/db/mysql/mysql_3306.sock -uroot -p --lock-tables --databases dbtest --tables st_online_time_away_ds > /data/st_online_time_away_ds_bak.sql

    3)删除st_online_time_away_ds表释放空间(大概需要5分钟):

           mysql>truncate table dbtest.st_online_time_away_ds;

    4)load数据(load数据的时间大概是dump的10倍，大概需要100分钟):

           mysql>load data infile "/data/st_online_time_away_ds_bak.sql" into table dbtest.st_online_time_away_ds;

           或

           /data/app/mysql/bin/mysql -S /data/db/mysql/mysql_3306.sock -uroot -p < /data/st_online_time_away_ds_bak.sql

    5)验证数据的可用性，删除dump的/data/st_online_time_away_ds_bak.sql文件。

3.方案三：为不影响生产的写，现改成以下步骤：

  1)将当前生产表改名临时表

  2)重新建立一张新的生产表，生产可直接写入数据，新建表时AUTO_INCREMENT会初始化一个较大值

  3)从临时表查询符合条件的数据写回信的生产表

  4)如果没有问题，则临时表可以删掉，否则进行回滚。

  以下是具体执行的sql:

  use dbtest;

  -- 1、先将表更名

  rename  table  dbtest.stat_server_online_67  to dbtest.stat_server_online_67_temp;

  -- 2、 重新建一张正式表 ， 生产可直接写进去

  CREATE TABLE dbtest.`stat_server_online_67` (

    `auto_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',

    `server_id` int(11) NOT NULL COMMENT '服务器ID',

    `stat_type` tinyint(4) NOT NULL COMMENT '采集类型, 1:在线;2:注册',

    `stat_time` datetime NOT NULL COMMENT '采集时间',

    `value` int(11) NOT NULL COMMENT '采集结果',

    `top` int(11) NOT NULL DEFAULT '0' COMMENT '当天最高在线',

    `low` int(11) NOT NULL DEFAULT '0' COMMENT '当天最低在线',

    `game_id` int(11) NOT NULL COMMENT '游戏ID',

    `platform_id` int(11) NOT NULL COMMENT '平台ID',

    `create_time` datetime NOT NULL COMMENT '创建时间',

    `system_platform` varchar(30) NOT NULL DEFAULT '' COMMENT '系统平台',

    `language_country` varchar(30) NOT NULL DEFAULT '' COMMENT '语言国别',

    PRIMARY KEY (`auto_id`),

    UNIQUE KEY `AK_Key_serverId_statType_statTime_sPlatform_lCountry` (`server_id`,`stat_type`,`stat_time`,`system_platform`,`language_country`),

    KEY `AK_Key_statTime_gameId_PlatformId` (`stat_time`,`game_id`,`platform_id`)

  ) ENGINE=InnoDB  AUTO_INCREMENT=1168939988 DEFAULT CHARSET=utf8 COMMENT='服务器在线/注册统计';

  -- 3、将保留数据保存到新表上

  insert into dbtest.stat_server_online_67 select * from dbtest.stat_server_online_67_temp where stat_time >= '2015-10-27 10:00:00' and  stat_time <= '2018-02-07 00:00:00' and game_id=67 and platform_id in (1,34,76,211,229,415,466);

  -- 4、清除旧表数据

  drop  table  dbtest.stat_server_online_67_temp;

  -- 5、回滚

  -- rename  table  dbtest.stat_server_online_67  to dbtest.stat_server_online_67_useless;

  -- rename  table  dbtest.stat_server_online_67_temp  to dbtest.stat_server_online_67;

4.方案四：如果表的数据量很大，方案三中执行第三步耗时较长，影响线上数据写入，还需优化下。

  如：stat_server_online_61表数据量比较大，已超过100G，为不影响线上写入，执行步骤改成如下：

  use dbtest;

  -- 1、先将表更名

  rename  table  dbtest.stat_server_online_61  to dbtest.stat_server_online_61_temp;

  -- 2、 重新建一张正式表 ， 生产可直接写进去

  CREATE TABLE dbtest.`stat_server_online_61` like dbtest.stat_server_online_61_temp;

  -- 3、新建一张中间表，中间表保存历史数据

  CREATE TABLE dbtest.`stat_server_online_61_2` like dbtest.stat_server_online_61_temp;

  -- 4、将保留数据保存到中间表上

  insert into dbtest.stat_server_online_61_2 (server_id,stat_type,stat_time,value,top,low,game_id,platform_id,create_time,system_platform,language_country)

  select server_id,stat_type,stat_time,value,top,low,game_id,platform_id,create_time,system_platform,language_country from dbtest.stat_server_online_61_temp where   stat_time <= '2018-02-07 00:00:00' and game_id=61 and platform_id in (1,28,34,37,40,52,64,76,160,163,166,199,211,229,235,247,265,271,283,286,373,415,451,466,478,484,499,505,568,577,697,925,997,1072,1081,1126,1162,1198);

  这一步遇到的问题和处理方式：由于这一步的操作涉及的数据量太大，导致在/tmp目录下的临时文件太大(可以使用#lsof | grep delete可以查看到mysql进程占用的未释放的文件，注意不能kill掉这些文件的PID，否则会导致kill掉mysql进程，需要查看进程评估之后再决定是否需要重启)，占用/根目录空间造成根目录满而操作中止，影响数据库实例和服务器的使用，此时通过#show processlists;#kill [insert进程id];无法kill掉进程。只能重启mysql实例以释放空间。重启mysql之后使用#show variables like "%tmp%";查看tmpdir=/tmp;修改这个路径到非根目录的其他足够空间的目录再执行以上insert操作即可。

  -- 5、把生产表改成临时表

  rename  table  dbtest.stat_server_online_61  to dbtest.stat_server_online_61_temp_2;

  -- 6、将中间表改成正式表

  rename  table  dbtest.stat_server_online_61_2  to dbtest.stat_server_online_61;

  -- 7、将临时表数据写回正式表（少量数据）

  insert into dbtest.stat_server_online_61 (server_id,stat_type,stat_time,value,top,low,game_id,platform_id,create_time,system_platform,language_country)

  select server_id,stat_type,stat_time,value,top,low,game_id,platform_id,create_time,system_platform,language_country from dbtest.stat_server_online_61_temp_2

  -- 8 清除旧表数据

  drop  table  dbtest.stat_server_online_61_temp;

  drop table dbtest.stat_server_online_61_temp_2;