首页 > 代码库 > 在大备份文件里抽取单个库或单个表的脚本

在大备份文件里抽取单个库或单个表的脚本

2024-07-06 08:16:14   220人阅读

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`

 


联系
我们