So Tired !_! 逆水行舟, 不进则退!

5Jun/12

mysql cluster 7 动态增加数据节点配置步骤

Posted by Nick Xu

vi /usr/local/mysql/mysql-cluster/config.ini

[ndbd default]

DataMemory =100M

IndexMemory =100M

NoOfReplicas = 2

DataDir = /usr/local/mysql/var/mysql-cluster

[ndbd]

Id = 1

HostName = 172.20.86.188

 

[ndbd]

Id = 2

HostName = 172.20.86.189

 

[mgm]

HostName = 172.20.86.185

Id = 10

 

[mysqld]

Id=20

HostName = 172.20.86.185

 

2在185上运行ndb_mgm

Ndb_mgm>show

-- NDB Cluster -- Management Client --
ndb_mgm>SHOW
Connected to Management Server at: 172.20.86.185:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=1   @172.20.86.188 (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)
id=2   @172.20.86.189 (5.1.32-ndb-7.0.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=10  @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]  2 node(s)
id=20  @172.20.86.185 (5.1.32-ndb-7.0.5)

 

 

Step 1: Update configuration file

 

[ndbd default]
DataMemory =100M
IndexMemory =100M
NoOfReplicas = 2
DataDir = /usr/local/mysql/var/mysql-cluster

[ndbd]
Id = 1
HostName = 172.20.86.188

[ndbd]
Id = 2
HostName = 172.20.86.189

[ndbd]
Id = 3
HostName=172.20.86.187
 
[ndbd]
Id = 4
HostName=172.20.86.186

[mgm]
HostName = 172.20.86.185
Id = 10

[mysqld]
Id=20
HostName = 172.20.86.185

 

 

Step 2: Restart the management server

Step 2.1停止管理节点

ndb_mgm>10 STOP
Node 10 has shut down.
Disconnecting to allow Management Server to shutdown
shell>

Step 2.2重新加载配置文件

shell>ndb_mgmd -f config.ini --reload
2008-12-08 17:29:23 [MgmSrvr] INFO    -- NDB Cluster Management Server.5.1.34-ndb-7.0.7
2008-12-08 17:29:23 [MgmSrvr] INFO    --Readingcluster configuration from 'config.ini'

Step 2.3查看新集群状态

-- NDB Cluster -- Management Client --
ndb_mgm>SHOW
Connected to Management Server at: 172.20.86.185:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=1   @172.20.86.188 (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)
id=2   @172.20.86.189 (5.1.32-ndb-7.0.5, Nodegroup: 0)
id=3 (not connected, accepting connect from 172.20.86.186)
id=4 (not connected, accepting connect from 172.20.86.187)

[ndb_mgmd(MGM)] 1 node(s)
id=10  @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]  1 node(s)
id=20  @172.20.86.185 (5.1.32-ndb-7.0.5)

 

Step 3: Perform. a rolling restart of the existing data nodes –轮流重新启动连接的数据节点

ndb_mgm>1 RESTART
Node 1: Node shutdown initiated
Node 1: Node shutdown completed, restarting, no start.
Node 1 is being restarted

ndb_mgm> Node 1: Start initiated (version7.0.5)
Node 1: Started (version7.0.5)

ndb_mgm>2 RESTART
Node 2: Node shutdown initiated
Node 2: Node shutdown completed, restarting, no start.
Node 2 is being restarted

 

Step 4: Perform. a rolling restart of all cluster API nodes –轮流重启sql节点

本案仅有一个sql节点

shell>mysqladmin -uroot -ppasswordshutdown
shell>mysqld_safe &

 

Step 5: Perform. an initial start of the new data nodes –初始化新的数据节点

 

在新增的数据节点上186和187上执行

Shell>ndbmtd –initial

或者

Shell>ndbd –initial

登陆管理节点查看集群状态

