mysql cluster，持续研究，一点点深入，最终希望部署到生产环境去

mchdba

-- =============================================================================
【13】 1528 错误
-- =============================================================================
mysql> CREATE LOGFILE GROUP lg_1
    -> ADD UNDOFILE 'undo_2.log'
    -> INITIAL_SIZE 634217728
    ->  UNDO_BUFFER_SIZE 134217728      
    -> ENGINE NDBCLUSTER;
ERROR 1528 (HY000): Failed to create LOGFILE GROUP
mysql>
mysql>
mysql>
mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1296 | Got error 1504 'Out of logbuffer memory' from NDB |
| Error   | 1528 | Failed to create LOGFILE GROUP                    |
+---------+------+---------------------------------------------------+
2 rows in set (0.00 sec)
【ok】
# UNDO_BUFFER_SIZE不能超过config.ini的配置RedoBuffer=32M大小，否则创建失败，
CREATE LOGFILE GROUP lg_1
ADD UNDOFILE 'undo_2.log'
INITIAL_SIZE 634217728     
  UNDO_BUFFER_SIZE 33554432      
ENGINE NDBCLUSTER;

CREATE TABLESPACE ts_1
       ADD DATAFILE 'data_10.dat'
       USE LOGFILE GROUP lg_1
       INITIAL_SIZE 32212254720
    EXTENT_SIZE 33554432
       ENGINE NDBCLUSTER;
    执行结束之后，需要去数据目录/var/lib/mysql-cluster/里面看看undo文件和data文件，如果存在，那么就证明建立成功了。
可以建立2个表空间
CREATE TABLESPACE ts_2
       ADD DATAFILE 'data_20.dat'
       USE LOGFILE GROUP lg_1
       INITIAL_SIZE 10737418240
    EXTENT_SIZE 33554432
       ENGINE NDBCLUSTER;
   
    create table bguserdb.mcbak like test.mc;
    alter table bguserdb.mcbak tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
    insert into bguserdb.mcbak select * from test.mc;
   
    CREATE TABLESPACE ts_1
       ADD DATAFILE 'data_10.dat‘ USE LOGFILE GROUP lg_1 INITIAL_SIZE 32212254720
    EXTENT_SIZE 33554432 ENGINE NDBCLUSTER;

mchdba

-- =============================================================================
【14】 1528 错误
-- =============================================================================         
mysql> insert into bguserdb.mv select * from test.mc;
ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER
mysql>

【】增加2个参数的值
TimeBetweenLocalCheckpoints=30
NoOfFragmentLogFiles=128

mchdba

-- =============================================================================
【15】 1005 错误
-- =============================================================================
mysql>    create table bu.mcbak like test.mc;
Query OK, 0 rows affected (0.07 sec)
mysql>    alter table bu.mcbak tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'bu.#sql-14ab_2' (errno: 140)
mysql> show errors;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1005 | Can't create table 'bu.#sql-14ab_2' (errno: 140) |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1296 | Got error 755 'Invalid tablespace' from NDB            |
| Error   | 1005 | Can't create table 'bu.#sql-14ab_2' (errno: 140) |
+---------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
【ok】重新建立表空间：
CREATE LOGFILE GROUP lg_1
ADD UNDOFILE 'undo_1.log'
INITIAL_SIZE 334217728     
  UNDO_BUFFER_SIZE 33554432      
ENGINE NDBCLUSTER;
CREATE TABLESPACE ts_1 ADD DATAFILE 'data_11.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 4294967296 EXTENT_SIZE 33554432 ENGINE NDBCLUSTER;
alter table bu.mc tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
insert into bu.mc select * from test.mc limit 0,100000;

mchdba

-- =============================================================================
【16】 1005 错误
-- =============================================================================
mysql> alter table oib TABLESPACE ts_1 STORAGE DISK  ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'os.#sql-711_3' (errno: 851)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                             |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 851 'Maximum 8052 bytes of FIXED columns supported, use varchar or COLUMN_FORMAT DYNMIC instead' from NDB |
| Error   | 1005 | Can't create table 'os.#sql-711_3' (errno: 851)                                                               |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[暂无解决方案]google之，无果！

mchdba

-- =============================================================================
【17】 1297 错误
-- =============================================================================
mysql> alter table ebelkTABLESPACE ts_1 STORAGE DISK  ENGINE=ndbcluster DEFAULT CHARSET=utf8 comment '';

ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql>
mysql> show errors;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message                                                      |
+-------+------+--------------------------------------------------------------+
| Error | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
| Error | 1296 | Got error 4350 'Transaction already aborted' from NDBCLUSTER |
| Error | 1180 | Got error 4350 during COMMIT                                 |
+-------+------+--------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

