MySQL集群在Ubuntu 9.0上的配置方法

大家在网上都能找到都是RedHat系统等等，对Ubuntu系统MySQL集群就没有介绍文章

我现在写写Ubuntu这个MySQL集群方法。新手不要骂我，呵呵。

介绍网络环境：

测试环境：

Server1: ndbd 192.168.245.11
Server2: ndbd 192.168.245.12
Server3: mysqld –ndb-cluster 192.168.245.13

ndbd： 数据库节点。

mysqld –ndb-cluster： MySQL服务器节点，程序直接访问的是这台机器的IP。默认端口仍是3306。

ndbd_mgm ndbd_mgmd：管理节点。管理／查看各库节点和服务器节点的状态。

二、集群方案

1.管理节点:server3(192.168.245.13)

2.存储节点:server1(192.168.245.11),server2(192.168.245.12)

3.SQL节点:server1(192.168.245.11),server2(192.168.245.12),server3(192.168.245.13)

三、mysql安装和配置

1.安装，sudo apt-get install mysql-server

2.配置这三台服务器上配置my.cnf，这三台服务器都要配置

vim /etc/mysql/my.cnf
—————————————–my.cnf开始——————————————–
Ubuntu@ubuntu:~$ cat /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - “/etc/mysql/my.cnf” to set global options,
# - “~/.my.cnf” to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with –help to get a list of available options and with
# –print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/serve ... ables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain “#” chars…
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port
 = 
3306
socket
 = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket
 = /var/run/mysqld/mysqld.sock
nice
 = 
0

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user
 = 
mysql
pid-file
 = /var/run/mysqld/mysqld.pid
socket
 = /var/run/mysqld/mysqld.sock
port
 = 
3306
basedir
 = /usr
datadir
 = /var/lib/mysql
tmpdir
 = /tmp
language
 = /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# 
bind-address
 = 
127
.0.0.1
#
# * Fine Tuning
#
key_buffer
 = 
16M
max_allowed_packet
 = 
16M
thread_stack
 = 
128K
thread_cache_size
 = 
8
#
max_connections
 = 
100
#
table_cache
 = 
64
#
thread_concurrency
 = 
10
#
# * Query Cache Configuration
#
query_cache_limit
 = 
1M
query_cache_size
 = 
16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#
log
 = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement
#
# Here you can see queries with especially long duration
#
log_slow_queries
 = /var/log/mysql/mysql-slow.log
#
long_query_time
 = 
2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#
server-id
 = 
1
#
log_bin
 = /var/log/mysql/mysql-bin.log
expire_logs_days
 = 
10
max_binlog_size
 = 
100M
#
binlog_do_db
 = 
include_database_name
#
binlog_ignore_db
 = 
include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * MyISAM
#
# MyISAM is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more MyISAM related options. There are many!
# You might want to disable MyISAM to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# 
chroot
 = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI “tinyca”.
#
# 
ssl-ca
=/etc/mysql/cacert.pem
# 
ssl-cert
=/etc/mysql/server-cert.pem
# 
ssl-key
=/etc/mysql/server-key.pem

ndbcluster
ndb-connectstring
=
192
.168.245.13

[mysqldump]
quick
quote-names
max_allowed_packet
 = 
16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer
 = 
16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)

# not from the NDB Management Nodes (ndb_mgmd processes).
#
[MYSQL_CLUSTER]

ndb-connectstring
=
192
.168.245.13

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with ‘.cnf’, otherwise they’ll be ignored.
#
!includedir /etc/mysql/conf.d/

Ubuntu@ubuntu:~$
———————————my.cnf结束———————————————————

主要在：[mysqld]下添加：

ndbcluster
ndb-connectstring
=
192
.168.245.13

和添加

[MYSQL_CLUSTER]
ndb-connectstring
=
192
.168.245.13

上面三台服务器都要一样

2.配置ndb_mgmd.cnf（192.168.245.13服务器上配置）

复制/usr/share/doc/mysql-server-5.0/examples/ndb_mgmd.cnf到/etc/mysql/ndb_mgmd.cnf

编辑ndb_mgmd.cnf

——————-开始————
Ubuntu@ubuntu:~$ cat /etc/mysql/ndb_mgmd.cnf
[NDBD DEFAULT]
NoOfReplicas
=
2
DataMemory
=
10MB
IndexMemory
=
25MB
MaxNoOfTables
=
256
MaxNoOfOrderedIndexes
=
256
MaxNoOfUniqueHashIndexes
=
128

