SQL – OSAKANA TAROのメモ帳

MSSQL Always on構成で、バックアップソフトを使ってデータベースバックアップを行っている場合、リストア/リカバリをどのように行うのか、わかりにくかったのでメモ。

(1) SQLマネージメントスタジオでAlways on設定から該当データベースを削除

(2) フェールオーバークラスタマネージャーにてSQLの役割を全て停止（一部だけだとうまくいかないケースあり)

(3)バックアップソフトからリストア実施

(4)フェールオーバークラスタマネージャーにてSQLの役割を開始

(5)SQLマネージメントスタジオでAlways on設定に該当データベースを追加

declare @i int declare @date numeric set @i = 1 while @i < 40 begin SELECT @date = DATEDIFF(s,'1970/1/1', GETUTCDATE() ); insert into [pubs].[dbo].[test3](counter)values (@date); set @i = @i + 1 waitfor delay '00:01:00' end

MySQLのレプリケーション機能を使うとどのような動きをするのかを確認中。

テストデータのインサートと確認を行うためのスクリプトを作成した。

標準で用意されているデータベース「test」内にテーブル「testtable」を作成して、そこにデータを投入することにした。

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

使い方
情報確認のみ「mysqltest.pl」
データ追加と情報確認「mysqltest.pl ins」

オプションとして「ins」(insert)を指定すると、データを追加する、というもの。

実行すると以下の様な形となる。

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

「単純増加する数字 name+ランダム数字日付」という出力内容のうち、最新の５件のみ表示する、というものになっている。

マスタサーバの出力内容と、スレーブサーバの出力内容を比べる、というもの。

2021/05/19

postgresql環境でのバックアップ試験のためにこのスクリプトを流用しようとしたところ、作り直しが必要なポイントがあった。

まずはテーブル作成時の違い

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,

int型auto_increment属性というのはMySQL特有で、postgresqlではserial型で置き換える。
「int(20) auto_increment」→「serial」に変更すると、以下のようになる。

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

また、perlモジュールは DBD:mysqlではなくDBD:Pgに変更になり、データベースとホスト指定手法が変わった。

パッケージとしては perl-DBD-Pg をインストールする。

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

カテゴリー: SQL

MSSQL Always on構成でのデータベースリカバリ手順の概要

SQL Management Studioでunixtimeを１分おきにINSERTするSQL文

MySQLとpostgresqlでのレプリケーション動作検証時使用perlスクリプト