MySQL- cluster集群----实现高可用

MySQL 集群 概述

MySQL集群概述和安装环境
MySQL Cluster是MySQL适合于分布式计算环境的高实用、高冗余版本。Cluster的汉语是“集群”的意思。它采用了NDB Cluster 存储引擎,允许在1个 Cluster 中运行多个MySQL服务器。
MySQL Cluster 是一种技术,该技术允许在无共享的系统中部署“内存中”数据库的 Cluster 。通过无共享体系结构,系统能够使用廉价的硬件,而且对软硬件无特殊要求。此外,由于每个组件有自己的内存和磁盘,不存在单点故障。

mysql 集群架构
在这里插入图片描述
图为mysql的一种常见集群

SQL节点: 给上层应用层提供sql访问。
管理节点(MGM): 管理整个集群。 启动,关闭集群。 通过ndb_mgmd命令启动集群
存储/数据节点: 保存cluster中的数据。 数据节点,可以提供副本。实现数据冗余。
NDB引擎:是一种 “内存中”的存储引擎 , 它具有可用性高和数据一致性好的特点。
拓展:NDB引擎介绍:
NDB引擎
MySQL Cluster 使用了一个专用的基于内存的存储引擎——NDB引擎,这样做的好处是速度快, 没有磁盘I/O的瓶颈,但是由于是基于内存的,所以数据库的规模受系统总内存的限制, 如果运行NDB的MySQL服务器一定要内存够大,比如4G, 8G, 甚至16G。NDB引擎是分布式的,它可以配置在多台服务器上来实现数据的可靠性和扩展性,理论上 通过配置2台NDB的存储节点就能实现整个数据库集群的冗余性和解决单点故障问题。
缺陷
基于内存,数据库的规模受集群总内存的大小限制
基于内存,断电后数据可能会有数据丢失,这点还需要通过测试验证。
多个节点通过网络实现通讯和数据同步、查询等操作,因此整体性受网络速度影响,因此速度也比较慢
2.2 优点
多个节点之间可以分布在不同的地理位置,因此也是一个实现分布式数据库的方案。
扩展性很好,增加节点即可实现数据库集群的扩展。
冗余性很好,多个节点上都有完整的数据库数据,因此任何一个节点宕机都不会造成服务中断。

Mysql cluster的下载地址:https://dev.mysql.com/downloads/cluster/
在这里插入图片描述
MySQL集群搭建
环境
5台机器

主机IP主机名节点名称主要安装的服务
10.10.100.39test1管理节点安装(server client)
10.10.100.28test2数据节点安装(server client)
10.10.100.30test3数据节点安装(server client)
10.10.100.31test4sql节点安装(server client)
10.10.100.34test5sql节点安装(server client)

服务器不要安装任何mysql

环境清理以及安装
这一部分虽然是初始过程但是比较复杂,请大家耐心配置。
所有主机上执行下面内容:
首先我们要清除旧版本,然后安装mysql cluster,最后是文件权限管理。
1.mysql旧版本清除
首先使用如下命令来清理之前操作系统自带的mysql安装:

[root@localhost ~]#yum -y remove mysql*       #卸载
[root@localhost ~]#yum -y remove mariadb*     #卸载
[root@localhost ~]# rpm -qa |grep mysql      #查看是否还有mysql命令
[root@localhost ~]# rm -rf /etc/my.cnf*      #删除配置文件
[root@localhost ~]# cat  /etc/profile        #查看环境变量是否有mysql

还需要查看是否还存在mysql相关的文件夹也一并删除

开始部署

安装扩展源

