Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to install and deploy MySQL Cluster7.2.4". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to install and deploy MySQL Cluster7.2.4.

1.1 introduction to mysql-cluster

To put it simply, MySQLCluster is actually a completely distributed database system without shared storage devices, which is mainly implemented by NDBCluster (NDB) storage engine.

When MySQLCluster was born, it can be said to be an in-memory database that can persist data. All data and indexes must be loaded in memory to run normally. However, the latest version of MySQLCluster can only load all indexes and index data in memory, and the actual data can not be all loaded into memory. The structure is shown below:

1.2Cluster structure diagram

1.3 main structure of Cluster

1. Manage (MGM) node:

The role of such nodes is to manage other nodes in the MySQLCluster, such as providing configuration data, starting and stopping nodes, running backups, and so on. Because such nodes are responsible for managing the configuration of other nodes, they should be started before starting other nodes. The MGM node is started with the command "ndb_mgmd".

There is no requirement for hardware, and a poor machine is perfectly competent. The default port is 1186.

2. Data node:

This type of node is used to hold Cluster data. The number of data nodes is related to the number of copies and is a multiple of the fragment. For example, for two replicas, each with two fragments, there are four data nodes. However, there is no need to set up multiple copies. The data node is started with the command "ndbd".

You can choose large memory, and cpu is also relatively good, especially ndbmtd's support for multicore Cpu in the future. Default port 2202

3. SQL node:

This is the node used to access Cluster data. For MySQLCluster, the client node is a traditional MySQL server that uses the NDBCluster storage engine. Typically, SQL nodes are started with the command "mysqld-ndbcluster" or with "mysqld" after adding "ndbcluster" to "my.cnf".

Higher requirements for cpu, multi-core, high-frequency CPU is better.

1.4 Cluster characteristics

Distributed: distributed storage engine, which can be clustered by multiple NDBCluster storage engines to store part of the overall data.

Supporting transactions: like Innodb, supporting transactions

But not on the same host as mysqld: it can exist separately from mysqld on a separate host, and then communicate with mysqld through the network.

The memory demand is huge: the new version of the index and the indexed data must be stored in memory, and all data and indexes of the old version must be stored in memory.

Cluster uses automatic key value identification data slicing scheme, users do not need to care about data slicing scheme (partition key rules are provided after 5.1and later), and transparently implement distributed database. Data slicing rules are completed according to 1, primary key, 2 unique index, 3 automatic row identification rowid, and then the number of clusters is distributed. Its access data is like RAID access mechanism, which can extract data from each node in parallel and hash data. When accessed with non-primary or partition keys, all cluster nodes will be scanned, affecting performance (this is a core challenge for Cluster)

Applicable scenarios:

Have very high concurrency requirements

The response to a single request is not very critical

The query is simple, the filtering conditions are relatively fixed, the amount of data per request is small, and you do not want to conduct horizontal Sharding by yourself.

Best practic

Make the query as simple as possible to avoid cross-node transmission of data; as far as possible to meet the computing performance of SQL nodes, larger cluster SQL nodes will obviously be more than Data nodes; use 10 Gigabit network environment to interconnect nodes as much as possible to reduce the delay of data transmission in the network layer

Advantages and disadvantages of 1.5 mysql-cluster

Advantages:

1) 99.999 per cent high availability

2) Fast automatic failover

3) flexible distributed architecture with no single point of failure

4) High throughput and low latency

5) strong scalability and support for online expansion

Disadvantages:

1) there are many restrictions, such as: foreign keys are not supported, data rows cannot exceed 8K (excluding data in BLOB and text)

2) deployment, management and configuration are very complex

3) large disk space and large memory

4) backup and recovery are not convenient

5) the performance of complex sql queries is average.

2 installation

2.1 introduction to the environment

Mysqlcluster7.2.4 installation configuration

Prepare the 64-bit version of 5 servers with 4G memory and 60G hard disk 2cpu

10.100.200.36 64-bit

10.100.200.37 64-bit

10.100.200.38 64 bit

