#!/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月17日星期二
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__
！
＠
＃
＄
％
＾
＆
＊
（
）
｛
｝
「
」
‘
；
：
”
？
＜
＞
、
。
・
￥
｜
ー
＋
＿
＝
【
】
［
］
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
未完
基本:
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
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，不过速度会慢一些。
設計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) 上创建了唯一索引，则任何两个员工都不能同姓。
有关唯一索引的更多信息，请参见创建唯一索引。
主键索引
数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。有关主键的更多信息，请参见定义主键。
聚集索引
在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。
如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。
使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，例如 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>
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日星期三
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.
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
自然想到把文件内容导入到 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)
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月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";
将一个列表当成一个标量 []
$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.
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.
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语句的执行计划呢，？
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 |
| \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> show variables like '%slave%';
+---------------------------+---------------+
| Variable_name | Value |
+---------------------------+---------------+
| init_slave | |
| log_slave_updates | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /Disk/mysql/tmp |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| sql_slave_skip_counter | |
+---------------------------+---------------+
9 rows in set (0.00 sec)
通常需要跳过一笔或n笔，使用
set global sql_slave_skip_counter=1; or
set global sql_slave_skip_counter=n;
start slave;
如想跳过所有错误修改配置文件
slave_skip_errors = ALL
restart mysql
+---------------------------+---------------+
| Variable_name | Value |
+---------------------------+---------------+
| init_slave | |
| log_slave_updates | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /Disk/mysql/tmp |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| sql_slave_skip_counter | |
+---------------------------+---------------+
9 rows in set (0.00 sec)
通常需要跳过一笔或n笔，使用
set global sql_slave_skip_counter=1; or
set global sql_slave_skip_counter=n;
start slave;
如想跳过所有错误修改配置文件
slave_skip_errors = ALL
restart mysql
订阅：
博文 (Atom)