DBA logs: 2009

2009年11月17日星期二

perl ASCII 码处理日文或中文字

#!/usr/bin/perl -w
use strict;
use warnings;
#binmod(utf8);

my @strings = qw(
!?
@
#
$
%
^
&
*
(
)
{
}
[
]
`
;
:
\'
"
?
<
>
,
.
/
\
|
-
+
_
=
！
＠
＃
＄
％
＾
＆
＊
（
）
｛
｝
「
」
‘
；
：

”
？
＜
＞
、
。
?
￥
｜
ー
＋
＿
＝
【
】
［
］
);

my @char=qw(
1个好人
$
<＋
<
);

my $Count=0;
my %hashmap;
my @ascii_character_numbers;
foreach(@strings)
{
@ascii_character_numbers = unpack("C*", "$_");
my $temp= "@ascii_character_numbers";
$hashmap{$temp}=$temp;
}

foreach my $k ( keys %hashmap )
{print $k,"-\t-";}

if (exists($hashmap{'163 189'})) {
#print " it exists \n";
}
print "*"x50,"\n";
my @ascii_characters;
foreach my $char (@char)
{
@ascii_characters = unpack("C*", "$char");
#print "@ascii_characters\n";
print "-"x50,"\n";
my @real=();
my $i=0;
while($i<=scalar(@ascii_characters))
{
#print $ascii_characters[$i],"\n";
if ($ascii_characters[$i]>127){
#print "是一个多字节字\n";
my $tt = $ascii_characters[$i]." ".$ascii_characters[$i+1];
push @real,$tt;
$i+=2;
}
else{
#print "是一个单字节字\n";
push @real,$ascii_characters[$i];
$i++;
}
if ($i==(scalar(@ascii_characters))) {
print "start to print real ary\n";
print join "\n",@real,"\n";
foreach my $key (@real){
if (!exists($hashmap{$key}))
{
print "No\n";
}
else{
print "YES\n";
$Count++;
}
}

}

}

}

print "All: $Count\n";

-------
下午有空做了修改
sub Check_Have_Defined{
# IN two argus: 1.productid 2.productname
# OUT 0: 所有字符都在字符列表中 1：有不在列表中的字符
my ( $pid, $pname ) = @_;
#print "$pid\t\t$pname\n";
my @ascii_characters = unpack("C*", "$pname");

my @real=();
my $i=0;
my $sign=0;
while($i<=scalar(@ascii_characters))
{
if ($ascii_characters[$i]>127){
#print "是一个多字节字\n";
my $tt = $ascii_characters[$i]." ".$ascii_characters[$i+1];
push @real,$tt;
$i+=2;
}
else{
#print "是一个单字节字\n";
push @real,$ascii_characters[$i];
$i++;
} #end else

if ($i==(scalar(@ascii_characters))){
#print "start to print real ary\n";
#print join "\n",@real,"\n";
foreach my $key (@real)
{
#print $key,"\n";
if (!exists($hashmap{$key}))
{
$sign++;
}
}
} #end if
} # while

#print "====== $sign =======\n";
if ($sign == 0)
{
#print "符合，所有字符在特殊列表中\n";
return 1;
}
else{
#print "不符合\n";
return 0;
}

} # sub

写成函数

调用:
use DBI;
my $host = "192.168.117.161";
my $db = "FrontEnd";
my $user = "dev";
my $passwd = "hereiserror";

my $Report="REPORT";
open(FHD,">$Report") || die "Can't write file";

my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host",$user,$passwd) or die "connecting : $DBI::errstr\n";
my $sth;
my @chids = qw/50 56 57 58 59 60/;
my $Count=0;

foreach my $chid (@chids)
{
print FHD "ChannelID : $chid \n";
my $SQL=qq(select productid,name from C${chid}Product);
$sth = $dbh->prepare($SQL) or die "Can't prepare : $dbh->errstr\n";
$sth->execute();

while(my @array = $sth->fetchrow_array() ) {
my $needed = Check_Have_Defined($array[0],$array[1]);
if ($needed==1)
{
print FHD "ProductID: $array[0]\t ProductName: $array[1]\n";
$Count++;
}
}
}
print FHD "All: $Count\n";

2009年11月16日星期一

perl找出需要的串

#!/usr/bin/perl -w
use DBI;
use strict;

my $host = "192.168.10.118";
my $db = "BE";
my $user = "dev";
my $passwd = "3h8hs3";

my $Report="SPREPORT";
my $Rep="tempEPORT";
open(FHD,">$Report") || die "Can't write file";

open(HD,">$Rep") || die "Can't write file";

my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host",$user,$passwd) or die "connecting : $DBI::errstr\n";
my $sth;

my @chids = qw/2 3 4 5 6 7 8 9 10/;
my @sum;
my %hm;
while()
{
chomp($_);
$hm{$_}=$_;
}

foreach my $chid (@chids)
{
print FHD "ChannelID : $chid \n";
print FHD "="x40,"\n";
my $SQL=qq(select productid,name from C${chid}Product);
#print "$SQL\n";
$sth = $dbh->prepare($SQL) or die "Can't prepare : $dbh->errstr\n";
$sth->execute();

my $productid;
my $name;

my $i = 0;
my $j = 0;
while(my @array = $sth->fetchrow_array() )
{
$productid=$array[0];nn
$name=$array[1];
chomp($name);
if ($productid==2536882) {
print "HERE\n";
}
if($name=~/^([!@#\$\%\^&*(){}\[\]`;:\'\"?<>,.\/\\|\-+_=])+$/)
{
$i++;
$j++;
print FHD "3. Sep characters ProductID:$productid \t Name:$name\n";
}
else{
my @sp = split //,$name;
my $sign=0;
foreach(@sp)
{
chomp($_);
$sign++ if ! exists $hm{$_};
}
if ($sign==0)
{
$i++;
$j++;
print FHD "3. Sep characters ProductID:$productid \t Name:$name\n";
}
else
{
$j++;
print HD " $j \n";
}
}
}
print FHD "ChannelID :$chid \t Count: $i\n";
push @sum,$i;
}

print "@sum\n";

$sth->finish();
$dbh->disconnect();

__DATA__
！
＠
＃
＄
％
＾
＆
＊
（
）
｛
｝
「
」
‘
；
：

”
？
＜
＞
、
。
・
￥
｜
ー
＋
＿
＝
【
】
［
］

2009年10月29日星期四

B+ tree and B-tree

B+ tree (BplusTree)

基本:
a balanced tree
从树的根到每个叶子结点路径长度相同
每个非叶结点有 n/2~n 个子结点
对于查询效率较高，但浪费一定的空间
每个典型的结点包含多达 n-1 个key :k1 k2 .. kn-1 和 n个指针 p1 p2 .. pn
查询的key值是顺利存储
p1 k1 p2 k2 .. pn-1 kn-1 pn
指针可以指向一个文件的某条记录或是某条指针（指向其他文件的记录）

每个叶子结点最多能够保存 (n - 1) 个值，但最少 [(n – 1) / 2] 个值
非叶结点指向树的叶结点，非叶结点至少有 n/2 个指针

更新
insert
do a search to determine what bucket the new record should go in
if the bucket is not full, add the record.
otherwise, split the bucket.
allocate new leaf and move half the bucket's elements to the new bucket
insert the new leaf's smallest key and address into the parent.
if the parent is full, split it also
now add the middle key to the parent node
repeat until a parent is found that need not split
if the root splits, create a new root which has one key and two pointers.

delete
It removes the search key value from the node.

文件存储结构
the leaf nodes of the tree stores the actual record

During insertion, the system locates the block that should contain the record. If there is enough free space in the node then the system stores it. Otherwise the system splits the record in two and distributes the records.

During deletion, the system first removes the record from the block containing it. If the block becomes less than half full as a result, the records in the block are redistributed.

perl 的B+tree实现Tree::BPTree

B - Tree Index Files
未完

处理xml一例

#!/usr/bin/perl
use strict;
use XML::Simple;
use Data::Dumper;
use warnings;

my $xmlfile = $ARGV[0];
my $ref = XMLin("$xmlfile",ForceArray=>1);
my $xmlname = `basename $xmlfile`;

my $Country ;
($Country = $xmlname ) =~ s/-\d+\.xml//g;
chop($Country);

my %prodprophash = (
"de" => "Produkteigenschaften",
"uk" => "Product properties",
"ca" => "Product properties",
"us" => "Product properties",
"es" => "Product properties",
"fr" => "Caract.ristiques du produit");