[root@localhost ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo 

这次部署使用yum的方式,下载yum源包

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

安装mysql社区版源安装包

[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm

开启mysql cluster 源

[root@localhost ~]# sed -i "55s/0/1/g" /etc/yum.repos.d/mysql-community.repo

刷新yum源缓存

[root@localhost ~]# yum clean all && yum list

安装解决Cluster的依赖包

[root@localhost ~]# yum install -y perl perl-Class-MethodMaker perl-DBI  libaio numactl

安装Mysql Cluster

[root@localhost ~]# yum install -y mysql-cluster-community-server
已安装:
  mysql-cluster-community-server.x86_64 0:7.5.16-1.el7                                                           

作为依赖被安装:
  mysql-cluster-community-client.x86_64 0:7.5.16-1.el7    mysql-cluster-community-common.x86_64 0:7.5.16-1.el7   
  mysql-cluster-community-libs.x86_64 0:7.5.16-1.el7      net-tools.x86_64 0:2.0-0.25.20131004git.el7 

查看安装路径

[root@localhost ~]# rpm -ql mysql-cluster-community-server
/etc/logrotate.d/mysql
/etc/my.cnf
/etc/my.cnf.d
/usr/bin/innochecksum
/usr/bin/lz4_decompress
/usr/bin/my_print_defaults
/usr/bin/myisam_ftdump
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql_install_db
/usr/bin/mysql_plugin
/usr/bin/mysql_secure_installation
/usr/bin/mysql_ssl_rsa_setup
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_upgrade
/usr/bin/mysqld_pre_systemd
/usr/bin/mysqldumpslow
/usr/bin/perror
/usr/bin/replace
/usr/bin/resolve_stack_dump
/usr/bin/resolveip
/usr/bin/zlib_decompress
/usr/lib/systemd/system/mysqld.service
/usr/lib/systemd/system/mysqld@.service
/usr/lib/tmpfiles.d/mysql.conf
/usr/lib64/mysql/mecab
/usr/lib64/mysql/mecab/dic
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/char.bin
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/dicrc
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/left-id.def
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/matrix.bin
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/pos-id.def
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/rewrite.def
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/right-id.def
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/sys.dic
/usr/lib64/mysql/mecab/dic/ipadic_euc-jp/unk.dic
/usr/lib64/mysql/mecab/dic/ipadic_sjis
/usr/lib64/mysql/mecab/dic/ipadic_sjis/char.bin
/usr/lib64/mysql/mecab/dic/ipadic_sjis/dicrc
/usr/lib64/mysql/mecab/dic/ipadic_sjis/left-id.def
/usr/lib64/mysql/mecab/dic/ipadic_sjis/matrix.bin
/usr/lib64/mysql/mecab/dic/ipadic_sjis/pos-id.def
/usr/lib64/mysql/mecab/dic/ipadic_sjis/rewrite.def
/usr/lib64/mysql/mecab/dic/ipadic_sjis/right-id.def
/usr/lib64/mysql/mecab/dic/ipadic_sjis/sys.dic
/usr/lib64/mysql/mecab/dic/ipadic_sjis/unk.dic
/usr/lib64/mysql/mecab/dic/ipadic_utf-8
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/char.bin
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/dicrc
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/left-id.def
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/matrix.bin
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/pos-id.def
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/rewrite.def
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/right-id.def
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/sys.dic
/usr/lib64/mysql/mecab/dic/ipadic_utf-8/unk.dic
/usr/lib64/mysql/mecab/etc
/usr/lib64/mysql/mecab/etc/mecabrc
/usr/lib64/mysql/plugin
/usr/lib64/mysql/plugin/adt_null.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/authentication_ldap_sasl_client.so
/usr/lib64/mysql/plugin/connection_control.so
/usr/lib64/mysql/plugin/debug
/usr/lib64/mysql/plugin/debug/adt_null.so
/usr/lib64/mysql/plugin/debug/auth_socket.so
/usr/lib64/mysql/plugin/debug/authentication_ldap_sasl_client.so
/usr/lib64/mysql/plugin/debug/connection_control.so
/usr/lib64/mysql/plugin/debug/group_replication.so
/usr/lib64/mysql/plugin/debug/ha_example.so
/usr/lib64/mysql/plugin/debug/innodb_engine.so
/usr/lib64/mysql/plugin/debug/keyring_file.so
/usr/lib64/mysql/plugin/debug/keyring_udf.so
/usr/lib64/mysql/plugin/debug/libmemcached.so
/usr/lib64/mysql/plugin/debug/libpluginmecab.so
/usr/lib64/mysql/plugin/debug/locking_service.so
/usr/lib64/mysql/plugin/debug/mypluglib.so
/usr/lib64/mysql/plugin/debug/mysql_no_login.so
/usr/lib64/mysql/plugin/debug/mysqlx.so
/usr/lib64/mysql/plugin/debug/rewrite_example.so
/usr/lib64/mysql/plugin/debug/rewriter.so
/usr/lib64/mysql/plugin/debug/semisync_master.so
/usr/lib64/mysql/plugin/debug/semisync_slave.so
/usr/lib64/mysql/plugin/debug/validate_password.so
/usr/lib64/mysql/plugin/debug/version_token.so
/usr/lib64/mysql/plugin/group_replication.so
/usr/lib64/mysql/plugin/ha_example.so
/usr/lib64/mysql/plugin/innodb_engine.so
/usr/lib64/mysql/plugin/keyring_file.so
/usr/lib64/mysql/plugin/keyring_udf.so
/usr/lib64/mysql/plugin/libmemcached.so
/usr/lib64/mysql/plugin/libpluginmecab.so
/usr/lib64/mysql/plugin/locking_service.so
/usr/lib64/mysql/plugin/mypluglib.so
/usr/lib64/mysql/plugin/mysql_no_login.so
/usr/lib64/mysql/plugin/mysqlx.so
/usr/lib64/mysql/plugin/rewrite_example.so
/usr/lib64/mysql/plugin/rewriter.so
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/validate_password.so
/usr/lib64/mysql/plugin/version_token.so
/usr/sbin/mysqld
/usr/sbin/mysqld-debug
/usr/share/doc/mysql-cluster-community-server-7.5.16
/usr/share/doc/mysql-cluster-community-server-7.5.16/ChangeLog
/usr/share/doc/mysql-cluster-community-server-7.5.16/INFO_BIN
/usr/share/doc/mysql-cluster-community-server-7.5.16/INFO_SRC
/usr/share/doc/mysql-cluster-community-server-7.5.16/LICENSE
/usr/share/doc/mysql-cluster-community-server-7.5.16/README
/usr/share/man/man1/innochecksum.1.gz
/usr/share/man/man1/lz4_decompress.1.gz
/usr/share/man/man1/my_print_defaults.1.gz
/usr/share/man/man1/myisam_ftdump.1.gz
/usr/share/man/man1/myisamchk.1.gz
/usr/share/man/man1/myisamlog.1.gz
/usr/share/man/man1/myisampack.1.gz
/usr/share/man/man1/mysql.server.1.gz
/usr/share/man/man1/mysql_install_db.1.gz
/usr/share/man/man1/mysql_plugin.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysql_ssl_rsa_setup.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/share/man/man1/mysql_upgrade.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man1/mysqlman.1.gz
/usr/share/man/man1/perror.1.gz
/usr/share/man/man1/replace.1.gz
/usr/share/man/man1/resolve_stack_dump.1.gz
/usr/share/man/man1/resolveip.1.gz
/usr/share/man/man1/zlib_decompress.1.gz
/usr/share/man/man8/mysqld.8.gz
/usr/share/mysql/dictionary.txt
/usr/share/mysql/fill_help_tables.sql
/usr/share/mysql/innodb_memcached_config.sql
/usr/share/mysql/install_rewriter.sql
/usr/share/mysql/magic
/usr/share/mysql/mysql-log-rotate
/usr/share/mysql/mysql_security_commands.sql
/usr/share/mysql/mysql_sys_schema.sql
/usr/share/mysql/mysql_system_tables.sql
/usr/share/mysql/mysql_system_tables_data.sql
/usr/share/mysql/mysql_test_data_timezone.sql
/usr/share/mysql/ndb_dist_priv.sql
/usr/share/mysql/uninstall_rewriter.sql
/var/lib/mysql
/var/lib/mysql-files
/var/lib/mysql-keyring
/var/run/mysqld

五台服务器都安装,所有服务器同一执行一遍。安装的时间有点久

管理节点创建配置目录(1台)test1

[root@localhost ~]# mkdir -p /usr/mysql-cluster/

管理节点安装management管理包

[root@localhost ~]# yum -y install mysql-cluster-community-management-server

数据节点主机都要安装数据节点的相关包(2台)teat2,test3

[root@localhost ~]# yum install -y mysql-cluster-community-data-node

搭建mysql集群

经过复杂的初始配置,现在开始搭建

1.在管理节点创建节点配置文件
[root@localhost ~]# vim /usr/mysql-cluster/config.ini #写入以下内容
[ndbd default]
NoOfReplicas=2 #数据写入数量。2表示两份,两个数据节点所以是2
DataMemory=200M #配置数据存储可使用的内存,越大越好
IndexMemory=100M #索引给100M可以配置大一点
[ndb_mgmd]
nodeid=1
datadir=/var/lib/mysql #管理结点的日志
HostName=10.10.100.39 #管理结点的IP地址。本机IP
######data node options: #存储结点
[ndbd]
HostName=10.10.100.28
DataDir=/var/lib/mysql #mysql数据存储路径
nodeid=2
[ndbd]
HostName=10.10.100.30
DataDir=/var/lib/mysql #mysql数据存储路径
nodeid=3
#SQL node options: #关于SQL结点
[mysqld]
HostName=10.10.100.31
nodeid=4
[mysqld]
HostName=10.10.100.34
nodeid=5
在这里插入图片描述
在这个文件里,分别给五个节点分配了ID,这有利于更好的管理和区分各个节点。当然,要是不指定,MySQL也会动态分配一个
配置数据节点/etc/my.cnf

[root@localhost ~]# mv /etc/my.cnf /etc/my.cnf.bak
[mysqld]
datadir=/var/lib/mysql						#mysql数据存储路径
ndbcluster     									#启动ndb引擎
ndb-connectstring=10.10.100.39				# 管理节点IP地址 
[mysqld_safe]  
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster] 
ndb-connectstring=10.10.100.39			#管理节点IP地址

两个管理节点的配置一样,直接复制粘贴即可

sql节点的配置

[root@localhost ~]# mv /etc/my.cnf /etc/my.cnf.bak
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
ndbcluster              
ndb-connectstring=10.10.100.39                    
[mysqld_safe]  
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=10.10.100.39

说明:数据节点和SQL结点配置文件区别 ,就多一行
数据结点有:datadir=/var/lib/mysql #mysql数据存储路径。
SQL节点上没有。

MySQL Cluster启动
初次启动命令以及用户密码更改调整:(请严格按照次序启动)
先启动:管理结点服务->数据结点服务->sql结点服务
关闭:关闭管理结点服务,关闭管理结点服务后,nbdb数据结点服务会自动关闭->手动把sql结点服务关了。
执行初次启动前请先确认 将两台机器的防火墙关闭(service iptables stop 或者 设定 防火墙端口可通,两个端口即通讯端口1186、数据端口3306 )

[root@localhost ~]# ndb_mgmd --ndb_nodeid=1 --initial -f /usr/mysql-cluster/config.ini 
MySQL Cluster Management Server mysql-5.7.28 ndb-7.5.16

查看端口

[root@localhost ~]# netstat  -antup | grep 1186
tcp        0      0 0.0.0.0:1186            0.0.0.0:*               LISTEN      16967/ndb_mgmd      
tcp        0      0 127.0.0.1:1186          127.0.0.1:41240         ESTABLISHED 16967/ndb_mgmd      
tcp        0      0 127.0.0.1:41240         127.0.0.1:1186          ESTABLISHED 16967/ndb_mgmd  

登录查看集群状态

[root@localhost ~]# 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 (not connected, accepting connect from 10.10.100.28)
id=3 (not connected, accepting connect from 10.10.100.30)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@10.10.100.39  (mysql-5.7.28 ndb-7.5.16)

[mysqld(API)]	2 node(s)
id=4 (not connected, accepting connect from 10.10.100.31)
id=5 (not connected, accepting connect from 10.10.100.34)

启动数据节点

[root@localhost ~]# ndbd --initial
2019-10-26 05:24:01 [ndbd] INFO     -- Angel connected to '10.10.100.39:1186'
2019-10-26 05:24:01 [ndbd] INFO     -- Angel allocated nodeid: 2

看到这样的消息,说明已经启动成功id=2
继续启动,另一个节点

[root@localhost ~]# ndbd --initial
2019-10-26 05:24:41 [ndbd] INFO     -- Angel connected to '10.10.100.39:1186'
2019-10-26 05:24:41 [ndbd] INFO     -- Angel allocated nodeid: 3

启动sql节点

[root@localhost ~]# systemctl start mysqld

第一次启动时间较长

全部启动完成,到管理节点查看状态

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2	@10.10.100.28  (mysql-5.7.28 ndb-7.5.16, Nodegroup: 0, *)
id=3	@10.10.100.30  (mysql-5.7.28 ndb-7.5.16, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@10.10.100.39  (mysql-5.7.28 ndb-7.5.16)

[mysqld(API)]	2 node(s)
id=4 (not connected, accepting connect from 10.10.100.31)
id=5 (not connected, accepting connect from 10.10.100.34)

数据同步实验测试
查看mysql root用户密码
修改sql节点的密码
[root@localhost ~]#grep password /var/log/messages

在my.cnf添加 validate-password=off 重启mysql
登录修改密码

mysql> set password for root@localhost =password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

两台做相同的操作。

在sql节点授权一个用户登录数据库

mysql> grant all privileges on *.* to cluster@"%" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

用外部机器做一个客户端连接sql节点

[root@localhost ~]# mysql -ucluster -p123456 -h10.10.100.34
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.28-ndb-7.5.16-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

注意:创建表的时候使用ndb引擎

mysql> create database db;
mysql> use db;
mysql> create table test(id int) engine=ndb;
mysql> insert into test values(1000);
mysql> select * from test;

登陆另一台sql节点查看

mysql -ucluster -p123456 -h 10.10.10.71
mysql> use db;
mysql> select * from test;

在管理节点

[root@localhost ~]# mysqladmin -uroot -p123456 shutdown
ndb_mgm> show 查看状态

关闭服务

[root@localhost ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> shutdown
Connected to Management Server at: localhost:1186
Node 2: Cluster shutdown initiated
Node 3: Cluster shutdown initiated
ndb_mgm> exit
ps -axu | grep  ndbd		#查看不到,说明数据节点已经被关

手动关闭SQL节点服务
在test4和test5sql节点上,手动关闭SQL节点服务

[root@localhost ~]#mysqladmin -uroot -p123456 shutdown

或者:方法二 kill掉

[root@localhost ~]#ps -axu | grep mysqld
[root@localhost ~]#kill -9 进程id

方法三

[root@localhost ~]#pkillall mysqld

再次启动,mysql集群启动
[root@localhost ~]# ndb_mgmd --ndb_nodeid=1 --initial -f /usr/mysql-cluster/config.ini
[root@localhost ~]# ndbd --initial
[root@localhost ~]# systemctl start mysqld

查看mysql 集群状态

[root@localhost ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show

下面异常处理,来自https://www.cnblogs.com/suncoolcat/p/3292104.html

MySQL Cluster测试过程中的错误汇总–ERROR 1296 (HY000)等等

– =============================================================================
【1】查看表空间数据文件大小
– =============================================================================

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";
+-----------------+------------+----------------+----------------+----------------+
| TABLESPACE_NAME | FILE_NAME  | TOTAL_MB       | FREE_MB        | EXTRA          |
+-----------------+------------+----------------+----------------+----------------+
| ts_1            | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=4 |
| ts_1            | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=5 |
| ts_1            | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=6 |
| ts_1            | data_2.dat | 10240.00000000 | 10162.00000000 | CLUSTER_NODE=7 |
| ts_1            | data_1.dat |   512.00000000 |    19.00000000 | CLUSTER_NODE=4 |
| ts_1            | data_1.dat |   512.00000000 |    19.00000000 | CLUSTER_NODE=5 |
| ts_1            | data_1.dat |   512.00000000 |    16.00000000 | CLUSTER_NODE=6 |
| ts_1            | data_1.dat |   512.00000000 |     0.00000000 | CLUSTER_NODE=7 |
+-----------------+------------+----------------+----------------+----------------+  
 
 

– ============================
【2】:建库报错

mysql> CREATE DATABASE zhang;
Query OK, 1 row affected, 2 warnings (0.01 sec)


mysql> 
mysql> 
mysql> 
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock  |
| Warning | 1296 | Got error 4009 'Cluster Failure' from Could not log query '%s' on other mysqld's |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

【ok】安全模式启动
/usr/local/mysql/bin/mysqld_safe &

– ==============================
【3】ERROR 1296 (HY000): Got error 157 ‘Unknown error code’ from NDBCLUSTER

mysql> use bg;
Database changed
mysql> 
mysql> 
mysql> show create table bgt1;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> show create table bgt1;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql> 
mysql> 
mysql> create table bgt2(id int,name varchar(20))engine=ndb;
ERROR 1005 (HY000): Can't create table 'bg.bgt2' (errno: 157)

【ok】之后关闭sql节点,重新启动,搞定了!

[root@banggo data]# /etc/rc.d/init.d/mysqld stop
Shutting down MySQL....                                    [确定]
[root@banggo data]# /etc/rc.d/init.d/mysqld start
Starting MySQL...                                          [确定]
[root@banggo data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.19-ndb-7.2.4-gpl MySQL Cluster Community Server (GPL)


Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


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


mysql> use bg;
Database changed
mysql> show tables;
+--------------+
| Tables_in_bg |
+--------------+
| bgt1         |
+--------------+
1 row in set (0.03 sec)


mysql> desc bgt1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

– =============================================================================
【4】ERROR 1296 (HY000): Got error 157 ‘Unknown error code’ from NDBCLUSTER
– =============================================================================

2012-07-18 09:58:15 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:16 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:16 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:17 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:17 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:18 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:18 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:19 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:19 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:20 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'


【ok】:在config.ini里面加空的sql节点,以便自动扩展s
[API]
[API]

– =============================================================================
【5】数据节点报错
– =============================================================================

2012-07-18 23:34:48 [ndbd] INFO     -- Start initiated (mysql-5.5.19 ndb-7.2.4)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
Adding 7164Mb to ZONE_LO (32896,229247)
Adding 4301Mb to ZONE_LO (262145,137607)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
WOPool::init(61, 9)
RWPool::init(22, 14)
2012-07-18 23:35:00 [ndbd] INFO     -- timerHandlingLab now: 12658118985 sent: 12658118306 diff: 679
2012-07-18 23:36:09 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=60029
2012-07-18 23:36:09 [ndbd] INFO     -- Watchdog: User time: 244  System time: 6855
2012-07-18 23:36:35 [ndbd] ALERT    -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.


【ok】:调整参数值
调小参数,内存超过了。

– =============================================================================
【6】建立表空间报错
– =============================================================================

mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 1024 M UNDO_BUFFER_SIZE 128 M ENGINE NDBCLUSTER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'M UNDO_BUFFER_SIZE 128 M ENGINE NDBCLUSTER' at line 1
mysql> 


【ok】M不识别
-- CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 536870912 UNDO_BUFFER_SIZE 67108864 ENGINE NDBCLUSTER;
mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 536870912 UNDO_BUFFER_SIZE 67108864 ENGINE NDBCLUSTER;
Query OK, 0 rows affected (27.95 sec)


-- CREATE TABLESPACE ts_1  ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 536870912 ENGINE NDBCLUSTER; 
mysql> CREATE TABLESPACE ts_1  ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 536870912 ENGINE NDBCLUSTER; 
Query OK, 0 rows affected (28.79 sec)

创建使用磁盘存储的表:

CREATE TABLE `bgtdisk` (    
  `Name` varchar(50) NOT NULL,
  `ProviderName` varchar(200) NOT NULL,
  PRIMARY KEY (`Name`)
) tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;

– =============================================================================
【7】配置报错
– =============================================================================

Caused by error 2353: 'Insufficent nodes for system restart(Restart error). Temporary error, restart node'.

– =============================================================================
【8】ERROR 1528 (HY000): Failed to create LOGFILE GROUP
– =============================================================================

mysql> CREATE LOGFILE GROUP lg_02 ADD UNDOFILE 'undo_02.log' INITIAL_SIZE 5368709120         UNDO_BUFFER_SIZE 67108864       ENGINE NDBCLUSTER; 
ERROR 1528 (HY000): Failed to create LOGFILE GROUP
mysql> 
mysql> 
mysql> 
mysql> show errors;
+-------+------+--------------------------------+
| Level | Code | Message                        |
+-------+------+--------------------------------+
| Error | 1528 | Failed to create LOGFILE GROUP |
+-------+------+--------------------------------+
1 row in set (0.00 sec)


mysql> 

解决办法:
原来现在的MYSQL只支持创建一个LOGFILE GROUP文件。
只有删掉原来的才可以创建新的。

– =============================================================================
【9】ERROR 1114 (HY000): The table ‘UserMvpbak’ is full
– =============================================================================

mysql> insert into UserMvpbak select * from UserMvp limit 800000,200000;
ERROR 1114 (HY000): The table 'UserMvpbak' is full
mysql> 
alter tablespace ts_1
         add datafile 'data_15.dat'
         initial_size 10737418240
           engine ndb;
  
【ok】原有的数据文件空间满了,需要增加新的数据文件   
mysql> alter tablespace ts_1
    ->      add datafile 'data_2.dat'
    ->      initial_size 10737418240
    ->      engine ndb;


Query OK, 0 rows affected (1 min 54.30 sec)


mysql>  insert into bguserdb.UserPoints_2012 select * from test.UserPoints_2012 limit 1200000,300000;
ERROR 1114 (HY000): The table 'UserPoints_2012' is full


alter tablespace ts_1
         add datafile 'data_16.dat'
         initial_size 10737418240
           engine ndb;




mysql> 

– =============================================================================
【10】ERROR 1114 (HY000): The table ‘UserMvpbak’ is full
– =============================================================================

mysql> insert into UserMvpbak select * from UserMvp limit 940000,3000000;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 


[ok]
mysql> replace into UserMvpbak select * from UserMvp limit 940000,800000;       
Query OK, 755105 rows affected (2 min 44.54 sec)
Records: 755105  Duplicates: 0  Warnings: 0

– =============================================================================
【11】Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11.
– =============================================================================

2012-08-15 12:35:38 [ndbd] INFO     -- Start initiated (mysql-5.5.25 ndb-7.2.7)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
2012-08-15 12:35:45 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Polling for Receive elapsed=159
2012-08-15 12:35:45 [ndbd] INFO     -- timerHandlingLab now: 498814955 sent: 498814782 diff: 173
2012-08-15 12:35:45 [ndbd] INFO     -- Watchdog: User time: 28  System time: 497
2012-08-15 12:35:47 [ndbd] INFO     -- timerHandlingLab now: 498816970 sent: 498816803 diff: 167
2012-08-15 12:35:49 [ndbd] INFO     -- Watchdog: User time: 39  System time: 741
2012-08-15 12:35:49 [ndbd] WARNING  -- Watchdog: Warning overslept 262 ms, expected 100 ms.
2012-08-15 12:35:49 [ndbd] INFO     -- timerHandlingLab now: 498818963 sent: 498818779 diff: 184
Adding 5201Mb to ZONE_LO (1152,166408)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
2012-08-15 12:35:56 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=6002
2012-08-15 12:35:56 [ndbd] INFO     -- Watchdog: User time: 61  System time: 1179
2012-08-15 12:36:02 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=12006
2012-08-15 12:36:02 [ndbd] INFO     -- Watchdog: User time: 64  System time: 1763
2012-08-15 12:36:08 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=18030
2012-08-15 12:36:08 [ndbd] INFO     -- Watchdog: User time: 70  System time: 1957
2012-08-15 12:36:12 [ndbd] ALERT    -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11.




[ok] 注释如下三个参数,启动ok!
#MaxNoOfConcurrentTransactions=100000
#MaxNoOfConcurrentOperations=10000000
#MaxNoOfLocalOperations=11000000

– =============================================================================
【12】启动管理节点报错
– =============================================================================

[root@banggo mysql-cluster]# ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini --reload
MySQL Cluster Management Server mysql-5.5.25 ndb-7.2.7
2012-08-15 21:36:54 [MgmtSrvr] ERROR    -- at line 18: Illegal value 128 for parameter LockPagesInMainMemory.
Legal values are between 0 and 2
2012-08-15 21:36:54 [MgmtSrvr] ERROR    -- at line 18: Could not parse name-value pair in config file.
2012-08-15 21:36:54 [MgmtSrvr] ERROR    -- Could not load configuration from '/usr/local/mysql/cluster-conf/config.ini'
2012-08-15 21:36:54 [MgmtSrvr] ERROR    -- Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid=<nodeid> on command line
[root@banggo mysql-cluster]# 
[root@banggo mysql-cluster]# 


【ok】LockPagesInMainMemory值设置太大了!改成默认的1吧。

– =============================================================================
【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_1.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.MvpCard;
  alter table bguserdb.mcbak tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
  insert into bguserdb.mcbak select * from test.MvpCard;
  
  CREATE TABLESPACE ts_1
       ADD DATAFILE 'data_10.dat‘ USE LOGFILE GROUP lg_1 INITIAL_SIZE 32212254720
  EXTENT_SIZE 33554432 ENGINE NDBCLUSTER;

– =============================================================================
【14】 1528 错误
– =============================================================================

mysql> insert into bguserdb.MvpCard select * from test.MvpCard;
ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER
mysql> 


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

– =============================================================================
【15】 1005 错误
– =============================================================================

mysql>    create table bguserdb.mcbak like test.MvpCard;
Query OK, 0 rows affected (0.07 sec)


mysql>    alter table bguserdb.mcbak tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'bguserdb.#sql-14ab_2' (errno: 140)
mysql> show errors;
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Error | 1005 | Can't create table 'bguserdb.#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 'bguserdb.#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 bguserdb.MvpCard tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
insert into bguserdb.MvpCard select * from test.MvpCard limit 0,100000;

– =============================================================================
【16】 1005 错误
– =============================================================================

mysql> alter table order_infobak TABLESPACE ts_1 STORAGE DISK  ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'openshop.#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 'openshop.#sql-711_3' (errno: 851)                                                               |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


CREATE TABLE `order_xxxx` (
  `order_sn` varchar(20) NOT NULL DEFAULT '' COMMENT '订单编号,唯一键',
  `xx` tinyint(3) NOT NULL DEFAULT '1' COMMENT '交易类型 1:款到发货 2:货到付款 3:担保交易',
  `xx` tinyint(1) DEFAULT '0' COMMENT '订单类型 0,正常订单 1,补发订单  2,换货订单',
  `xxxxxx` varchar(30) DEFAULT '' COMMENT '关联换货单原订单号',
  `xxxxxx` varchar(30) NOT NULL DEFAULT '' COMMENT '关联退单编号(该项目用来存放换货单时生成的退货单所对应的退单号)',
  `xxxx` varchar(30) DEFAULT NULL COMMENT '关联退款单订单号(该项目用来存放换货时生成的退款单的对应退单号)',
  `user_id` varchar(60) NOT NULL DEFAULT '' COMMENT '用户ID,关联user_users表的user_id',
  `user_name` varchar(60) NOT NULL DEFAULT '' COMMENT '用户名',
  `use_level` int(6) DEFAULT '0' COMMENT '下单时会员等级',
  `xx` int(6) DEFAULT '0' COMMENT '下单时的淘宝拍拍会员等级',
  `aa` varchar(60) NOT NULL DEFAULT '' COMMENT '收货人的姓名,用户页面填写,默认取值于表user_address',
  `order_status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '订单状态  (0,未确认;1,已确认;2,已取消;3,无效;4,退货;5,锁定;6,解锁;7,完成;8,拒收;9,已合并;10,已拆分;)',
  `aa` tinyint(3) NOT NULL DEFAULT '0' COMMENT '支付总状态(0,未付款;1,部分付款;2,已付款;3,已结算)',
  `cc` tinyint(3) NOT NULL DEFAULT '0' COMMENT '发货总状态(0,未发货;1,备货中;2,部分发货;3,已发货;4,部分收货;5,客户已收货)',
  `cc` int(11) NOT NULL DEFAULT '0' COMMENT '订单锁定状态(0,未锁定;1,已锁定)',
  `ee` tinyint(1) NOT NULL DEFAULT '0' COMMENT '处理状态 (0 正常  1 挂起)',
  `tt` tinyint(1) NOT NULL DEFAULT '0' COMMENT '问题单状态 (0 否  1 是)',
  `notice_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '通知收款状态 (0 否  1 是)',
  `timeout_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否超时单 (0 正常  1 超时)',
  `depot_status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '分仓发货状态(0,未分仓 1,已分仓未通知 2,已分仓已通知)',
  `return_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0未退货、1部分退货申请中、2退货申请中、3已部分退货、4已退货',
  `refund_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0未退款、1部分退款申请中、2退款申请中、3已部分退款、4已退款',
  `is_order_print` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否要发票',
  `money_treatment_type` tinyint(1) DEFAULT '1' COMMENT '钱款处理方式:1.补款 2,退款 3,不补不退',
  `order_from` varchar(20) NOT NULL DEFAULT '' COMMENT '订单来源',
  `merge_from` varchar(255) DEFAULT NULL COMMENT '合并来源',
  `split_to` varchar(255) DEFAULT NULL COMMENT '订单拆分为',
  `split_from` varchar(255) DEFAULT '' COMMENT '由XX订单拆分生成',
  `add_time` datetime DEFAULT NULL COMMENT '订单生成时间',
  `confirm_time` datetime DEFAULT NULL COMMENT '订单确认时间',
  `finish_time` datetime DEFAULT NULL COMMENT '订单完成时间',
  `clear_time` datetime DEFAULT NULL COMMENT '订单结算时间',
  `question_time` datetime DEFAULT NULL COMMENT '设为问题单时间',
  `lock_time` datetime DEFAULT NULL COMMENT '订单锁定时间',
  `update_time` datetime DEFAULT NULL COMMENT '订单最后更新时间',
  `notice_time` datetime DEFAULT NULL COMMENT '通知收款时间',
  `order_out_sn` varchar(255) NOT NULL DEFAULT '' COMMENT '外部交易号',
  `beneficiary_id` mediumint(8) NOT NULL DEFAULT '0' COMMENT '受益人ID,如果开启提成功能',
  `from_ad` smallint(5) NOT NULL DEFAULT '0' COMMENT '订单由某广告带来的广告id,应该取值于ad',
  `pr_ids` varchar(255) NOT NULL DEFAULT '' COMMENT '参加了哪些促销',
  `pr_name` varchar(255) NOT NULL DEFAULT '' COMMENT '参加促销的名字(多个)',
  `referer` varchar(255) NOT NULL DEFAULT '' COMMENT '订单的来源媒体',
  `how_oos` varchar(120) NOT NULL DEFAULT '' COMMENT '缺货处理方式,等待所有商品备齐后再发; 取消订单;与店主协商',
  `hhh` varchar(30) NOT NULL DEFAULT '' COMMENT '通过活动购买的商品的代号;GROUP_BUY是团购AUCTION,是拍卖;SNATCH,夺宝奇兵;正常普通产品该处为空',
  `extension_id` mediumint(8) NOT NULL DEFAULT '0' COMMENT '通过活动购买的物品的id,取值goods_activity;如果是正常普通商品,该处为0',
  `is_separate` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0,未分成或等待分成;1,已分成;2,取消分成;',
  `parent_id` mediumint(8) NOT NULL DEFAULT '0' COMMENT '能获得推荐分成的用户id,id取值于表users',
  `outlet_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '代销类型(0无;1是代销;2是CPS)',
  `jjj` varchar(250) DEFAULT NULL COMMENT 'CPS来源',
  `inv_payee` varchar(120) NOT NULL DEFAULT '' COMMENT '发票抬头,用户页面填写',
  `inv_content` varchar(120) NOT NULL DEFAULT '' COMMENT '发票内容,用户页面选择,取值shop_config的code字段的值为invoice_content的value',
  `inv_type` varchar(60) NOT NULL DEFAULT '' COMMENT '发票类型,用户页面选择,取值shop_config的code字段的值为invoice_type的value',
  `tax` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '发票税额',
  `invoice_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '发票状态',
  `delivery_station_id` int(10) NOT NULL DEFAULT '0' COMMENT '发票ID',

  ......

  `goods_count` int(11) NOT NULL DEFAULT '0' COMMENT '订单商品总数',

  `discount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '折扣金额',
  `confirm_lasttime` datetime DEFAULT NULL COMMENT '订单确认期限',
  `complete_lasttime` datetime DEFAULT NULL COMMENT '订单完成最后期限',
  `settled_lasttime` datetime DEFAULT NULL COMMENT '结算最后期限',
  `all_process_lasttime` datetime DEFAULT NULL COMMENT '订单全程处理期限',
  `urgent_level` tinyint(3) NOT NULL DEFAULT '0' COMMENT '订单加急等级',
  `period_series` int(8) NOT NULL DEFAULT '0' COMMENT '周期系列ID',
  `to_buyer` varchar(255) NOT NULL COMMENT '商家给客户的留言,当该字段有值时可以在订单查询看到',
  `chased_or_not` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已追单(0 否  1 是)',
  `is_agent` tinyint(2) DEFAULT '-1' COMMENT '是否代理换货',
  `is_update` tinyint(1) DEFAULT '0' COMMENT '是否更新(ERP用)',
  `update_type` varchar(255) DEFAULT NULL COMMENT '更新类型(ERP用)',
  `last_update_time` datetime DEFAULT NULL COMMENT '最后更新时间(ERP用)',
  `is_return` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已退款(暂不使用)',
  `return_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额(暂不使用)',
  `integral_money` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '使用积分金额(暂不使用)',
  `wait_pay` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否等待买家付款订单(0 否  1 是)(暂不使用)',
  `tel` varchar(60) NOT NULL DEFAULT '' COMMENT '收货人的电话号码',
  `mobile` varchar(60) NOT NULL DEFAULT '' COMMENT '收货人的手机号码',
  `email` varchar(60) NOT NULL DEFAULT '' COMMENT '收货人的电子邮件',
  `total_market_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `real_shipping_total_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '真实的总快递费用,不免邮',
  `cancel_code` varchar(20) NOT NULL DEFAULT '' COMMENT '取消原因编码',
  `cancel_reason` varchar(20) NOT NULL DEFAULT '' COMMENT '取消原因名称',
  `order_category` int(11) DEFAULT '1' COMMENT '订单种类 1:零售 2:物资领用 3:其它出库',
  `reason` varchar(5) NOT NULL DEFAULT 'S' COMMENT '原因: S(零售) ;J (物资领用 ); T(其它出库 );',
 .......
  PRIMARY KEY (`order_sn`),
  KEY `user_id` (`user_id`),
  KEY `order_from` (`order_from`),
  KEY `order_out_sn` (`order_out_sn`),
  KEY `order_status` (`order_status`),
  KEY `addtime` (`add_time`),
  KEY `is_update` (`is_update`),
  KEY `relating_return_sn` (`relating_return_sn`),
  KEY `confirm_lasttime` (`confirm_lasttime`),
  KEY `complete_lasttime` (`complete_lasttime`),
  KEY `settled_lasttime` (`settled_lasttime`),
  KEY `all_process_lasttime` (`all_process_lasttime`),
  KEY `in_consignee` (`consignee`),
  KEY `in_last_update_time` (`last_update_time`),
  KEY `in_source_code` (`source_code`)
) TABLESPACE ts_1 STORAGE DISK  ENGINE=ndbcluster DEFAULT CHARSET=utf8 COMMENT='信息表';

ERROR 1005 (HY000): Can’t create table ‘openshop.order_info’ (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 'openshop.order_info' (errno: 851)                                                               |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[ok] ndb对表的varchar总长度有限制。

– =============================================================================
【17】 1297 错误
– =============================================================================

mysql> alter table ERP_BULK_ETL_FLAGE 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> 
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]
1 增加数据文件,数据文件空间可能不够了。 
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;
2 增加undo文件
alter LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_21.log'  ENGINE NDBCLUSTER;


-- =============================================================================
【18】 1297 错误
-- =============================================================================
mysql> alter table account_adjust_users 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> 

– =============================================================================
【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'.


【ok1】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节点,执行检查数据,my god,原来的数据文件都无法显示了。
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> 


【ok2】第一次ndbd不成功,之后,再重新启动以一次ndbd,

– =============================================================================
【20】 Out of undo space
– =============================================================================

mysql> insert into MvpCard select * from zzbak_MvpCard 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)


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;
mysql> insert into MvpCard select * from zzbak_MvpCard limit 0,10000;
Query OK, 10000 rows affected (0.80 sec)
Records: 10000  Duplicates: 0  Warnings: 0

– =============================================================================
【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,不会报异常信息了。

– =============================================================================
【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 重新启动起来

– =============================================================================
【22】 管理台老是报警,诡异
– =============================================================================

ndb_mgm> Node 7: Data usage increased to 80%(64016 32K pages of total 80000)
Node 4: Data usage increased to 80%(64007 32K pages of total 80000)
Node 5: Data usage increased to 80%(64014 32K pages of total 80000)
Node 6: Data usage increased to 80%(64001 32K pages of total 80000)
Node 4: Data usage increased to 90%(72005 32K pages of total 80000)
Node 5: Data usage increased to 90%(72014 32K pages of total 80000)
Node 7: Data usage increased to 90%(72010 32K pages of total 80000)
Node 6: Data usage increased to 90%(72009 32K pages of total 80000)
Node 6: Data usage decreased to 89%(71772 32K pages of total 80000)
Node 7: Data usage decreased to 89%(71271 32K pages of total 80000)
Node 4: Data usage decreased to 89%(71215 32K pages of total 80000)
Node 5: Data usage decreased to 88%(70607 32K pages of total 80000)
Node 5: Data usage decreased to 79%(63850 32K pages of total 80000)
Node 7: Data usage decreased to 79%(63673 32K pages of total 80000)
Node 4: Data usage decreased to 78%(62947 32K pages of total 80000)
Node 6: Data usage decreased to 78%(62932 32K pages of total 80000)

– =============================================================================
【23】 ERROR 1297 (HY000)
– =============================================================================

mysql> insert into bguserdb.UserPoints_2012 select * from test.UserPoints_2012 limit 1200000,10000; 
ERROR 1297 (HY000): Got temporary error 899 'Rowid already allocated' from NDBCLUSTER
mysql> 


【ok】网上都说需要调大datamemory,但是我的是磁盘表,应该不是这个原因,不得已只有restart cluster了试试了,还好搞定了!
mysql>  replace into bguserdb.UserPoints_2012 select * from test.UserPoints_2012 limit 1200000,300000;
Query OK, 300000 rows affected (59.97 sec)
Records: 300000  Duplicates: 0  Warnings: 0

– =============================================================================
【24】 ERROR – c_gcp_list.seize() failed:
– =============================================================================

2012-08-19 17:00:24 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/6) ref: 0fa2000e from: 0fa2000e
2012-08-19 17:00:24 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/6) ref: 0fa20010 from: 0fa20010
2012-08-19 17:00:24 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/6) ref: 0fa2000a from: 0fa2000a
2012-08-19 17:00:25 [ndbd] ERROR    -- c_gcp_list.seize() failed: gci: 1002110359437319 nodes: 0000000000000000000000000000000000000000000000000000000000015400
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/7) ref: 0fa20010 from: 0fa20010
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/7) ref: 0fa2000e from: 0fa2000e
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/7) ref: 0fa2000a from: 0fa2000a
2012-08-19 17:00:25 [ndbd] ERROR    -- c_gcp_list.seize() failed: gci: 1002110359437320 nodes: 0000000000000000000000000000000000000000000000000000000000015400
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/8) ref: 0fa2000e from: 0fa2000e
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/8) ref: 0fa2000a from: 0fa2000a
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/8) ref: 0fa20010 from: 0fa20010
2012-08-19 17:00:25 [ndbd] ERROR    -- c_gcp_list.seize() failed: gci: 1002110359437321 nodes: 0000000000000000000000000000000000000000000000000000000000015400
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/9) ref: 0fa20010 from: 0fa20010
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/9) ref: 0fa2000e from: 0fa2000e
2012-08-19 17:00:25 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/9) ref: 0fa2000a from: 0fa2000a
2012-08-19 17:00:25 [ndbd] ERROR    -- c_gcp_list.seize() failed: gci: 1002114654404608 nodes: 0000000000000000000000000000000000000000000000000000000000015400


