MySQL数据库框架结构---Cluster,Replication,MyISAM综合

<转自http://blog.chinaunix.net/space.php?uid=20313874&do=blog&id=299470 >

为了设计一个合理的 MySQL Cluster To MySQL Server 的Replication 结构，

既能满足现有代码的修改量，又想能提高系统的并发注文量，

经过几周的摸索，设计以下的结构：

http://blog.chinaunix.net/photo/33594_081104223443.jpg

http://blog.chinaunix.net/photo/33594_081104223443.jpg

为了应对我们的交易系统中非常高的并发注文的能力，

特设计了一下的结构：

特点介绍：

1，采用 MySQL Cluster  To  MySQL Server  的Replication 结构。

   将MySQL Cluster 的并发插入能力 在 Cluster上发挥，将许许多多的

   Join 查询扔到Slave 上进行查询，也是扬长避短吧，虽然有些延迟，但对此要求不高的

   就可以直接在Slave上查询，

    http://bugs.mysql.com/39622

   在搭建之初遇到很郁闷的问题，所幸通过在Cluster的SQL节点上参数： 

   ndb-log-update-as-write=0

   得以解决。否则会出现更新数据从服务器上出现主键重复的错误。

    从服务器上的 mysql.ndb_apply_status 表要重新创建，

   修改成 MyISAM 表，并将主键去掉，并建立一个索引。

   为了在切换的时候查询的速度能快些。

2，采用 NDB表 和 INNODB表 放一起的方式，图中的SQL-4 SQL-5，

   两台机器属于SQL节点中的两个普通节点，但是在SQL-4上同时又

   创建了很多的Innodb的表，对Innodb的操作就直接在此上操作，

   通过设定SQL-5 上的Replication 信息来将这些 Innodb 的表

   内容全部复制到 SQl-5上，作为SQL-4 的贴身Slave，可以作为

   SQL-5 的真正的替换，而不出现系统中丢失数据的问题。

3，SQL-4,SQL-5 采用MM 的结构，他们间虽然是互为复制，但

   同时在写的，对应用程序来说只有一台，这样不会产生一些不必要

   的混乱。

   对SQL-5 为什么一定要是Cluster中的节点而不是 单独拿出来

   作为一个普通的Slave 的原因是：

   1）系统的设计目标是 SQL-4 宕机后，系统还能正常交易，因为主要的

      交易表都在Cluster中，全部的配置相关表都在应用的缓存中， SQL-4

      宕机2-3 分钟不会影响应用程序。

   2）在SQL-4宕机的过程切换过程中，系统的交易数据的写入是不停止的，

      如何保证将这部分数据续写到众多的Slave 中去，这就是SQL-5作为

      Cluster中节点的目的。

      作为SQL节点，打开了binlog 日志并设置为row复制后，其他 SQL节点

      上的写入的信息就会反映在SQL-5 上的日志中，并记录到 mysql.ndb_binlog_index 

      表中，通过 从服务器上 停顿的时间点的查询,看 mysql.ndb_apply_status 表

      中最大的 epoch 值，到SQL-5 中的 mysql.ndb_binlog_index来找下一个binglog  

      日志 和 postion ，注意这点和以前的 Master----->Master-Slave ----> Slave 

      的切换方式不一样，和 ( Master --- Master ) -----> Slave 的切换方式也完全

      不一样。

4，虽然MySQL5.1 还没有 GA , 但是 mysql_5.1.23_ndb_6.2.15  Cluster 

   已经是一个稳定的版本了，有很多成功的案例，在试用阶段系统也会稳定，

   因此 各方面最终都同意了这个版本。

5，附上

  SQL1 :10.4.6.220,

  SQL5 :10.4.6.226,

  SQL4 :10.4.6.227,

  Slave01 : 10.4.6.228

  Manager : 10.4.6.224 这样结构的所有 my.cnf ,ndbconfig.ini 的配置文件信息：

 SQL1 :10.4.6.220,

 [MYSQLD]

