Perl accesses MSSQL and migrates to MySQL database script instance _perl

Linux is not specifically designed for MSSQL access to the library, but between the MSSQL is derived from Sybase, so access to Sybase's library can naturally access Mssql,freetds is such a realization.
Perl typically uses DBI to access the database, so after the system has FreeTDS installed, you can use DBI to access the MSSQL database via FreeTDS, for example:

Copy Code code as follows:

Using DBI;
My $cs = "Driver={freetds}; Server= host; port=1433;database= database; Uid=sa; pwd= password; tds_version=7.1;charset=gb2312 ";
My $dbh = Dbi->connect ("DBI:ODBC: $cs") or Die $@;

Because I do not use Windows, in order to study QQ group database, need to migrate data from MSSQL to MySQL, specially for QQ Group database installed a Windows Server 2008 and SQL Server 2008r2, but in a few days the assessment expires, The study of MySQL's Workbench has the ability to migrate data from MS SQL Server, but it is too cumbersome for the QQ group to have such huge data and the data for the sub-database, so write a generic Perl script to migrate the database from MSSQL to MySQL. Combined with bash, it's convenient to transfer hundreds of tables from more than 20 libraries to the following Perl code:

Copy Code code as follows:

#!/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 server; Port=1433;database= $db; Uid=sa; Pwd=mssql password; 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 server", "MySQL User name", "MySQL password") 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上创建对应的数据库
$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;");
#获取表结构, to generate the DDL used for MySQL
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";
#创建索引
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";
}
}
#转移数据
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;");
#结束, output task information
My $elapsed = time-$start;
Print "Child process $$ a 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";

This script fork a subprocess and a corresponding database connection based on each table, so make sure that the maximum number of connections that the target MySQL database configures can withstand before doing this migration.
And then execute it under bash.

Copy Code code as follows:

For x in {1..11};d O./qq.pl quninfo$x; Done
For x in {1..11};d O./qq.pl groupdata$x; Done

Instead, the script creates the same structure and configures the index on the MySQL side based on the table structure of MSSQL.