# mysql -u root
mysql> use test;
mysql> create table testtable (
id int(20) not null auto_increment,
name char(100) default '' not null,
text char(255) default '',
primary key (id)
);
mysql> grant all on test.* to テストユーザ名@ホスト名 identified by 'パスワード';
mysql>
スクリプトはperl。 perl-DBD-MySQLパッケージをインストールしておくこと。
#!/usr/bin/perl
use DBI;
my $mysqluser="MySQL内のユーザ名";
my $mysqlpassword="パスワード";
my $masterserver="マスタサーバ名";
my $slaveserver="スレーブサーバ名";
my $dbname="test";
my $tablename="testtable";
$max=5;
sub getselect{
my($server,$user,$password,$dbname,$tablename)=@_;
my $db=DBI->connect("DBI:mysql:$dbname:$server",$user,$password);
my $sth=$db->prepare("select id,name,text from $tablename order by id desc");
$sth->execute;
print "\tname\ttext\n";
my $count=$max;
for(my $i=0; $i<$sth->rows; $i++){
my @tmp=$sth->fetchrow_array;
if($count>0){
print $tmp[0] ."\t". $tmp[1] ."\t". $tmp[2] ."\n";
$count--;
}
}
$sth->finish;
$db->disconnect;
}
sub insertvalue{
my($server,$user,$password,$dbname,$tablename)=@_;
my $db=DBI->connect("DBI:mysql:$dbname:$server",$user,$password);
my $str1=substr("00".rand(100),-3);
my $str2=localtime();
my $sth=$db->prepare("insert into $tablename values (NULL,'name$str1','$str2')");
$sth->execute;
$sth->finish;
$db->disconnect;
}
if($ARGV[0] =~ /ins/){
print "=== insert ===\n";
insertvalue($masterserver,$mysqluser,$mysqlpassword,$dbname,$tablename);
}
print "=== master:$masterserver ===\n";
getselect($masterserver,$mysqluser,$mysqlpassword,$dbname,$tablename);
print "=== slave:$slaveserver ===\n";
getselect($slaveserver,$mysqluser,$mysqlpassword,$dbname,$tablename);
#!/usr/bin/perl
use DBI;
my $mysqluser="postgresql内のユーザ名";
my $mysqlpassword="パスワード";
my $masterserver="マスタサーバ名";
my $slaveserver="スレーブサーバ名";
my $dbname="test";
my $tablename="testtable";
$max=5;
sub getselect{
my($server,$user,$password,$dbname,$tablename)=@_;
my $db=DBI->connect("DBI:Pg:dbname=$dbname;host=$server",$user,$password);
my $sth=$db->prepare("select id,name,text from $tablename order by id desc");
$sth->execute;
print "\tname\ttext\n";
my $count=$max;
for(my $i=0; $i<$sth->rows; $i++){
my @tmp=$sth->fetchrow_array;
if($count>0){
print $tmp[0] ."\t". $tmp[1] ."\t". $tmp[2] ."\n";
$count--;
}
}
$sth->finish;
$db->disconnect;
}
sub insertvalue{
my($server,$user,$password,$dbname,$tablename)=@_;
my $db=DBI->connect("DBI:Pg:dbname=$dbname;host=$server",$user,$password);
my $str1=substr("00".rand(100),-3);
my $str2=localtime();
my $sth=$db->prepare("insert into $tablename (name, text) values ('name$str1','$str2')");
$sth->execute;
$sth->finish;
$db->disconnect;
}
if($ARGV[0] =~ /ins/){
print "=== insert ===\n";
insertvalue($masterserver,$mysqluser,$mysqlpassword,$dbname,$tablename);
}
print "=== postgresql:$masterserver ===\n";
getselect($masterserver,$mysqluser,$mysqlpassword,$dbname,$tablename);
print "=== slave:$slaveserver ===\n";
getselect($slaveserver,$mysqluser,$mysqlpassword,$dbname,$tablename);
#!/usr/bin/perl
use strict;
use warnings;
use LWP;
use URI::Escape;
use JSON;
use Time::Local;
my $lwpua = LWP::UserAgent->new;
my $url="https://api.iijmio.jp/mobile/d/v1/log/packet/";
my $developerid="＜DeveloperID＞";
my $usertoken ="＜アクセストークン＞";
my $lwpreq = HTTP::Request->new(GET => "$url");
$lwpreq->header("X-IIJmio-Developer" => "$developerid");
$lwpreq->header("X-IIJmio-Authorization" => "$usertoken");
my $lwpres= $lwpua->request($lwpreq);
my $data = decode_json($lwpres->content);
for(my $i=0;$i<@{$data->{packetLogInfo}};$i++){
#print $i . ":" . $data->{packetLogInfo}->[$i]->{hddServiceCode} ."\n";
my $hddServiceCode=$data->{packetLogInfo}->[$i]->{hddServiceCode};
for(my $j=0;$j<@{$data->{packetLogInfo}->[$i]->{hdoInfo}};$j++){
#print $i . "-" . $j . ":" . $data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{hdoServiceCode} . "\n";
my $hdoServiceCode=$data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{hdoServiceCode};
if(! -f "$hdoServiceCode.rrd"){
print "rrdtool create $hdoServiceCode.rrd --start 1384786740 --step 86400 DS:withCoupon:GAUGE:86400:0:U DS:withoutCoupon:GAUGE:86400:0:U RRA:LAST:0.5:1:100000 \n";
system("rrdtool create $hdoServiceCode.rrd --start 1384786740 --step 86400 DS:withCoupon:GAUGE:86400:0:U DS:withoutCoupon:GAUGE:86400:0:U RRA:LAST:0.5:1:100000");
}
for(my $k=0;$k<@{$data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{packetLog}};$k++){
#print " " . $data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{packetLog}->[$k]->{date} ;
#print ":" . $data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{packetLog}->[$k]->{withCoupon} ;
#print ":" . $data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{packetLog}->[$k]->{withoutCoupon} ;
#print "\n";
my $d=$data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{packetLog}->[$k]->{date};
my $year = substr($d,0,4);
my $mon = substr($d,4,2) - 1;
my $mday = substr($d,6,2);
my $unixtime = timelocal(0,59,23,$mday,$mon,$year);
my $withCoupon=$data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{packetLog}->[$k]->{withCoupon};
my $withoutCoupon=$data->{packetLogInfo}->[$i]->{hdoInfo}->[$j]->{packetLog}->[$k]->{withoutCoupon};
print "rrdtool update $hdoServiceCode.rrd $unixtime:$withCoupon:$withoutCoupon \n";
system("rrdtool update $hdoServiceCode.rrd $unixtime:$withCoupon:$withoutCoupon");
}
}
}
これを「get.pl」というファイル名で保存します。
実行すると、「hdo810xxxxx.rrd」というような感じのファイルがSIMカード分作成されます。
なお、手抜きをしているので、２回目以降は、同じ値を入力しようとし、「ERROR: hdo8371xxxx.rrd: illegal attempt to update using time 1387378740 when last update time is 1387378740 (minimum one second step)」みたいなエラーがでてしまいますが、仕様です。
また、rrdtoolは一度登録した情報の更新ができない構造をしているので、日付が変わる直前に実行することを推奨します。
