NinGoo.net

使用Perl发送邮件

NinGoo — Thu, 09 Jul 2009 22:29:54 +0800

在之前的监控系统中，邮件系统配置为只能本地调用mail命令发送，这样如果要在每台数据库服务上发送邮件就比较麻烦，需要通过ssh远程调用邮件服务器上的发送脚本。如果要发送文件，还要先将文件复制到邮件服务器上。

随着机器数量的增加，这种方式越来越不灵活。因此希望能通过传统的smtp的方式在任何主机上都能发送邮件，于是花了点时间研究了一下sendmail，根据这篇文章很轻松的让sendmail跑了起来：

修改sendmail配置模板/etc/mail/sendmail.mc

define(QUEUE_DIR, `/var/spool/mqueue/q*')dnl
TRUST_AUTH_MECH(`EXTERNAL DIGEST-MD5 CRAM-MD5 LOGIN PLAIN')dnl
define(`confAUTH_MECHANISMS', `EXTERNAL GSSAPI DIGEST-MD5 CRAM-MD5 LOGIN PLAIN')dnl
dnl DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl
DAEMON_OPTIONS(`Port=25, Name=MTA')dnl

第一行是新增，支持多个队列
第二三行去掉注释
第四行加上注释
第五行新增

根据模板生成配置文件

m4 /etc/mail/sendmail.mc > /etc/sendmail.cf

建立多个队列的目录

cd /var/spool/mqueue/
mkdir q1 q2 q3 q4 q5 q6

启动sendmail

/etc/rc.d/init.d/sendmail start

若要随机自启动sendmail，修改一下chkconfig设置即可

chkconfig --level 35 sendmail on

测试了一下发送邮件成功：

mail -s "test mail" NinGoo@test.com < /tmp/test.log

在CPAN上搜索了一下，Mail:Sender模块发送邮件比较方便，支持多附件，语法也比较简单。需要安装如下两个组件：

MIME::Base64
Mail::Sender

发送邮件的perl示例代码如下，为便于查看邮件发送的详细情况，这里开启了debug模式：

#!/usr/bin/perl -w
use Mail::Sender;
open my $DEBUG, ">> /tmp/mail.log" or die "Can't open the debug file: $!\n";
$sender = new Mail::Sender
 {smtp => 'smtp_server',
   from => 'send@mail.com'};
#发送普通邮件
$sender->MailMsg(
 {to => 'NinGoo@test.com',
 subject => 'test mail',
 msg => 'hello word',
 debug => $DEBUG
 });
#发送带附件的邮件
$sender->MailFile(
 {to => 'NinGoo@test.com',
 subject => 'test mail with attached file',
 msg => 'hello word',
 file => '/tmp/test.txt'
 debug => $DEBUG
 });

经过测试，在邮件服务器本机发送成功，但在远程主机上则失败了，通过debug的log文件，发现错误如下：

>> 550 5.7.1 < NinGoo@test.com>... Relaying denied. Proper authentication required.

因为是内部监控使用，可以配置远程发送到特定的域的邮件无须认证，修改/etc/mail/relay-domains文件，每行加入一个目标域，如taobao.com。

修改配置后注意重启sendmail

/etc/rc.d/init.d/sendmail restart

发送正常的log如下：

>> 250 2.0.0 n69DbJwt011946 Message accepted for delivery

Related Articles

PermLink: http://www.ningoo.net/html/2009/sendmail_by_perl.html

Add Comments(0) | Follow NinGoo@Twitter | Google Reader

xtrabackup 0.8版发布

NinGoo — Wed, 08 Jul 2009 21:17:41 +0800

Author:NinGoo posted on NinGoo.net

前段时间开始测试xtrabackup 0.7的时候，在下载的地方已经看到有0.8的目录和源代码，只是各个平台的编译版本还没有，当时就猜0.8版本很快就要出来了。不过最近很少有时间看Google Reader，今天才发现其实7月1号0.8版就已经出来了，可以到这里下载。

看了一下新特性，没有特别让人心动的功能出来。innobackupex脚本开始支持增量备份（xtrabackup本身早已经支持）；开始支持archive引擎的备份；stream备份模式支持tar4ibd；增加了–export选项以支持InnoDB表在不同库之间移动（只能导入到XtraDB引擎，详细参考这里）；另外修复了一些bug。

更多介绍可以参考这里。

Related Articles

PermLink: http://www.ningoo.net/html/2009/xtrabackup-08_released.html

Add Comments(0) | Follow NinGoo@Twitter | Google Reader

在Crontab中调度Perl程序的环境变量问题

NinGoo — Wed, 08 Jul 2009 13:55:28 +0800

Author:NinGoo posted on NinGoo.net

在corntab中调度perl程序时，如何获取并设置环境变量是一大问题。在shell脚本中，我一般是通过直接调用~/.profile或者~/.bash_profile（视shell类型而定）来解决。而Perl中则没有一个现成的比较方便的方法。

老楼最近在一篇blog中给出了一种方法，基本上能解决上述问题，但主要是针对ORACLE_HOME，其他的还需要再写一个差不多的函数，并且还需要配置文件，通用性不是太好。

我的办法是，直接利用.profile或者.bash_profile，在perl中循环将所有的环境变量一次性设置好。这个设置过程，我写成了一个函数放在公共模块中，只需要在脚本中包含该模块，然后调用一次该函数即可。因为要兼容Oracle和MySQL，按照我们的安装规范，Oracle安装用户为oracle，而MySQL数据库的安装用户为mysql，相应的脚本使用相应的用户。自动化是需要建立在规范之上的，下面是函数代码：

sub set_env{
  my $user=`whoami`;
  chomp($user);
  my $profile="/home/".$user."/.profile";
  if (! -e $profile ){
    $profile="/home/".$user."/.bash_profile"
  }
  open(NEWENV, ". $profile && env|");
  while (){
    if (/(\w+)=(.*)/){
      $ENV{$1}="$2";
    }
  }
  close NEWENV;
}

Related Articles

PermLink: http://www.ningoo.net/html/2009/set_env_for_perl_script_crontab_schedule.html

Add Comments(0) | Follow NinGoo@Twitter | Google Reader

使用Xtrabackup备份MySQL数据库(续)

NinGoo — Wed, 08 Jul 2009 11:55:53 +0800

Author:NinGoo posted on NinGoo.net

在上一篇里，演示了使用xtrabackup对InnoDB进行在线物理热备，使用innobackupex脚本，可以很简单的一条命令行搞定InnoDB和MyISAM的物理备份。

本文则演示如何从xtrabackup的备份中进行恢复。本次恢复的是一个600GB大小的InnoDB数据库，备份的时候没有使用gzip压缩。

首先将备份好的tar文件解开到目标数据库的数据路径下，这一步类似oracle的restore database：

tar xvf mysqlbak.tar /opt/mysqldata

注意解出来的文件和目录的属主以及权限是否正确。如果是将备份恢复到一台全新的环境，则需要修改/etc/my.cnf，将innodb_data_file_path等参数设置和原备份的库一致。然后执行：

$innobackupex-1.5.1 --apply-log /opt/mysqldata

这一步类似于oracle的recover database，从日志来看，差不多一个小时执行完毕，该InnoDB数据库分配空间600GB，实际使用空间约590GB，并且数据的更新量还是比较大的，大约一个小时apply-log完成。运行的日志简单记录如下：

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackup
           prints "innobackup completed OK!".

090708 09:50:44  innobackupex: Starting ibbackup with command:
xtrabackup --prepare --target-dir=/opt/mysqldata

xtrabackup  Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)
xtrabackup: cd to /opt/mysqldata
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2882535424, start_lsn=(514 2288109039)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M...;ibdata60:10000M
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2882535424
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Log scan progressed past the checkpoint lsn 514 2288109039
090708  9:50:45  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Doing recovery: scanned up to log sequence number 514 2293351424 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2298594304 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2303837184 (0 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2309080064 (0 %)
090708  9:50:47  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 514 2314322944 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2319565824 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2324808704 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2330051584 (1 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 2335294464 (1 %)
...这里省略若干行
InnoDB: Doing recovery: scanned up to log sequence number 514 3881944064 (62 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 3887186944 (62 %)
InnoDB: Doing recovery: scanned up to log sequence number 514 3887732530 (62 %)
090708 10:52:00  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 350504077, file name mysql-bin.000748
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015
090708 10:52:17  InnoDB: Started; log sequence number 514 3887732530

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
090708 10:52:17  InnoDB: Starting shutdown...
090708 10:52:24  InnoDB: Shutdown completed; log sequence number 514 3887732530

090708 10:52:24  innobackupex: Restarting xtrabackup with command:
xtrabackup --prepare --target-dir=/opt/mysqldata
for creating ib_logfile*

xtrabackup  Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)
xtrabackup: cd to /opt/mysqldata
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M;...;ibdata60:10000M
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 4
xtrabackup:   innodb_log_file_size = 104857600
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
090708 10:52:25  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
090708 10:52:25  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
090708 10:52:26  InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
090708 10:52:26  InnoDB: Log file ./ib_logfile3 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile3 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090708 10:52:27  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 350504077, file name mysql-bin.000748
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015
090708 10:52:27  InnoDB: Started; log sequence number 514 3887732748

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
090708 10:52:27  InnoDB: Starting shutdown...
090708 10:52:29  InnoDB: Shutdown completed; log sequence number 514 3887732748
090708 10:52:29  innobackupex: innobackup completed OK!

运行完毕后，启动mysql即可。

Related Articles

PermLink: http://www.ningoo.net/html/2009/using_xtrabackup_backup_mysql_database_2.html

Add Comments(0) | Follow NinGoo@Twitter | Google Reader

使用Xtrabackup备份MySQL数据库

NinGoo — Thu, 25 Jun 2009 15:55:05 +0800

Author:NinGoo posted on NinGoo.net

MySQL数据库的备份，一直是个比较头痛的问题。各种工具虽然不少，但一个真正好用易用的，却又非常难找。Mysqldump做为数据的逻辑备份工具还行，但是无法进行在线热备，而没有物理备份工具，在数据量比较大的时候，恢复的时间也会长得无法接受。InnoDB倒是有个商业的InnoDB Hotbackup，可以对InnoDB引擎的表实现在线热备。最近发现了一个工具，percona出品的Xtrabackup，是InnoDB Hotbackup的一个开源替代品，可以在线对InnoDB/XtraDB引擎的表进行物理备份，试用了一下，非常的不错，值得向MySQL DBA们推荐。

下面是一个实际备份的例子，采用了gzip将备份流进行压缩，约20GB的数据库，压缩后大小为340MB，当然，压缩后的大小跟数据库中实际使用的空间是相关的。备份时间约6分44秒。

innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的，主要是为了方便的同时备份InnoDB和MyISAM引擎的表，并且加入了一些使用的选项。如–slave-info可以记录备份恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。

注意，从备份后的tar包解包的时候，需要使用-i参数。最新发布的是0.7版，猛击这里下载

MySQL DBA们，你是如何做备份的呢？欢迎到这里探讨。

$innobackupex-1.5.1 --user=root --stream=tar /bak/ --slave-info | gzip > /bak/bak_mysql.tar.gz

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackup
           prints "innobackup completed OK!".

innobackupex: Using mysql  Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (x86_64)
 using  EditLine wrapper
innobackupex: Using mysql server version 5.0.67-log

innobackupex: Created backup directory /bak
090625 15:23:00  innobackupex: Starting mysql with options: --unbuffered --user=root
090625 15:23:00  innobackupex: Connected to database with mysql child process (pid=3431)
090625 15:23:04  innobackupex: Connection to database server closed

090625 15:23:04  innobackupex: Starting ibbackup with command:
xtrabackup --backup --suspend-at-end --log-stream --target-dir=./
innobackupex: Waiting for ibbackup (pid=3565) to suspend
innobackupex: Suspend file '/opt/mysqldata/xtrabackup_suspended'

xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /opt/mysqldata
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = /opt/mysqldata
xtrabackup:   innodb_data_file_path = ibdata1:10G;ibdata2:10G
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 4
xtrabackup:   innodb_log_file_size = 104857600
xtrabackup: use O_DIRECT
xtrabackup: Stream mode.
>> log scanned up to (0 3053406941)

090625 15:23:06  innobackupex: Continuing after ibbackup has suspended

innobackupex: Starting to backup InnoDB tables and indexes
innobackupex: from original InnoDB data directory '/opt/mysqldata'
innobackupex: Backing up as tar stream 'ibdata1'
>> log scanned up to (0 3053416714)
...这里省略若干行
>> log scanned up to (0 3054123851)
tar: ibdata1: file changed as we read it
innobackupex: Backing up as tar stream 'ibdata2'
>> log scanned up to (0 3054142116)
...这里省略若干行
>> log scanned up to (0 3054618483)
innobackupex: Backing up file '/opt/mysqldata/test/sp.ibd'
innobackupex: Backing up file '/opt/mysqldata/test/tmp_dy.ibd'
innobackupex: Backing up files '/opt/mysqldata/testdb/*.ibd' (206 files)
>> log scanned up to (0 3054638401)
>> log scanned up to (0 3054668860)
tar: testdb/group_group_thread_0027.ibd: file changed as we read it
>> log scanned up to (0 3054695015)
>> log scanned up to (0 3054928216)
tar: testdb/group_thread_reply_0007.ibd: file changed as we read it
>> log scanned up to (0 3054952588)
>> log scanned up to (0 3055005439)
tar: testdb/group_user_0001.ibd: file changed as we read it
>> log scanned up to (0 3055028610)
>> log scanned up to (0 3055044650)
tar: testdb/group_user_0006.ibd: file changed as we read it
>> log scanned up to (0 3055060461)
innobackupex: Backing up file '/opt/mysqldata/testdb/comments.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_content.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_ids.ibd'
090625 15:29:17  innobackupex: Starting mysql with options: --unbuffered --user=root
090625 15:29:17  innobackupex: Connected to database with mysql child process (pid=5458)
>> log scanned up to (0 3055072495)
090625 15:29:21  innobackupex: Starting to lock all tables...
>> log scanned up to (0 3055087148)
>> log scanned up to (0 3055119993)
090625 15:29:39  innobackupex: All tables locked and flushed to disk

090625 15:29:39  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, and .opt files in
innobackupex: subdirectories of '/opt/mysqldata'
innobackupex: Backing up file '/opt/mysqldata/test/sp.frm'
innobackupex: Backing up file '/opt/mysqldata/test/tmp_dy.frm'
innobackupex: Backing up files '/opt/mysqldata/testdb/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (207 files)
innobackupex: Backing up file '/opt/mysqldata/testdb/comments.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_content.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_ids.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/db.opt'
innobackupex: Backing up files '/opt/mysqldata/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (52 files)
090625 15:29:40  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '0:3054881736'
>> log scanned up to (0 3055120013)
xtrabackup: Transaction log of lsn (0 3053102766) to (0 3055120013) was copied.
090625 15:29:44  innobackupex: All tables unlocked
090625 15:29:44  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/bak/'
innobackupex: MySQL binlog position: filename '', position
innobackupex: MySQL slave binlog position: master host '127.0.0.1',
filename 'mysql-bin.000006', position 227207755
090625 15:29:44  innobackupex: innobackup completed OK!
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.

Related Articles

PermLink: http://www.ningoo.net/html/2009/using_xtrabackup_backup_mysql_database.html

Add Comments(3) | Follow NinGoo@Twitter | Google Reader

在同一台主机配置MySQL Cluster

NinGoo — Sun, 21 Jun 2009 23:51:37 +0800

Author:NinGoo posted on NinGoo.net

MySQL cluster是share nothing的集群，采用ndb存储引擎，和Oracle的RAC采用集中存储不同，是一种分布式的架构，所以可以很轻松的实现在同一台主机上配置MySQL Cluster，相对RAC来说要简单得多。

在MySQL Cluster中，有三种不同的节点：

管理节点：守护进程为ndb_mgmd，用于管理cluster。
数据节点: 守护进程为ndbd，用于存放数据
SQL节点：守护进程为mysqld，就是一个MySQL实例，对外供客户端连接访问数据。

本文将在一台64位linux主机上同时配置上述三种节点。在实际生产环境中，根据需要，可以在相同或者不同的机器部署节点。本次实例中共包含以下节点：

一个管理节点
两个数据节点
三个SQL节点

下载Mysql cluster，当前版本7.0.6，http://dev.mysql.com/downloads/cluster/7.0.html

解压，并复制到安装目录

tar zxvf mysql-cluster-gpl-7.0.6-linux-x86_64-glibc23.tar.gz
cp mysql-cluster-gpl-7.0.6-linux-x86_64-glibc23 /u01/mysql-cluster

cd /u01/mysql-cluster

创建三个MySQL实例的数据目录

mkdir data/data1
mkdir data/data2
mkdir data/data3

mkdir data/data1/mysql
mkdir data/data1/test

mkdir data/data2/mysql
mkdir data/data2/test

mkdir data/data3/mysql
mkdir data/data3/test

Cluster配置文件如下

$ more 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

三个MySQL实例配置文件如下

$ more conf/my1.cnf
[mysqld]
ndb-nodeid=4
ndbcluster=true
datadir=/u01/mysql-cluster/data/data1
basedir=/u01/mysql-cluster
port=9306
server-id=1
log-bin

$ more conf/my2.cnf
[mysqld]
ndb-nodeid=5
ndbcluster=true
datadir=/u01/mysql-cluster/data/data2
basedir=/u01/mysql-cluster
port=9307
server-id=2
log-bin

$ more conf/my3.cnf
[mysqld]
ndb-nodeid=6
ndbcluster=true
datadir=/u01/mysql-cluster/data/data3
basedir=/u01/mysql-cluster
port=9308
server-id=3
log-bin

初始化三个MySQL实例

script/mysql_install_db --basedir=/u01/mysql-cluster --datadir=/u01/mysql-cluster/data/data1
script/mysql_install_db --basedir=/u01/mysql-cluster --datadir=/u01/mysql-cluster/data/data2
script/mysql_install_db --basedir=/u01/mysql-cluster --datadir=/u01/mysql-cluster/data/data3

初始化管理节点

bin/ndb_mgmd --initial -f conf/config.ini --configdir=/u01/mysql-cluster

进入管理节点查看配置

$ bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> help
---------------------------------------------------------------------------
 NDB Cluster -- Management Client -- Help
---------------------------------------------------------------------------
HELP                                   Print help text
HELP COMMAND                           Print detailed help for COMMAND(e.g. SHOW)
SHOW                                   Print information about cluster
CREATE NODEGROUP ,...          Add a Nodegroup containing nodes
DROP NODEGROUP                     Drop nodegroup with id NG
START BACKUP [NOWAIT | WAIT STARTED | WAIT COMPLETED]
START BACKUP [] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
START BACKUP [] [SNAPSHOTSTART | SNAPSHOTEND] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
                                       Start backup (default WAIT COMPLETED,SNAPSHOTEND)
ABORT BACKUP                Abort backup
SHUTDOWN                               Shutdown all processes in cluster
CLUSTERLOG ON [] ...         Enable Clus
 = ALERT | CRITICAL | ERROR | WARNING | INFO | DEBUG
 = STARTUP | SHUTDOWN | STATISTICS | CHECKPOINT | NODERESTART | CONNECTION
| INFO | ERROR | CONGESTION | DEBUG | BACKUP
    = 0 - 15
       = ALL | Any database node id

For detailed help on COMMAND, use HELP COMMAND.

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.34 ndb-7.0.6)

[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)

初始化数据节点

bin/ndbd --initial -c localhost:1186
2009-06-21 23:13:19 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1
bin/ndbd --initial -c localhost:1186
2009-06-21 23:13:50 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1

启动SQL节点

bin/mysqld --defaults-file=conf/my1.cnf&
[1] 17843
 090621 23:14:36 [Warning] No argument was provided to --log-bin,
and --log-bin-index was not used; so replication may break when this MySQL server acts as a master
and has his hostname changed!! Please use '--log-bin=test-bin' to avoid this problem.
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
090621 23:14:36  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
090621 23:14:36  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
090621 23:14:36  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
090621 23:14:36  InnoDB: Started; log sequence number 0 0
090621 23:14:36 [Note] NDB: NodeID is 4, management server 'localhost:1186'
090621 23:14:37 [Note] NDB[0]: NodeID: 4, all storage nodes connected
090621 23:14:37 [Note] Starting Cluster Binlog Thread
090621 23:14:37 [Note] Event Scheduler: Loaded 0 events
090621 23:14:37 [Note] bin/mysqld: ready for connections.
Version: '5.1.34-ndb-7.0.6-cluster-gpl-log'  socket: '/tmp/mysql.sock'
port: 9306  MySQL Cluster Server (GPL)
090621 23:14:38 [Note] NDB Binlog: Ndb tables initially read only.
090621 23:14:38 [Note] NDB: Creating mysql.ndb_schema
090621 23:14:38 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_schema
090621 23:14:38 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
090621 23:14:38 [Note] NDB: Creating mysql.ndb_apply_status
090621 23:14:39 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_apply_status
090621 23:14:39 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2009-06-21 23:14:39 [NdbApi] INFO     -- Flushing incomplete GCI:s < 20/3
2009-06-21 23:14:39 [NdbApi] INFO     -- Flushing incomplete GCI:s < 20/3
090621 23:14:39 [Note] NDB Binlog: starting log at epoch 20/3
090621 23:14:39 [Note] NDB Binlog: ndb tables writable

bin/mysqld --defaults-file=conf/my2.cnf&
bin/mysqld --defaults-file=conf/my3.cnf&

查看cluster

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.34 ndb-7.0.6, Nodegroup: 0, Master)
id=3    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)

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

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

创建测试表

bin/mysql -uroot -h localhost -P 9306  --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)

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

root@(none) 11:21:24>use test
Database changed
root@test 11:21:26>create table test_ndb(i int,a varchar(20),primary key(i)) engine=ndb;
090621 23:22:32 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/test_ndb
090621 23:22:32 [Note] NDB Binlog: logging ./test/test_ndb (UPDATED,USE_WRITE)
090621 23:22:32 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/test_ndb
090621 23:22:32 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/test_ndb
090621 23:22:32 [Note] NDB Binlog: logging ./test/test_ndb (UPDATED,USE_WRITE)
090621 23:22:32 [Note] NDB Binlog: logging ./test/test_ndb (UPDATED,USE_WRITE)
Query OK, 0 rows affected (1.10 sec)

root@test 11:22:32>insert into test_ndb values(1,'NinGoo');
Query OK, 1 row affected (0.02 sec)

[mysql@dbconsole mysql-cluster]$ ls -l data/data1/test
total 12
-rw-rw----  1 mysql dba 8578 Jun 21 23:22 test_ndb.frm
-rw-rw----  1 mysql dba    0 Jun 21 23:22 test_ndb.ndb
[mysql@dbconsole mysql-cluster]$ ls -l data/data2/test
total 12
-rw-rw----  1 mysql dba 8578 Jun 21 23:22 test_ndb.frm
-rw-rw----  1 mysql dba    0 Jun 21 23:22 test_ndb.ndb
[mysql@dbconsole mysql-cluster]$ ls -l data/data3/test
total 12
-rw-rw----  1 mysql dba 8578 Jun 21 23:22 test_ndb.frm
-rw-rw----  1 mysql dba    0 Jun 21 23:22 test_ndb.ndb

$ bin/mysql -uroot -P 9307 --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)

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

root@(none) 11:24:38>use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@test 11:24:41>select * from test_ndb;
+---+--------+
| i | a      |
+---+--------+
| 1 | NinGoo |
+---+--------+
1 row in set (0.02 sec)

Related Articles

PermLink: http://www.ningoo.net/html/2009/setup_mysql_cluster_on_single_linux_node.html

Add Comments(0) | Follow NinGoo@Twitter | Google Reader

MySQL还能走多远?

NinGoo — Fri, 19 Jun 2009 23:41:00 +0800

Author:NinGoo posted on NinGoo.net

MySQL最近这一年多可谓命途多舛。先是被日薄西山的Sun收购，又随Sun一起嫁入Oracle。因此不少人开始担忧起MySQL的前途来。其实这是大可不必的，一个产品的生命力，在于客户和市场。只要客户和市场认可了，不怕没有人挤破头来发展。

Oracle虽然在市场上称王，但论数据库的普及程度，估计接触过MySQL的人还是要更多一些，LAMP大名，早已名声在外。只是大部分接触MySQL的人，大多只是将MySQL做为一个简单的数据盒子，并没有能更深入的研究。这也跟应用场景有关。MySQL在之前大多只是在一些小应用中小打小闹，在真正的企业级市场，基本没他什么事。

但互联网企业这几年的逐渐红火，这种情况开始改变。一个成功的网站，系统所面临的压力，在一些传统行业是很难想像的。而大多数网站在初创时，基于技术和资金等多方面的考虑，往往都乐于选择开源的MySQL做为数据库解决方案。随着网站业务的发展，压力成指数倍的增加，MySQL数据库的架构设计便便的非常重要，公司也会愿意投入大量的人力资源去深入的研究MySQL。这就形成了一个良性的循环。

MySQL随着web2.0一起红火，MySQL DBA的职位也开始变得吃香。然而MySQL DBA人才的相对匮乏，也将可能成为制约MySQL发展的一道坎。好在国内这几年不断有一些优秀的MySQL DBA开始展露头角。比如阿里巴巴的小伙子简朝阳，毕业不过短短三年，从Oracle转向MySQL的时间更短，却能潜心研究，最近正式出版的新书《MySQL性能调优与架构设计》，可以说是填补国内关于MySQL架构类数据的空白。相信对于MySQL的普及推广，一定能起到积极的作用。

是的，不用怀疑。MySQL一定能走很远。MySQL DBA一定大有可为。正是相信这一点，我在前几天注册了一个域名：http://mysqldba.net，并用discuz!搭建了一个论坛，希望MySQL DBA们能够多多交流，共同进步。希望以后招聘MySQL DBA，不至于一年也难招到一位合适的了，呵呵。

Related Articles

PermLink: http://www.ningoo.net/html/2009/how_far_can_mysql_be.html

Add Comments(2) | Follow NinGoo@Twitter | Google Reader

MSN上的闲聊

NinGoo — Thu, 18 Jun 2009 15:35:11 +0800

Author:NinGoo posted on NinGoo.net

A: 现在搞起mysql了？
B: 嗯
A: 现在比较多的项目在使用mysql了，所以现在mysql也变成一种趋势了
B: 在互联网行业，MySQL分布式是大势所趋
A: 呵呵，是的，现在我们这边也有很多mysql的项目在线上
B: 不过目前国内MySQL的交流是不太多
B: 而且要招一个合适的人相当的难
A: 呵呵，是的，很多mysql都是从Oracle转过来或者是带着做的，不过有Oracle的技术，转或者带着做问题还不是很大
A: 对于互联网的应用来说，DB这块主要的还是HA这块是重中之中，Mysql这块在分布式来说做的还行
B: 基本的技术原理什么的是没啥难的，不过要用好，尤其是分布式架构，要做好还是需要功力的
A: 呵呵，是的，对于技术这东西很多还是考经验和实践，上了几个项目后会有很大的积累
B: 嗯，是这样的
B: 去年我们开始推MySQL的时候，开发人员都有很大的担心和抵触
B: 现在是想不让他们用都不行，哈哈
A: 哈哈，其实mysql用起来还行，有些数据仓库还使用的呢
A: 但是对于大容量，高并发的事务处理，mysql还是有待提高的
A: 现在很多做互联网的，除了核心库外，别的基本上都在往这上面转，其实很多时候还是人的心理面在作梗
B: 分布式，其实把很多东西都交给应用架构了
B: 对于数据库本身，要求反倒很低了，就是用来存放持久数据而已
A: 没错，那样DB真的是DB了，就是简单的存储了
A: 哈哈，没错，哎，很多东西大家的想法很一直
B: 嗯，技术就那么点，关键是把想法落实，做出东西来
A: 是的，所以说你们那边还是比较幸福的
很多东西好落实
B: 我们也是在不断摸索
B: 不过摸索的过程，是比较长经验值的
A: 是的，摸索只是一种开始，实施的过程才是真正验证摸索的阶段，如果只有摸索，没有实践，那也只是空话，所以说你们那边是幸福的，很多时候有开始，有结束，但是很多时候，很多单位不是这样，摸索永远当作一种摸索，没有结束，呵呵

http://www.mysqldba.net/viewthread.php?tid=18&extra=page%3D1

Related Articles

PermLink: http://www.ningoo.net/html/2009/chat_about_mysqldba_on_msn.html

Add Comments(1) | Follow NinGoo@Twitter | Google Reader

DataGuard环境慎用alter tablespace drop datafile

NinGoo — Tue, 16 Jun 2009 20:50:12 +0800

Author:NinGoo posted on NinGoo.net

Oracle10g开始提供alter tablespace drop datafile的特性，允许删除没有数据的空文件。本来这是一个还有那么点用的功能，可是，慎用，有bug。

ALTER TABLESPACE ... DROP DATAFILE ... produces bad
redo which typically does not then drop the datafile
either during recovery or at a standby. This can lead
to subsequent errors such as ORA-600 [3689] of
subsequent redo tries to add new datafile.

如果在data guard环境中，可能你在主库这么一执行，备库就挂了，报ora-600

ORA-00600: internal error code, arguments: [3689], [28], [], [], [], [], [], []

这个bug 5623467，从10.2.0.1一直影响到10.2.0.4，好在10.2.0.4后出了一个 Data Guard Physical Recommended Patch Bundle #1(Doc ID: 7936993.8)，总算是修复了这个问题，已经升级或者准备升级到10.2.0.4的朋友，一定要主机打上这个patch bundle，Oracle总是在一些你不注意的地方留个坑，一不小心就让你灰头土脸。

Workaround
  Avoid the use of the ALTER TABLESPACE .. DROP DATAFILE ..
  command on the primary.

10.2.0.4 Data Guard Physical Recommended Patch Bundle #1主要修复的bug如下：

Bug:3934160 Intermittent OERI[ksfdgfnm1]
Bug:5126719 LGWR may terminate the instance due to ORA-16164
Bug:5404871 Local first not used archiving closed thread at higher protection mode
Bug:5476236 RFS logminer client inoperative after physical standby switchover to primary
Bug:5623467 Corrupt redo from ALTER TABLESPACE DROP DATAFILE
Bug:6024730 V$BACKUP . STATUS shows “UNKNOWN ERROR”
Bug:6070225 Terminal recovery encounters ORA-308 when RMAN is active
Bug:6074620 LGWR unconditionally writes to trace file
Bug:6345573 OERI [krsmseqa.2] during terminal recovery
Bug:6469211 Slow startup with many log files on ASM
Bug:6490140 LGWR may crash the instance
Bug:6711853 V$ARCHIVE_DEST does not show new ‘binding’ on the physical standby
Bug:6840740 Switchover to physical standby allowed even if there are no physical standbys
Bug:6874522 V$DATAGUARD_STATS . VALUE may be NULL in logical standby
Bug:6882739 Controlfile enqueue contention from DataGuard
Bug:6919819 Flashback database may not correctly flashback some data blocks
Bug:6941717 Logical standby considered synchronous during dictionary load
Bug:6954829 OERI[kcramr_8] during media recovery
Bug:6955744 Archive log is not automatically registered when second CDC is built
Bug:6976005 CF enqueue held for longer than needed with flashback enabled
Bug:6980597 Heartbeat redo is not generated for single instance primary with RTA logical
Bug:7013124 30 second delay for 2nd pass instance recovery
Bug:7044551 ALTER TABLESPACE BACKUP CONTROLFILE TO TRACE can be slow
Bug:7136489 Unwanted trace lines “FAIL: tkrsf_al_read: Invalid archive log file contents”
Bug:7140204 OERI[ktupdb_0] undo block corruption on readable standby
Bug:7159505 Standby MRP stuck when no recovery area space left
Bug:7197445 Standby Recovery session cancelled due to ORA-600 [3020]
Bug:7257461 ORA-16146 during block media recovery on a primary with no standby destination
Bug:7257770 OERI:krffReserveCFEntry_1 / flashback disabled in RAC
Bug:7276960 Intermittent ORA-16014 errors in Streams environment
Bug:7432601 Hang holding FAL queue latch
Bug:7460818 Flashback logs are not balanced between redo threads
Bug:7494333 MRP may request wrong (too old) log
Bug:7568556 Slow standby recovery
Bug:7593835 Write failure when “selecting” new SRL is not handle properly
Bug:7643632 High log file sync in Data Guard maximum availability (sync) mode
Bug:8230457 Physical standby media recovery gets OERI[krr_media_12]
Bug:8283650 A dump can occur under krsmchksl with trace enabled
Bug:8287155 Final gap error (ORA-16416) reported during switchover to standby
Bug:8304589 Terminal recovery may hang

Related Articles

PermLink: http://www.ningoo.net/html/2009/do_not_use_alter_tablespace_drop_datafile_on_dataguard.html

Add Comments(0) | Follow NinGoo@Twitter | Google Reader

招聘这事儿

NinGoo — Sat, 13 Jun 2009 01:25:50 +0800

Author:NinGoo posted on NinGoo.net

最近事情偏多，blog更新几近于无，这不是一个好现象。事情多，业务增长快，不可避免的，需要招人。经过权衡考虑，我们将在北京设置一个远程产品DBA团队，负责数据库管理/优化/存储备份管理等。这是一个巨大的挑战，我们的工作模式，我们的沟通方式，我们的技术架构，都需要为此做出改变。变化就是机遇，所以这也是一个很好的机会。

新的团队，我们需要一个Team Leader，需要你：
1、精通Oracle/Mysql中至少一个关系型数据库，熟悉数据库理论基础，有一定的技术前瞻力和执行力。
2、熟悉linux/unix操作系统，能熟练使用shell/perl，熟悉SAN存储。
3、具有一定的团队管理经验，能带领团队一起达成团队目标。
4、沟通能力强，积极主动，具备高度的激情与创新能力
5、具备一定的系统架构规划设计能力

同时，我们还需要专业的Oracle DBA，需要你：
1、精通Oracle数据库系统架构与管理
2、熟悉物理与逻辑data guard，streams或者RAC等高可用架构
3、熟悉Linux/unix操作系统，能熟练编写Shell/Perl脚本
4、具备一定的主机/存储的硬件知识能力者优先
5、有想法，具备良好的学习能力

MySQL做为我们后续一个非常重要的发展方向，今年将超过200台的规模，需要专业的MySQL DBA：
1、精通Mysql大型集群的批量管理与监控
2、熟悉MySQL高可用架构设计，熟悉MySQL的备份与恢复
3、熟悉Linux/unix操作系统，能熟练编写Shell/Perl脚本，能利用脚本完成大批量数据库的管理
4、具备一定的主机/存储的硬件知识能力者优先
5、有想法，具备良好的学习能力

以上职位，base在北京。同时如果想在杭州发展，也可以base在杭州。如果你有兴趣，赶紧行动。简历中请注明是应聘杭州还是北京的职位。
简历请发送至: jiangfeng#taobao.com

Related Articles

PermLink: http://www.ningoo.net/html/2009/taobao_dba_jobs_in_beijing.html

Add Comments(0) | Follow NinGoo@Twitter | Google Reader