请教MySQL_Cluster-配置的几个问题

zaochun_liyi

我参考了 Mysql 5.0.22 Cluster for RedHat AS4-U2
    http://www.linuxeden.com/forum/t146917.html  
    我的配置环境和文中提到的很相似,也是 AS4-U2 (up2date-4.4.5-1), 用的 MySQL 版本也是 mysql-max-5.0.22-linux-i686-glibc23.tar.gz

三台机器如下:
PC1(MySQLNode1):    210.40.39.12
PC2(MySQLNode2):    210.40.39.7
PC3(MySQLManager):  210.40.39.2

问题主要如下:
(1) 我装 LInux 系统的时候,没有选择安装任何的 MySQL 包,但是现在却有:

[root@MySQL1 ~]# rpm -qa | grep mysql
mysql-4.1.7-4.RHEL4.1
[root@MySQL1 ~]#

无法用 rpm -e mysql-4.1.7-4.RHEL4.1 卸载掉, 提示:

[root@MySQL1 ~]# rpm -e mysql-4.1.7-4.RHEL4.1
error: Failed dependencies:
        libmysqlclient.so.14 is needed by (installed) perl-DBD-MySQL-2.9004-3.1.i386
        libmysqlclient.so.14 is needed by (installed) dovecot-0.99.11-2.EL4.1.i386
[root@MySQL1 ~]#

不知道这个对于后来安装的 MySQL 有没有致命的影响??

(2) 修改后的 /etc/my.cnf 配置文件内容:
按照你的流程操作到:
PC1/2 都
cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf
之前我 mv /etc/my.cnf /etc/my.cnf.bak

现在的 /etc/my.cnf 文件配置如下(红色字体是我按照文中说的添加的):

[root@MySQL1 ~]# more /etc/my.cnf
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
ndbcluster
ndb-connectstring=210.40.39.2

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data/
#innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[ndbd]
connect-string=210.40.39.2
[ndb_mgm]
connect-string=210.40.39.2
[ndb_mgmd]
config-file=/var/lib/mysql-cluster

现在的问题是
1) 没有 3306 端口的任何信息

[root@MySQL1 ~]# netstat -nat
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State
tcp        0      0 0.0.0.0:32769               0.0.0.0:*                   LISTEN
tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN
tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN
tcp        0      0 210.40.39.12:32859          202.108.9.174:80            ESTABLISHED
tcp        0      0 :::22                       :::*                        LISTEN
tcp        0      0 ::ffff:210.40.39.12:22      ::ffff:210.0.202.19:53787   TIME_WAIT
tcp        0      0 ::ffff:210.40.39.12:22      ::ffff:210.0.202.19:41732   ESTABLISHED
tcp        0      0 ::ffff:210.40.39.12:22      ::ffff:210.40.39.7:32778    ESTABLISHED
[root@MySQL1 ~]#

2) 找不到 /tmp/mysql.sock 或者 /var/lib/mysql/mysql.sock

[root@MySQL1 ~]# mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
[root@MySQL1 ~]#

不知道 /etc/my.cnf 配置以及缺少 mysql.sock 文件是不是有问题?

3) 开机自检的时候提示: 启动 MySQL 失败
提示:
Unable to connect with connect string nodeid=0.210.40.39.2:1186

PC3: 210.40.39.2 的 1186 端口是怎么回事情?
是和 PC1/2 实际通信的端口还是?

查看 Error log 如下:

[root@MySQL1 data]# more /usr/local/mysql/data/MySQL1.err
060814 18:26:32  mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
060814 18:26:33  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
060814 18:26:33  InnoDB: Log file ./ib_logfile0 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
060814 18:26:33  InnoDB: Log file ./ib_logfile1 did not exist: new to be createdInnoDB: 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
060814 18:26:34  InnoDB: Started; log sequence number 0 0
060814 18:26:34 [ERROR] Fatal error: Can't open and lock privilege tables: Failed to open 'host', error while unpacking from engine
060814 18:26:34  mysqld ended

