MYSQL通常单机单实例，每个实例下面有很多不同的库，备份时带上--all-databases,有时候会需要从备份文件里面抽取单个库，或者某个库下的某个表。
如果还原全部数据库，肯定是可以做到的，但是时间代价非常大。下面这个脚本就是实现快速提取单个库，单个表
#/bin/bash #author :zuoxingyu #create_date:2014-05-28 #usage :./single_table_restore.sh back.sql DB1 #usage :./single_table_restore.sh back.sql DB1 T1 BACKUP_FILE=$1 DB_NAME=$2 TB_NAME=$3 #至少有2个参数,第一个参数是备份文件名称，第二个参数是库名称，第三个参数是表名称，第三个参数不存在时，恢复库 if [ "-$1" = "-" ];then echo "you must privide backup file." exit 0 fi if [ "-$2" = "-" ];then echo "you must provide database name or table name." exit 0 fi echo "restore DB_NAME:" $DB_NAME "restore TB_NAME:" $TB_NAME echo "generate restore file start" `date "+%Y-%m-%d %H:%M:%S"` if [ "-$3" != "-" ];then sed -n "/^-- Current Database: \`$DB_NAME\`/,/^-- Current Database:/p" $BACKUP_FILE|sed -n "/^-- Table structure for table \`$TB_NAME\`/,/^UNLOCK TABLES/p" >restore.sql else sed -n "/^-- Current Database: \`$DB_NAME\`/,/^-- Current Database:/p" $BACKUP_FILE>restore.sql fi echo "generate restore file end" `date "+%Y-%m-%d %H:%M:%S"` echo "filename:./restore.sql"
抽取单个表：
[root@meizuDB data]# ./single_table_restore.sh back.sql DB1 T1 restore DB_NAME: DB1 restore TB_NAME: T1 generate restore file start 2014-05-28 19:25:22 generate restore file end 2014-05-28 19:25:22 filename:./restore.sql [root@meizuDB data]# cat restore.sql -- Table structure for table `T1` -- DROP TABLE IF EXISTS `T1`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `T1` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `T1` -- LOCK TABLES `T1` WRITE; /*!40000 ALTER TABLE `T1` DISABLE KEYS */; INSERT INTO `T1` VALUES (1),(2),(3); /*!40000 ALTER TABLE `T1` ENABLE KEYS */; UNLOCK TABLES;
抽取单个库
[root@meizuDB data]# ./single_table_restore.sh back.sql DB1 restore DB_NAME: DB1 restore TB_NAME: generate restore file start 2014-05-28 19:29:43 generate restore file end 2014-05-28 19:29:43 filename:./restore.sql [root@meizuDB data]# cat restore.sql -- Current Database: `DB1` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `DB1` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `DB1`; -- -- Table structure for table `T1` -- DROP TABLE IF EXISTS `T1`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `T1` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `T1` -- LOCK TABLES `T1` WRITE; /*!40000 ALTER TABLE `T1` DISABLE KEYS */; INSERT INTO `T1` VALUES (1),(2),(3); /*!40000 ALTER TABLE `T1` ENABLE KEYS */; UNLOCK TABLES; -- -- Current Database: `DB2`
