解决从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/ =========================================================================================
先全备份整个MySQL数据库
[root@git-server opt]# mysqldump -uroot -p -A -B >allbak.sql Warning: Using a password on the command line interface can be insecure.
进行删库测试:
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)
从全备份的数据库中过滤出需要恢复的数据库的数据:
[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
[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]#