2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/1) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/2) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/3) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/4) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/5) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/6) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/7) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/8) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233322/9) ref: 0fa2000c from: 0fa2000c
2012-08-19 17:01:05 [ndbd] WARNING  -- ACK wo/ gcp record (gci: 233323/0) ref: 0fa2000c from: 0fa2000c

– =============================================================================
【24】 ERROR 1114 (HY000): The table ‘card_coin’ is full
– =============================================================================

mysql> insert into bgcarddb.card_coin select * from zbakbgcarddb.card_coin limit 0,300000;


ERROR 1114 (HY000): The table 'card_coin' is full
mysql> 
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                        |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 902 'Out of memory in Ndb Kernel, ordered index data (increase DataMemory)' from NDB |
| Error   | 1114 | The table 'card_coin' is full                                                                  |
+---------+------+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


mysql> 


导入大量数据的时候或者执行大量的insert或者update操作的时候,经常会出现如下报错:
ERROR 1297 (HY000) at line 1: Got temporary error 1218 'Send Buffers overloaded in NDB kernel' from NDBCLUSTER

这个报错的原因有几个,

第一、是参数SendBufferMemory设置过小,

第二、和mysqld的二进制日志有关系,

第三、和网络环境有关系

解决办法:查看网络环境是否OK ,是否出现双工模式部队,是否都是千兆网或者百兆网,加大SendBufferMemory的设置,一般2M就够了,
设置TotalSendBufferMemory = 256M,最后如果还是出现如此报错,建议关闭mysql的二进制日志,在my.cnf中注释掉相关的参数就可以了,比如:
#log-bin=mysql-bin