10.100.200.39 64 bit

10.100.200.41 64 bit

Management Node (ndb_mgmd) 10.100.200.36 64-bit

Data Node (ndbd) 10.100.200.37 64 bit 10.100.200.38 64 bit

SQL node (mysqld/api) 10.100.200.39 64-bit 10.100.200.41 64-bit

Download the installation package: mysql-cluster-gpl-7.2.4-linux2.6-x86_64.tar.gz

Http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.2/

2.1 install the management node (10.100.200.36)

2.1.1 extract the file

Shell > tar-xvfmysql-cluster-gpl-7.2.4-linux2.6-x86_64.tar.gz

2.1.2 create an installation directory

Shell > mkdir-p / usr/local/mysql/bin

Shell > mkdir-p / usr/local/mysql/ndbdata

2.1.3 add execution file path

Shell > vi ~ / .bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

2.1.4 copy the execution file

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

Shell > cp bin/ndb_mgmd/usr/local/mysql/bin

Shell > cp bin/ndb_mgm/usr/local/mysql/bin

2.1.5 create a log file storage directory

Shell > mkdir/var/lib/mysql-cluster

2.1.6 create configuration file directory and modify configuration file

Shell > mkdir/usr/local/mysql/cluster-conf

Shell > vi / usr/local/mysql/cluster-conf/config.ini

2.2 manage Node Profil

[NDBDDEFAULT]

# TotalSendBufferMemory= 256M

NoOfReplicas=2

DataMemory=2500M

IndexMemory=256M

DataDir=/var/lib/mysql-cluster

# 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

MaxNoOfExecutionThreads=4

TransactionDeadLockDetectionTimeOut=10000

BatchSizePerLocalScan=512

# Increasing the LongMessageBufferb/c of a bug (20090903)

LongMessageBuffer=8M

# Heartbeating

HeartbeatIntervalDbDb=15000

HeartbeatIntervalDbApi=15000

[MGMDEFAULT]

PortNumber:1186

DataDir:/var/lib/mysql-cluster1

[TCPDEFAULT]

SendBufferMemory:64M

# #

# Change HOST1 to the name of the NDB_MGMD host

# Change HOST8 to the name of the NDBD host

# Change HOST9 to the name of the NDBD host

# #

[NDB_MGMD]

NodeId:1

HostName:10.100.200.36

ArbitrationRank:1

[NDBD]

NodeId:4

HostName:10.100.200.37

[NDBD]

NodeId:5

HostName:10.100.200.38

# # #

# Note: The following can be MySQLDconnections or #

# NDB API application connecting to thecluster #

# # #

[API]

NodeId:10

HostName:10.100.200.39

ArbitrationRank:2

[API]

NodeId:11

HostName:10.100.200.39

ArbitrationRank:2

[API]

NodeId:12

HostName:10.100.200.39

ArbitrationRank:2

[API]

NodeId:13

HostName:10.100.200.39

ArbitrationRank:2

[API]

NodeId:14

HostName:10.100.200.39

ArbitrationRank:2

[API]

NodeId:15

HostName:10.100.200.41

ArbitrationRank:2

[API]

NodeId:16

HostName:10.100.200.41

ArbitrationRank:2

[API]

NodeId:17

HostName:10.100.200.41

ArbitrationRank:2

3 install data node (10.100.200.37 10.100.200.38)

Extract the file

Shell > tar-zxvfmysql-cluster-gpl-7.2.4-linux2.6-x86_64.tar.gz

Create an installation directory

Shell > mkdir-p / usr/local/mysql/bin

Shell > mkdir-p / usr/local/mysql/ndbdata

Copy execution file

Shell > cp mysql-cluster-gpl-7.2.4-linux2.6-x86_64/bin/ndbd/usr/local/mysql/bin

Add execution file path

Shell > vim ~ / .bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

Create a profile:

Shell > vim / etc/my.cnf

Add the following to the host:

[mysql_cluster]

# configure data node connection management node

Ndb-connectstring=10.100.200.36