ndbcluster

ndb-connectstring=10.4.6.224

default-storage-engine=ndbcluster

max_connections=1024

default-character-set=cp932

ndb-log-update-as-write=0

memlock

#ndb-cluster-connection-pool=4

#added 2008 08 20 for ...

#BASE config.....

skip-name-resolve  

back_log =350 

log-slow-queries = slow.log

long_query_time = 1

table_cache =350

sort_buffer_size = 1M

query_cache_size=512M

query_cache_limit=2M

ndb_force_send=1

engine_condition_pushdown=1

ndb_use_exact_count=0

#1 -- means enable, 0 -- means disable

slave-allow-batching=1

#transaction-isolation = READ-COMMITTED

#add by wangxl ,for "converting HEAP to MyISAM "

max_heap_table_size=64M

tmp_table_size=64M

thread_cache_size=350

#add by liuyf  for primary key repeat

auto_increment_increment=4

auto_increment_offset=4

delayed_queue_size=500

bulk_insert_buffer_size=16M

#MySQL slave config ...........

log-bin=mfx220-bin

#binlog_format="STATEMENT"

binlog_format="ROW"

#binlog_format="MIXED"

server-id       = 20

  SQL5 :10.4.6.226,

  [[email protected] ~]# cat /etc/my.cnf

[MYSQL_CLUSTER]

ndb-connectstring=10.4.6.224

[MYSQLD]

ndbcluster

ndb-connectstring=10.4.6.224

default-storage-engine=ndbcluster

max_connections=1024

default-character-set=cp932

ndb-log-update-as-write=0

#ndb-cluster-connection-pool=4

memlock

#added 2008 08 20 for ...

#BASE config.....

skip-name-resolve  

back_log =350 

log-slow-queries = slow.log

long_query_time = 1

table_cache =350

sort_buffer_size = 1M

query_cache_size=512M

query_cache_limit=2M

ndb_force_send=1

engine_condition_pushdown=1

ndb_use_exact_count=0

#1 -- means enable, 0 -- means disable

slave-allow-batching=1

transaction-isolation = READ-COMMITTED

#add by wangxl ,for "converting HEAP to MyISAM "

max_heap_table_size=64M

tmp_table_size=64M

thread_cache_size=350

#add by liuyf  for primary key repeat

auto_increment_increment=4

auto_increment_offset=3

#MySQL slave config ...........

log-bin=mfx226-bin

#binlog_format="STATEMENT"

#binlog_format="ROW"

binlog_format="MIXED"

server-id       = 26

replicate-do-db = MAIN

master-host     = 10.4.6.227

master-user     = root

master-password = 123456

replicate-ignore-table =  MAIN.JHF_SYS_POSITION_INSERT

replicate-ignore-table =  mysql.%

replicate-ignore-table =  test.%

relay-log=mfx226-relay-bin

skip-slave-start

   SQL4 :10.4.6.227,

[MYSQLD]

ndbcluster

ndb-connectstring=10.4.6.224

default-storage-engine=ndbcluster

max_connections=1024

default-character-set=cp932

ndb-log-update-as-write=0

memlock

#ndb-cluster-connection-pool=4

#added 2008 08 20 for ...

#BASE config.....

skip-name-resolve  

back_log =350 

log-slow-queries = slow.log

long_query_time = 1

table_cache =350

sort_buffer_size = 1M

query_cache_size=512M

query_cache_limit=2M

ndb_force_send=1

engine_condition_pushdown=1

ndb_use_exact_count=0

#1 -- means enable, 0 -- means disable

slave-allow-batching=1

transaction-isolation = READ-COMMITTED

#add by wangxl ,for "converting HEAP to MyISAM "

max_heap_table_size=64M

tmp_table_size=64M

thread_cache_size=350

#add by liuyf  for primary key repeat

auto_increment_increment=4

auto_increment_offset=4

delayed_queue_size=500

bulk_insert_buffer_size=16M

#MySQL slave config ...........

log-bin=mfx227-bin