– =============================================================================
【25】 修改了config.in之后,data node 启动,需要 ndbd --initial重新加载之后,
原有的*.dat数据文件无效,启动不起来,sql节点启动之后,show tables; 原来的磁盘表都不见了,诡异啊!
– =============================================================================

1 修改config.inf
2 ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini --reload 重新加载管理节点
3 ndbd --initial  重新启动data node
4 service mysqld start
5 use bu; show tables; 原来的表不存在了,丢失了。


看的文档是说,原有的数据文件加载不了,我想问的是:
1 如果我已经导入了很多数据,这个时候发现自己的配置参数不合理,我要修改配置参数,能不能在线修改并生效。
2 如果1失败,我能否在重新启动管理节点之后,启动data node的时候,能加载进去以前的数据呢?

– =============================================================================
【25】 批量insert 500W记录报错
– =============================================================================

mysql> insert into bgcarddb.card_package_20120120 select * from zbakbgcarddb.card_package_20120120 ;
ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
mysql> 


mysql> insert into bgcarddb.card_package select * from zbakbgcarddb.card_package limit 0,5000000;
ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
mysql> 

– =============================================================================
【26】 批量insert 500W记录报错
– =============================================================================

2012-08-26 00:37:25 [ndbd] INFO     -- findNeighbours from: 4861 old (left: 5 right: 5) new (65535 65535)
2012-08-26 00:37:25 [ndbd] INFO     -- Watchdog: User time: 976564  System time: 1189718
2012-08-26 00:37:25 [ndbd] WARNING  -- Watchdog: Warning overslept 410 ms, expected 100 ms.
2012-08-26 00:37:25 [ndbd] INFO     -- Arbitrator decided to shutdown this node
2012-08-26 00:37:25 [ndbd] INFO     -- QMGR (Line: 5975) 0x00000002
2012-08-26 00:37:25 [ndbd] INFO     -- Error handler shutting down system
2012-08-26 00:37:25 [ndbd] INFO     -- Error handler shutdown completed - exiting
2012-08-26 00:37:38 [ndbd] ALERT    -- Node 6: 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'.