my $PROPerty = $prodprophash{"$Country"};
my $errnum = 0;my $warnnum = 0;
foreach (@{$ref->{'product'}}){
print "="x25,"\n";
print "Country : \"",$Country,"\"\n";
print "category id:";
print $_->{'category-id'}->[0],"\n";
print "Product id :"; print $_->{id}->[0],"\n";
my $ImgCheck="N";
my $ImgCheck="N";
if (exists($_->{'image-url'}->[0] ) )
{
if( !( ($_->{'image-url'}->[0]->{'content'} ne "") && ($_->{'image-url'}->[0]->{'content'} =~ /^http:\/\/www.abc.com\/product/)) )
...

[ array标记
{ hash标记

外层用SHELL调用
在特定目录下，循环利用 0000~0029 目录，利用一个文件来保存最后写入的文件夹名称

if [ x"$2" != x ]
then
tmpdir="$2"
downloadXml="N"
else
downloadXml="Y"

if ! [ -d "$batchDir" ]
then
mkdir -p "$batchDir"
echo "0" > "$batchDir"/lastBatchId.txt
fi

temp=$(expr $(cat "$batchDir"/lastBatchId.txt) + 1)
batchId=$(expr $temp % 30 )
batchIdDir=$(printf "%04d" $batchId)

if [ -d "$batchDir"/"$batchIdDir" ]
then
rm -rf "$batchDir"/"$batchIdDir"
fi

mkdir -p "$batchDir"/"$batchIdDir"
echo $(expr $batchId) > "$batchDir"/lastBatchId.txt

tmpdir="$batchDir"/"$batchIdDir"
fi

UTF-8

from wiki

設計UTF-8的理由
UTF-8的設計有以下的多字元組序列的特質：
單位元組字符的最高有效位元永遠為0。
多位元組序列中的首個字元組的幾個最高有效位元決定了序列的長度。最高有效位為110的是2位元組序列，而1110的是三位元組序列，如此類推。
多位元組序列中其餘的位元組中的首兩個最高有效位元為10。
UTF-8的這些特質，保證了一個字符的位元組序列不會包含在另一個字符的位元組序列中。這確保了以位元組為基礎的部份字串比對（sub-string match）方法可以適用於在文字中搜尋字或詞。有些比較舊的可變長度8位元編碼（如Shift JIS）沒有這個特質，故字串比對的算法變得相當複雜。雖然這增加了UTF-8編碼的字串的信息冗餘，但是利多於弊。另外，資料壓縮並非Unicode 的目的，所以不可混為一談。即使在傳送過程中有部份位元組因錯誤或干擾而完全遺失，還是有可能在下一個字符的起點重新同步，令受損範圍受到限制。
另一方面，由於其位元組序列設計，如果一個疑似為字符串的序列被驗證為UTF-8編碼，那麼我們可以有把握地說它是UTF-8字符串。一段兩位元組隨機序列碰巧為合法的UTF-8而非ASCII 的機率為32分1。對於三位元組序列的機率為256分3，對更長的序列的機率就更低了。

优点及缺点
关于字符串长度的一个注解：
总体来说，在Unicode字符串中不可能由码点数量决定显示它所需要的长度，或者显示字符串之后在文本缓冲区中光标应该放置的位置；组合字符、变宽字体、不可打印字符和从右至左的文字都是其归因。
所以尽管在UTF-8字符串中字元数量与码点数量的关系比UTF-32更为复杂，在实际中很少会遇到有不同的情形。
总体
优点
UTF-8是ASCII的一个超集。因为一个纯ASCII字符串也是一个合法的UTF-8字符串，所以现存的ASCII文本不需要转换。为传统的扩展ASCII字符集设计的软件通常可以不经修改或很少修改就能与UTF-8一起使用。
使用标准的面向字节的排序例程对UTF-8排序将产生与基于Unicode代码点排序相同的结果。（尽管这只有有限的有用性，因为在任何特定语言或文化下都不太可能有仍可接受的文字排列顺序。）
UTF-8和UTF-16都是可扩展标记语言文档的标准编码。所有其它编码都必须通过显式或文本声明来指定。[2]
任何面向字节的字符串搜索算法都可以用于UTF-8的数据（只要输入仅由完整的UTF-8字符组成）。但是，对于包含字符记数的正则表达式或其它结构必须小心。
UTF-8字符串可以由一个简单的算法可靠地识别出来。就是，一个字符串在任何其它编码中表现为合法的UTF-8的可能性很低，并随字符串长度增长而减小。举例说，字元值C0,C1,F5至FF从来没有出现。为了更好的可靠性，可以使用正则表达式来统计非法过长和替代值（可以查看W3 FAQ: Multilingual Forms上的验证UTF-8字符串的正则表达式）。
缺点
一份写得很差（并且与当前标准的版本不兼容）的UTF-8解析器可能会接受一些不同的伪UTF-8表示并将它们转换到相同的Unicode输出上。这为设计用于处理八位表示的校验例程提供了一种遗漏信息的方式。

使用UTF-8的原因
ASCII轉换成UCS-2，在編碼前插入一個0x0。用這些編碼，會含括一些控制符，比如 " 或 '/'，這在UNIX和一些C函數中，將會産生嚴重錯誤。因此可以肯定，UCS-2不適合作為Unicode的外部編碼，也因此誕生了UTF-8。

不利于正则表达式检索
正则表达式可以进行很多英文高级的模糊检索。例如，[a-h]表示a到h间所有字母。
同样GBK编码的中文也可以这样利用正则表达式，比如在只知道一个字的读音而不知道怎么写的情况下，也可用正则表达式检索，因为GBK编码是按读音排序的。只是UTF-8不是按读音排序的，所以会对正则表达式检索造成不利影响。但是這種使用方式並未考慮中文中的破音字，因此影響不大。Unicode是按部首排序的，因此在只知道一個字的部首而不知道如何發音的情况下，UTF-8 可用正则表达式检索而GBK不行。

其他
與其他 Unicode 編碼相比，特別是UTF-16，在 UTF-8 中 ASCII 字元佔用的空間只有一半，可是在一些字元的 UTF-8 編碼佔用的空間就要多出，特別是中文、日文和韓文（CJK）這樣的象形文字，所以具體因素因文檔而異，但不論哪種情況，差別都不可能很明顯。

utf8_unicode_ci 和 utf8_general_ci 区别
在 phpMyAdmin 中有多种字符集，其中 utf8_unicode_ci 和 utf8_general_ci 是最常用的，但是 utf8_general_ci 对某些语言的支持有一些小问题，如果可以接受，那最好使用 utf8_general_ci ，因为它速度快。否则，请使用较为精确的 utf8_unicode_ci，不过速度会慢一些。

2009年10月22日星期四

数据库基础--索引

From baidu baike

使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，例如 employee 表的姓（lname）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。
　　索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
　　索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。
　　在数据库关系图中，您可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。
　　注意并非所有的数据库都以相同的方式使用索引。作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。在多数情况下，索引用于数据检索的速度优势大大超过它的不足之处。但是，如果应用程序非常频繁地更新数据或磁盘空间有限，则可能需要限制索引的数量。
　　可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。
　　如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置判据，那么在这两列上创建多列索引将很有意义。
　　确定索引的有效性：
　　检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。
　　对新索引进行试验以检查它对运行查询性能的影响。
　　考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
　　检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。
　　检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型。
　　
建立索引的优点
　　1.大大加快数据的检索速度;
　　2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
　　3.加速表和表之间的连接;
　　4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
　　
索引的缺点
　　1.索引需要占物理空间。
　　2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

索引列
　　
　　可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。
　　
索引类型
　　根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。
　　提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。有关这些约束的更多信息，请参见主键约束和唯一约束。
　　唯一索引
　　唯一索引是不允许其中任何两行具有相同索引值的索引。
　　当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。
　　有关唯一索引的更多信息，请参见创建唯一索引。
　　主键索引
　　数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
　　在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。有关主键的更多信息，请参见定义主键。
　　聚集索引
　　在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。
　　如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

2009年9月24日星期四

单机体验MySQL Cluster

mkdir /var/lib/mysql-cluster/

download package: mysql-cluster-gpl-7.0.7-linux-i686-glibc23.tar.gz

tar zcf mysql-cluster-gpl-7.0.7-linux-i686-glibc23.tar.gz
mv mysql-cluster-gpl-7.0.7-linux-i686-glibc23 7_0_7
chown -R mysql.mysql 7_0_7
cd 7_0_7/data
mkdir data1 data2 data3
mkdir data1/mysql data1/test data2/mysql data2/test data3/mysql data3/test
cd ..
scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data1

scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data2

scripts/mysql_install_db --basedir=/var/lib/mysql-cluster/7_0_7 --datadir=/var/lib/mysql-cluster/7_0_7/data/data3

cd /var/lib/mysql-cluster/7_0_7
mkdir conf ; cd conf

::::::::::::::
config.ini
::::::::::::::
[ndbd default]
noofreplicas=2

[ndbd]
hostname=localhost
id=2

[ndbd]
hostname=localhost
id=3

[ndb_mgmd]
id = 1
hostname=localhost

[mysqld]
id=4
hostname=localhost

[mysqld]
id=5
hostname=localhost

[mysqld]
id=6
hostname=localhost

::::::::::::::
my.1.conf
::::::::::::::
[mysqld]
ndb-nodeid=4
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data1
basedir=/var/lib/mysql-cluster/7_0_7
port=3306
server-id=1
#log-bin
::::::::::::::
my.2.conf
::::::::::::::
[mysqld]
ndb-nodeid=5
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data2
basedir=/var/lib/mysql-cluster/7_0_7
port=3307
server-id=2
log-bin

::::::::::::::
my.3.conf
::::::::::::::
[mysqld]
ndb-nodeid=6
ndbcluster=true
datadir=/var/lib/mysql-cluster/7_0_7/data/data3
basedir=/var/lib/mysql-cluster/7_0_7
port=3308
server-id=3
log-bin

cd /var/lib/mysql-cluster/7_0_7
bin/ndb_mgmd --initial -f conf/config.ini --configdir=/var/lib/mysql-cluster/7_0_7/conf

bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from localhost)
id=3 (not connected, accepting connect from localhost)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost (mysql-5.1.35 ndb-7.0.7)

[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from localhost)
id=5 (not connected, accepting connect from localhost)
id=6 (not connected, accepting connect from localhost)

ndb_mgm> quit

bin/ndbd --initial -c localhost:1186
bin/ndbd --initial -c localhost:1186

bin/ndbd --initial -c localhost:1186
2009-08-31 00:19:36 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1
bin/ndbd --initial -c localhost:1186
2009-08-31 00:19:39 [ndbd] INFO -- Configuration fetched from 'localhost:1186', generation: 1

bin/mysqld --defaults-file=conf/my.1.conf --user=mysql &
bin/mysqld --defaults-file=conf/my.2.conf --user=mysql &
bin/mysqld --defaults-file=conf/my.3.conf --user=mysql &

[root@centOS02 conf]# netstat -ultn | grep 330
tcp 0 0 :::3306 :::* LISTEN
tcp 0 0 :::3307 :::* LISTEN
tcp 0 0 :::3308 :::* LISTEN

bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0, Master)
id=3 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)

[mysqld(API)] 3 node(s)
id=4 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
id=5 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
id=6 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)

