利用perl脚本实现跨服务器的数据转移

本人最近接到一个任务,要求利用perl脚本实现数据的转移。因为之前是利用Spring定时任务做的，虽然也能实现，但是所占内存难以释放，现在要求改进。刚接到这个要求的时候，确实感觉困难重重——从大致了解perl语言，到跟mysql进行jdbc交互，一路走来真是不易啊！好了，废话不多说，现在把过程大致展现出来。

1.新建一个以.pl结尾的perl脚本文件。

2.前期引入：

use Benchmark;
use threads;
use DBI;
use LWP::UserAgent;
use Cwd;
use POSIX qw( strftime );
use Encode;
use utf8;

3.涉及到两个服务器数据库的连接参数:

my $driver="DBI:mysql";
my $database_203="xxxx"; my $database_198="xxxx"; 
my $user_203="xxxx"; my $user_199="xxxx";
my $passwd_203="xxxx#"; my $passwd_198="xxxx#"; 
my $host_203="xxxx"; my $host_198="xxxx"; 
my $port_203="xxxx"; my $port_198="xxxx"; 
my $dbh_203; my $dbh_198; 
my $sth_203; my $sth_198;

4.动态表名

my $curMonth=strftime("%Y%m", localtime);my $tableName="mm_mo_sms_".$curMonth;

5.添加主线程和工作函数

sub main{
	my $workThread = threads->create('work');
	if(defined($workThread)) {
		print("The app stops, id:".$workThread->tid()."\n");
		$workThread->join;
	}else{
		print("The app start fail");
	}
}

sub work{
#DB connection
#连接203数据库
[color=red]$dbh_203=DBI->connect("$driver:database=$database_203;host=$host_203;port=$port_203;user=$user_203;password=$passwd_203")
	or die "Connect failed……".DBI->errstr;[/color]
#这三行必须添加,否则在跨服务器的数据转移中，中文会出现乱码哦！
$dbh_203->do("SET character_set_client = 'utf8'");
$dbh_203->do("SET character_set_connection = 'utf8'");
$dbh_203->do("SET character_set_results= 'utf8'");

[color=red]$dbh_198=DBI->connect("$driver:database=$database_198;host=$host_198;port=$port_198;user=$user_198;password=$passwd_199")[/color]
	or die "Connect failed……".DBI->errstr;
$dbh_198->do("SET character_set_client = 'utf8'");
$dbh_198->do("SET character_set_connection = 'utf8'");
$dbh_198->do("SET character_set_results= 'utf8'");

	doMo($dbh_198,$dbh_199,$dbh_203);
	$dbh_198->disconnect();
	$dbh_203->disconnect();
}

6.实现数据的转移(即把198服务器上面的数据select出来之后插入到203服务器的数据库上面)：

my $sql_198;
sub doMo{
	#198->203
	[color=red]my $select_198=$dbh_198->prepare[/color]("SELECT 
  		 msg_id msgId,tran_id tranId,mo_from moFrom,mo_to moTo,access_mode accessMode,sp_id spId,msg_fmt msgFmt,msg_content msgContent,link_id linkedId,
		 ip_address ipAddress,service_id serviceId,prov	,city,cp_id	cpId,cat_id catId,fee_id feeId,fee_cp feeCp,pd_id pdId,sub_id subId,
		task_status taskStatus,mo_type moType,sync_status syncStatus,status,sync_time syncTime,sync_times syncTimes,sync_result syncResult,create_time createTime 
		FROM
  		  mm.mm_mo_sms
		WHERE create_time like '%2014-10-06%' ORDER BY create_time ASC");
	[color=blue]$select_198->execute();[/color]
	while(my $row=$select_198->fetchrow_hashref()) {
		$sql_198="insert into ".$tableName."
		(msg_id,tran_id,mo_from,mo_to,access_mode,sp_id,msg_fmt,msg_content,link_id,ip_address,service_id,prov,city,cp_id,cat_id,
		fee_id,fee_cp,pd_id,sub_id,task_status,mo_type,sync_status,status,sync_time,sync_times,sync_result,create_time)
		values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		[color=blue]$sth_203=$dbh_203->prepare_cached($sql_198);[/color]
		[color=brown]$sth_203->bind_param(1,$row->{'msgId'});[/color]
		$sth_203->bind_param(2,$row->{'tranId'});
		$sth_203->bind_param(3,$row->{'moFrom'});
		$sth_203->bind_param(4,$row->{'moTo'});
		$sth_203->bind_param(5,$row->{'accessMode'});
		$sth_203->bind_param(6,$row->{'spId'});
		$sth_203->bind_param(7,$row->{'msgFmt'});
		$sth_203->bind_param(8,$row->{'msgContent'});
		$sth_203->bind_param(9,$row->{'linkedId'});
		$sth_203->bind_param(10,$row->{'ipAddress'});
		$sth_203->bind_param(11,$row->{'serviceId'});
		$sth_203->bind_param(12,$row->{'prov'});
		$sth_203->bind_param(13,$row->{'city'});
		$sth_203->bind_param(14,$row->{'cpId'});
		$sth_203->bind_param(15,$row->{'catId'});
		$sth_203->bind_param(16,$row->{'feeId'});
		$sth_203->bind_param(17,$row->{'feeCp'});
		$sth_203->bind_param(18,$row->{'pdId'});
		$sth_203->bind_param(19,$row->{'subId'});
		$sth_203->bind_param(20,$row->{'taskStatus'});
		$sth_203->bind_param(21,$row->{'moType'});
		$sth_203->bind_param(22,$row->{'syncStatus'});
		$sth_203->bind_param(23,$row->{'status'});
		$sth_203->bind_param(24,$row->{'syncTime'});
		$sth_203->bind_param(25,$row->{'syncTimes'});
		$sth_203->bind_param(26,$row->{'syncResult'});
		$sth_203->bind_param(27,$row->{'createTime'});

		[color=blue]$sth_203->execute();
		$sth_203->finish();[/color]
	}
}

注：这种写法有点类似于jdbc，不过它又具有自己的优势。比之jdbc更为灵活和简洁。
其中的要点有:
[color=red](1)DBI连接数据库;
(2)DBI的sql参数设置;
(3)遍历查询结果的语法[/color]

至此,执行该脚本,就可以实现数据的跨服务器转移了.可以进一步把它放在Linux定时任务中定时执行.