按数据库拆分一个带有多个数据库的mysqldump文件 [英] Split up a mysqldump file with multiple databases, by database

问题描述

我有一个包含多个数据库的mysqldump文件(5).其中一个数据库需要很长时间才能加载，是否可以通过数据库拆分mysqldump文件，或者只是告诉mysql仅加载指定的数据库之一?

I have a mysqldump file of multiple databases (5). One of the database takes a very long time to load, is there a way to either split the mysqldump file by database, or just tell mysql to load only one of the specified databases?

Manish

推荐答案

此Perl脚本应该可以解决问题.

This Perl script should do the trick.

#!/usr/bin/perl -w
#
# splitmysqldump - split mysqldump file into per-database dump files.

use strict;
use warnings;

my $dbfile;
my $dbname = q{};
my $header = q{};

while (<>) {

    # Beginning of a new database section:
    # close currently open file and start a new one
    if (m/-- Current Database\: \`([-\w]+)\`/) {
    if (defined $dbfile && tell $dbfile != -1) {
        close $dbfile or die "Could not close file!"
    } 
    $dbname = $1;
    open $dbfile, ">>", "$1_dump.sql" or die "Could not create file!";
    print $dbfile $header;
    print "Writing file $1_dump.sql ...\n";
    }

    if (defined $dbfile && tell $dbfile != -1) {
    print $dbfile $_;
    }

    # Catch dump file header in the beginning
    # to be printed to each separate dump file.  
    if (! $dbname) { $header .= $_; }
}
close $dbfile or die "Could not close file!"

对包含所有数据库的转储文件运行此操作

Run this for the dump file containing all databases

./splitmysqldump < all_databases.sql

这篇关于按数据库拆分一个带有多个数据库的mysqldump文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！