ndb_mgm> quit
[root@centOS02 7_0_7]# bin/mysql -h localhost -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.35-ndb-7.0.7-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table assets (name varchar(30) not null primary key,
-> value int) engine=ndb;
090831 0:23:33 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/assets
090831 0:23:33 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
090831 0:23:33 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/assets
090831 0:23:33 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
Query OK, 0 rows affected (1.98 sec)

mysql> insert into assets values ('Car','1900');
Query OK, 1 row affected (0.16 sec)

mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| Car | 1900 |
+------+-------+
1 row in set (0.05 sec)

mysql> quit
Bye
[root@centOS02 7_0_7]# bin/mysql -h localhost -P 3307
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.35-ndb-7.0.7-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| Car | 1900 |
+------+-------+
1 row in set (0.00 sec)

mysql>

2009年8月26日星期三

oracle 用户密码为何不可以以数字开头

哪位了解详解的，来说说

xtrabackup - for innodb backup and restore

From Percona

Install:
$tar xzf xtrabackup-0.7.tar.gz
$cd xtrabackup-0.7
$./configure --innodb_file_io_threads=5 --innodb_file_per_table --innodb_force_recovery=4 --innodb_open_files=4096
$make
$cd innobase/xtrabackup/
$make

export PATH=$PATH:/usr/local/src/xtrabackup-0.7/innobase/xtrabackup

bakcup all databases:

innobackupex-1.5.1 --user=root --stream=tar /data/mysqlbak/ | gzip > /data/mysqlbak/bak`date +%F`_mysql.tar.gz

restore :

/etc/init.d/mysqld stop

tar zxvfi /data/mysqlbak/bak`date +%F`_mysql.tar.gz -C /data/mysql3306

/etc/init.d/mysqld start

verify

Note:
---> root@centOS01 (0.08) ^_^ # innobackupex-1.5.1 --help

Usage:
innobackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP] [--user=NAME]
[--password=WORD] [--port=PORT] [--socket=SOCKET] [--no-timestamp]
[--ibbackup=IBBACKUP-BINARY] [--slave-info] [--stream=tar]
[--defaults-file=MY.CNF]
[--databases=LIST] [--remote-host=HOSTNAME] BACKUP-ROOT-DIR
innobackup --apply-log [--use-memory=MB] [--uncompress] [--defaults-file=MY.CNF]
[--ibbackup=IBBACKUP-BINARY] BACKUP-DIR
innobackup --copy-back [--defaults-file=MY.CNF] BACKUP-DIR

--apply-log Prepare a backup for starting mysql server on the backup.
Expand InnoDB data files as specified in
backup-dir/backup-my.cnf, using backup-dir/ibbackup_logfile,
and create new log files as specified in
backup-dir/backup-my.cnf.

--copy-back Copy data and index files from backup directory back to
their original locations.

--remote-host=HOSTNAME
If this option is specified, backup files will be created
at the remote host by using ssh connection..

--include=REGEXP
This option is passed to the ibbackup child process.
It tells ibbackup to backup only those per-table data
files which match the given regular expression. For
each table with a per-table data file a string of the
form db_name.table_name is checked against the regular
expression. If the regular expression matches the
complete string db_name.table_name, the table is
included in the backup. The regular expression should
be of the POSIX 1003.2 "extended" form.
Try 'ibbackup --help' for more details on this option.

--databases=LIST
This option is used to specify the list of databases that
innobackup should backup. The list is of the form
"db_name[.table_name] db_name1[.table_name1] ...".
If this option is not specified all databases containing
MyISAM and InnoDB tables will be backed up.
Please make sure that --databases contains all of the
innodb databases & tables so that all of the innodb .frm
files are also backed up. In case the list is very long,
this can be specified in a file and the full path of the
file can be specified instead of the list.

--user=NAME This option is passed to the mysql child process.
It defines the user for database login if not current user.
Try 'mysql --help' for more details on this option.

--password=WORD
This option is passed to the mysql child process.
It defines the password to use when connecting to database.
Try 'mysql --help' for more details on this option.

--port=PORT This option is passed to the mysql child process.
It defines the port to use when connecting to local database
server with TCP/IP.
Try 'mysql --help' for more details on this option.

--slave-info
This option is useful when backing up a replication
slave server. It prints the binary log position and
name of the binary log file of the master server.
It also writes this information to the 'ibbackup_slave_info'
file as a 'CHANGE MASTER' command. A new slave for this
master can be set up by starting a slave server on this
backup and issuing a 'CHANGE MASTER' command with the binary
log position saved in the 'ibbackup_slave_info' file.

--socket=SOCKET
This option is passed to the mysql child process.
It defines the socket to use when connecting to local database
server with UNIX domain socket.
Try 'mysql --help' for more details on this option.

2009年7月24日星期五

how import an excel data into mysql safely

手上来了个任务，对一些表更新，更新相应的字段都存放在excel中，如果是直接更新相应的字段倒好办，直接拼SQL就可以了，却要关联到其他二个表

自然想到把文件内容导入到 database，再处理会相对快些
and do :open the excel file and save as csv file ,and load data local infile 'x' into table tbname fields terminated by ',' lines terminated by '\r\n' ignore 1 lines;
Unfortunately-^-^这个单词拼了半天，最后还是GG了，呵
sth error,there's many common in the last column

such as
1, 2003 ,44445, http:// ,here is ok
3 ,2002 ,45645, http:// ,here has, some ,

主要过程如下：
step 1:copy excel content into editplus or ue
Note: charset
step 2:change ' " ... 为 \' \"
step 3:tab -> ","
step 4: ^(reg)->(" $(reg)->"),
step 5: set names utf8;
drop table if exists tmp;
create table tmp
(
ChID int,
CID int,
word varchar(255),
index(ChID),
index(CID),
index(Kword)
);
select "start to insert word",now();
insert into tmp values

mysql < filename

END

如果你的文件比较规范，或是没有特殊字符，不妨考虑下第一种方法，另外注意一些项
如果table是myisam
mysql> show variables like 'con%insert';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | 1 |
+-------------------+-------+
1 row in set (0.00 sec)

如果table是innodb
mysql> show variables like '%checks%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
| innodb_checksums | ON |
| unique_checks | ON |
+--------------------+-------+
3 rows in set (0.00 sec)

good luck

mysql sql_mode date problem

今天在向数据仓库导数据(mysql -> oracle )时，发现有时间不正确的行，看了下，是由于mysql sql_mode的原因
mysql> use test
Database changed
mysql> SHOW VARIABLES LIKE "%sql_mode%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)