4 install SQL Node 1 (10.100.200.39 10.100.200.41)

Extract the file

Shell > tar-zxvfmysql-cluster-gpl-7.2.4-linux2.6-x86_64.tar.gz

Copy installation files

Shell > mvmysql-cluster-gpl-7.2.4-linux2.6-x86_64 / usr/local/mysql

Add mysql user

Shell > groupaddmysql

Shell > useradd-gmysqlmysql

Add permission

Shell > chown-Rmysql:mysql/usr/local/mysql

Copy configuration fil

Shell > cp / usr/local/mysql/support-files/my-large.cnf/etc/my.cnf

Initialize the database

Shell > cd / usr/local/mysql

Shell > scripts/mysql_install_db--user=mysql

Copy the files required by the daemon

Shell > cp / usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld

Configure daemon

Shell > chkconfig--addmysqld

Shell > chkconfig--level 35mysqld on

Add execution file path

Shell > vim~/.bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

Change the configuration file

Shell > vim / etc/my.cnf

Edit the following information separately:

[mysqld] # configure the SQL node to connect to the management node

Port= 3307

Socket= / tmp/mysql.sock

Ndbcluster

Ndb-connectstring=10.100.200.36

5 start and shut down cluster

Launch sequence Management Node-> data Node-> SQL Node

A, start the management node

[root@banggocluster-conf] # ndb_mgmd-f / usr/local/mysql/cluster-conf/config.ini

B. Start the data node

Ndbd-initial # (options must be added for the first startup, backup / restore, and configuration file modification also need to be performed)

Ndbd # is not the first time to start a command that needs to be executed

C. Start the SQL node

/ etc/rc.d/init.d/mysqldstart or servicemysqldstart

After startup, you can use mysql-uroot-p to verify the mysql service status.

Closing order: SQL Node-> Management Node

A. Close the SQL node

/ etc/rc.d/init.d/mysqldstop or servicemysqld stop

B. Close the data node

Ndbdstop

C. Close the management node

Ndb_mgm > shutdown

6 verify the status of cluster

Verify the correctness of the configuration

1. Execute in the management node: ndb_mgm- > show

[root@banggo~] # ndb_mgm

-NDB Cluster-Management Client--

Ndb_mgm > show

Connectedto Management Server at: localhost:1186

ClusterConfiguration

-

[ndbd (NDB)] 2 node (s)

Id=4 @ 10.100.200.37 (mysql-5.5.19 ndb-7.2.4, Nodegroup:0, Master)

Id=5 @ 10.100.200.38 (mysql-5.5.19 ndb-7.2.4, Nodegroup:0)

[ndb_mgmd (MGM)] 1 node (s)

Id=1 @ 10.100.200.36 (mysql-5.5.19 ndb-7.2.4)

[mysqld (API)] 2 node (s)

Id=10 @ 10.100.200.39 (mysql-5.5.19 ndb-7.2.4)

.

Id=14 @ 10.100.200.41 (mysql-5.5.19 ndb-7.2.4)

.

7 functional testing of ndbcluster storage engine (compared with innoDB)

8 MySQLClster backup and recovery

Backup

Use the ndb_mgm management client to backup. Execute ndb_mgm at the management node to enter the management command line. Enter startbackup to start the backup of all nodes. If followed by the id of the corresponding data node, only the corresponding data node will be backed up.