[ok]:google之,大部分的方案都是 restart data node。


而且一般的ndbd启动无效,需要ndbd --initial 重新加载才行。

– =============================================================================
【27】 导入数据错误;
– =============================================================================

 CREATE TABLE `pp_order` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` varchar(255) DEFAULT NULL,
  `itemCode` varchar(255) DEFAULT NULL,
  `itemLocalCode` varchar(255) DEFAULT NULL,
  `stockLocalCode` varchar(255) DEFAULT NULL,
  `itemDetailLink` varchar(255) DEFAULT NULL,
  `itemName` varchar(255) DEFAULT NULL,
  `itemPicLink` varchar(255) DEFAULT NULL,
  `itemRetailPrice` varchar(255) DEFAULT NULL,
  `itemDealPrice` varchar(255) DEFAULT NULL,
  `stockAttr` varchar(255) DEFAULT NULL,
  `itemAdjustPrice` varchar(255) DEFAULT NULL,
  `itemDealCount` varchar(255) DEFAULT NULL,
  `itemDealState` varchar(255) DEFAULT NULL,
  `account` varchar(255) DEFAULT NULL,
  `itemFlag` varchar(255) DEFAULT NULL,
  `refundState` varchar(255) DEFAULT NULL,
  `refundStateDesc` varchar(255) DEFAULT NULL,
  `availableAction` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `dealCode` (`pid`(30))
) TABLESPACE ts_1 STORAGE DISK  DEFAULT CHARSET=utf8;
mysql> ERROR 1005 (HY000): Can't create table 'bgapidb.pp_order' (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 'bgapidb.pp_order' (errno: 851)                                                                  |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[OK],磁盘表的varchar列的占据的总长度不能多于8052B,所以合理设计字段的长度。搞定

mysql>  CREATE TABLE bgapidb.`pp_order` (
    ->   `Id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `pid` VARCHAR(30) DEFAULT NULL,
    ->   `itemCode` VARCHAR(40) DEFAULT NULL,
    ->   `itemLocalCode` VARCHAR(16) DEFAULT NULL,
    ->   `stockLocalCode` VARCHAR(16) DEFAULT NULL,
    ->   `itemDetailLink` VARCHAR(100) DEFAULT NULL,
    ->   `itemName` VARCHAR(100) DEFAULT NULL,
    ->   `itemPicLink` VARCHAR(100) DEFAULT NULL,
    ->   `itemRetailPrice` VARCHAR(10) DEFAULT NULL,
    ->   `itemDealPrice` VARCHAR(10) DEFAULT NULL,
    ->   `stockAttr` VARCHAR(120) DEFAULT NULL,
    ->   `itemAdjustPrice` VARCHAR(10) DEFAULT NULL,
    ->   `itemDealCount` VARCHAR(10) DEFAULT NULL,
    ->   `itemDealState` VARCHAR(30) DEFAULT NULL,
    ->   `account` VARCHAR(10) DEFAULT NULL,
    ->   `itemFlag` VARCHAR(10) DEFAULT NULL,
    ->   `refundState` VARCHAR(32) DEFAULT NULL,
    ->   `refundStateDesc` VARCHAR(32) DEFAULT NULL,
    ->   `availableAction` VARCHAR(60) DEFAULT NULL,
    ->   PRIMARY KEY (`Id`),
    ->   KEY `dealCode` (`pid`(30))
    -> ) TABLESPACE ts_1 STORAGE DISK  ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.24 sec)

