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 掉 数据库就好了。