perl訪問mssql并遷移到mysql數(shù)據(jù)庫腳本實例

字號:


    linux下沒有專門為mssql設計的訪問庫,不過介于mssql本是從sybase派生出來的,因此用來訪問sybase的庫自然也能訪問mssql,freetds就是這么一個實現(xiàn)。
    perl中通常使用dbi來訪問數(shù)據(jù)庫,因此在系統(tǒng)安裝了freetds之后,可以使用dbi來通過freetds來訪問mssql數(shù)據(jù)庫,例子:
    代碼如下:
    using dbi;
    my $cs = driver={freetds};server=主機;port=1433;database=數(shù)據(jù)庫;uid=sa;pwd=密碼;tds_version=7.1;charset=gb2312;
    my $dbh = dbi->connect(dbi:odbc:$cs) or die $@;
    因為本人不怎么用windows,為了研究qq群數(shù)據(jù)庫,需要將數(shù)據(jù)從mssql中遷移到mysql中,特地為了qq群數(shù)據(jù)庫安裝了一個windows server 2008和sql server 2008r2,不過過幾天評估就到期了,研究過mysql的workbench有從ms sql server遷移數(shù)據(jù)的能力,不過對于qq群這種巨大數(shù)據(jù)而且分表分庫的數(shù)據(jù)來說顯得太麻煩,因此寫了一個通用的perl腳本,用來將數(shù)據(jù)庫從mssql到mysql遷移,結合bash,很方便的將這二十多個庫上百張表給轉移過去了,perl代碼如下:
    代碼如下:
    #!/usr/bin/perl
    use strict;
    use warnings;
    use dbi;
    die usage: qq db\n if @argv != 1;
    my $db = $argv[0];
    print connectin to databases $db...\n;
    my $cs = driver={freetds};server=mssql的服務器;port=1433;database=$db;uid=sa;pwd=mssql密碼;tds_version=7.1;charset=gb2312;
    sub db_connect
    {
    my $src = dbi->connect(dbi:odbc:$cs) or die $@;
    my $target = dbi->connect(dbi:mysql:host=mysql服務器, mysql用戶名, mysql密碼) or die $@;
    return ($src, $target);
    }
    my ($src, $target) = db_connect;
    print reading table schemas....\n;
    my $q_tables = $src->prepare(select name from sysobjects where xtype = 'u' and name != 'dtproperties';);#獲取所有表名
    my $q_key_usage = $src->prepare(select table_name, column_name from information_schema.key_column_usage;);#獲取表的主鍵
    $q_tables->execute;
    my @tables = ();
    my %keys = ();
    push @tables, @_ while @_ = $q_tables->fetchrow_array;
    $q_tables->finish;
    $q_key_usage->execute();
    $keys{$_[0]} = $_[1] while @_ = $q_key_usage->fetchrow_array;
    $q_key_usage->finish;
    #獲取表的索引信息
    my $q_index = $src->prepare(qq(
    select t.name, c.name
    from sys.index_columns i
    inner join sys.tables t on t.object_id = i.object_id
    inner join sys.columns c on c.column_id = i.column_id and i.object_id = c.object_id;
    ));
    $q_index->execute;
    my %table_indices = ();
    while(my @row = $q_index->fetchrow_array)
    {
    my ($table, $column) = @row;
    my $columns = $table_indices{$table};
    $columns = $table_indices{$table} = [] if not $columns;
    push @$columns, $column;
    }
    $q_index->finish;
    #在目標mysql上創(chuàng)建對應的數(shù)據(jù)庫
    $target->do(drop database if exists `$db`;) or die cannot drop old database $db\n;
    $target->do(create database `$db` default charset = utf8 collate utf8_general_ci;) or die cannot create database $db\n;
    $target->disconnect;
    $src->disconnect;
    my $total_start = time;
    for my $table(@tables)
    {
    my $pid = fork;
    unless($pid)
    {
    ($src, $target) = db_connect;
    my $start = time;
    $src->do(use $db;);
    #獲取表結構,用來生成mysql用的ddl
    my $q_schema = $src->prepare(select column_name, is_nullable, data_type, character_maximum_length from information_schema.columns where table_name = ? order by ordinal_position;);
    $target->do(use `$db`;);
    $target->do(set names utf8;);
    my $key_column = $keys{$table};
    my $ddl = create table `$table` ( \n;
    $q_schema->execute($table);
    my @fields = ();
    while(my @row = $q_schema->fetchrow_array)
    {
    my ($column, $nullable, $datatype, $length) = @row;
    my $field = `$column` $datatype;
    $field .= ($length) if $length;
    $field .= primary key if $key_column eq $column;
    push @fields, $field;
    }
    $ddl .= join(,\n, @fields);
    $ddl .= \n) engine = myisam;\n\n;
    $target->do($ddl) or die cannot create table $table\n;
    #創(chuàng)建索引
    my $indices = $table_indices{$table};
    if($indices)
    {
    for(@$indices)
    {
    $target->do(create index `$_` on `$table`(`$_`);\n) or die cannot create index on $db.$table$.$_\n;
    }
    }
    #轉移數(shù)據(jù)
    my @placeholders = map {'?'} @fields;
    my $insert_sql = insert delayed into $table values( .(join ', ', @placeholders) . );\n;
    my $insert = $target->prepare($insert_sql);
    my $select = $src->prepare(select * from $table;);
    $select->execute;
    $select->{'longreadlen'} = 1000;
    $select->{'longtruncok'} = 1;
    $target->do(set autocommit = 0;);
    $target->do(start transaction;);
    my $rows = 0;
    while(my @row = $select->fetchrow_array)
    {
    $insert->execute(@row);
    $rows++;
    }
    $target->do(commit;);
    #結束,輸出任務信息
    my $elapsed = time - $start;
    print child process $$ for table $db.$table done, $rows records, $elapsed seconds.\n;
    exit(0);
    }
    }
    print waiting for child processes\n;
    #等待所有子進程結束
    while (wait() != -1) {}
    my $total_elapsed = time - $total_start;
    print all tasks from $db finished, $total_elapsed seconds.\n;
    這個腳本會根據(jù)每一個表fork出一個子進程和相應的數(shù)據(jù)庫連接,因此做這種遷移之前得確保目標mysql數(shù)據(jù)庫配置的最大連接數(shù)能承受。
    然后在bash下執(zhí)行
    代碼如下:
    for x in {1..11};do ./qq.pl quninfo$x; done
    for x in {1..11};do ./qq.pl groupdata$x; done
    就不用管了,腳本會根據(jù)mssql這邊表結構來在mysql那邊創(chuàng)建一樣的結構并配置索引。