# Sogou Cluster MySQL preliminary study data finishing - installation and deployment of new performance testing, etc.

Classification:

1.1 MySQL-clusterbrief introduction

Simply say,MySQLClusterIn fact, it is a completely distributed database system, which is mainly implemented in the case of non shared memory devices.NDBCluster(short forNDBStorage engine to implement.

MySQLClusterJust the birth of the time can be said to be a persistent data for the memory database, all data and the index must be loaded in memory to be able to operate normally, but the latestMySQLClusterVersion has been able to do all of the index and index data can be loaded in memory, the actual data can not be loaded into memory, the structure as shown below:

One point threeClusterprimary structure

-OneManagement(MGM)node:

The function of this kind of node is management.MySQLClusterOther nodes, such as providing a set of data, start and stop nodes, running backups, etc.. Since this type of node is responsible for managing the configuration of other nodes, it should first initiate such a node before starting the other nodes.MGMNode is a command"Ndb_mgmd"Starting.

No requirements on the hardware, the difference of the machine is fully capable of. Default portOne thousand one hundred and eighty-six.

-
-TwoData node:

This class of nodes is used to saveClusterData. The number of data nodes is related to the number of copies, which is a multiple of the fragments. For example, for two copies, each copy has two pieces, then there isFourData node. But there is no need to set up multiple copies. Data node is using the command"Ndbd"Starting.

Can choose large memory,CPUAlso relatively speaking good, especially afterNdbmtdOn multi coreCpuSupport. Default portTwo thousand two hundred and two

-
-Three,SQLnode:

This is used to accessClusterData node. aboutMySQLCluster, the client node is usedNDBClusterStorage engine traditionMySQLThe server Usually,SQLNode is using the command"Mysqld-Ndbcluster"To start, or to"Ndbcluster"Add to"My.cnf"After use"Mysqld"Start up.

YesCPUHigher requirements, select multi core, high frequencyCPUpreferably

One point fourClusterCharacteristic

-Distributed: a distributed storage engine that can be composed of multipleNDBClusterThe storage engine is part of the cluster, which is stored as a whole.
Support transactions: and
InnodbLike, support transactions
With the
MysqldNot in a host: can andMysqldSeparate in the independent host, and then through the network andMysqldCommunication interaction.
Memory demand is huge: the new version of the index and the indexed data must be stored in memory, the old version of all the data and the index must exist and memory
-
-ClusterThe use of automatic identification key data partitioning scheme, users do not need to care about the data slicing scheme (inFive point oneAnd later provided the partition key rules), transparent implementation of the distributed database, the rules of the data in accordance with the rulesOnePrimary key,TwoUnique index,ThreeAutomatic line identificationROWIDCompletion, and then the number of cluster distribution, its access to the data as ifRAIDLike the access mechanism, which can extract data from each node in parallel, hash data, when the use of non primary key or partition key access, will result in all the cluster node scan, the impact of performanceClusterThe face of the core challenges
-
-Applicable scenarios:

With very high concurrency requirements
The response to a single request is not very
Critical
The query is simple, the filter conditions are relatively fixed, each request data is less, and do not want to make their own levelSharding

-Best practice

As far as possible to allow the query is simple, to avoid cross node data transmission; as far as possible to meetSQLNode's computing performance, larger clustersSQLNode will be redundantDataIn order to reduce the time delay of data transmission in the network layer, the network environment is used as far as possible.

-

99.999) 1% high availability

2)Fast automatic failure switching

3)Flexible distributed architecture with no single point of failure

4)High throughput and low latency

5)Scalability, support online expansion

-

1)There are a lot of limitations, such as: does not support data for not more than the foreign key.8KDoes not includeBLOBandTextData in the middle of the

2)Deployment, management, configuration is very complex

3)Take up disk space, big memory

4)Backup and recovery is not convenient

5)complexSQLQuery performance in general

2 installation

2.1 environmental introduction

