不想保留旧数据并希望删除它,该如何做呢?
如果想删除一个月前最后一次访问的所有行。如果表很小(<10000行),则可以直接使用以下方法:
如果表很大,会怎样?InnoDB创建了一个UNDO日志来恢复失效的事务,因此,所有别删除的行都会
被保存在UNDO日志空间中,以便在DELETE语句的执行中止时恢复它们。不幸的是,如果DELETE语句
在执行时被中止,InnoDB将从UNDO日志空间将行复制到表中,这可能使表无法访问。
为了克服这种弊端,可以限制删除的行数然后提交事务,循环做这个操作,直到删除所有不需要的行。
以下是一个伪代码示例:
如果last_accessed上没有索引,则会锁定该表。在这种情况下,需要找到要删除的行的主键,并且基于
主键删除这些行。
下面是伪代码(假设id是主键):
可以使用Percona的pt-archiver工具来代替编写删除行的代码,不仅可以完成上面的任务,这个工具还
提供了许多其他选项,比如将行保存到另一个表或文件中,对加载和复制延迟进行精细的控制,等等。
如何操作
pt-archiver中有许多选项,下面从简单的清除操作(purge)开始。
清除数据
如果想要删除employees表中hire_date超过30年的所有行,可以执行以下操作:
DELETE FROM
WHILE count<=0:
DELETE FROM
LIMIT 10000;
count=SELECT COUNT( ) FROM
INTERVAL -1 MONTH);
WHILE count<=0:
SELECT id FROM
MONTH) LIMIT 10000;
DELETE FROM
count=SELECT COUNT() FROM
INTERVAL -1 MONTH);
云计算-legolas制作
如果报以下错误,说明表没有索引,需要添加索引。
添加索引:
–source选项传递主机名、数据库和表名;
–limit选项限制在批处理操作中删除的行数;
–progress选项:其输出是一个标题行,外加持续的状态输出。状态输出中的每一行都会列出当前
的日期和时间、pt-archiver已经运行了多少秒,以及它已经归档了多少行;
–statistics选项:pt-archiver会输出定时时间(timing)和其他信息,以帮助确定归档过程的哪一
部分花的时间最多;
–check-slave-lag选项:pt-archiver将暂停归档,直到从库的延时少于–max-lag。
归档数据
如果想把删除后剩余的行保存到一个单独的表或文件中,可以指定–dest选项。
如果想将test1.tb02表中所有行移到test2.test01表中,可以执行以下操作:
首先创建新表结构:
如果指定–where=“1=1”,则将复制所有的行。
复制数据
如果想将数据从一个表复制到另一个表,可以使用mysqldump或mysqlpump来备份某些行,然后将它
们加载到目标表中。
也可以使用pt-archiver来复制数据。如果指定–no-delete选项,pt-archiver将不会从源表中删除行:
#先复制一个employees表
mysql> create table test1.tb02 as select * from employees.employees;
#然后删除符合条件的行
[root@www ~]# pt-archiver --source h=localhost,D=test1,t=tb02 -uroot -
pCom.123456 \
–where=“hire_date<DATE_ADD(NOW(), INTERVAL -30 YEAR)” --no-check-charset
–limit 10000 --commit-each --purge
Cannot find an ascendable index in table at /usr/bin/pt-archiver line 3216.
mysql> alter table tb02 add index (last_name);
Query OK, 0 rows affected (1.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> use test2;
Database changed
mysql> create table test01 like test1.tb02;
Query OK, 0 rows affected (0.01 sec)
[root@www ~]# pt-archiver --source h=localhost,D=test1,t=tb02
–dest h=localhost,D=test2,t=test01 -uroot -pCom.123456
–where=“1=1” --no-check-charset --commit-each
云计算-legolas制作
[root@www ~]# pt-archiver --source h=localhost,D=test2,t=test02
–dest h=localhost,D=test1,t=tb02 -uroot -pCom.123456
–where=“1=1” --no-check-charset --limit 60000 --commit-each --no-delete