– =============================================================================
【28】 Got temporary error 4010
– =============================================================================

mysql> insert into bgapidb.top_deliverysend_queue select * from zbakapi.top_deliverysend_queue limit 0,200000;  
ERROR 1297 (HY000): Got temporary error 4010 'Node failure caused abort of transaction' from NDBCLUSTER
【ok】内存不足,清空掉一些无用的大表,释放内存从60%降到30%
ndb_mgm> all report memory;
Node 4: Data usage is 30%(39215 32K pages of total 128000)
Node 4: Index usage is 16%(18575 8K pages of total 115232)
Node 5: Data usage is 30%(39196 32K pages of total 128000)
Node 5: Index usage is 16%(18575 8K pages of total 115232)
Node 6: Data usage is 30%(39489 32K pages of total 128000)
Node 6: Index usage is 16%(18554 8K pages of total 115232)








CREATE TABLE `top_trade` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_nick` varchar(255) DEFAULT NULL,
  `buyer_nick` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `created` varchar(20) DEFAULT NULL,
  `iid` varchar(60) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `pic_path` varchar(255) DEFAULT NULL,
  `num` int(5) DEFAULT NULL,
  `tid` varchar(20) DEFAULT NULL,
  `buyer_message` varchar(255) DEFAULT NULL,
  `sid` varchar(255) DEFAULT NULL,
  `shipping_type` varchar(50) DEFAULT NULL,
  `alipay_no` varchar(60) DEFAULT NULL,
  `payment` decimal(10,2) DEFAULT NULL,
  `discount_fee` decimal(10,2) DEFAULT NULL,
  `adjust_fee` decimal(10,2) DEFAULT NULL,
  `snapshot_url` varchar(255) DEFAULT NULL,
  `snapshot` varchar(255) DEFAULT NULL,
  `status` varchar(60) DEFAULT NULL,
  `seller_rate` varchar(20) DEFAULT NULL,
  `buyer_rate` varchar(20) DEFAULT NULL,
  `buyer_memo` varchar(255) DEFAULT NULL,
  `seller_memo` varchar(255) DEFAULT NULL,
  `pay_time` varchar(20) DEFAULT NULL,
  `end_time` varchar(20) DEFAULT NULL,
  `modified` varchar(20) DEFAULT NULL,
  `buyer_obtain_point_fee` int(10) DEFAULT NULL,
  `point_fee` int(10) DEFAULT NULL,
  `real_point_fee` int(10) DEFAULT NULL,
  `total_fee` decimal(10,2) DEFAULT NULL,
  `post_fee` decimal(10,2) DEFAULT NULL,
  `buyer_alipay_no` varchar(100) DEFAULT NULL,
  `receiver_name` varchar(100) DEFAULT NULL,
  `receiver_state` varchar(60) DEFAULT NULL,
  `receiver_city` varchar(60) DEFAULT NULL,
  `receiver_district` varchar(80) DEFAULT NULL,
  `receiver_address` varchar(255) DEFAULT NULL,
  `receiver_zip` varchar(10) DEFAULT NULL,
  `receiver_mobile` varchar(100) DEFAULT NULL,
  `receiver_phone` varchar(100) DEFAULT NULL,
  `consign_time` varchar(20) DEFAULT NULL,
  `buyer_email` varchar(255) DEFAULT NULL,
  `commission_fee` decimal(10,2) DEFAULT NULL,
  `seller_alipay_no` varchar(100) DEFAULT NULL,
  `seller_mobile` varchar(50) DEFAULT NULL,
  `seller_phone` varchar(50) DEFAULT NULL,
  `seller_name` varchar(30) DEFAULT NULL,
  `seller_email` varchar(60) DEFAULT NULL,
  `available_confirm_fee` decimal(10,2) DEFAULT NULL,
  `has_post_fee` varchar(20) DEFAULT NULL,
  `received_payment` decimal(10,2) DEFAULT NULL,
  `cod_fee` decimal(10,2) DEFAULT NULL,
  `timeout_action_time` varchar(20) DEFAULT NULL,
  `is_3d` varchar(20) DEFAULT NULL,
  `is_lgtype` tinyint(3) DEFAULT NULL COMMENT '是否需要物流宝发货标识',
  `is_brand_sale` tinyint(3) DEFAULT NULL COMMENT '是否品牌特卖订单',
  `is_force_wlb` tinyint(3) DEFAULT NULL COMMENT '是否强制使用物流宝发货',
  `outer_order_sn` varchar(255) DEFAULT NULL,
  `is_change` tinyint(3) NOT NULL DEFAULT '1',
  `is_os` tinyint(3) NOT NULL DEFAULT '0',
  `channel_code` varchar(20) NOT NULL DEFAULT '',
  `channel_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `tid` (`tid`),
  KEY `change_chl_status` (`channel_code`(10),`status`(24),`is_change`),
  KEY `is_os` (`is_os`),
  KEY `created` (`created`)
) TABLESPACE ts_1 STORAGE DISK  ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 14000. You have to change some columns to TEXT or BLOBs