-Mysqlcluster7.2.4Installation and configuration
-Get readySixty-fourBit versionFiveTaiwan server4Ginternal memory60Ghard disk2cpu
-10.100.200.36 64position
-10.100.200.37 64position
-10.100.200.38 64position
-10.100.200.39 64position
-10.100.200.41 64position
-Management node(Ndb_mgmd10.100.200.36 64position
-data node(Ndbd10.100.200.37 64position10.100.200.38 64position
-SQLnode(Mysqld/API10.100.200.39 64position10.100.200.41 64position
-

Two point oneInstallation management node(10.100.200.36)
-2.1.1decompressing files

Tar shell> -XvfMysql-cluster-gpl-7.2.4-linux2.6-x86_64.tar.gz

-2.1.2Create installation directory

Shell>MKDIR-p /Usr/local/Mysql/bin

Shell>MKDIR-p /Usr/local/Mysql/Ndbdata

Shell> vi.Bash_profile

PATH=$PATH:$HOME/bin:/Usr/local/Mysql/bin

-2.1.4Copy execution file

Shell>CDMysql-cluster-gpl-7.2.4-linux2.6-x86_64

CP bin/ shell>Ndb_mgmd/Usr/local/Mysql/bin

CP bin/ shell>Ndb_mgm/Usr/local/Mysql/bin

-2.1.5Create a log file storage directory

Shell>MKDIR/Var/lib/Mysql-cluster

-2.1.6Create a configuration file directory and modify the configuration file

Shell>MKDIR/Usr/local/Mysql/cluster-conf

VI shell> /Usr/local/Mysql/cluster-conf/config.ini

Two point twoManage node configuration file

[NDBDDEFAULT]

#TotalSendBufferMemory= 256M

NoOfReplicas=2

DataMemory=2500M

IndexMemory=256M

#FileSystemPath=/data/Dbdata

#Redolog

FragmentLogFileSize=32M

InitFragmentLogFiles=SPARSE

NoOfFragmentLogFiles=8

RedoBuffer=1024M

StringMemory=25

LockPagesInMainMemory=1

MaxNoOfTables=4096

MaxNoOfOrderedIndexes=2048

MaxNoOfUniqueHashIndexes=1024

MaxNoOfAttributes=24576

MaxNoOfTriggers=14336

ODirect=1

MaxNoOfConcurrentTransactions=10000

MaxNoOfConcurrentOperations=50000

MaxNoOfLocalOperations=55000

TimeBetweenGlobalCheckpoints=1000

TimeBetweenEpochs=100

TimeBetweenWatchdogCheckInitial=60000

TransactionBufferMemory=10M

DiskCheckpointSpeed=10M

DiskCheckpointSpeedInRestart=100M

TimeBetweenLocalCheckpoints=20

SchedulerSpinTimer=400

SchedulerExecutionTimer=100

RealTimeScheduler=1

BackupMaxWriteSize=1M

BackupDataBufferSize=16M

BackupLogBufferSize=4M

BackupMemory=20M

BatchSizePerLocalScan=512

The ###IncreasingLongMessageBufferOf a bug b/c (20090903)

LongMessageBuffer=8M

###Heartbeating

HeartbeatIntervalDbDb=15000

HeartbeatIntervalDbApi=15000

[MGMDEFAULT]

PortNumber: 1186

[TCPDEFAULT]

SendBufferMemory: 64M

#######################################

HOST1 to the name #Change of the NDB_MGMD host

HOST8 to the name #Change of the NDBD host

HOST9 to the name #Change of the NDBD host

#######################################

[NDB_MGMD]

NodeId: 1

HostName10.100.200.36

ArbitrationRank: 1

[NDBD]

NodeId: 4

HostName10.100.200.37

[NDBD]

NodeId: 5

HostName10.100.200.38

######################################################

The following can be #Note:MySQLDConnections or #

NDB API application connecting to thecluster # #

######################################################

[API]

NodeId: 10

HostName10.100.200.39

ArbitrationRank: 2

[API]

NodeId: 11

HostName10.100.200.39

ArbitrationRank: 2

[API]

NodeId: 12

HostName10.100.200.39

ArbitrationRank: 2

[API]

NodeId: 13

HostName10.100.200.39

ArbitrationRank: 2

[API]

NodeId: 14

HostName10.100.200.41

ArbitrationRank: 2

[API]

NodeId: 15

HostName10.100.200.41

ArbitrationRank: 2

[API]

NodeId: 16

HostName10.100.200.41

ArbitrationRank: 2

[API]

NodeId: 17

HostName10.100.200.41

ArbitrationRank: 2

ThreeInstall data node(10.100.200.38 10.100.200.37)
-解压文件

mysql-cluster-gpl-7.2.4-linux2.6-x86壳> CP /斌/ _ 64ndbd/usr本地/ /MySQL/仓

Vim ~ /壳>。_ bash profile

Vim my.cnf /壳>

[_ MySQL集群]
#配置数据节点连接管理节点

4安装SQL节点1（10.100.200.39 10.100.200.41）

CP /壳>usr本地/ /MySQLmy-large.cnf /支持/ etc /文件my.cnf

CP /壳>usr本地/ /MySQL文件/支持/mysql.server／etc／init.d/mysqld

Vim ~ /壳>。_ bash profile

Vim my.cnf /壳>

[mysqld# ]配置SQL节点与管理节点连接
Port =？

ndbcluster

5启动以及关闭群集

、启动管理节点

[banggo根”#集群会议]NDB _协议F /usr本地/ /MySQL/会议/ config.ini群集

B、启动数据节点

ndbd（–initial #第一次启动必须添加选项，另外备份/恢复，修改配置文件也需要执行

ndbd#不是第一次启动需要执行的命令

C、启动SQL节点

／etc／rc.d/init.d/mysqld启动服务mysqld启动

启动后可以用MySQL-P验证MySQL服务状态

、关闭SQL节点

／etc／rc.d/init.d/mysqld停止服务mysqld停止

B、关闭数据节点

ndbd停止

C、关闭管理节点

NDB _米高梅〉shutdown

6验证群集的状态

1、在管理节点执行NDB _米高梅- >节目：

[ ~ ] #根”banggo NDB _米高梅

————NDB集群管理客户。

NDB _米高梅>节目

connectedto管理服务器在本地：1186

clusterconfiguration

---------------------

ndbd（NDB）[ 2 ]节点（S）

“10.100.200.37 id = 4（NDB - mysql-5.5.19 7.2.4节：0，nodegroup大师）

“10.100.200.38 id = 5（NDB mysql-5.5.19：0 - nodegroup 7.2.4节）

_ NDB协议（MGM）[ 1 ]节点（S）

10.100.200.36（ID = 1“NDB - mysql-5.5.19 7.2.4）

[ 2 ] mysqld（API）节点（S）

“10.100.200.39 id = 10（NDB - mysql-5.5.19 7.2.4）

……………….

“10.100.200.41 id = 14（NDB - mysql-5.5.19 7.2.4）

……………….

7ndbcluster存储引擎功能测试InnoDB对比

8MySQLclster备份与恢复

NDB _ / usr /协议F / usr /地方/ / / config.ini--reload MySQL MySQL集群

ndbd / usr /——的初步

_ / usr /恢复10.100.200.37-n NDB - C - M - 11 - B - 1 _ /数据备份路径= 1 / /备份/备份

_ / usr /恢复10.100.200.37-n NDB C B 1 - R - 11 - _ /数据备份路径= 1 / /备份/备份

-c 192.168.100.223 -n /usr/bin/ndb_restore 11 -b 1 --backup_path=/data/dbdata1/BACKUP/BACKUP-1/ -r

-Increase or decrease the number of data nodes andNoOfReplicasAbout, generally speakingNoOfReplicasareTwo, then increase or decrease the number should also be in pairs, or else to set the otherNoOfReplicas.
-The first is to add the two node settings in the configuration file, as follows:

[NDBD]

Id=21

HostName=10.100.200.37

-

[NDBD]

Id=22

HostName=10.100.200.38

-
-useNdb_mgmTools, stop the management node, and then restart the management node
-executionNdb_mgmTo enter the management command interface, to find the management nodeID, and then execute the management nodeIdstopExit
-
-execution
-
-Landing on useShowCommand view, whether there is a new addition to the two nodes!
-Ring reset (a reboot of data nodes andSQLNode)
-
-Initialize the newly added two nodes:
-Ndbd--initialAfter the initialization is performed, the data files are automatically created,UndoLog files, etc.
-throughNdb_mgmEstablishNodegroup
-21,22s CREATENODEGROUP
-
-redistributionClusterData
-TABLE_NAME, FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE ='NDBCLUSTER'SELECTTABLE_SCHEMA;
-TABLE ALTERONLINETable_namePARTITION REORGANIZE;
-Table alteronlineCard_packagePartition reorganize;
-
-Then throughShowCommand or useNdb_descCommand view
-Ndb_desc10.80.30.39 -d -cBgcarddbCard_package-p
--cBehind with the management nodeIP,-dWith the name of the database,Table_nameTable name,-pOutput partition details

TenMySQLClsterUser rights sharing

-atSQLnodeForty-oneAbove implementationMysql-Uroot< / -pUsr/local/Mysql/share/ndb_dist_priv.sql
-
-atMysqlTerminal call nameMysql_cluster_move_privilegesThe function of this stored procedure is to backup the permissions table, and then convert the storage engine of the table to theNdbclusterEngine.
Mysql>CALLMysql.mysql_cluster_move_privileges();
-
-Automatic backup authority table

Shell>MysqldumpMysqlUserdbTables_privColumns_privProcs_priv>Backup_file.sql

-Authentication authorization table

MysqlCreate table >showMysql.user;seeEngineWhether asNdbcluster.

-atForty-oneAbove plus test account

Mysql>grant all on to test@'10.100.200.%'identified by'test123';

[Root@banggo~]#Mysql-h10.100.200.39 -UTest-P3307 -ptest123;

OK, can connect, test success!

Eleven point oneMySQLClsterBackup and restore extension(See operationNo8)

-OneCluster backup concept
A backup is a snapshot of the database at a given time. Backup consists of three main parts:
(
One)Metadata(Yuan Shuju): the name and definition of all database tables.
(
Two)Tablerecords(table record): data that is stored in a database table when a backup is performed.
(
Three)Transactionlog(transaction log): specify how and when to keep data in a database in a continuous record.
Each part
(These three parts)Will be saved on all data nodes involved in the backup. In the backup process, each node will be saved in the three parts of the three files in the disk(mean to say,There are several nodes,Will put the same data,Save a few copies.such as2.Data node,Then it will be inTwoOn a node,PreservationTwoTimes, save the directory by default
[NDBD]
DateDirThUsr/local/Mysql/BACKUP
-
(Four)BACKUP-Backup_id.node_id.ctl
Control file containing control information and metadata. Each node will be defined in the same table (for all the tables in the cluster) kept in the file.
(
Five)BACKUP-backup_id-0.node_id.data
Data file that contains the table records, which is saved by the fragment, that is, in the course of the backup, the different nodes will save the different fragments. Each node holds the file to indicate the title of the record sheet. There is a footnote on all records in the back of the list.

(
Six)BACKUP-Backup_id.node_id.log
A log file that contains a record of the transaction that has been committed. In the log, save the transaction on the table that has been saved in the backup. The nodes that participate in the backup will save a different record, because the different nodes hold different database segments.
In the contents listed above,
Backup_idRefers to the backupID,Node_idIs the only node to create a fileID.
Make sure that the cluster is properly configured for backup operations before you use the management server to create a backup start backup.

-TwoBackup parameter(Following parameters,All written inMgmdrealConfig.iniConfiguration file)
The parameters discussed in this section define the memory buffer set associated with the execution of the online backup.
(
One)BackupDataBufferSize
In the process of creating a backup, in order to send data to the disk, the two type of buffer is used. The backup data buffer is used to fill the data recorded by the scan node. Once the buffer is filled to the specified levelBackupWriteSize(see the following introduction), you will send the page to disk. While the page is written to the disk, the backup process can continue to fill the buffer until its space consumption is completed. In this case, the backup process will pause until some of the disk write operations complete and release the memory, and then scan to continue.
The default value for this parameter is
2MB.
(
Two)BackupLogBufferSize
The backup log buffer plays a role similar to that of the backup data buffer. The difference is that it is used to generate a log of all the tables that are performed during the execution of the backup. The same principle applies to the page write in the case of backup data buffer, the difference is that when there is no extra space in the backup log buffer, the backup will fail. For this reason, the size of the backup log buffer should be sufficient to handle the load generated during the execution of the backup.
The default value for this parameter is appropriate for most applications. In fact, the reason for the failure of backup is more likely because the disk write speed is not enough, rather than the backup log buffer. The cluster is likely to be unable to perform the required operations if the write load configuration disk subsystem is not configured for the application. It is best to configure the cluster in the right way, so that the processor becomes a bottleneck, not a disk or network connection. Default value is
2MB.
(
Three)BackupMemory
The parameter isBackupDataBufferSizeandBackupLogBufferSizeAnd. Default value is2MB 2MB+ = 4MB.
(
Four)BackupWriteSize
This parameter specifies the message size of the disk from the backup log buffer and the backup data buffer. Default value is32KB.
#
Can change the default backup directory,BackupDataDirThMysqlback
#
Of course premise,MKDIR/Mysqlback,Need to run on all data nodes
Can also specify a directory to store backups. By default, the directory is
FileSystemPath/BACKUP
(Six)FileSystemPath
This parameter specifies all the files that are stored as metadata,REDOJournalUNDODirectory of log and data files. Default directoryDataDirSpecified. Attention, startNdbdPrior to the process, the directory must already exist.
(
This parameter specifies the storage trace file, log file,PIDDirectory of files and error logs.

12.1 JoinImprovementOne

In the current version of the mysqlcluster 7.2 test, the join function is a major improvement, it can be said to be a milestone version, because some time ago to do testing found that 7.1.10 join performance is very bad, in addition, if the jion table is relatively large data transfer between nodes is very large.
We know that before the 7.2 version is the mysqld in the jion node execution, so you need to interact with data from the data nodes, resulting in very poor performance of join. Now version 7.2 join will be put to the data node execution, parallel processing, less data in the SQL node interaction, so the performance is greatly improved.
-
Import data test (omitted, see join.txt) first to turn off the ndb_join_pushdown parameter test
Mysql>setNdb_join_pushdown=0;
Variables like'%ndb_join_pushdown%'mysql>show;

+-------------------+-------+

Variable_name Value

+-------------------+-------+

Ndb_join_pushdown OFF

+-------------------+-------+

Sql_no_cacheCOUNT FROM (*) residents postcodesWHERE, residents.postcode=postcodes.postcodeAND postcodes.town= "MAIDENHEAD" mysql>SELECT";
- +----------+
|COUNT (*)
- +----------+
20000.
- +----------+
In set 1row (17.68 sec)

Mysql>setNdb_join_pushdown=1;
Variables like'%ndb_join_pushdown%'mysql>show;
- +-------------------+-------+
|Variable_name the Value of the
- +-------------------+-------+
ON |ndb_join_pushdown|
- +-------------------+-------+
-
•mysql > SELECT SQL NO _ _ cachecount（*）从居民，postcodeswhere postcodes.postcodeand postcodes.town residents.postcode = =“处女膜”；
•+选择+
•| | count（*）
•+选择+
•| 20000 |
•+选择+

•参考文章：http：/ / / /正在急剧增加www.clusterdb.com MySQL MySQL集群连接性能和自适应查询本地化/

13.1磁盘存储表

•••••#创建日志文件组，并添加松开日志文件
•••••createlogfile集团LG _ 2

_ 536870912初始尺寸

_ _ 67108864撤消缓冲大小

ndbcluster引擎；

#创建表空间，并添加数据文件

•••••createtablespace TS _ 2

LG集团_ 2使用日志文件

_ 536870912初始尺寸

ndbcluster引擎；

•••••createtable bgtdisk（# 创建使用磁盘存储的表：

叫varchar（50）不空，

providernamevarchar（2）不空，

•••••可以去信息_ schema.filesndbinfo.logspaces里面获取日志文件和数据文件的信息。
•••••修改表的存储引擎：altertable test.t1表空间TS = 1存储磁盘_引擎ndbclusterUTF8的默认字符集；
•••••录入数据

MySQL>插入bgtdisk选择*从邮政编码；

affected rows查询好，100000（9.68秒）

•••••再去数据节点查看存储情况数据文件已经增长如图所示：，，，

•••••这里面有totalsize还有freesize一减就是已经存储占用的大小
•••••MySQL_ >选择表空间上的_档案名称的名字，_大小×1024 1024 /页面/总_ astotal _ MB的大小，页面上的_ *免费_／1024 1024 MB / _作为额外的自由，从信息_ schema.files在_ type =“文件文件”；
•••••+——+——+——————+ + +做
•••••|表空间名称文件名称_ | _ |完全免费_ MB的MB | _ |额外|
•••••+——+——+——————+ + +做
•••••1 | _ | TS数据_ 1.dat | 512.00000000 | 301.00000000 | _ = 4 |集群节点
•••••1 | _ | TS数据_ 1.dat | 512.00000000 | 301.00000000 | _ = 5 |集群节点
•••••1 | _ | TS数据_ 1.dat | 512.00000000 | 300.00000000 | _ = 6 |集群节点
•••••+——+——+——————+ + +做
•••••在3rows集（0.05秒）
•••••# _ _撤消缓冲大小不能超过config.ini的配置redobuffer＝32m大小，否则创建失败，
•••••  1 _ createlogfile集团LG
•••••添加“撤消”undofile _ 2.log首次_ size634217728撤消缓冲_大小33554432 ndbcluster _引擎；
•••••
•••••createtablespace TS 1 _添加数据文件数据文件的使用_ 11.dat LG集团_ 1上首次_ size4294967296 _ ndbcluster大小33554432引擎；
•••••
•••••     执行结束之后，需要去数据目录/VaR/图书馆/MySQL集群/里面看看松开文件和数据document，如果存在，那么就证明建立成功了。
•••••
•••••可以建立2个表空间
•••••  createtablespace TS _ 2
•••••添加数据文件使用的数据_ 20.dat logfilegroup _ 1 10737418240首次_大小LG
•••••_ ndbcluster程度大小33554432引擎；
•••••
•••••altertablebguserdb.mvpcard表空间TS = 1存储磁盘_引擎ndbclusterUTF8的默认字符集；
•••••MySQL>插入bguserdb.mvpcardSELECT * FROMtest.mvpcard1000001000000极限；

•••••这里面有totalsize还有freesize一减就是已经存储占用的大小
•••••MySQL_ >选择表空间上的_档案名称的名字，_大小×1024 1024 /页面/总_ astotal _ MB的大小，页面上的_ *免费_／1024 1024 MB / _作为额外的自由，从信息_ schema.files在_ type =“文件文件”；
•••••+——+——+——————+ + +做
•••••|表空间名称文件名称_ | _ |完全免费_ MB的MB | _ |额外|
•••••+——+——+——————+ + +做
•••••1 | _ | TS数据_ 1.dat | 512.00000000 | 301.00000000 | _ = 4 |集群节点
•••••1 | _ | TS数据_ 1.dat | 512.00000000 | 301.00000000 | _ = 5 |集群节点
•••••1 _ | TS数据_ | 1.dat | 512.00000000 | 300.00000000 | _ = 6 |群集节点
+ + + +十+ ----------、------------ +；

17
0

*以上用户言论只代表其个人观点，不代表csdn网站的观点或立场
个人资料
• 访问：968185次
• 积分：15671
• 等级：
• 排名：第323名
• 原创：434篇
• 转载：3篇
• 原文：2篇
• 评论：523条
我的课程
 linux下oracle1…… 高级难度： 技术教程类型：
 mysql数据库管理 高级难度： 技术教程类型：
博客专栏
 SQLite 6篇文章： 阅读：16026
 cacti + nagios监控 14篇文章： 29762阅读：
 mysql管理开发由浅入深 33篇文章： 118707阅读：
技术网址连接
http：/ / /医生/ dev.mysql.com
文章分类
文章存档
阅读排行
最新评论