DBA之伤-truncate/drop

【是否原创】是
【首发渠道】TiDB 社区
【首发渠道链接】其他平台首发请附上对应链接
【正文】

引言

DBA职业是一种高危职业，承担着公司数据库健壮稳定高效运行工作，在日常工作很容易就触碰到DML或者TRUNCATE 或者 DROP操作。没有删过库，没有TRUNCATE表的DBA不是一个好厨师。

介绍

日常工作中出现业务误操作或者DBA误操作执行了DML操作，可以使用FLUSHBACK功能。但是DDL闪回却很难实现。当有一个任务SQL工单需求是需要TRUNCATE或者DROP表时候，那么如何对业务RPO影响到最小呢？😱，可以使用FLUSHBAKC 、 RECOVER TABLE，在GC lifetime时间失效了。怎么办？

有的大佬说执行TRUNCATE或者DROP语句之前先备份。这个操作是正确的。在表数据库非常小的时候，不管备份或者恢复都会非常快。那么……如果表数据量是非常多,恰好又必须要执行操作的工单，那么我们如何考虑备份与恢复的快速呢？

实现思路

MySQL 的DML语句 FLUSHBACK功能实现：解析BINLOG，DDL FLUSHBACK功能实现: 增加一个回收站库，不管是MySQL还是其他数据库都可以使用这样方式进行操作

实现

需求：

• 支持所有数据库类型

• 支持参数列表

  • TRUNCATE
  • DROP
  • 集群名称
  • ip and port
  • 执行人
  • 全库 or 单标 or 多表
  • 执行SQL记录数据库中以及回滚SQL生成

代码片段

表结构

CREATE TABLE `rds_recycle_info` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `cluster_id` int(11) NOT NULL COMMENT '集群id',
  `cluster_name` varchar(64) NOT NULL DEFAULT '' COMMENT '群集英文名称',
  `category` varchar(32) NOT NULL DEFAULT '' COMMENT 'eg.MySQL,SQLServer,Oracle,Mongo,Tidb',
  `action` varchar(32) NOT NULL DEFAULT '' COMMENT 'eg.drop,truncate,recover',
  `env` varchar(16) NOT NULL DEFAULT 'prod' COMMENT '环境,prod、test',
  `ip` varchar(32) NOT NULL COMMENT 'IP地址',
  `port` int(11) NOT NULL COMMENT '端口',
  `source_db` varchar(32) NOT NULL DEFAULT '' COMMENT '源DB',
  `source_table` varchar(255) NOT NULL DEFAULT '' COMMENT '源tables',
  `dest_db` varchar(32) NOT NULL DEFAULT '' COMMENT '目标DB',
  `dest_table` varchar(255) NOT NULL DEFAULT '' COMMENT '目标tables',
  `drop_sql` longtext NOT NULL COMMENT '删表SQL',
  `recover_sql` longtext NOT NULL COMMENT 'rename 表SQL',
  `operator` varchar(100) NOT NULL DEFAULT '' COMMENT '操作人',
  `created_stime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_stime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_del` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除，0:正常，1:删除',
  `memo` varchar(255) NOT NULL DEFAULT '' COMMENT '执行备忘录',
  PRIMARY KEY (`id`),
  KEY `idx_created_stime` (`is_del`,`created_stime`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COMMENT='回收站详细表'

程序代码代码入口

func main() {
	// 定义参数
	flag.Parse()

	// 执行操作
	switch {
	// drop table
	case action == "drop":
		md.DropTable(destdb, dbname, table_name)
		//fallthrough
	// truncate table
	case action == "truncate":
		md.TruncateTable(destdb, dbname, table_name)
		//fallthrough
	// 默认
	default:
		fmt.Println("模式没有指定")
		os.Exit(11)
	}
}

执行操作

// 刪除表
func (md *MetaData) DropTable(fromdestdb *gorm.DB, dbname string, tablename string) {
				fmt.Printf("RENAME TABLE `%s`.`%s` TO `recycle_bin`.`del_dba_%s-%s-%s`;\"n", dbname, str, getetime, dbname, str)
				sql := fmt.Sprintf("RENAME TABLE `%s`.`%s` TO `recycle_bin`.`del_dba_%s-%s-%s`", dbname, str, getetime, dbname, str)
				fromdestdb.Exec(sql)
}

// 清空表
func (md *MetaData) TruncateTable(fromdestdb *gorm.DB, dbname string, tablename string) {
				fmt.Printf("TRUNCATE TABLE `%s`.`%s` TO `recycle_bin`.`del_dba_%s-%s-%s`;\"n", dbname, str, getetime, dbname, str)
				//db.Exec("RENAME TABLE ?.? TO recycle_bin.__?-?-?", dbname, string(str[i]), getetime, dbname, string(str[i]))
				//db.Exec("CREATE TABLE ?.? LIKE recycle_bin.__?-?-?", dbname, string(str[i]), getetime, dbname, string(str[i]))
				fromdestdb.Exec(fmt.Sprintf("RENAME TABLE `%s`.`%s` TO `recycle_bin`.`del_dba_%s-%s-%s`", dbname, str, getetime, dbname, str))
				fromdestdb.Exec(fmt.Sprintf("CREATE TABLE `%s`.`%s` LIKE `recycle_bin`.`del_dba_%s-%s-%s`", dbname, str, getetime, dbname, str))
}

收益

1、通过查询数据库就能发现是什么时候执行的操作
2、可以快读定位到执行语句以及回滚SQL
3、安全执行TRUNCATE、DROP

截图