060814 22:27:03  mysqld started
060814 22:27:03  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060814 22:27:03  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 36808.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
060814 22:27:04  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
060814 22:27:04  InnoDB: Started; log sequence number 0 43655
060814 22:27:10 [Note] Recovering after a crash using mysql-bin
060814 22:27:10 [Note] Starting crash recovery...
060814 22:27:10 [Note] Crash recovery finished.
060814 22:27:10 [ERROR] Fatal error: Can't open and lock privilege tables: Failed to open 'host', error while unpacking from engine
060814 22:27:10  mysqld ended

060815 09:18:11  mysqld started
060815  9:18:11  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060815  9:18:12  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43655.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
060815  9:18:12  InnoDB: Started; log sequence number 0 43655
060815  9:18:18 [Note] Recovering after a crash using mysql-bin
060815  9:18:18 [Note] Starting crash recovery...
060815  9:18:18 [Note] Crash recovery finished.
060815  9:18:18 [ERROR] Fatal error: Can't open and lock privilege tables: Failed to open 'host', error while unpacking from engine
060815 09:18:18  mysqld ended

060815 10:12:30  mysqld started
060815 10:12:30  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060815 10:12:30  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43655.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
060815 10:12:30  InnoDB: Started; log sequence number 0 43655
060815 10:12:30 [Note] Recovering after a crash using mysql-bin
060815 10:12:30 [Note] Starting crash recovery...
060815 10:12:30 [Note] Crash recovery finished.
060815 10:12:31 [ERROR] Fatal error: Can't open and lock privilege tables: Failed to open 'host', error while unpacking from engine
060815 10:12:31  mysqld ended

060815 10:32:32  mysqld started
060815 10:32:32  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060815 10:32:32  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43655.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
060815 10:32:32  InnoDB: Started; log sequence number 0 43655
060815 10:32:32 [Note] Recovering after a crash using mysql-bin
060815 10:32:32 [Note] Starting crash recovery...
060815 10:32:32 [Note] Crash recovery finished.
060815 10:32:32 [ERROR] Fatal error: Can't open and lock privilege tables: Failed to open 'host', error while unpacking from engine
060815 10:32:32  mysqld ended

060815 10:35:42  mysqld started
060815 10:35:42  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060815 10:35:42  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43655.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
060815 10:35:42  InnoDB: Started; log sequence number 0 43655
060815 10:35:42 [Note] Recovering after a crash using mysql-bin
060815 10:35:42 [Note] Starting crash recovery...
060815 10:35:42 [Note] Crash recovery finished.
060815 10:35:42 [ERROR] Fatal error: Can't open and lock privilege tables: Failed to open 'host', error while unpacking from engine
060815 10:35:42  mysqld ended

060815 10:36:55  mysqld started
060815 10:36:55  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060815 10:36:55  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43655.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
060815 10:36:55  InnoDB: Started; log sequence number 0 43655
060815 10:36:55 [Note] Recovering after a crash using mysql-bin
060815 10:36:55 [Note] Starting crash recovery...
060815 10:36:55 [Note] Crash recovery finished.
060815 10:36:55 [ERROR] Fatal error: Can't open and lock privilege tables: Failed to open 'host', error while unpacking from engine
060815 10:36:55  mysqld ended

[root@MySQL1 data]#

都已经卡住 10 多天了,
麻烦各位老大老哥帮忙看看问题所在.
我的联系方式: E-Mail: liyi_1980@163.com

[ 本帖最后由 zaochun_liyi 于 2006-8-15 12:10 编辑 ]

qlks

你的mysql服务器呢？
cluster需要mysql服务器器，node节点,manager管理节点3部分
在node节点上你是否ndbd --initial了
具体的很难说清
你可以看看mysql的Reference Manual

zaochun_liyi