mysql> create table `date` ( id integer primary key,col2 date);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `date` VALUES (1, "2009-12-21");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `date` VALUES (3, "2009-2-31");
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col2' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from `date` where id=3;
+----+------------+
| id | col2 |
+----+------------+
| 3 | 0000-00-00 |
+----+------------+
1 row in set (0.05 sec)

mysql> SET SESSION sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `date` VALUES (4, "2009-2-31");
ERROR 1292 (22007): Incorrect date value: '2009-2-31' for column 'col2' at row 1
mysql> select * from `date` where id=4;
Empty set (0.00 sec)

mysql> SET SESSION sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `date` VALUES (4, "2009-2-31");
Query OK, 1 row affected (0.00 sec)

mysql> select * from `date` where id=4;
+----+------------+
| id | col2 |
+----+------------+
| 4 | 2009-02-31 |
+----+------------+
1 row in set (0.00 sec)

mysql> SELECT id ,col2 + INTERVAL 0 DAY FROM `date`;
+----+-----------------------+
| id | col2 + INTERVAL 0 DAY |
+----+-----------------------+
| 1 | 2009-12-21 |
| 2 | 2009-02-21 |
| 3 | NULL |
| 4 | 2009-03-03 |
+----+-----------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO `date` VALUES (5, "2008-2-31");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id ,col2 + INTERVAL 0 DAY FROM `date`;
+----+-----------------------+
| id | col2 + INTERVAL 0 DAY |
+----+-----------------------+
| 1 | 2009-12-21 |
| 2 | 2009-02-21 |
| 3 | NULL |
| 4 | 2009-03-03 |
| 5 | 2008-03-02 |
+----+-----------------------+
5 rows in set (0.00 sec)

2009年7月20日星期一

oracle领地

AskTom

Metalink

Oracle OTN Forum

2009年7月8日星期三

有趣的perl

引用《大骆驼》中那一个例子：jacob has four wives

将一个列表当成一个标量 []
$wife{"Jacob"} = ["Leah", "Rachel", "Bilhah", "Zilpah"];
$wife{"Jacob"}[0] = "Leah";
$wife{"Jacob"}[1] = "Rachel";
$wife{"Jacob"}[2] = "Bilhah";
$wife{"Jacob"}[3] = "Zilpah";

every wife has her own kids
将散列结构也当成一个标量 {}
$kids_of_wife{"Jacob"} = {
"Leah" => ["Reuben", "Simeon", "Levi", "Judah", "Issachar", "Zebulun"],
"Rachel" => ["Joseph", "Benjamin"],
"Bilhah" => ["Dan", "Naphtali"],
"Zilpah" => ["Gad", "Asher"],
};

$kids_of_wife{"Jacob"}{"Leah"}[0] = "Reuben";
$kids_of_wife{"Jacob"}{"Leah"}[1] = "Simeon";
$kids_of_wife{"Jacob"}{"Leah"}[2] = "Levi";
$kids_of_wife{"Jacob"}{"Leah"}[3] = "Judah";
$kids_of_wife{"Jacob"}{"Bilhah"}[1] = "Naphtali";
$kids_of_wife{"Jacob"}{"Zilpah"}[0] = "Gad";
$kids_of_wife{"Jacob"}{"Zilpah"}[1] = "Asher";

2009年7月7日星期二

Replication Basic

Setting up a server as master
The steps that are needed to configure a server to be a master are:
1. Add logbin and serverid options to my.cnf file
[mysqld]
serverid = 1
logbin = masterbin.log

2. Start server and connect a client to the server
./mysqld defaultsfile=master.cnf
./mysql defaultsfile=master.cnf uroot

3. Add a replication user
master> CREATE USER repl_user@localhost

4. Give the replication user REPLICATION SLAVE privileges
master> GRANT REPLICATION SLAVE ON *.*
> TO repl_user@localhost IDENTIFIED BY ‘xyzzy’;

Setting up a server as slave
To set up a server to act as a slave, the following steps have to be done:
1. Add configuration options for the relay log to the configuration file
[mysqld]
relaylogindex = slaverelaybin.index
relaylog = slaverelaybin

2. Direct the slave server to a master server
./mysqld defaultsfile=slave.cnf

3. Start the slave
./mysqld defaultsfile=slave.cnf
slave> CHANGE MASTER TO
> MASTER_HOST = 'localhost',
> MASTER_PORT = 12000,
> MASTER_USER = 'repl_user',
> MASTER_PASSWORD = 'xyzzy';
slave> START SLAVE;

4. Test that replication works
create a table and add some content on master,and select from slave

5. Check what hosts are connected to a master using SHOW SLAVE HOSTS

The binary log
how replication is done using the binary log.
1. Get a list of the binary logs on the master
2. Investigate(查看) contents of the binary log
3. What is the difference between the binary log formats?

Each binary log file consists of a sequence of event
If the binary log file active, there is no rotate event written last (yet),the header event indicates that this binary log file is not yet closed.
Whenever the binary logs are rotated, a rotate event is written last in the binary log, the binary
log is marked as complete in the header event, and a new binary log file is created and a
format description log event is written to it.

master> SHOW BINARY LOGS;

master> SHOW BINLOG EVENTS;
can be purged either by number or by date
mysql> PURGE BINARY LOGS TO 'masterbin.000002';
to purge all binary log files except the current one
mysql> PURGE BINARY LOGS BEFORE NOW();

mysql> set @id=100;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into stu values(@id);
Query OK, 1 row affected (0.06 sec)

mysql> select * from stu;
+------+
| id |
+------+
| 100 |
+------+
1 row in set (0.00 sec)

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
| mysql-bin.000006 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.26-rc-log, Binlog ver: 4 |
| mysql-bin.000006 | 106 | Query | 1 | 192 | use `etl`; create table stu(id int) |
| mysql-bin.000006 | 192 | User var | 1 | 235 | @`id`=100 |
| mysql-bin.000006 | 235 | Query | 1 | 324 | use `etl`; insert into stu values(@id) |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
4 rows in set (0.00 sec)

Using mysqlbinlog
../bin/mysqlbinlog mysql-bin.000006

Replication files on slave
The most important files on the slave are the relay log files,The relay log is written by the slave I/O thread,and read by the slave SQL thread.
containing information about the progress of replication;
the files are master.info, and relaylog.info.
master.info:hold information about the master being replicated from and how much of the master binary log that has been replicated.
relaylog.info:keep track of how much of the binary log has been applied.
In this file, the figures give what position in the relay log that has been applied, and
what position and the master binary log that it corresponds to.
slaverelaybin.000001 # Relay log name
856 # Relay log pos
masterbin.00001 # Master log name
802 # Master log pos

Basic replication use scenarios
1. Redundancy, or high-availability
2. Load balancing reads
3. Offline processing to avoid stopping the master

Using a slave for
taking a backup
reporting or analysis
read scale-out

Adding new slaves
we clone an existing slave by taking a backup of the slave, restore the
backup on the new slave, and then start the slave replicating from the position that
corresponds to the position in the binary log that the backup corresponds to.

How to clone a slave
decide on a point in the master binary log where we want to stop the slave and use the START SLAVE UNTIL command to have the slave stop exactly at a specified position.
The easiest way of getting a position in the master binary log
FLUSH TABLES WITH READ LOCK on the master to flush all tables and add a
read lock to the tables of the database,After we have that position, we can use the START
SLAVE UNTIL to have the slave stop at the right position..
The steps that has to be done to get a backup and a binlog position for what the backup
corresponds to are:
1. Stop slave
slave> STOP SLAVE;
2. Flush tables on master with a read lock
master> FLUSH TABLES WITH READ LOCK;
3. Get master position
master> SHOW MASTER STATUS;
+++++
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+++++
| masterbin.000003 | 742 | | |
+++++
4. Unlock tables to let the master start running again
master> UNLOCK TABLES;
5. Start the slave to run until the binlog position from point 3
slave> START SLAVE UNTIL
> MASTER_LOG_FILE='masterbin.000003',
> MASTER_LOG_POS=742;
Query OK, 0 rows affected (0.01 sec)
6. Wait for the slave to stop
slave> DO MASTER_POS_WAIT('masterbin.000003', 742);
7. Take a backup of the slave and take a note of the binary log file and position that it corresponds to
$ cat >slave/data/binlog_pos.dat
masterbin.000003
742
$ tar cxf slavebackup.tar.gz slave/data
8. Start the slave again
slave> START SLAVE;

Creating the new slave
$ ./mysqld defaultsfile=slave2.cnf
$ tar zxf slavebackup.tar.gz slave2/data
$ cat slave2/data/binlog_pos.dat
$ ./mysql defaultsfile=slave1.cnf uroot
slave2> CHANGE MASTER TO
> MASTER_HOST = 127.0.0.1,
> MASTER_PORT = 12000,
> MASTER_USER = 'repl_user',
> MASTER_PASSWORD = 'xyzzy',
> MASTER_LOG_FILE = 'masterbin.000003',
> MASTER_LOG_POS = 742;
slave2> START SLAVE;

