カテゴリー: OS

# 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>

#!/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);

# mysqltest.pl ins
=== insert ===
=== マスタサーバ ===
name    text
248     name284 Fri Jun 20 11:42:01 2014
247     name563 Fri Jun 20 11:35:01 2014
246     name934 Fri Jun 20 11:28:01 2014
245     name769 Fri Jun 20 11:21:01 2014
244     name227 Fri Jun 20 11:14:01 2014
=== スレーブサーバ ===
name    text
248     name284 Fri Jun 20 11:42:01 2014
247     name563 Fri Jun 20 11:35:01 2014
246     name934 Fri Jun 20 11:28:01 2014
245     name769 Fri Jun 20 11:21:01 2014
244     name227 Fri Jun 20 11:14:01 2014
#

create table testtable (
	id int(20) not null auto_increment,
	name char(100) default '' not null,
	text char(255) default '',
	primary key (id) 
);

ERROR:  syntax error at or near "("
LINE 2: id int(20) not null auto_increment,

create table testtable (
	id serial not null,
	name char(100) default '' not null,
	text char(255) default '',
	primary key (id) 
);

#!/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);