[ok]增加数据文件，数据文件空间可能不够了。
alter tablespace ts_1 add datafile 'data_12.dat' initial_size 10737418240 engine NDBCLUSTER;
alter tablespace ts_1 add datafile 'data_13.dat' initial_size 10737418240 engine NDBCLUSTER;
alter tablespace ts_1 add datafile 'data_14.dat' initial_size 10737418240 engine NDBCLUSTER;

mchdba

-- =============================================================================
【18】 1297 错误
-- =============================================================================
mysql> alter table aau TABLESPACE ts_1 STORAGE DISK  ENGINE=ndbcluster DEFAULT CHARSET=utf8 comment '';
ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level   | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1297 | Got temporary error 1501 'Out of undo space' from NDB        |
| Error   | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
| Warning | 1296 | Got error 4350 'Transaction already aborted' from NDB        |
| Error   | 1296 | Got error 4350 'Transaction already aborted' from NDBCLUSTER |
| Error   | 1180 | Got error 4350 during COMMIT                                 |
+---------+------+--------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>


【】暂时未解决。

mchdba

-- =============================================================================
【19】 1297 错误
-- =============================================================================
ndb_mgm> Node 7: Forced node shutdown completed. Occured during startphase 4. Caused by error 2303: 'System error, node killed during node restart by other node(Internal error, programming error or missing error message, please report a bug). Temporary error, restart node'.
Node 4: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
Node 5: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
Node 6: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.

【ok】baidu，google之，没有得到相似的案列，看到一个emporary error, restart node的提示，不得已 restart node 节点
重新在4个data 节点，执行 ndbd --initial，等待2分钟，搞定！oh，my god，不知道其中的原理，但是data node确实是起来了。
ndb_mgm> Node 4: Started (version 7.2.7)
Node 7: Started (version 7.2.7)
Node 5: Started (version 7.2.7)
Node 6: Started (version 7.2.7)

启动sql节点，执行检查数据
mysql> SELECT TABLESPACE_NAME, FILE_NAME, EXTENT_SIZE*TOTAL_EXTENTS/1024/1024 AS TOTAL_MB, EXTENT_SIZE*FREE_EXTENTS/1024/1024 AS FREE_MB, EXTRA FROM information_schema.FILES WHERE FILE_TYPE="DATAFILE";
Empty set (0.00 sec)

mysql>

oh my god，原来的数据文件都无法显示了。简直是暴力破解啊，^_^！

mchdba

-- =============================================================================
【20】 Out of undo space
-- =============================================================================
mysql> insert into mc select * from zzbak_mc limit 0,10000;
ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level   | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1297 | Got temporary error 1501 'Out of undo space' from NDB        |
| Error   | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
+---------+------+--------------------------------------------------------------+
2 rows in set (0.00 sec)
【ok】参考网址 http://forums.mysql.com/read.php?25,413217,413217
I add another undo log file ,it's ok now ,but counld some one tell me how can I know the usage of my undo log file ? SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG'\G;
执行：alter LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_21.log' UNDO_BUFFER_SIZE 33554432 ENGINE NDBCLUSTER;
mysql> alter LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_21.log' ENGINE NDBCLUSTER;
Query OK, 0 rows affected (4.33 sec)

-- 查看系统已有的undo文件
mysql> SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG';
+-------------+
| FILE_NAME   |
+-------------+
| undo_2.log  |
| undo_2.log  |
| undo_2.log  |
| undo_2.log  |
| undo_21.log |
| undo_21.log |
| undo_21.log |
| undo_21.log |
| NULL        |
+-------------+
9 rows in set (0.05 sec)

mysql>SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG'\G;

搞定，OK。
mysql> insert into mc select * from zzbak_mc limit 0,10000;
Query OK, 10000 rows affected (0.80 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql> insert into mc select * from zzbak_mc limit 810000,890000;
Query OK, 890000 rows affected (2 min 10.10 sec)
Records: 890000  Duplicates: 0  Warnings: 0

mchdba

-- =============================================================================
【21】 后台日志报错，批量insert数据
-- =============================================================================
2012-08-16 22:38:05 [ndbd] ERROR    -- c_gcp_list.seize() failed: gci: 38285338476552 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa2000e from: 0fa2000e
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] ERROR    -- c_gcp_list.seize() failed: gci: 38285338476553 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa2000e from: 0fa2000e
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] ERROR    -- c_gcp_list.seize() failed: gci: 38289633443840 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa2000e from: 0fa2000e

【ok】减少insert的数量，一次批量从100W减少到50W，ok，不会报异常信息了。

mchdba

-- =============================================================================
【22】 第4个data节点意外down了
-- =============================================================================
ndb_mgm> Node 7: Forced node shutdown completed. Caused by error 2305: 'Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary error, restart node'.

ndb_mgm>


【ok】执行ndbd 重新启动起来