Connected to Management Server at: 172.20.86.185:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=1   @172.20.86.188 (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)
id=2   @172.20.86.189 (5.1.32-ndb-7.0.5, Nodegroup: 0)
id=3   @172.20.86.186 (5.1.32-ndb-7.0.5,no nodegroup)
id=4   @172.20.86.187 (5.1.32-ndb-7.0.5,no nodegroup)

[ndb_mgmd(MGM)] 1 node(s)
id=10  @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]  2 node(s)
id=20  @172.20.86.185 (5.1.32-ndb-7.0.5)

Step 6: Create a new node group.

ndb_mgm>CREATE NODEGROUP 3,4
Nodegroup 1 created
 
ndb_mgm>SHOW
Connected to Management Server at: 172.20.86.185:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=1   @172.20.86.188 (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)
id=2   @172.20.86.189 (5.1.32-ndb-7.0.5, Nodegroup: 0)
id=3   @172.20.86.186 (5.1.32-ndb-7.0.5,Nodegroup: 1)
id=4   @172.20.86.187 (5.1.32-ndb-7.0.5,Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=10  @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]  1 node(s)
id=20  @172.20.86.185 (5.1.32-ndb-7.0.5)

Step 7: Redistribute cluster data. –数据的重新分配

 

对于新增的数据节点一些访问量较高的数据和比较重要的数据需要从新分配。

 

分发数据命令:

Alter online table ips reorganize partition;

查看数据的分配情况:

ndb_mgm>ALL REPORT MEMORY

Node 1: Data usage is 5%(177 32K pages of total 3200)
Node 1: Index usage is 0%(108 8K pages of total 12832)
Node 2: Data usage is 5%(177 32K pages of total 3200)
Node 2: Index usage is 0%(108 8K pages of total 12832)
Node 3: Data usage is 0%(0 32K pages of total 3200)
Node 3: Index usage is 0%(0 8K pages of total 12832)
Node 4: Data usage is 0%(0 32K pages of total 3200)
Node 4: Index usage is 0%(0 8K pages of total 12832)

或者

shell>ndb_desc -c 192.168.0.10 -d n ips -p
-- ips --
Version: 1
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 6
Number of primary keys: 1
Length of frm data: 340
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 2
TableStatus: Retrieved
-- Attributes --
id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
country_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
type Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
ip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
addresses Bigunsigned NULL AT=FIXED ST=MEMORY
date Bigunsigned NULL AT=FIXED ST=MEMORY

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex

-- Per partition info --
Partition  Row count  Commit count Frag fixed memory  Frag varsized memory
0          26086      26086        1572864            557056
1          26329      26329        1605632            557056

NDBT_ProgramExit: 0 - OK

You can cause the data to be redistributed among all of the data nodes by performing, for eachNDBCLUSTERtable, anALTER ONLINE TABLE ... REORGANIZE PARTITIONstatement in themysqlclient. After issuing the statementALTER ONLINE TABLE ips REORGANIZE PARTITION, you can see usingndb_descthat the data for this table is now stored using 4 partitions, as shown here (with the relevant portions of the output in bold type):

shell>ndb_desc -c 172.20.86.185 -d n ips -p
-- ips --
Version: 16777217
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 6
Number of primary keys: 1
Length of frm data: 341
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 4
TableStatus: Retrieved
-- Attributes --
id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
country_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
type Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
ip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
addresses Bigunsigned NULL AT=FIXED ST=MEMORY
date Bigunsigned NULL AT=FIXED ST=MEMORY

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex

-- Per partition info --
Partition  Row count  Commit count Frag fixed memory  Frag varsized memory
0          12981      52296        1572864            557056
1          13236      52515        1605632            557056
2          13105      13105        819200             294912
3          13093      13093        819200             294912
Tagged as: , No Comments
4Jun/12

在同一台主机配置MySQL Cluster

Posted by Nick Xu

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)

无觅相关文章插件,快速提升流量

Tagged as: , , No Comments
   
site
site