[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
[NDB_MGMD]
Id
=
1
 # the NDB Management Node (this one)
HostName
=
192
.168.245.13
DataDir
= /var/lib/mysql-cluster

[NDBD]
Id
=
2
 # the first NDB Data Node
HostName
=
192
.168.245.11
DataDir
= /var/lib/mysql-cluster

[NDBD]
Id
=
3
 # the second NDB Data Node
HostName
=
192
.168.245.12
DataDir
=/var/lib/mysql-cluster

[MYSQLD]
Id
=
4
 # the first SQL node
HostName
=
192
.168.245.13
[MYSQLD]
Id
=
5
 # the first SQL node
HostName
=
192
.168.245.11
[MYSQLD]
Id
=
6
 # the first SQL node
HostName
=
192
.168.245.12

Ubuntu@ubuntu:~$

——————-结束————

先启动：

13服务器：

sudo /etc/init.d/mysql-ndb-mgm start

然后启动

11.12服务器

sudo /etc/init.d/mysql-ndb start

最后启动，13.11.12服务器mysql服务

sudo /etc/init.d/mysql start

测试：

13服务器上：

neo@mgm:~$ 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
 @192.168.245.11 (Version: 5.0.51, Nodegroup: 0)
id
=
3
 @192.168.245.12 (Version: 5.0.51, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id
=
1
 @192.168.245.13 (Version: 5.0.51)

[mysqld(API)] 2 node(s)
id
=
4
 @192.168.245.13 (Version: 5.0.51)
id
=
5
 @192.168.245.11 (Version: 5.0.51)
id
=
5
 @192.168.245.12 (Version: 5.0.51)

ndb_mgm
>

与没有使用簇的MySQL相比，在MySQL簇内操作数据的方式没有太大的区别。

执行这类操作时应记住三点

1.表必须用ENGINE=NDB或ENGINE=NDBCLUSTER选项创建，或用ALTER TABLE选项更改，以使用NDB Cluster存储引擎在 Cluster内复制它们。如果使用mysqldump的输出从已有数据库导入表，可在文本编辑器中打开SQL脚本，并将该选项添加到任何表创建语句，或 用这类选项之一替换任何已有的ENGINE（或TYPE）选项。

2.另外还请记住，每个NDB表必须有一个主键。如果在创建表时用户未定义主键，NDB Cluster存储引擎将自动生成隐含的主键。（注释：该隐含 键也将占用空间，就像任何其他的表索引一样。由于没有足够的内存来容纳这些自动创建的键，出现问题并不罕见）。

3.当你在一个节点上运行create database mydb;你去其他sql node上执行show databases;将不能看到mydb,你需要创建它，然后use mydb; show tables;你将看到同步的表。

SQL Node 1
neo@sql:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.0.51a-3Ubuntu5.1 (Ubuntu)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql
>
 create database cluster;
Query OK, 1 row affected (0.00 sec)

mysql
>
 use cluster
Database changed
mysql
>
 create table city( id mediumint unsigned not null auto_increment primary key,
name varchar(20) not null default ” ) 
engine
 = 
ndbcluster
 default

charset utf8;
Query OK, 0 rows affected (1.07 sec)

mysql
>
 insert into city values(1, ‘Shenzhen’);
Query OK, 1 row affected (0.12 sec)

mysql
>
 insert into city values(2, ‘Guangdong’);
Query OK, 1 row affected (0.00 sec)

SQL Node 2

neo@sql:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.0.51a-3Ubuntu5.1 (Ubuntu)

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql
>
 show databases;
——————–
| Database |
——————–
| information_schema |
| example |
| mydb |
| mysql |
| neo |
——————–
6 rows in set (0.13 sec)

mysql
>
 create database cluster;
Query OK, 1 row affected (0.00 sec)
mysql
>
 show databases;
——————–
| Database |
——————–
| information_schema |
| cluster |
| example |
| mydb |
| mysql |
| neo |
——————–
6 rows in set (0.13 sec)

mysql
>
 use cluster;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql
>
 show tables;
——————-
| Tables_in_cluster |
——————-
| city |
——————-
1 row in set (0.01 sec)

mysql
>
 select * from city;
—- ———–
| id | name |
—- ———–
| 1 | Shenzhen |
| 2 | Guangdong |
—- ———–
2 rows in set (0.03 sec)

mysql
>