原帖由 qlks 于 2006-8-16 11:30 发表
你的mysql服务器呢？
cluster需要mysql服务器器，node节点,manager管理节点3部分
在node节点上你是否ndbd --initial了
具体的很难说清
你可以看看mysql的Reference Manual

原来出现的很多问题 现在得到了一些解决,具体如下:
[root@MySQL1 ~]# rpm -qa | grep mysql
mysql-4.1.7-4.RHEL4.1
[root@MySQL1 ~]#
这个东西就是罪魁祸首
在安装 mysql-max-5.0.22-linux-i686-glibc23 之前必需核查系统有没有安装了
虽然我安装新的 OS 的时候没有安装任何与 MySQL 相关的东西
但是后来排查,发现是系统在安装其他包的时候安装了 dovecot 他与 MySQL 有倚赖关系--在这个问题上卡住了很多天! 汗\!
通过 rpm -e 之  解决了好多问题: (执行完 /usr/local/mysql/bin/ndbd --initial) 之后/tmp/mysql.sock 已经有了

现在最大的问题是:
1) PC1/2 开机自检的时候提示: 启动 MySQL 成功
但是接者提示:
Unable to connect with connect string nodeid=0.210.40.39.2:1186

PC3: 210.40.39.2 的 1186 端口是怎么回事情?
是和 PC1/2 实际通信的端口还是?

2) PC3 执行以下明令以后的信息:

[root@MySQLManager tmp]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 210.40.39.2:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2 (not connected, accepting connect from 210.40.39.12)
id=3 (not connected, accepting connect from 210.40.39.7)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @210.40.39.2  (Version: 5.0.22)

[mysqld(API)]   3 node(s)
id=4 (not connected, accepting connect from any host)
id=5 (not connected accepting connect from any host)
id=6 (not connected, accepting connect from any host)

3) 查看 PC3 MySQLManager 的日志, 信息如下:

[root@MySQLManager data]# more /usr/local/mysql/data/MySQLManager.err
060817 09:51:59  mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
060817  9:51:59  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
060817  9:51:59  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...
060817  9:51:59  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
060817  9:52:00  InnoDB: Started; log sequence number 0 0
060817  9:52:10 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.22-max-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Experimental
(GPL)
060817  9:54:55 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

060817  9:54:55  InnoDB: Starting shutdown...
060817  9:54:58  InnoDB: Shutdown completed; log sequence number 0 43655
060817 09:56:11  mysqld started
060817  9:56:12  InnoDB: Started; log sequence number 0 43655
060817  9:56:12 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.22-max-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Experimental
(GPL)
[root@MySQLManager data]#

不知道有没有什么问题?


现在问题的关键就是如何处理 1186 这个端口问题,需要在 /etc/my.cnf 之中还是在什么地方如何修改? 请各位大虾指点指点,拜谢了!

[ 本帖最后由 zaochun_liyi 于 2006-8-17 10:34 编辑 ]

archangle

感觉问题说的很混乱。
配置一个集群，简单说来，需要规划好管理节点(manage node)，数据节点(ndb node),mysql节点(也可以叫api node).这些节点可以分散在不同的机器上，也可以在同一台机器上。
设置步骤简单说来是，
1.首先写好管理节点的配置文件，然后启动管理节点。
2.配置ndb节点并启动
3.启动mysql 节点
4.用ndb_mgm 查看集群的信息。
其实参考mysql的官方手册，详细的读几遍，自然一眼就能看出问题出在哪里了。

archangle

1186端口是ndb_mgmd节点的监听端口，ndb和mysql节点通过这端口来读取集群的配置文件。
所以你要在你的管理节点上启动ndb_mgmd服务。你确认你启动了吗？

zaochun_liyi

多谢老哥的提醒
问题所在归结于：开启了防火墙
后来允许 1186：tcp 就好了
只是出于测试
所以只是简单的开启了 1186 端口