mysql删除数据库的过程
在dba圈混迹很久了,早早就想写点博客,总结一些经验什么的,总是因为时间少呀,人懒呀,觉得文笔不好呀啥杂七杂八原因,一直很少写点东西,
不过前几天和一朋友讨论了一下删库的过程,我觉得挺有意思,
准备记录一下。
有删库需求情况挺多,比如有一天项目结束了需要,开发说要删除这个数据库。也许机器磁盘不够了,上面正好有一个很大的库,但是好像又没用,删除可以释放资源,比如某一天你突然觉得这个没有的库放在那里肯碍眼,想删除它,
当然也有可能是业务需求,会经常创建和删除库 等等情况, 其实说明 删库这个需求确认真实存在的。
也许你会觉得删库这个问题很简单,一个命令而已, drop database db_name; 是不是很简单。
我们删除属于DDL操作,不能回滚数据,如果需要恢复,那基本需要全量备份+binlog,
影响恢复时间的因素很多: 恢复决策的时间,dba恢复数据熟练度,实例大小,数据修改频繁度,以及机器配置,网络传输速度 和其他因素
而且不管怎么说,如果删除的数据库,里面还有业务访问,影响业务是肯定,不能快速恢复也是很蛋疼的事儿。
所以我需要做的就是,尽量减少删除后需要恢复的可能性。
我们需要考虑的情况比较多,
下面列举一部分常见,其他根据不同场景大家可以自己考虑考虑,也是一个总结的过程哇
一、开发或者业务提出要删库:
1、开发提出需要删除 某某库,db应该需要确认该库是否可以删除
a)、如果程序使用了长连接,我们可以先看看是否有该库的连接,如果有该库的连接,直接驳回,让开发确认程序是否下线完成。可以使用下面语句确认。
select * from information_schema.processlist where DB='$db_name';
b)、如果检查未发现长连接,则需要查看是否有短连接的程序访问:如果有访问依旧需要找开发确认,如果没有访问,有条件可以多观察一段时间。
可以借助简单的工具 mysql-sniffer :
./mysql-sniffer -i bond0 -p 6006 |grep "$db_name" ,可以获取到访问ip,库名,sql语句等,通过grep工具过滤可以得到很小的结果集,使用非常方便。
项目为360开源项目:https://github.com/Qihoo360/mysql-sniffer,当然也可以使用tcpdump,开启general_log 的方式来做。办法有很多种
c)、已经确认确实程序已经没有使用程序访问该库,我们就需要开了删库了,
方案1、先remoke 该库相关的权限,然后观察一段时间,如果没有问题就可以删库。
优点:就是以后建立同名库,不存在权限问题,
缺点:如果考虑操作回滚,需要备份数据库的权限,最好还只能备份被remoke的权限,这个过程其实不好做,考虑的因素挺多。
方案2、不remoke 权限,直接rename 数据库名,原理是先建立一个新库,将需要重命名的库里面的表全部通过RENAME TABLE tbl_name TO new_tbl_name的方式将表放入新的库里面,
然后将老库删除。需要注意,新老库要放在同一个磁盘分区里面,不然效率会特别低,这个过程使整个实例hang住,后果很严重。
优点:不用做任何单独备份任何东西,回滚非常方便。
缺点:考虑同名库权限问题,还是需要回收相关的权限,由于我们没有同名数据库的问题,所以权限并未回收。
个人倾向方案2,简单好实施,并且有简单的脚本:
#!/bin/sh function executeSql() { sql="$1" if [ "$sql" = "" ] then cat | mysql --default-character-set=utf8 -N else echo "$sql" | mysql --default-character-set=utf8 -N fi } function mysqlDBRename() { tempFromDBName="${fromDBName}" tempToDBName="${toDBName}" #建库 echo "create database ${toDBName};" | executeSql if [ "$?" -ne 0 ]; then echo "Error: Some error occur when create database ${toDBName}." exit 0 fi echo "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$fromDBName' and TABLE_TYPE='BASE TABLE';" | executeSql | while read tableName; do echo "alter table ${fromDBName}.${tableName} rename ${toDBName}.${tableName};" | executeSql if [ "$?" -ne 0 ]; then echo "Error: Some error occur when execute 'alter table ${fromDBName}.${tableName} rename ${toDBName}.${tableName};'" exit 1 fi done if [ "$?" -ne 0 ]; then exit 0 fi echo "drop database ${fromDBName}" | executeSql if [ "$?" -ne 0 ]; then echo "Error: Some error occur when drop database ${fromDBName}." exit 1 fi break } function main() { fromDBName="$1" toDBName="$2" mysqlDBRename } main "$1" "$2"
d)、删库;
当步骤c做了一段时间,比如1周,确认程序已经完全下了后。
1、如果该实例上面没有其他的实例,则可以直接将实例shutdown,删除实例,将最后一个备份文件归档,整个过程就算完成
2、如果该实例还有其他业务正在使用,则还需要考虑更多问题:
需要删除是库是否很大,是否有很多数量的表,比如超过1000张表,如果磁盘性能不够好,可能会对线上查询元数据的操作阻塞,例如:使用了存储过程,那么都会阻塞在读取存储过程这个步骤。
因此建议 1、分批次删除表,
2、如果有大量分区的分区表,建议先删除分区。
我这有一个存储过程可以做这个事儿,前提是需要将dbman rename成 del_xxxx这种格式的。在需要删除分区的库里面创建该存储过程,然后调用存储过程就可以。
DELIMITER $$ DROP PROCEDURE IF EXISTS `droppartion_del`$$ CREATE PROCEDURE `droppartion_del`() SQL SECURITY INVOKER label1:BEGIN declare _droppart int; declare _i int; DECLARE _dbname varchar(40); declare _droppartlist varchar(500); set _dbname=database(); if (select count(*) from information_schema.PROCESSLIST where db =_dbname) >1 then select -1,'some process on db, please check it !'; LEAVE label1; end if ; if substring_index(_dbname,'_',1)='del' then drop table if exists tablename_tmp; create table tablename_tmp(`id` int(11) unsigned NOT NULL AUTO_INCREMENT,table_name varchar(40),primary key (id)); insert into tablename_tmp(table_name) select table_name from information_schema.PARTITIONS where table_schema =_dbname and partition_name is not null group by table_name HAVING count(*) >3; select count(*) into @tablenum from tablename_tmp; set _i=1; while _i <=@tablenum do select substring(partition_name,2) into _droppart from information_schema.PARTITIONS where table_schema = _dbname and table_name =(select table_name from tablename_tmp where id=_i ) order by partition_name desc limit 2,1; select group_concat(partition_name) into _droppartlist from information_schema.PARTITIONS where table_schema =_dbname and table_name=(select table_name from tablename_tmp where id=_i ) and substring(partition_name,2) <_droppart group by table_schema,table_name; SET @stmt = CONCAT('alter table ' ,(select table_name from tablename_tmp where id=_i),' drop partition ' ,_droppartlist,' ;'); prepare SQL1 FROM @stmt; EXECUTE SQL1; set _i=_i+1; end while; truncate table tablename_tmp; insert into tablename_tmp(table_name) select table_name from information_schema.TABLES where table_schema =_dbname and table_name<>'tablename_tmp'; select count(*) into @tablenum from tablename_tmp; set _i=1; while _i <=@tablenum do SET @stmt = CONCAT('drop table if exists ' ,(select table_name from tablename_tmp where id=_i),' ;'); prepare SQL1 FROM @stmt; EXECUTE SQL1; set _i=_i+1; end while; select 1,'succeed!!'; else select -2,'we can only drop database which name like del_XXX,please check it!'; LEAVE label1; end if; END$$ DELIMITER ;
二、DBA认为某些库需要删除:
这种情况,一般是空间不足,然后有dba 认为不需要使用的库,可以清理的时候。
流程基本和上面一种情况差不多,只是要dba主动找开发,业务确认,并且做好个方便的准备来确认库是否还有业务访问。
三、业务逻辑需要按规则删除库:
对于这个需求,主要是游戏业务,合区过后清除数据的时候使用,这种相对来说判断就要简单一些,只是不确定每个表的大小,表的数量,建议在做整合脚本的时候,尽量化整为零,多次删除。
总结,以上情况都是基于线上环境考虑的, 如果仅仅是开发测试环境,个人觉得没有相对没那么麻烦,
做了基本确认, 然后做rename database, 备份,一段时间drop 掉 数据库就好了。