Perl访问MSSQL并迁移到MySQL数据库脚本实例
linux下没有专门为mssql设计的访问库,不过介于mssql本是从sybase派生出来的,因此用来访问sybase的库自然也能访问mssql,freetds就是这么一个实现。
perl中通常使用dbi来访问数据库,因此在系统安装了freetds之后,可以使用dbi来通过freetds来访问mssql数据库,例子:
using dbi;
my $cs = "driver={freetds};server=主机;port=1433;database=数据库;uid=sa;pwd=密码;tds_version=7.1;charset=gb2312";
my $dbh = dbi->connect("dbi:odbc:$cs") or die $@;
因为本人不怎么用windows,为了研究qq群数据库,需要将数据从mssql中迁移到mysql中,特地为了qq群数据库安装了一个windows server 2008和sql server 2008r2,不过过几天评估就到期了,研究过mysql的workbench有从ms sql server迁移数据的能力,不过对于qq群这种巨大数据而且分表分库的数据来说显得太麻烦,因此写了一个通用的perl脚本,用来将数据库从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上创建对应的数据库
$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";
#创建索引
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;");
#结束,输出任务信息
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";
这个脚本会根据每一个表fork出一个子进程和相应的数据库连接,因此做这种迁移之前得确保目标mysql数据库配置的最大连接数能承受。
然后在bash下执行
for x in {1..11};do ./qq.pl quninfo$x; done
for x in {1..11};do ./qq.pl groupdata$x; done
就不用管了,脚本会根据mssql这边表结构来在mysql那边创建一样的结构并配置索引。
上一篇: python批量下载壁纸的实现代码
下一篇: 求婚示爱的Perl代码之改写篇