#binlog_format="STATEMENT"

#binlog_format="ROW"

binlog_format="MIXED"

server-id       = 27

  Slave01 : 10.4.6.228

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-locking

key_buffer = 512M

max_allowed_packet = 1M

table_cache =128

sort_buffer_size =1M

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

#transaction-isolation = READ-COMMITTED

max_connections=1024

skip-name-resolve  

back_log = 300

log-slow-queries = slow.log

long_query_time = 1

server-id       = 228

master-host     = 10.4.6.227

master-user     = root

master-password = 123456

#replicate-do-db = MAIN

#replicate-do-db = mysql

#replicate-ignore-table=mysql.ndb_apply_status

relay-log=mfx222-relay-bin

skip-slave-start

innodb_flush_log_at_trx_commit = 1

innodb_buffer_pool_size =128M 

innodb_log_buffer_size = 16M 

innodb_log_file_size =8M 

innodb_file_per_table

innodb_status_file

innodb_thread_concurrency= 32

query_cache_size=512M

query_cache_limit=2M

[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

Manager : 10.4.6.224

[[email protected] ~]# cat /etc/ndbconfig.ini 

# file "config.ini" - 2 data nodes and 2 SQL nodes

# This file is placed in the startup directory of ndb_mgmd (the management

# server)

# The first MySQL Server can be started from any host. The second can be started

# only on the host mysqld_5.mysql.com

[NDBD DEFAULT]

NoOfReplicas=1

MaxNoOfConcurrentOperations=  100000

MaxNoOfLocalOperations     =  110000

#MaxNoOfConcurrentTransactions default 4096,

#MaxNoOfConcurrentTransactions = 

#    (maximum number of tables accessed in any single transaction + 1)

#    * number of cluster SQL nodes

MaxNoOfConcurrentTransactions=40960

#DataMemory= 1280M

#IndexMemory= 256M

DataMemory= 640M

IndexMemory= 128M

StringMemory=25  ##use in ndb62

RedoBuffer=128M

DataDir=/usr/local/mysql/cluster

TimeBetweenWatchDogCheck= 30000

#Default ConcurrentScan.......

 MaxNoOfConcurrentScans=256

 MaxNoOfLocalScans=256

    # Default BatchSize

 BatchSizePerLocalScan=64

    MaxNoOfOrderedIndexes= 1024 

    #   REDO logs of 16X128M=2G.

NoOfFragmentLogFiles=16

FragmentLogFileSize=128M ## use in ndb62

    # Default 1000

 MaxNoOfAttributes=80000

    # Default 128,limit 1600..

 MaxNoOfTables=1500

    # Default 64.

 MaxNoOfUniqueHashIndexes=3500

    #Default is 768 ..

 MaxNoOfTriggers=1000

 TransactionDeadlockDetectionTimeout=20000

    #yunfei test 

 TransactionInactiveTimeout=16000

#This parameter defines the interval between synchronisation epochs 

#for MySQL Cluster Replication. The default value is 100 milliseconds. 

#TimeBetweenEpochs is part of the implementation of micro-GCPs, 

#which can be used to improve the performance of MySQL Cluster Replication. 

TimeBetweenEpochs=20 ## use in ndb62

BackupDataDir=/usr/local/mysql/BACKUP

#yunfei test

LockPagesInMainMemory=1

[ndb_mgmd default]

DataDir=/usr/local/mysql/cluster

[TCP default]

SendBufferMemory=2M

ReceiveBufferMemory=2M

[NDB_MGMD]

Hostname= 10.4.6.224

logDestination=FILE:filename=cluster.log,maxsize=100000,maxfiles=6

[NDBD]

HostName= 10.4.6.223

[MYSQLD default]

BatchByteSize=64k

BatchSize=128

[MYSQLD]

HostName= 10.4.6.220

[MYSQLD]

HostName= 10.4.6.226

[MYSQLD]

HostName= 10.4.6.227

[MYSQLD]

[MYSQLD]

完 。。。