在同一台主机配置MySQL Cluster

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)

MySQL还能走多远?

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，不至于一年也难招到一位合适的了，呵呵。

MSN上的闲聊

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

DataGuard环境慎用alter tablespace drop datafile

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

常用标签： oracle life MySQL Oracle11g blog dba 新特性 oow2009 oow wordpress

最新评论 | Recent comments

订阅更新 | Subscribe

文章分类 | Categories

最近文章 | Recent posts

站点信息 | Statistics