perl 远程 mysql_写的一个perl脚本，用于发送远程MySQL命令

想写一些简化管理操作的脚本，下面是基础脚本之一。

对于一个从来没使用过perl脚本的我来说还是有些难度的，直接上代码。

此脚本用于发送远程MySQL命令并且接收结果，功能比较简单，后面会渐渐完善。

#!/usr/bin/perl

use Getopt::Long;

use DBI;

Getopt::Long::GetOptions(

'host|h=s' => \$host,'user|u=s' => \$user,'password|pw=s' => \$password,'port|p=s' => \$port,'command|c=s' => \$command,'groupfile|f=s' => \$groupfile,'help' => \$help

);

#print help info

my $printh=q{

usage :

mysqlcon -h 192.168.0.33 -u root -pw 'xiaojun' -p 3306 -c 'show global status'

or

mysqlcon -g 2.txt -c 'select user();'

cat 2.txt:

192.168.0.33 root xiaojun 3306

192.168.0.34 root xiaojun 3306

options:

-h database server *

-u account name *

-pw password for account *

-p port for mysqld *

-c command to execute *

-help print help

};

=pod

if(!defined($host)){

print "page flag set to $page "

}

if(defined($user)){

print "user flag set to $user\n ";

}

if(defined($password)){

print "onoff flag set to $password \n";

}

if(defined($command)){

print "help flag set to $command \n";

}

if(defined($help)){

print $printh

}

=cut

sub execute_sql{

my $dsn = "DBI:mysql:database=mysql;host=$_[0]:$_[1]";

my ($dbh,$sth,@ary);

$dbh = DBI->connect($dsn,$_[2],$_[3],{'RaiseError' => 1});

$sth = $dbh->prepare("$_[4]");

$sth->execute();

while(@ary = $sth->fetchrow_array()){

print join("\t",@ary),"\n";

}

$sth->finish;

$dbh->disconnect;

}

#&execute_sql($host,$port,$user,$password,$command) ;

unless (!defined($help)) { die "$printh" };

if(defined($groupfile)){

unless (defined($command)) { die "Wrong usage : No command input .\n $printh" };

open(IN,$groupfile);

while($line=){

my @args=split /\s/,$line;

print "host:@args[0]\ncommand:$command\n******BEGIN\n";

&execute_sql(@args[0],@args[3],@args[1],@args[2],$command) ;

print "******END \n\n\n*****************************************************\n\n\n";

}

}else{

unless (defined($host)) { die "Wrong usage : No host input .\n $printh" };

unless (defined($user)) { die "Wrong usage : No account input .\n $printh" };

unless (defined($password)) { die "Wrong usage : No password input .\n $printh" };

unless (defined($port)) { die "Wrong usage : No port number input .\n $printh" };

unless (defined($command)) { die "Wrong usage : No command input .\n $printh" };

&execute_sql($host,$command) ;

}

使用方法

[root@centos511 ~]# ./6.perl -h 192.168.0.33 -u root -pw 'xiaojun' -p 3306 -c 'select user()' ;

root@192.168.0.33

[root@centos511 ~]# ./6.perl -g 2.txt -c 'select user()'

host:192.168.0.33

command:select user()

******BEGIN

root@192.168.0.33

******END

*****************************************************

host:192.168.0.33

command:select user()

******BEGIN

root@192.168.0.33

******END

*****************************************************

[root@centos511 ~]# cat 2.txt

192.168.0.33 root xiaojun 3306

192.168.0.33 root xiaojun 3306

[root@centos511 ~]# ./6.perl -help

usage :

mysqlcon -h 192.168.0.33 -u root -pw 'xiaojun' -p 3306 -c 'show global status'

or

mysqlcon -g 2.txt -c 'select user();'

cat 2.txt:

192.168.0.33 root xiaojun 3306

192.168.0.34 root xiaojun 3306

options:

-h database server *

-u account name *

-pw password for account *

-p port for mysqld *

-c command to execute *

-help print help