Oracle DBA|MySQL DBA|数据库管理,架构,监控与性能优化 --- NinGoo.net

在同一台主机配置MySQL Cluster

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

在MySQL Cluster中,有三种不同的节点:

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

下载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