fetch_db_dumpsql.pl从mysqldump全备中恢复指定库的数据

解决从mysqldump全备中恢复指定库的数据,
从全备中抽取指定数据库(可多个)的sql,每个数据库导出为数据库名命名的sql文件
注意:mysqldump时不能包含参数 --skip-comments

[root@git-server opt]# perl fetch_db_dumpsql.pl --help
=========================================================================================
Info  :
        Created By babaoqi
Usage :
Command line options :
	-h, --help          Print Help Info. 
	-s, --srcfile       src dumpsql file
	-B, --databases     fetch some databases. 
	-O, --outfile       output sql file. default:cur path

Sample :
   shell> perl fetch_db_dumpsql.pl -s=xxx.sql -B=db1
   shell> perl fetch_db_dumpsql.pl -s=xxx.sql -B=db1,db2 
   shell> perl fetch_db_dumpsql.pl -s=xxx.sql -B=db1,db2,db3 -O=/tmp/
=========================================================================================
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

先全备份整个MySQL数据库

[root@git-server opt]# mysqldump -uroot -p -A -B >allbak.sql
Warning: Using a password on the command line interface can be insecure.
  • 1.
  • 2.

进行删库测试

MySQL [zhangyou]> drop database zixun3;
Query OK, 88 rows affected (0.07 sec)

MySQL [zhangyou]> drop database zhangyou;
Query OK, 88 rows affected (0.01 sec)

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |       |
| performance_schema |
| test001            |
+--------------------+
5 rows in set (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

从全备份的数据库中过滤出需要恢复的数据库的数据:

[root@git-server opt]#  perl fetch_db_dumpsql.pl -s=/opt/allbak.sql -B=zhangyou,zixun3 -O=/tmp/
srcfile=/opt/allbak.sql, databases=zhangyou,zixun3, output=/tmp/ 
============= start fetch zhangyou sqls
zhangyou pos: 2804--5519
============= start fetch zixun3 sqls
zixun3 pos: 5520--8233
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
[root@git-server opt]# ll /tmp/zhangyou.sql 
-rw-r--r-- 1 root root 931528 Jun  6 16:52 /tmp/zhangyou.sql
[root@git-server opt]# ll /tmp/zixun3.sql 
-rw-r--r-- 1 root root 14493249 Jun  6 16:52 /tmp/zixun3.sql
[root@git-server opt]# 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
posted @ 2018-06-06 17:06  勤奋的蓝猫  阅读(1)  评论(0编辑  收藏  举报  来源