Restore (reference: http://xxtianxiaxing.iteye.com/blog/563063)

Use the command ndb_restore to restore as follows:

Start the management node

/ usr/bin/ndb_mgmd-f / usr/local/mysql/mysql-cluster/config.ini--reload

Start the data node:

/ usr/bin/ndbd-- initial

When the first node is restored

Restore the table structure at the first node (if the change in the table structure is not involved, do not use the parameter-m)

/ usr/bin/ndb_restore-c 10.100.200.37While n 11-b 1-m-backup_path=/data/backup/backup-1/

Restore data, restore data can be carried out by several ndb nodes together, the speed will be faster!

/ usr/bin/ndb_restore-c 10.100.200.37While n 11-b 1-r-- backup_path=/data/backup/backup-1/

When other nodes are restored (later nodes do not need to add-m parameter)

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

9 MySQLClster add Node 1 online

Increasing or decreasing the number of data nodes is related to NoOfReplicas. Generally speaking, NoOfReplicas is 2, so the increase or decrease in number should also be in pairs, otherwise another NoOfReplicas should be set.

The first step is to add the settings of two nodes to the configuration file, as follows:

? viconfig.ini add

[NDBD]

Id=21

HostName=10.100.200.37

[NDBD]

Id=22

HostName=10.100.200.38

Using the ndb_mgm tool, stop the management node, and then restart it

Execute ndb_mgm to enter the management command interface, find the id of the management node, then execute the management node idstop, and exit

Execution

/ usr/bin/ndb_mgmd-f / usr/local/mysql/mysql-cluster/config.ini-- reload restart the management node

Log in and use the show command to see if there are two newly added nodes!

Ring restart (one restart of data nodes and sql nodes)

Initialize the two newly added nodes:

Ndbd--initial (data files, undo log files, etc., are automatically created after initialization)

Create a nodegroup through ndb_mgm

CREATENODEGROUP 21,22s

Reallocate data from cluster

SELECTTABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'NDBCLUSTER'

ALTERONLINE TABLE table_nameREORGANIZE PARTITION

Alteronline table card_packagereorganize partition

Then use the show command or use the ndb_desc command to view

Ndb_desc-c 10.80.30.39-dbgcarddbcard_package-p

-c is followed by the ip,-d of the management node and the name of the database, table_name is the table name, and-p outputs the details of the partition

10 MySQLClster user rights sharing

Execute mysql-uroot-p on sql node 41

< /usr/local/mysql/share/ndb_dist_priv.sql 在mysql终端调用名字为mysql_cluster_move_privileges的存储过程，这个存储过程的功能是备份权限表，然后将表的存储引擎转换为ndbcluster引擎。 mysql>

CALLmysql.mysql_cluster_move_privileges ()

Automatic backup permissions table

Shell > mysqldumpmysql userdbtables_privcolumns_privprocs_priv > backup_file.sql

Verify permissions table

Mysql > show create tablemysql.user; to see whether engine is ndbcluster.

Add a test account to 41

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

[root@banggo~] # mysql-h20.100.200.39-utest-ptest123-P3307

OK, can connect, the test is successful!

11.1 MySQLClster backup and recovery extension (see No8 for operation)

1. The concept of cluster backup

Backup refers to a snapshot of a database at a given time. A backup consists of three main parts:

(1) Metadata (metadata): names and definitions of all database tables.

(2) Tablerecords (table record): the data actually saved in the database table when the backup is performed.

(3) Transactionlog (transaction log): a continuous record that indicates how and when data is saved in a database.

Each part (these three parts) is saved on all the data nodes that participated in the backup. During the backup process, each node will save these three parts in three files on disk (that is, several nodes will save several copies of the same data. For example, if there are two data nodes, it will be saved twice on each of the two nodes, and the saved directory defaults to

[NDBD]

DateDir=/usr/local/mysql/BACKUP

(4) BACKUP-backup_id.node_id.ctl

A control file that contains control information and metadata. Each node saves the same table definition (for all tables in the cluster) in its own file

(5) BACKUP-backup_id-0.node_id.data

A data file that contains table records, which is saved as fragments, that is, different nodes save different fragments during backup. The file saved by each node begins with a title indicating the table to which the record belongs. There is a footnote containing the checksum of all records at the end of the record list.

(6) BACKUP-backup_id.node_id.log

A log file that contains records of committed transactions. In the log, only transactions on tables that have been saved in the backup are saved. The nodes participating in the backup will keep different records because different nodes hold different database fragments.

In the above list, backup_id refers to the backup ID,node_id that is the only ID of the node on which the file was created.

Before you use the management server to create a backup and start a backup, ensure that the cluster is properly configured for the backup operation.

2. Backup parameters (the following parameters are written in the config.ini configuration file of mgmd)

The parameters discussed in this section define the set of memory buffers related to the execution of online backups.

(1) BackupDataBufferSize

In the process of creating a backup, two types of buffers are used to send data to disk. The backup data buffer is used to populate the data recorded by the table of the scan node. Once the buffer is populated to the specified horizontal BackupWriteSize (see introduction below), the page is sent to disk. While the page is written to disk, the backup process can continue to fill the buffer until it runs out of space. When this occurs, the backup process pauses the scan until some disk writes complete and memory is freed, and then the scan continues.

The default value for this parameter is 2MB.

(2) BackupLogBufferSize

Backup log buffering plays a role similar to backup data buffering, except that it is used to generate logs for all table writes made during the backup. The same principle applies to page writes in the case of backup data buffers, except that when there is no extra space in the backup log buffer, the backup will fail. For this reason, the backup log buffer should be large enough to handle the load generated when the backup is performed.

The default value for this parameter is appropriate for most applications. In fact, the reason for backup failure is more likely to be due to insufficient disk writes than full backup log buffers. If the disk subsystem is not configured for the write load generated by the application, the cluster will most likely not be able to perform the required operations. It is best to configure the cluster in an appropriate way so that the processor becomes a bottleneck rather than a disk or network connection. The default value is 2MB.

(3) BackupMemory

This parameter is the sum of BackupDataBufferSize and BackupLogBufferSize. The default value is 2MB + 2MB = 4MB.

(4) BackupWriteSize

This parameter specifies the size of messages written to disk by backup log buffers and backup data buffers. The default value is 32KB.

(5) BackupDataDir

# change the default backup directory, BackupDataDir=/mysqlback

# of course, mkdir/mysqlback, it needs to run on all data nodes

You can also specify the directory where the backup is stored. By default, the directory is FileSystemPath/BACKUP

(6) FileSystemPath

This parameter specifies the directory where all files, REDO logs, UNDO logs, and data files created for metadata are stored. The default directory is specified by DataDir. Note that this directory must already exist before you start the ndbd process.

(7) DataDir

This parameter specifies the directory where trace files, log files, pid files, and error logs are stored.

12.1 improvement of Join 1

In the current mysqlcluster 7.2test version, the function of join has been greatly improved, which can be said to be a milestone version. As a result of testing some time ago, it was found that the performance of mysqlcluster 7.1.10 on join is very bad. In addition, if the table of jion is relatively large, it will lead to a very large data transfer between nodes.

We know that in versions prior to 7.2, jion was executed on mysqld nodes, which required interactive transfer of data from data nodes, resulting in very poor performance of join. Now version 7.2 puts join on the data node for execution and parallel processing, reducing the interaction of data in the sql node, so the performance is greatly improved.

Import data test (omit, see join.txt) first turn off the ndb_join_pushdown parameter for testing

Click (here) to collapse or open

Mysql > SELECT TABLESPACE_NAME, FILE_NAME, EXTENT_SIZE*TOTAL_EXTENTS/1024/1024 ASTOTAL_MB, EXTENT_SIZE*FREE_EXTENTS/1024/1024 AS FREE_MB, EXTRA FROMinformation_schema.FILESWHERE FILE_TYPE=\ "DATAFILE\"

+-+

| | TABLESPACE_NAME | FILE_NAME | TOTAL_MB | FREE_MB | EXTRA | |

+-+

| | ts_1 | data_1.dat | 512.00000000 | 301.00000000 | CLUSTER_NODE=4 | |

| | ts_1 | data_1.dat | 512.00000000 | 301.00000000 | CLUSTER_NODE=5 | |

| | ts_1 | data_1.dat | 512.00000000 | 300.00000000 | CLUSTER_NODE=6 | |

+-+

3rows in set (0.05sec)

At this point, I believe you have a deeper understanding of "how to install and deploy MySQL Cluster7.2.4". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.