SHOW SLAVE STATUS

Replication for high-availability
Dual masters
[mysqld]
...
log-slave-updates

A commonly used but incorrect way to stop the slave
we assume that the master is under heavy use, and there are writes going on all the time

save_master_pos;
sync_with_master;
slave> DO MASTER_POS_WAIT('masterbin.000001', 440);

master> SHOW MASTER STATUS;
slave> DO MASTER_POS_WAIT(...);
slave> STOP SLAVE;
$ tar zcf slavebackup.tar.gz slave/data
slave> START SLAVE;
The problem is that the slave keeps running after the wait, which means
that there potentially can be several inserts done into the binary log between that statement
and stopping the slave.

正在读一本书 Head First SQL

SQL的经典著作，之前没用心读过，现在重新拜读，全书共十二章

新疆事件--让人心寒呀

http://www.tagstory.com/video/video_post.aspx?media_id=V000338241
一段视频，暴力事件！

修改mysql proc definer

Database: mysql
Table: proc

2009年7月1日星期三

MySQL Query Profiler

对于一个SQL执行之后花费的CPU等资源可以使用系统自带的profiler来查看（>=5.0.37）
mysql> show profiles;
Empty set (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.01 sec)

mysql> set profiling=on;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ETL |
| mysql |
| test |
+--------------------+
4 rows in set (0.07 sec)

mysql> use ETL
Database changed
mysql> show tables;
+---------------+
| Tables_in_ETL |
+---------------+
| Customer |
| Mer |
+---------------+
2 rows in set (0.00 sec)

mysql> select count(*) from Mer;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.02 sec)

mysql> show profiles;
+----------+------------+--------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------+
| 0 | 0.01201500 | set profiling=on |
| 1 | 0.07709700 | show databases |
| 2 | 0.00003500 | SELECT DATABASE() |
| 3 | 0.00318600 | show tables |
| 4 | 0.01772300 | select count(*) from Mer |
+----------+------------+--------------------------+
5 rows in set (0.01 sec)

mysql> show profile for query 4;
+--------------------+------------+
| Status | Duration |
+--------------------+------------+
| Opening tables | 0.00064400 |
| System lock | 0.01148000 |
| Table lock | 0.00050300 |
| init | 0.00002400 |
| optimizing | 0.00084600 |
| executing | 0.00070300 |
| end | 0.00179700 |
| query end | 0.00002100 |
| freeing items | 0.00001300 |
| closing tables | 0.00100200 |
| logging slow query | 0.00067500 |
| cleaning up | 0.00001500 |
+--------------------+------------+
12 rows in set (0.01 sec)

如何获得DML语句的执行计划呢，？

为了启动的纪念 perl 正则

有时想不起来，记一下：（源自perl by example edition 4）