– =============================================================================
【29】 Got temporary error 899 ‘Rowid already allocated’ from NDB
– =============================================================================

mysql> create table bglogdb.t1(id int, n varchar(30)) TABLESPACE ts_1 STORAGE DISK  ENGINE=ndbcluster;
ERROR 1005 (HY000): Can't create table 'bglogdb.t1' (errno: 899)
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1297 | Got temporary error 899 'Rowid already allocated' from NDB |
| Error   | 1005 | Can't create table 'bglogdb.t1' (errno: 899)               |
+---------+------+------------------------------------------------------------+
2 rows in set (0.00 sec)


【ok】,google之,没有找到办法,无奈从新启动cluster系统,建表成功。

– =============================================================================
【30】 Got temporary error 899 ‘Rowid already allocated’ from NDB
– =============================================================================

CREATE TABLE bglogdb.`nc_api_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `ymd` datetime NOT NULL COMMENT '请求日期',
  `request_url` varchar(200) NOT NULL COMMENT '请求URL',
  `post_data` text COMMENT '提交参数',
  `return_data` text COMMENT '返回参数',
  `ipaddr` varchar(20) DEFAULT NULL COMMENT '发送请求IP',
  `status` char(3) DEFAULT NULL COMMENT '请求状态:0  已请求 返回异常     1  已请求 正常返回  ',
  `created` datetime DEFAULT NULL COMMENT '该记录创建时间',
  `modified` datetime DEFAULT NULL COMMENT '记录修改时间',
  `api_type` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster  DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'bglogdb.nc_api_logs' (errno: 708)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                  |
+---------+------+------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 708 'No more attribute metadata records (increase MaxNoOfAttributes)' from NDB |
| Error   | 1005 | Can't create table 'bglogdb.nc_api_logs' (errno: 708)                                    |
+---------+------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


【ok】,将MaxNoOfAttributes值增加到24760,搞定。

– ==================================================================================================================
【31】 ERROR : Got temporary error 1234 ‘REDO log files overloaded (increase disk hardware)’ from NDBCLUSTER
– ==================================================================================================================

[root@banggo ~]# time mysqlslap --engine=ndbcluster --auto-generate-sql-write-number=100000 --auto-generate-sql-guid-primary  --concurrency=200,400,600 --number-of-queries=1000000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=ndb –-commint=1000 --auto-generate-sql-load-type=write   
mysqlslap: Cannot run query INSERT INTO t1 VALUES (uuid(),412380163,1002100976,271523606,1458696239,1603135659,696200156,1997806484,42367420,1574962246,
1628033484,'5QF0PnnE7cfW1qKl2J245d0XcXRDB6THOlHqOwwXZc1aQDKLOEPTrHsJgCOsIu1aXAGv6DQXpJ67PISpOlJ723QbFrRPlK08WG3STL0IYyIxGmnYOyfI15t9oKaPYa',
'Z2jcNCpNNMLhJTJy1jog2idvrQ8PrgsPbbt
ynbD2xaj833zwmncgTDcKXkmNGWgr5Zpk82nxSM6NH5JwkncEtCaOoCf5iJwfteOtY3r1PpGjmSfWF7k0KBP0F45yO2','T9Ytu4olxEQEpCiKjpRRqfW7gm3lcJf5SBrF8f05tRBaXtuEbEaPt4OZiRJI
nqO727M2mF7YaJ654tCX7mw0iCQHgdbJRrQLyhNdwN9Mi7ResXAz9yssBI1jnc2EKK','SAtrnhEo3ooNJguTY4JOoMap5LHzRdCvo5fRnlGiQ45lcs68wPYcoyPlBilfCpQ0uxJZjR89X5m8xs8lHhLxX
ejjLKEP9YHvWcL54T6RasROkc9t9Nrg1QFEeLffuw','QGZnDT8JwbBOrns0g1NNZHhpXJAByw7P4Y4H15cygfEXQ6NzzfEYwwDJ7GW6CWNHuJQwO3mwasJtqbkp6yL2uRD2jk0v6F40QXojQQX0Irl8IT
xOSBIEeX8L788DF5','6acmH2DouXXgYp9T5TYNk2nsFutO00LyaxkrrxgdL3HttJEqofEOh1Hxn2Dn2addNMK6BsjYwtPhCYXsBdHkWaZISmxWCbzRpKZ04HZ0AqZEE56PiNADpbNHMKgQnf',
'ucrJdwc2oekMszRzG1EI64uLD8itaLPKyGgRC8Ttf5FX4aMDRTXQPPnePTOsjpCh5k0AlL3OQAnNk2c3XzTwHHQieqaLfnulNun8FiOas4xS6PNTOtPh2jOgayu5dg',
'itbTtiBIGvE5FKWfpvY6Saps6FjsLCZ3x0Zrac1jOFofbBIGyiMQG3BFJmzgqOjoGGXrlOQ9fEo6H7M7pcQyfT5y7wexLMddW8K7YmG3tx9AwNAdrcRN5YLCLs1i6f',
'BSfYc4As73h96dOGFGsifp7stSku2iZDB5dn9oXGr5Pxiq5xyxgTefDX7yQ9YrMjwsM5gmMXrfLZLQNBGvgmauIhkci8RwkGo6LKkbkR75tSNGYX34IdySAa4kjXgR',
'nLP1h2SPTbMEwmWS9PYR2Nn4prgOFLrui9vF3OhYr3nnh9FrbgATvyQkh69wJsjt1e9wuqTel8PR9ZIcDKy8IGksEtoamXRf2ScoAxQwxkFyHPRMlPMwZ6onsSDesh') 
ERROR : Got temporary error 1234 'REDO log files overloaded (increase disk hardware)' from NDBCLUSTER


【ok】google很久,增加了那2个值,但是启动起来还是报很多错误,后来想到一个外招,换个库名字,--create-schema=ndb12,搞定。

– ==================================================================================================================
【32】 ERROR 1114 (HY000): The table ‘’ is full
– ==================================================================================================================

mysql> replace into bgapidb.yi_refund select * from zbakapi.yi_refund;
ERROR 1114 (HY000): The table '' is full
mysql> 
mysql> 
mysql> 
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" and TABLESPACE_NAME='ts_1'  order by UPDATE_TIME desc;
+-----------------+-------------+----------------+------------+----------------+
| TABLESPACE_NAME | FILE_NAME   | TOTAL_MB       | FREE_MB    | EXTRA          |
+-----------------+-------------+----------------+------------+----------------+
| ts_1            | data_11.dat | 20480.00000000 | 0.00000000 | CLUSTER_NODE=4 |
+-----------------+-------------+----------------+------------+----------------+
1 row in set (0.01 sec)




Dbspj::execSTTOR() inst:0 phase=1
2012-12-06 10:21:39 [ndbd] INFO     -- Start phase 1 completed
2012-12-06 10:26:57 [ndbd] INFO     -- Watchdog: User time: 3406  System time: 15757
2012-12-06 10:26:57 [ndbd] WARNING  -- Watchdog: Warning overslept 213 ms, expected 100 ms.
2012-12-06 10:30:51 [ndbd] INFO     -- Watchdog: User time: 5174  System time: 19204
2012-12-06 10:30:51 [ndbd] WARNING  -- Watchdog: Warning overslept 219 ms, expected 100 ms.
2012-12-06 10:32:42 [ndbd] INFO     -- Watchdog: User time: 5997  System time: 20911
2012-12-06 10:32:42 [ndbd] WARNING  -- Watchdog: Warning overslept 208 ms, expected 100 ms.
【ok】网络状况不是很好,修改参数值:
heartbeatintervaldbdb=10000 heartbeatintervaldbapi=10000 




#Redo log
FragmentLogFileSize=512M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=256
RedoBuffer=256M
其中修改FragmentLogFileSize值需要 ndbd --initial 启动
Error data: Invalid file size for redo logfile,  size only changable with --initial
Error object: DBLQH (Line: 15200) 0x00000002
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Rio520

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值