利用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数据库
$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;
#这三行必须添加,否则在跨服务器的数据转移中，中文会出现乱码哦！
$dbh_203->do("SET character_set_client = 'utf8'");
$dbh_203->do("SET character_set_connection = 'utf8'");
$dbh_203->do("SET character_set_results= 'utf8'");
$dbh_198=DBI->connect("$driver:database=$database_198;host=$host_198;port=$port_198;user=$user_198;password=$passwd_199")
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
my $select_198=$dbh_198->prepare("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_idcpId,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");
$select_198->execute();
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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
$sth_203=$dbh_203->prepare_cached($sql_198);
$sth_203->bind_param(1,$row->{'msgId'});
$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'});
$sth_203->execute();
$sth_203->finish();
}
}

注：这种写法有点类似于jdbc，不过它又具有自己的优势。比之jdbc更为灵活和简洁。
其中的要点有:
(1)DBI连接数据库;
(2)DBI的sql参数设置;
(3)遍历查询结果的语法
至此,执行该脚本,就可以实现数据的跨服务器转移了.可以进一步把它放在Linux定时任务中定时执行.