Character Class: Single Characters and Digits
.	Matches any character except a newline
[a–z0–9]	Matches any single character in set
[^a–z0–9]	Matches any single character not in set
\d	Matches one digit
\D	Matches a nondigit, same as [^0–9]
\w	Matches an alphanumeric (word) character
\W	Matches a nonalphanumeric (nonword) character
Character Class: Whitespace Characters
\s	Matches a whitespace character, such as spaces, tabs, and newlines
\S	Matches nonwhitespace character
\n	Matches a newline
\r	Matches a return
\t	Matches a tab
\f	Matches a form feed
\b	Matches a backspace
\0	Matches a null character
Character Class: Anchored Characters
\b	Matches a word boundary (when not inside [ ])
\B	Matches a nonword boundary
^	Matches to beginning of line
$	Matches to end of line
\A	Matches the beginning of the string only
\Z	Matches the end of the string or line
\z	Matches the end of string only
\G	Matches where previous m//g left off
Character Class: Repeated Characters
x?	Matches 0 or 1 x
x*	Matches 0 or more occurrences of x
x+	Matches 1 or more occurrences of x
(xyz)+	Matches 1 or more patterns of xyz
x{m,n}	Matches at least m occurrences of x and no more than n occurrences of x
Character Class: Alternative Characters
was\|were\|will	Matches one of was, were, or will
Character Class: Remembered Characters
(string)	Used for backreferencing
\1 or $1	Matches first set of parentheses
\2 or $2	Matches second set of parentheses
\3 or $3	Matches third set of parentheses
Character Class: Miscellaneous Characters
\12	Matches that octal value, up to \377
\x811	Matches that hex value
\cX	Matches that control character; e.g., \cC is -C and \cV is -V
\e	Matches the ASCII ESC character, not backslash
\E	Marks the end of changing case with \U, \L, or \Q
\l	Lowercase the next character only
\L	Lowercase characters until the end of the string or until \E
\N	Matches that named character; e.g., \N{greek:Beta}
\p{PROPERTY}	Matches any character with the named property; e.g., \p{IsAlpha}/
\P{PROPERTY}	Matches any character without the named property
\Q	Quote metacharacters until \E
\u	Titlecase next character only
\U	Uppercase until \E
\x{NUMBER}	Matches Unicode NUMBER given in hexadecimal
\X	Matches Unicode "combining character sequence" string
\[	Matches that metacharacter
\\	Matches a backslash

slave忽略复制错误

MySQL Master-Master Replication Manager (转)

转Tsung's Blog

什麼是 MySQL Master-Master Replication Manager(MMM)?

MMM (MySQL Master-Master Replication Manager) 是整合 Script 做監控/管理 MySQL Master-Master replication cluster 的設定配置.
Master-Master 的意思不是同時有兩台 Master 應付寫入的動作, 而是要做備援, 若 Master 死掉, 可於最短的時間內(幾秒內), 將備援的 Master 替換上線, 而這台備援的 Master, 平常也可以當 Slave (Reader).
當 Slave 的 Replication Failed 時, 會自動讓此 Slave 停止提供服務, 避免不同步的狀況發生.
MMM 可解決 Master 死掉時, 需要停機或停止所有寫入的問題.

MMM 的特色

上述的事情, 只要在程式判斷 Master 死掉, 之後就永遠寫 Slave 等, 就可以做到(Master 回復時, 再手動去做切換), 但是 MMM 能夠多處理哪些東西呢? MMM 的設定/原理大概是怎麼樣呢?

MMM 的基本設定是 Master-Master (就是兩台 MySQL Server 互相設對方為自己的 Master, 自己是對方的 Slave), 所以在任何一台正常回復, 會將資料自動透過 Replication 同步.
MMM 能夠在 Master(db1) 死掉時, 在最短的時間, 自動切換到另一台當 Master(db2). 當 Master(db1) 回復時, 會直接當現在 Master(db2) 的 Slave, 不過不會直接上線, 要等資料 sync 同步後, MMM 才會允許手動設定上線.
另可參考: Typical Use Cases 使用情境(常見使用架構)

MMM 的運作原理

MMM 主要做法是 Master(db1), Master(db2) 設定固定 IP, 然後 Monitor 也設定一個固定 IP, 再來 Monitor 的設定再加上兩個 IP(Writer, Reader), 程式讀取/寫入都是透過 Monitor 另外設的這兩個 IP, 當任何一台機器死掉(不管是 Reader/Writer), Monitor 會自動將 IP 設
到另一台機器上.

註: 透過 ARP(Address Resolution Protocol) 和 iproute 將 IP 指到另一台.

設定依正常設定, 兩台 MySQL Server 各設自己原本的 IP, Monitor 會依照設定檔另外再綁上各自讀寫用的 IP, 跟之前主要的差異是, 兩台
MySQL Server 要多跑 mmm_agent daemon, Monitor 要跑 mmm_mon daemon.

MMM 是透過下述 check MySQL servers 是否活著:

IP connectivity (fping)
MySQL connectivity (mysql connect and SELECT NOW())
MySQL IO and SQL threads status (SHOW SLAVE STATUS)
MySQL replication backlog - seconds behind master (SHOW SLAVE STATUS)

MMM 的官方資源

MMM 官方網站說明: MySQL Master-Master Replication Manager
MMM 下載: mysql-master-master - Google Code
MMM 問答討論區: MySQL Multi Master Manager Development | Google Groups

MMM 的更多介紹、說明

MySQL Master-Master replication manager released
Master-Master Replication Example using MMM
mysql-master-master wiki list- Google Code - 官方 Wiki 的說明文件
Introduction - mysql-master-master
MMM Documentation table of contents
Using MMM to ALTER huge tables

此文假設已經玩過 MySQL Replication, 所以 Replication 設定的部份不多做解釋, 若有不懂的, 可以參考: MySQL 設定 Replication (Master - Slave)

MySQL Master-Master Replication Manager 前置作業

MySQL Master-Master 的架構, 需要準備的資訊如下:

機器 3台以上(最少3台): 2台做 MySQL Server, 1台做 Monitor(監控機器可與 Apache 等共用即可).
IP 5個以上(2N+1): MySQL Server 有幾台, 需要的 IP 是 MySQL Server 台數 x 2, 再加上 Monitor 要一個 IP.

MMM 前置作業手上該有的資訊:

db-1 192.168.1.181
db-2 192.168.1.182
db-mon 192.168.1.183
db-r 192.168.1.184 # 這個不用設進 db-1、db-2, db-mon 會自動設給它.(但需先決定db-1, db-2 哪台要先當 reader 或 writer)
db-w 192.168.1.185 # 這個不用設進 db-1、db-2, db-mon 會自動設給它.(但需先決定db-1, db-2 哪台要先當 reader 或 writer)
MySQL Replication 要設定的帳號: replication、密碼: slave.
Monitor 存取 MySQL 需要的帳號: rep_monitor、密碼: RepMonitor.
MySQL Agent 要設定的帳號: rep_agent、密碼: RepAgent.

開始之前, 除了上述該有的資訊外, 手上應該要有 3台機器, 3台機器設定分別如下:

db-1 192.168.1.181
db-2 192.168.1.182
db-mon 192.168.1.183
若有要用到 mmm_clone, mmm_backup, mmm_restore 等功能, 需要 LVM 支援, 除此之外, 沒有 LVM 還是能正常監控/轉換等, 下述環境也是在沒有 LVM 的狀況下測試的.

MySQL Master-Master Replication Manager 環境建置、架設

下述設定參考自: Master-Master Replication Example using MMM (設定檔參考: Configuration Examples)

註: 下述環境、設定檔位置是以 Debian Lenny 為主.

建置環境步驟

建置環境步驟主要如下述:

db-1, db-2 安裝 mysql-server
db-1, db-2 互設對方為 Master, 自己是對方的 Slave
抓取 mmm 的檔案, 裝需要的 Package 後, 執行 install.pl
設定 mmm_agent.conf 後, 於 db-1, db-2 跑 mmm_agent
db-mon 安裝需要的 Package 後, 執行 install.pl
設定 mmm_mon.conf 後, 於 db-mon 跑 mmm_mon
將 db-1, db-2 設定上線 mmm_control set_online db1, mmm_control set_online db2
測試 mmm_control show 是否正常, 將 /usr/local/mmm/scripts/init.d, logrotate.d 設定到 /etc 去.
使用 rcconf 設定開啟啟動即可.

實際執行步驟 - db-, db-2 互設 Replication, db-mon 建置

db-1 192.168.1.181

apt-get install mysql-server
vim /etc/mysql/my.cnf
# bind-address = 127.0.0.1
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
/etc/init.d/mysql restart

做完此步驟, 請跳到 db-2 也先把此步驟做完.(順便抄下 db-2 的 show master status)

db-2 做完上述步驟後, 再繼續下面:

mysql -u root
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'192.168.1.183' identified by 'RepMonitor';
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.181' identified by 'RepAgent';
mysql> GRANT replication slave on *.* to 'replication'@'%' identified by 'slave';
mysql> change master to master_host='192.168.1.182', master_port=3306, master_user='replication', master_password='slave'; # 文件上寫的做法
mysql> change master to master_host='192.168.1.182', master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004', master_log_pos=98; # 我喜歡保守點的做法.(抓 db-2 mysql> show master status 資料)
/etc/init.d/mysql restart
mysql -u root
mysql> slave start;
mysql> show slave status \G

這樣子應該 Replication 已經設定完成, Master 是 db2, 自己是 Slave, 再下來就是架設 MMM 囉~

架設 MMM 步驟如下:

apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
apt-get install iproute
wget http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2
tar xvf mmm-1.0.tar.bz2
cd mmm-1.0
./install.pl
cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf
vim /usr/local/mmm/etc/mmm_agent.conf # 下述只將修改部份列出
cluster_interface eth0

# Define current server id
this db1
mode master

# For masters
peer db2

# Cluster hosts addresses and access params
host db1
ip 192.168.1.181
port 3306
user rep_agent
password RepAgent

host db2
ip 192.168.1.182
port 3306
user rep_agent
password RepAgent
mmmd_agent # 執行 agent, 或者 /usr/local/mmm/scripts/init.d/mmm_agent start
ps aux | grep mmmd
root 16115 0.0 0.0 41936 6904 ? S 15:02 0:00 perl /usr/local/sbin/mmmd_agent
修改完成的設定檔參考可下載: mmm_agent.conf
再來就繼續下述 db-2 的設定囉~

db-2 192.168.1.182

apt-get install mysql-server
vim /etc/mysql/my.cnf
# bind-address = 127.0.0.1
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
/etc/init.d/mysql restart

做完此步驟, 再回 db-1 繼續. (順便抄下 db-1 的 show master status)

mysql -u root
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'192.168.1.183' identified by 'RepMonitor';
mysql> GRANT replication slave on *.* to 'replication'@'%' identified by 'slave';
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.182' identified by 'RepAgent';
mysql> change master to master_host='192.168.1.181', master_port=3306, master_user='replication', master_password='slave'; # 文件上寫的做法
mysql> change master to master_host='192.168.1.181', master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004', master_log_pos=98; # 我喜歡保守點的做法.(抓 db-1 mysql> show master status 資料)
/etc/init.d/mysql restart
mysql -u root
mysql> slave start;
mysql> show slave status \G

這樣子應該 Replication 已經設定完成, Master 是 db1, 自己是 Slave, 再下來就是架設 MMM 囉~(下述步驟與上面一致, 只有設定檔有差異而已)

架設 MMM 步驟如下:

apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
apt-get install iproute
wget http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2
tar xvf mmm-1.0.tar.bz2
cd mmm-1.0
./install.pl
cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf
vim /usr/local/mmm/etc/mmm_agent.conf
cluster_interface eth0

# Define current server id
this db2
mode master

# For masters
peer db1

# Cluster hosts addresses and access params
host db1
ip 192.168.1.181
port 3306
user rep_agent
password RepAgent

host db2
ip 192.168.1.182
port 3306
user rep_agent
password RepAgent
mmmd_agent # 執行 agent, 或者 /usr/local/mmm/scripts/init.d/mmm_agent start
ps aux | grep mmmd
root 8837 0.0 0.0 41936 6904 ? S 15:02 0:00 perl /usr/local/sbin/mmmd_agent
修改完成的設定檔參考可下載: mmm_agent.conf

db-mon 192.168.1.183

apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
apt-get install iproute
apt-get install subversion # 目前 1.0 stable 的 mmm_mon 程式有 bug, 所以需要直接 checkout trunk 的來用
svn checkout http://mysql-master-master.googlecode.com/svn/trunk/ mysql-master-master-read-only
cd mysql-master-master-read-only/
./install.pl
cp /usr/local/mmm/etc/examples/mmm_mon.conf.example /usr/local/mmm/etc/mmm_mon.conf
vim /usr/local/mmm/etc/mmm_mon.conf
email root@localhost # 修改成有狀況要通知的 Email.

host db1
ip 192.168.1.181
port 3306
user rep_monitor
password RepMonitor
mode master
peer db2

host db2
ip 192.168.1.182
port 3306
user rep_monitor
password RepMonitor
mode master
peer db1

# Mysql Reader role
role reader
mode balanced
servers db1, db2
ip 192.168.1.185, 192.168.1.184

# Mysql Writer role
role writer
mode exclusive
servers db1, db2
ip 192.168.1.185
/usr/local/mmm/scripts/init.d/mmm_mon start
mmm_control show
mmm_control set_online db1 # 讓 db1 上線
mmm_control set_online db2 # 讓 db2 上線
mmm_control show # 看到下述就成功了~
Config file: mmm_mon.conf
Daemon is running!
Servers status:
db1(192.168.1.181): master/ONLINE. Roles: reader(192.168.1.185;), writer(192.168.1.185;)
db2(192.168.1.182): master/ONLINE. Roles: reader(192.168.1.184;)
修改完成的設定檔參考可下載: mmm_mon.conf

設定 log rotate

下面這些步驟分別在 db1, db2, mon 設定即可.

cp /usr/local/mmm/scripts/logrotate.d/mmm /etc/logrotate.d/
vim /etc/logrotate.d/mmm
/opt/mmm/var/*.log { 修改成 /usr/local/mmm/var/*.log
olddir /opt/mmm/var/old 修改成 olddir /usr/local/mmm/var/old

設定開機自動啟動

db1, db2 設定開機自動啟動

cp /usr/local/mmm/scripts/init.d/mmm_agent /etc/init.d/
apt-get install rcconf
rcconf
選取 mmm_agent 即可

mon 設定開機自動啟動

cp /usr/local/mmm/scripts/init.d/mmm_mon /etc/init.d/
apt-get install rcconf
rcconf
選取 mmm_mon 即可

再下來就只要會 mmm_control show, mmm_control set_online, mmm_control set_offline 即可.

當機/重開機的 SOP

若有重開機等狀況, 檢查步驟:

mysql -u root # 看 MySQL 是否有啟動, 若沒啟動 /etc/init.d/mysql start
ps aux | grep mmm # 看 mmm_agent 或 mmm_mon 是否有啟動, 若沒啟動 /etc/init.d/mmm_[agent|mon] start
再來在 mon 的機器下: mmm_control show, 若沒上線, 發現已經在 AWAITING_RECOVERY 狀態, 就可以 set_online 讓他上線囉~

測試

db1, db2 mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'mmm'@'%' IDENTIFIED BY 'mmm_password';
db1, db2 mysql> FLUSH PRIVILEGES;
寫程式去對 192.168.1.184, 192.168.1.185 做寫入/讀取的動作, 並試著重開等看看反應~
註: 目前測試狀況, 機器死掉時, 在 2秒內就會自動切換過去. :)

MMM LVM 設定

因為機器安裝時沒設 LVM, 所以沒辦法測試, 不過需要安裝的 Package 和設定大概如下:

apt-get install lvm2 dmsetup mdadm reiserfsprogs xfsprogs
MMM LVM 設定範例: mmm_lvm.conf # 此設定範例需要另外建立 /backup, /mmm_snapshot 目錄
設 LVM 可以直接在 db2 將 db1 的資料 clone 過來, ex: db2# mmm_clone --host db1 --clone-mode master-master # db2 的 MySQL 會幫你停掉, 然後再 scp db1 data 過來.

MMM 的問與答

將其中一台 MySQL 關掉, Mon 該如何動作?

Mon 會自動把 IP 轉到另外一台, 等 MySQL 回復後自然會還原.
mmm_control show # 若沒有自動還原
mmm_control set_online db1 # 將沒有還原(假設是 db1)那台的設定上線.

先將 mmmd_agent kill 掉, 再把 MySQL kill 掉, 然後再把 MySQL start, Mon 會如何動作?

因為 Mon 是自動去連 MySQL 的, 所以不會因為 mmmd_agent 砍掉而有影響, MySQL kill 掉時, Mon 一樣會知道那台是掛掉的,(master/HARD_OFFLINE)
此時於另外一台 MySQL 寫的資料, 於此台 MySQL start 時, 會自動寫入, 不過 Mon 無法將這台回復的機器登記入可以讀寫,(master/AWAITING_RECOVERY)
因為這台的 mmmd_agent 還沒起來, 沒辦法知道是否已經跟上 replication 沒.
所以要先啟動 mmmd_agent, Mon 再 mmm_control set_online db1.

Mon 的 mmmd_mon kill 掉, 對其它有沒有影響?

沒有影響, /usr/local/mmm/scripts/init.d/mmm_mon start 即可.

Master 測試重新開機 shutdown -n -f -r now

重新開機完後的處理步驟:

於重開機的機器(Master)
1. /etc/init.d/mysql start # 這種重開法, MySQL 不一定能正常啟動.
2. ps aux | grep mmm # 看 mmmd_agent 是否有正常啟動.
於 Mon 的機器
1. mmm_control show # 會發現啟動後, master 的狀態會由 HARD_OFFLINE -> AWAITING_RECOVERY
2. mmm_control set_online db1 # 讓它啟動接受服務.

Mon 測試重新開機 shutdown -n -f -r now

重新開機完後的處理步驟:

於 Mon 的機器

ps aux | grep mmm # 看 mmm_mon 是否有正常啟動, 沒有正常啟動就執行 /etc/init.d/mmm_mon start
mmm_control show # 看監控的機器是否都還正常

Master 死掉, 然後造成 Reader 和 Writer 互換, 若 Mon 重新啟動會回復原始設定嗎? 若不會, 想要互換要怎麼換?

不會回復原始設定, 用的是最後跑的狀態.
想要將設定的互換, 步驟如下:
1. master mysql restart
2. slave mysql> start slave;
3. 這樣子就會互換了 (因為 Master MySQL restart, Slave 那台 replication 會停掉, 所以 Mon 會將所有 IP 都指向 Master, 再將 Slave replication 搞定, 就會再把 reader 指回 slave 了)
設定互換的另一種方法, 就是把另外一台先設 mmm_control set_offline, 再 set_online 即可.
設定互換的另一種方法, 使用 mmm_control move_role writer db1, 設定設回 Writer 去.(不過此方法我測試似乎不會動)

寫入, 讀取應該要用的IP, 要用本機IP 還是設定的 db-r, db-w IP?

寫入/讀取要用的 IP, 是要用設定的 db-r, db-w 的IP, 設定的 db-r 的 IP(只要 Writer 沒有指到此 IP), INSERT/UPDATE/DELETE 的動作會把你擋掉, 會出現 ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement 的錯誤訊息.(不過 CREATE/DROP Table 等動作不會被擋掉.)

在 Master 上的 mmm/bin/* 有很多檔案, 要怎麼讓他可以使用?

要先讓它有存取 MySQL 的權限, 於 mysql> 就這樣子下: (帳號/密碼/IP 請自行修改)

mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.181' identified by 'RepAgent';
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.182' identified by 'RepAgent';
mysql> FLUSH PRIVILEGES;
不過 mysql_deny_write 並不會讓它不能寫入.(此程式會幫你執行: set global read_only=1, 不過 CREATE/DROP 還是可以使用. XD)

想要一台 Mon 管理多台 agent 要怎麼設定?

將 /usr/local/mmm/etc/mmm_mon.conf 改名成 mmm_mon_c1.conf
就可以 mmm_control @c1 show 來管理 c1 的 Cluster, 就可由此來做其它 Cluster 的 Mon 設定囉~

mmm_control 哪些狀態可以將機器 set_online?

master/AWAITING_RECOVERY 或 master/ADMIN_OFFLINE 的狀態.

mmm_contorl 全部狀態有哪些?

mmm_control show 會看到機器有下述的狀態: (原文可見: HowToUse - mysql-master-master)

master/ONLINE - 此機器沒有任何問題, 正常執行.
master/AWAITING_RECOVERY - 此機器的 MySQL 可能有重開, 並沒有被設定上線, 於 replication 跟上進度後(若 replication 有損壞的狀況, 需自行修復), 使用 mmm_control @C1 set_online 機器名稱的命令讓他上線.
master/ADMIN_OFFLINE - 管理者設定讓這台機器下線.
master/HARD_OFFLINE - 此 MySQL 是沒辦法 ping 到, 可能是網路問題或者機器當掉或重開等等.
master/REPLICATION_FAIL - MySQL 的 replication error. 遇到先修復 replication 的狀況, 才能讓它再上線.
master/REPLICATION_DELAY - MySQL 死掉時, 可能 MMM 自動移動 reader 的角色到 Master 去, 但是 Reader 起來後, Replication 還根不上進度, 需要等它跟上才能恢復上線.

於 lvm 的環境下, 如何快速建立一台 Master (Master-Master) 架構?

LVM 安裝: apt-get install lvm2 dmsetup mdadm reiserfsprogs xfsprogs # LVM 詳細安裝步驟可參考: A Beginner's Guide To LVM
設定好 /usr/local/mmm/etc/mmm_lvm.conf
就可以於 db2# mmm_clone --host db1 --clone-mode master-master (此指令會將 db2 的 MySQL 停掉, 再 scp 過來)
註: 不過這個我也沒試過.

讀取都是透過 Mon 指定的那兩個 Writer/Reader 的 IP, 但是 Mon 死掉的話, 會不會就無法寫入/讀取?

不會, 因為 Mon 是透過 Address Resolution Protocol (ARP), 而 ARP 在每台機器是都會有暫存區, 存: IP <-對應-> Mac Address 的資料, 所以 Mon 死掉在 ARP Cache 過期前, 都不會有影響.

多主一从架构实现

Tungsten replicator

基于JAVA，支持异构DB之间的复制
有机会试一下

2009年6月29日星期一

The v2 patch 之 InnoDB

Innodb_dict_size - number of bytes used for the InnoDB dictionary
Innodb_have_atomic_builtins - indicates whether InnoDB uses atomic memory operations in place of pthreads synchronization functions
Innodb_heap_enabled - indicates whether the InnoDB malloc heap was enabled -- see Bug38531
Innodb_long_lock_wait - set when there is a long lock wait on an internal lock. These usually indicate an InnoDB bug. They also occur because the adaptive hash latch is not always released when it should be (such as during an external sort).
Innodb_long_lock_waits - incremented once for each internal long lock wait
Innodb_os_read_requests - from SHOW INNODB STATUS
Innodb_os_write_requests - from SHOW INNODB STATUS
Innodb_os_pages_read - from SHOW INNODB STATUS
Innodb_os_pages_written - from SHOW INNODB STATUS
Innodb_os_read_time - from SHOW INNODB STATUS
Innodb_os_write_time - from SHOW INNODB STATUS
Innodb_time_per_read - milliseconds per read
Innodb_time_per_write - milliseconds per write
Innodb_deadlocks - application deadlocks, detected automatically
Innodb_transaction_count - from SHOW INNODB STATUS
Innodb_transaction_purge_count - from SHOW INNODB STATUS
Innodb_transaction_purge_lag - count of work to be done by the InnoDB purge thread, see InnodbLag
Innodb_active_transactions - from SHOW INNODB STATUS
Innodb_summed_transaction_age - from SHOW INNODB STATUS
Innodb_longest_transaction_age - from SHOW INNODB STATUS
Innodb_lock_wait_timeouts - count of lock wait timeouts
Innodb_lock_waiters - from SHOW INNODB STATUS
Innodb_summed_lock_wait_time - from SHOW INNODB STATUS
Innodb_longest_lock_wait - from SHOW INNODB STATUS
Innodb_pending_normal_aio_reads - from SHOW INNODB STATUS
Innodb_pending_normal_aio_writes - from SHOW INNODB STATUS
Innodb_pending_ibuf_aio_reads - from SHOW INNODB STATUS
Innodb_pending_log_ios - from SHOW INNODB STATUS
Innodb_pending_sync_ios - from SHOW INNODB STATUS
Innodb_os_reads - from SHOW INNODB STATUS
Innodb_os_writes - from SHOW INNODB STATUS
Innodb_os_fsyncs - from SHOW INNODB STATUS
Innodb_ibuf_inserts - from SHOW INNODB STATUS
Innodb_ibuf_size - counts work to be done by the insert buffer, see InnodbLag
Innodb_ibuf_merged_recs - from SHOW INNODB STATUS
Innodb_ibuf_merges - from SHOW INNODB STATUS
Innodb_log_ios_done - from SHOW INNODB STATUS
Innodb_buffer_pool_hit_rate - from SHOW INNODB STATUS

mysql> show variables like 'INNODB%';
+----------------------------------+-------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------------------------------+
| innodb_additional_mem_pool_size | 2097152 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 16777216 |
| innodb_checksums | ON |
| innodb_clear_replication_status | OFF |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_confirm_checksum_on_write | OFF |
| innodb_crash_if_init_fails | OFF |
| innodb_data_file_path | ibdata1:2000M;ibdata2:2000M;ibdata3:2000M:ibdata4:100M:autoextend |
| innodb_data_home_dir | /usr/local/mysql5037/var/ |
| innodb_disallow_writes | OFF |
| innodb_doublewrite | ON |
| innodb_extra_dirty_writes | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 100 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_read_io_threads | 1 |
| innodb_write_io_threads | 1 |
| innodb_max_merged_io | 64 |
| innodb_status_update_interval | 0 |
+----------------------------------+-------------------------------------------------------------------+
44 rows in set (0.00 sec)

The v2 patch 之 SemiSyncReplication

引自:http://code.google.com/p/google-mysql-tools/wiki/SemiSyncReplicationDesign
半同步复制:
原理图

概要：
改变了传统的复制协议（tcp/ip）
改变了commit的方式:至少一个副本已经确认得到更改后进行commit，基本可以保证数据的一致性，主从服务器都要设定,目前仅对innodb有效
parameters:

rpl_semi_sync_enabled configures a master to use semi-sync replication.
rpl_semi_sync_slave_enabled configures a slave to use semi-sync replication. The IO thread must be restarted for this to take effect.
rpl_semi_sync_timeout is the timeout in milliseconds for the master

mysql> show variables like 'rpl_semi_sync_enabled';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| rpl_semi_sync_enabled | 0 |
+-----------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'rpl_semi_sync_slave_enabled';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| rpl_semi_sync_slave_enabled | 0 |
+-----------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'rpl_semi_sync_timeout';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| rpl_semi_sync_timeout | 10 |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'rpl%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Rpl_semi_sync_clients | 0 |
| Rpl_semi_sync_net_avg_wait_time(us) | 0 |
| Rpl_semi_sync_net_wait_time | 0 |
| Rpl_semi_sync_net_waits | 0 |
| Rpl_semi_sync_no_times | 0 |
| Rpl_semi_sync_no_tx | 0 |
| Rpl_semi_sync_status | 0 |
| Rpl_semi_sync_slave_status | 0 |
| Rpl_semi_sync_timefunc_failures | 0 |
| Rpl_semi_sync_tx_avg_wait_time(us) | 0 |
| Rpl_semi_sync_tx_wait_time | 0 |
| Rpl_semi_sync_tx_waits | 0 |
| Rpl_semi_sync_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_wait_sessions | 0 |
| Rpl_semi_sync_yes_tx | 0 |
| Rpl_status | NULL |
| Rpl_transaction_support | OFF |
+-------------------------------------+-------+
17 rows in set (0.00 sec)

Replication_fail_io_connections - on a slave, number of times the IO thread has disconnected from the master because of an error
Replication_total_io_connections - number of connections made by the IO thread to the master
Replication_last_event_buffered - on a slave, time when last replication event received
Replication_last_event_done - on a slave, time when last replication event replayed

The v2 patch 之 SqlChanges

杀死idle线程

KILL IF_IDLE can be used to kill a connection but only if it is idle.

MAX_QUERIES_PER_MINUTE can be used in place of MAX_QUERIES_PER_HOUR. This version of MySQL enforces query limits per minute rather than per hour and the value stored in the MySQL privilege table is the rate per minute.

CREATE MAPPED USER 'foo' ROLE 'bar' and
DROP MAPPED USER 'foo' support mapped users. See MysqlRoles for more details.

SHOW PROCESSLIST WITH ROLES and
SHOW USER_STATISTICS WITH ROLES use the role name rather than the user name in results..

有用的用户表状态监控 UserTableMonitoring

SHOW USER_STATISTICS
SHOW TABLE_STATISTICS
SHOW INDEX_STATISTICS
SHOW CLIENT_STATISTICS
FLUSH TABLE_STATISTICS
FLUSH INDEX_STATISTICS
FLUSH CLIENT_STATISTICS

MysqlRateLimiting
MAKE USER 'foo' DELAYED 1000
MAKE CLIENT '10.0.0.1' DELAYED 2000
SHOW DELAYED USER
SHOW DELAYED CLIENT

SHOW INNODB LOCKS provides more details on InnoDB lock holders and waiters.

FLUSH SLOW QUERY LOGS rotates the slow query log.

MAKE MASTER REVOKE SESSION disconnects all sessions but the current one and prevents future connections from all users unless they have SUPER, REPL_CLIENT or REPL_SLAVE privileges. MAKE MASTER GRANT SESSION undoes this.

mysql> SHOW TABLE_STATISTICS;
+--------------+-----------+--------------+-------------------------+--------+
| Table | Rows_read | Rows_changed | Rows_changed_x_#indexes | Engine |
+--------------+-----------+--------------+-------------------------+--------+
| ETL.Customer | 0 | 2 | 2 | InnoDB |
| mysql.db | 2 | 0 | 0 | MyISAM |
| ETL.Mer | 8 | 1 | 1 | MyISAM |
| mysql.user | 6 | 0 | 0 | MyISAM |
+--------------+-----------+--------------+-------------------------+--------+
4 rows in set (0.00 sec)

mysql> SHOW USER_STATISTICS\G
*************************** 1. row ***************************
User: root
Total_connections: 1
Concurrent_connections: 1
Connected_time: 923
Busy_time: 0
Cpu_time: 0
Bytes_received: 0
Bytes_sent: 352
Binlog_bytes_written: 0
Rows_fetched: 1
Rows_updated: 0
Table_rows_read: 0
Select_commands: 1
Update_commands: 0
Other_commands: 1
Commit_transactions: 0
Rollback_transactions: 0
Denied_connections: 0
Lost_connections: 0
Access_denied: 0
Empty_queries: 0

google-mysql-tools - The v2 patch

install:
498 wget http://downloads.mysql.com/archives/mysql-5.0/mysql-5.0.37.tar.gz
499 mv mysql-5.0.37.tar.gz mysql
500 cd mysql/
502 md5sum mysql-5.0.37.tar.gz

508 gunzip all.v2-mysql-5.0.37.patch.gz
517 cd mysql-5.0.37
518 patch -p1 < ../all.v2-mysql-5.0.37.patch
521 ./configure --prefix=/usr/local/mysql5037
522 make
523 make install
534 cp my.cnf /etc/my.cnf
526 cd /usr/local/mysql5037/
530 bin/mysql_install_db --user=mysql
531 chown -R mysql var 532
chgrp -R mysql . 533

netstat -ultn 535
bin/mysqld_safe --user=mysql &

后来对每个新功能做一介绍

订阅：评论 (Atom)