Pandora:Documentation en:MySQL Cluster

From Pandora FMS Wiki

Jump to: navigation, search

Go back to Pandora FMS documentation index

Contents

HA in Pandora FMS with MySQL Cluster

Introduction

MySQL Cluster allows the database clustering in a non sharing scenario. This reduces the number of single points of failure as it's possible to use inexpensive hardware with few requirements while still having redundancy of hardware.

MySQL Cluster mixes the MySQL database server with an in memory clustered storage engine called NDB. In our documentation when we talk about NDB we talk about the storage engine, meanwhile when we talk about MySQL Cluster we talk about the combination of the database server technology and the NDB storage engine. A MySQL Cluster is a set of servers each one running several processes including MySQL servers, data nodes for the NDB storage engine, management severs, and (probably) specific programs to access the data.

All data stored in a MySQL Cluster can be replicated so it can handle the failure of a single node without any more impact than a few transactions aborted as their status was lost with the node. As transactional applications are supposed to handle transaction errors this shouldn't be a problem.

Cluster related terms used in Pandora FMS documentation

Data Node
This kind of node stores the cluster data. There are as much data nodes as replicas times the number of fragments (at least). For example, with tow replicas, each with two fragments, four data nodes are needed. There is no need of having more than one replica. A data node is started with the command ndbd (or ndbmtd if the multithreaded version is started).
SQL Node (or API Node)
This is the node that access the data stored in the cluster. For MySQL Cluster this is a traditional MySQL server using NDB Cluster engine. A SQL node is started by the command mysqld with the option ndbcluster added in the my.cnf configuration file.
Manager or MGM
This is the cluster administration node. The role of this node is to manage all the other nodes in the cluster, allowing tasks like give configuration parameters, start and stop nodes, create backups, and in general all the management tasks of the cluster. As this is the node that manages the cluster configuration one of this kind of nodes should be started the first one, before any other one. The management node is started with the command ndb_mgmd.

Cluster Architecture to use with Pandora FMS

The sample architecture used in this documentation has two servers that will run data nodes, and SQL nodes, also it has two management servers used to manage the cluster.



Pandora cluster.png



The sample architecture has Pandoradb1 and Pandoradb2 as data and SQL nodes, Pandoradbhis and Pandora2 as managers, and finally Pandora1, Pandor2 and Pandora3 running pandora servers and pandora consoles.

There is also some assumptions in this architecture:

  • There is a load balancer in the front-end, balancing the tentacle and SNMP traffic to the three Pandora FMS servers with a RR (RoundRobin) type of algorithm.
  • There is a load balancer in the back-end to balance the queries done by the pandora servers and pandora consoles to the SQL nodes.

Those load balancers are external to pandora and can be either software or hardware. To use a software load balancer there is documentation in Pandora FMS about how to setup a keepalievd.

The purpose of the database cluster is to share the workload of the database when monitoring a high number of machines and parameters. For the cluster to work properly it's very important that the load balancer is well designed and works properly.

The database cluster characteristics are the following:

  • Works on memory, dumping to disk logs of the transactions.
  • Needs a manager to operate the recovery process.
  • Needs fast disks and fast network.
  • It has strict memory requirements.
  • It has to store all the database in memory to work fast.

To improve the performance of the cluster, more RAM can be added. In this example it's supposed that the requirement of RAM is 16 GiB for each server involved in the database.

Installation and Configuration

The documentation is based on a SUSE installation where the installation of MySQL Cluster implies the rpms with the MySQL cluster software, in this case the rpms are the following files:

  • MySQL-Cluster-gpl-client-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-extra-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-management-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-server-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-shared-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-storage-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-test-7.0.6-0.sles10.x86_64.rpm
  • MySQL-Cluster-gpl-tools-7.0.6-0.sles10.x86_64.rpmlibmysqlclient16

Configuring SQL Node and Data

In each data node or SQL node we should modify the /etc/my.cnf configuration file, that besides the current MySQL configuration should also contain some extra parameters of the cluster configuration. Next these parameters are described, and also the values we should give to them (the complete final configuration is at the end of this annex). The cluster configuration parameters in the my.cnf file are applied two two sections: mysqld and mysql_cluster.In the mysqld section the following parameters should be added:

  • ndbcluster: order to the mysql motor that it have to star the NDB motor for databases in cluster.
  • ndb-connectstring="10.1.1.215:1186;10.1.1.216:1186":contains the connection string to the /node/s of management. It is a string of characters with th host format: port,host:port.
  • ndb-cluster-connection-pool=10: connexion number in the connexion reserve, the cluster config.ini file should also define at least one MySQL node or an API node) for each connection
  • ndb-force-send=1: force the buffers to be sent inmediately without waiting for other threads.
  • ndb-use-exact-count=0:deactivate the NDB forced to count the registers while the consulting SELECT COUNT (*) planning to make the queries quicker.
  • ndb-autoincrement-prefetch-sz=256:determines the possibility of leaving blanks in an self incremented column. With a value of 1 the blanks, higher values speed the insertions, but reduce the possibilities that the consecutives numbers would be used in group insertions.

In the mysql_cluster section, the following parameters should be added:

  • ndb-connectstring="10.1.1.230:1186;10.1.1.220:1186": has the connection string to it/ the management node/s. It consist of a string of characters with the host format:port,host:port.

Here we can see an extract of the file:

[mysqld]
# Run NDB storage engine
ndbcluster
# Location of management servers
ndb-connectstring="10.1.1.215:1186;10.1.1.216:1186"   
# Number of connections in the connection pool, the config.ini file of the
# cluster have to define also [API] nodes at least for each connection.
ndb-cluster-connection-pool=10
# Forces sending of buffers to NDB  immediately, without waiting
# for other threads. Defaults to ON. 
ndb-force-send=1
# Forces NDB to use a count of records during SELECT COUNT(*) query planning 
# to speed up this type of query. The default value is ON. For faster queries
# overall, disable this feature by setting the value of ndb_use_exact_count
# to OFF. 
ndb-use-exact-count=0

# Determines the probability of gaps in an autoincremented column. 
# Set it to 1 to minimize this. Setting it to a high value for 
# optimization — makes inserts faster, but decreases the likelihood
# that consecutive autoincrement numbers will be used in a batch
# of inserts. Default value: 32. Minimum value: 1. 
ndb-autoincrement-prefetch-sz=256

# Options for ndbd process:
[mysql_cluster]
# Location of management servers (list of host:port separated by ;)
ndb-connectstring="10.1.1.230:1186;10.1.1.220:1186" 

Info.png

The final version of this file is on Annex 1

 


Manager Configuration

First we should create the directory where the information of the cluster (/var/lib/mysql-cluster/) will be kept and in this directory will be created the cluster configuration file from which we are going to give a summary with the most relevant parameters:


# MySQL Cluster Configuration file 
# By Pablo de la Concepción Sanz <pablo.concepcion@artica.es>
# This file must be present on ALL the management nodes
# in the directory /var/lib/mysql-cluster/

##########################################################
# MANAGEMENT NODES                                       #
# This nodes are the ones running the management console #
##########################################################

# Common configuration for all management nodes:

[ndb_mgmd default]

ArbitrationRank=1
# Directory for management node log files
datadir=/var/lib/mysql-cluster  

[ndb_mgmd]
id=1
# Hostname or IP address of management node
hostname=<hostname_nodo_de_gestion_1>        

[ndb_mgmd]
id=2
# Hostname or IP address of management node
hostname=<hostname_nodo_de_gestion_2> 

.
.
.

The final version of this file is at the end of this document.

The config.ini file is divided in the following options:

  • [ndb_mgmd default]: common configuration for all the management nodes.
  • [ndb_mgmd]:individual configuration of each management node.
  • [ndbd default]: common configuration of the data nodes.
  • [ndbd]: Configuración individual de cada nodo de datos
  • [mysqld default]: common configuration of all API or SQL nodes
  • [mysqld]: individual configuration of each API or SQL node
  • [tcp default]: Connection buffers configuration

Parameters of the common configuration of the management nodes

Arbitration Rank:

This parameter is useful to define which node will be the arbitrator (the management nodes and SQL nodes can arbitrate, it is recommended that there would be the management nodes will be the ones that have high priority), could have values from 0 to 2:

  • 0: The node will be never be used as arbitrator
  • 1: The node will have high priority, it will have priority over the nodes of low priority
  • 2: The node will have low priority and will only used as arbitratos if there are not other nodes of higher priority availables

Datadir: Directory where are kept the logs of the management node

Parameters of individual configuration of the two management nodes

There should be a section [ndb_mgmd] for each management node.

id: node identificator. It should be the only one in all the configuration file.

Hostname:host name or IP adress of the management node

Common Configuration Parameters for the Storage Nodes

NoOfReplicas: Redundancy, number of replies for each table kept in the cluster. This parameter also specifies the size of the node groups. A group of nodes is a set of nodes that keeps all the same information. It is recommended to stablish the number of replies to 2 that allow to have high availability.

Datadir: Directory where are kept the files related with the data node (logs, trace files,error files, files with the pid)

DataMemory: This parameter fix the space (in bytes) that is available to keep registers of the database, all the space that is shown is reserved in memory, so it is extremely important that there should be enough physical memory to reserve without the necesity of using the exchange memory.

IndexMemory: This parameter monitors the storage quantity used by hash index in MySQL Cluster. The hash index are always used by index with primary key, unique index and unique restrictions.

StringMemory: This parameter shows how much memory is reserved for strings of characters ( such as tht names of the tables), a value between 0 and 100 is taken as a percentage of the maximum value( that changes according to a big number of factors) while a value higher to 100 is interpreted as the number of bytes. (25% should be enough).

MaxNoOfConcurrentTransactions:This parameter shows the maximum number of transactions in a node. It should be the same for all data nodes. This is due to that if a node fails, the older node of the ones that are left, start again to create all the transactions of the fallen node (change the value of this parameter implies a complete stop of the cluster).

MaxNoOfConcurrentOperations: Shows the maximum number of registers that could be simultaneously in updating phase or bloqued

MaxNoOfLocalOperations: It is recommended to stablish this parameter with a value of the 110% of MaxNoOfConcurrentOperations.

MaxNoOfConcurrentIndexOperations: This parameter has a default value of 8192 and only in cases of extremely high parallelism that use unique hash index, it should be necessary to increase its value. It is posible to reduce its value if the Database administrator considers that there is not much parallelism and with it saving some memory.

MaxNoOfFiredTriggers: This parameter has by default a value of 4000, and it should be enough in the majority of cases. Some times it would be even posible to reduce its value if the Database administrator considers that there is not much parallelism.

TransactionBufferMemory: This temporal memory storage is used while the update of the index tables and of reading of unique index for keeping the key and the column in this operations, and usually, we should not modify the 1M default value.

MaxNoOfConcurrentScans: This parameter shows the maximum number of parallel scanning that the cluster could do, that could be able to support so many scans as the selected ones for this parameter in each node.

MaxNoOfLocalScans: This parameter shows the number of registers scanned locally if several scans are not made completely in parallel. If it is not specified, it is calculated as the product of MaxNoOfConcurrentScans by the number of data nodes.

BatchSizePerLocalScan:Shows the number of bloqued registers that are used to deal with concurrent scanning operations

LongMessagesBuffer: This parameter determines the size of a temporary internal storage for the information exchange between nodes.

NoOfFragmentLogFiles: This parameter shows how many redo log blocks will be generated and together with FragmentLogFileSize allows to determine the total size of the redo log.

FragmentLogFileSize: Size of the redo log extracts makes a redo log and it is the size with wich is reserved the space of redo log. A bigger size of the 16M of FragmentLogSize allows a bigger performance when there is much writting. In this case it is very recommended to increase the value of this parameter.

InitFragmentLogFiles: this parameter can have two values: SPARSE or FULL

  • SPARSE: this is the default value. The log fragments are created in a separated way.
  • FULL: forces to that all the bytes of the log fragments are written in disk.

MaxNoOfOpenfiles: This parameter limits the number of threads for the file opening. Any situation that requires to change this parameter could be reported as a bug.

InitialNoOfOpenFiles: Initial number of threads for the file opening.

MaxNoOfSavedMessages: Maximum number of trace files that are kept before to start overwriting the old ones.

MaxNoOfAttributes: Defines the maximum number of features that could be defined in the cluster. Each feature takes up about 200 bytes of storage for each node due to that all the metadata are replied in the servers.

MaxNoOfTables: Defines the total maximum of objects (table, unique hash index and ordered index) in the cluster.

MaxNoOfOrderedIndexes: For each ordered index in this cluster, an object is reserved that describes what is indexed and its storage segments. By default, each defined index defined an ordered index too. Each unique index and primary key has an ordered index and a hash index.

MaxNoOfTriggers: Defines the maximum number of triggers in the cluster.

LockPagesOnMainMemory: Lock the data node processes in the memory avoiding that they become swap. The possible values of the parameter are:

  • 0: Disables the lockout (default value).
  • 1: does the lockout after reserving the process memory.
  • 2: does the lockout before reserving the process memory.

StopOnError: Shows if the data node processes ends after an error or if they are restarted automatically. Diskless: Force to all the cluster to work without disk, in memory. This way the online backups are deactivated and it is not possible to start the cluster partially.

ODirect: Activating this parameter we use O_DIRECT writing in local checkpoints and redo logs, reducing the CPU load. It is recommended to activate it for systems on a Linux with a kernel 2.6 or higher.

CompressedBackup: When it is activated (1), it does a compression similar to gzip -fast saving up to 50% space in the backup files.

CompressedLCP: when it is activated (1), it does a compression similar to gzip -fast saving up to 50% space in the Checkpoint files.

TimeBetweenWatchDogCheck: Number of miliseconds of the WatchDog checking interval (thread that checks that the main thread is not lockout) if after 3 checks the main thread is in the same state in the watchdog will end the main thread.

TimeBeweenWatchDogCheckInitial:Has the same function that TimeBetweenWachdogCheck, but this value is applied in the initial phase of the cluster start, when the memory reserve is done.

StartPartialTimeout: Shows how long you have to wait from the cluster launching process is started until all the data node will be up. This parameter is ignored if it is a cluster starting. Its function is that the cluster would not be half launched.

StartPartitionedTimeout: If the cluster is ready to start without waiting Start PartialTimeout,but it is in a partitioned state, the cluster also wait to this timeout pass. This parameter is ignored if it is a cluster starting.

StartFailureTimeout: If a node has not finished its starting time and when this timeout ends the start fails, a 0 value shows that is indefinitely waited.If the node has much information (several data gigabytes), the this parameters should be increased ( the start with big amount of data could take 10 or 15 minutes).

HeartbeatIntervalDbDb: Shows how often are sent the pulse signals and how often we can expect to receive pulse signals. If we do not receive pulse signals from a node for 3 consecutive intervales, the node will be considered as down, so the maximum time for discovering a fail through the pulse sending process 4 times the value of this parameter. This parameter should not be changed very often and it should have the same value for all modes.

HeartbeatIntervalDbApi:Each node sends pulse signals to each MySQL or API node in order to make sure that the contact is kept. If a MySQL node can not send the pulse in time (following the criteria of the 3 pulses explained in HeartbeatIntervalDbDb), the it will be considered as down and all current transactions are finished and the resources will be released. A node can not reconnect until the resources of the previous instance would be released.

TimeBetweenLocalCheckpoints: is useful to avoid that in a cluster with low load will be done local checkpoints (if there is much load usually we start a new one inmediately after ending with the previous one). It is a value given as a logarithm in base 2 with the size to store in any checkpoint.

TimeBetweenGlobalCheckpoints: Shows how often the transactions are dumped into disk.

TimeBetweenEpochs: Shows the interval of the replication times of the cluster.Defines a timeout for the synchronization times of the cluster reply, if a module is not able to participate in a global checkpoint in the period fixed for this parameter, the node will be switched off.

TransactionDeadlockDetectionTimeout: Shows how long the transaction coordinator will wait for another mode will complete a query before aborting the transaction. This parameter is important for the deadlocks management and the nodes fail.

DiskSyncSize: Maximum size stored before dumping data to a local checkpoint file.

DiskCheckpointSpeed: transfer velocity in bytes by second of data sent to disk during a local checkpoint.

DiskCheckpointSpeedInRestart: transfer velocity in bytes by second of data sent to disk during a local checkpoint that is part of a Restart operation.

ArbitrationTimeout: times that a node waits for an arbitrator message. If this time is out, then it will be assumed that the network is divided.

UndoIndexBuffer: is used during the local checkpoints to registry the activities during the local checkpoints writting.


Template warning.png

It is not safe to reduce the value of this parameter

 


UndoDataBuffer: has the same function that the previous one, except that in this case it refers to the data memory instead of that of the index.


Template warning.png

It is not safe to reduce the value of this parameter

 


RedoBuffer: registry the update activities in order they could be executed again in case of the system restart and leave the cluster in a consistent state.

log levels comes from 0(nothing is reported to the log) to 15 (all related activity is reported to the log).

LogLevelStartup: log level of activity during the starting process.

LogLevelShutdown: log level of activity during the stopping process.

LogLevelStatistic:log level of statistic events activity (reading of primary keys, updates, insertions, etc...)

LogLevelCheckpoint: log level of activity during local and global checkpoints.

LogLevelNodeRestart: log level of activity during the restart of a Node.

LogLevelConnection: log level of activity of events generated through connections between nodes.

LogLevelError: log level of warning and error activity.

LogLevelCongestion: Log level of cluster congestion activity.

LogLevelInfo: Log level of the cluster general information activity.

MemReportFrequency:Number of seconds between registers of memory use of the data nodes. The data and index memory is recorded either in percentage as in 32KB pages number.

StartupStatusReportFrequency: Shows the reports when the redologs are started because a data node has been fired. The redologs start process could be large if the size of these are big, and this parameter allow to register the evolution of this start.

BackupReportFrequency: Shows the frecuency with witch the backup evolution is registered in the log during the process of creating a security copy.

BackupDataBufferSize: During the Backup process there are two buffers that are used to send data to the disk, when the buffer is full to the BackupWriteSize size and the Backup process could continue filling this buffer while it has space. The size of this parameter should be at least that of the BackupWriteSize + 188 KB

BackupLogBufferSize: Register the writing in tables during the Backup process. If it has no space in the backup log buffer, then the backup will fail. The size of this parameter should be at least the one of BackupWriteSize + 16 KB.

BackupMemory: Simply the sum of BackupDataBufferSize and BackupLogBufferSize.

BackupWriteSize: Tamaño por defecto de los mensajes almacenados en disco por el backup log buffer y el backup data buffer.

BackupMaxWriteSize:Size by default of the messages stored in the disk by the backup log buffer and the backup data buffer. The size of this parameter should be at least the one of BackupWriteSize.

BackupDataDir: Directory where the security copies are kept, in this directory is created a subdirectory called BACKUPS an in it one for each security copy that is called BACKUP-X (where X is the number of the security copy).


LockExecuteThreadToCPU:String with the CPUs identifiers in which the data node threads (ndbmtd) will be executed. It should be as many identifiers as the MaxNoOfExecutionThreads parameters say.

RealTimeScheduler: Fix this parameter to 1 activates the real time scheduler of the threads.

SchedulerExecutionTimer: Time in microseconds of thread execution in the scheduler before they be sent.

SchedulerSpinTimer: Time of execution in microseconds of the threads before sleeping.

MaxNoOfExecutionThreads: Number of execution threads (for 8 or more cores it is recommended to fix this parameter with an 8 value).

Individual Configuration Parameters for each Data node

It should be a section [ndbd] for each data node.


id: node identifier, it should be unique in all the configuration file.

Hostname: host name or IP address of the data node.

Common Parameters to API or SQL

ArbitrationRank: this parameter is useful to define which node works as arbitrator(the management nodes and the SQL nodes could work as arbitrators, it is recommended that the management nodes would have high priority(), you can take values from 0 to 2:

  • 0:The node will never be used as arbitrator.
  • 1: the node has high priority.It will have priority on nodes of low priority.
  • 2: the node has low priority, and will be only used as arbitrator if there are no other priority nodes.

In case of API or SQL nodes, it is recommended to fix the ArbitrationRank value to 2, allowing that it would be the manager nodes (that should have ArbitrationRank to 1) which have the rule of arbitrator

BatchByteSize: limits the process blocks by batchs that are used when we do complete scans of the tables or scans by ranks on indexes.

BatchSize: limits the process blocks by batchs that are used when we do complete scans of the tables or scans by ranks on indexes.

MaxScanBatchSize: total limit for all the cluster of the size of process blocks by batchs that are used when we do complete scans of the tables or scans by ranks on index. This parameter avoid that too many data would be sent from many nodes in parallel.

Total limit for all the cluster of the size of process blocks by batchs that are used when complete scans of tables are done or scans by ranks on indexes. This parameter avoids that too many data will be sent from many nodes in parallel.

Individual Configuration Parameters for each API or SQL node

It should be a section [mysqld] for each API or SQL node, there should be also extra sections [mysqld] to allow check or backup connections. For it, it is recommended to define these extra connections giving them a node identifier, but not a hostname, so any host could connect through the extra connections.

id: node identifier.It should be unique in all the configuration file.

Hostname: host name or Ip adress of the data node.


Info.png

In our example documentation an architecture, we have done that the API/SQL nodes and the NDB data node would be phisically in the same system. This has not to be like this

 


Starting the Cluster

Starting the Manager

Info.png

We have configured the servers for the automatic stop/launch of the cluster management demons.The procedures that we detail here are to do the manual stops and starts and to know the functioning of them. We have developed an script for the stop and start and we have scheduled the default start level of the systems (level 3)

 


Once we have done the installing and configuring procedures of the Manager system, we should start the service.

To start the administration node, we should execute the following command of the console: (as root) Administration node 1:

ndb_mgmd  --config-file=/var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster

In the same way, through the script that has been developped for this at:

/etc/init.d/cluster_mgmt start

Administration node 2:

ndb_mgmd -c 10.1.1.221:1186 –-ndb-nodeid=2

In the same way, through the script that has been developed for this at:

/etc/init.d/cluster_mgmt start

If you want also load a new version of the configuration file, you should pass to both nodes start the –initial parameter.

The control script of the service (etc/init/cluster_mgmt) could be used to start the node (start) and to stop it (stop) or restart it (restart) and also to know its status (status).

Start of the Cluster Data Nodes (ONLY INSTALATION!)

Once that the Manager has been launched, we start to launch the nodes with the following command in the console (as root):

ndbmtd -–initial

This fix the initial configuration of the nodes (that obtain from the manager) and keep the redo log space. "Normal" start of the cluster data nodes.

In case of the restart of one of the nodes, due to fall or to some kind of technical stop, the nodes will be started using only ndbmtd, sin el --initial, so this parameter does that the configuration loads from zero and it restart the node data files and the redo logs (making necessary to restore data from a Backup).

ndbmtd

You could use the script developed for the control of the demon of the cluster storage node:

/etc/init.d/cluster_node start

This script could be used to start the node (start) and to stop it (stop) or to restart it (restart), and also to know its status (status).

Template warning.png

Due to the importance of the starting process of the cluster data nodes, this process WILL BE NOT AUTOMATED.This is, you have to do it manually after a restart

 


the starting process of nodes is very delicate (if you have done a messy stop, or if the cluster has been left in a non synchronized status, then you should check the logs and the manufacturer documentation (MySQL) to know how solving the problem before firing the nodes.

The start process of a data node could be an SLOW process. It could take between 10 and 20 minutes.To check the status, in the starting proccess, use the "SHOW" command in the MySQL cluster manager console, such as we are going to show later.

Starting SQL Nodes

The SQL Nodes are started using the command:

/etc/init.d/mysql start

And they are stopped with

/etc/init.d/mysql stop 

As if it were a normal Mysql server.This does that all the threads defined in the /etc/my.cnf would be connectoed to the cluster, finishing this way the complete start of the cluster.

Visualizing the Cluster Status

Once we have all the elements started, es can see if they have been correctly connected to the cluster. For it, in the Manager console we should writte:

ndb_mgm

And we enter in the cluster administration interface, once in it, we write:

show

And we will obtain something like this:

Connected to Management Server at: 10.1.1.221:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @10.1.1.215  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=4    @10.1.1.216  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @10.1.1.221  (mysql-5.1.34 ndb-7.0.6)
id=2    @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]   29 node(s)
id=11   @10.1.1.215  (mysql-5.1.34 ndb-7.0.6)
id=12   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
id=13   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
id=14   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
id=15   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
id=16   @10.1.1.216  (mysql-5.1.34 ndb-7.0.6)
.

As we can see in this exit, we have the management nodes, the Data nodes and the SQL or API nodes connected to the cluster. There are also a serial of SQL or API nodes that are free, without connections, that accept connections from any host, and that are used to status checks, backup creation, etc...

If we have just started the data nodes, we could see a message as the following:

[ndbd(NDB)] 2 node(s) id=3 @10.1.1.215 (mysql-5.1.34 ndb-7.0.6, starting, Nodegroup: 0, Master) id=4 @10.1.1.216 (mysql-5.1.34 ndb-7.0.6, starting, Nodegroup: 0)

This shows that the system is still starting the data nodes.

Start and Stop of Nodes from the Manager

It is possible to start and stop the nodes in the cluster from the Manager, this is, without having to go to the console of each node.

To stop a node, we will use the order:


<id> stop

Being the <id> the number that is shown when you do a show.

2 stop

To start the node that we have stopped, we use the order:

<id> start

Being the <id> the number that is shown when we do a show.Example:

2 start

Cluster Backups

It is recommended to do a security copy of the cluster data and structures. For it, you have to follow these instructions:

  1. .Start the administration server (ndb_mgm).
  2. .Execute the START BACKUP command.
  3. .We will get an exit like this:
ndb_mgm> START BACKUP
Waiting for completed, this may take several minutes
Node 2: Backup 6 started from node 1
Node 2: Backup 6 started from node 1 completed
StartGCP: 267411 StopGCP: 267414
#Records: 2050 #LogRecords: 0
Data: 32880 bytes Log: 0 bytes

It is possible to start the shell security copy of the system using:

ndb_mgm -e "START BACKUP"

These backups will create a serial of files in the directory: /var/lib/mysql-cluster/BACKUP/BACKUP-X of each node of the cluster, where the x is the backup number.

In this directory are kept a serial of files with the following extensions:

  • Data: cluster data
  • .ctl: cluster metadata
  • .log: cluster LOG files.

Restoring Security Copies

Each node keeps "one part" of the DDBB in the backups, so to recompose the "complete balance" yo should do a restore of all the elements of the cluster, in order and one by one.

Previous Steps

To restore a backup, you have previously to "restart" the nodes and eliminate their content. This is, to start them with the –initial parameter.

ndbmtd –initial

Order of the Restoring Process

To restore a backup, you have to do it first with the node selected as "master". The first restoring will create the metadata, the rest only the data.

Restoring Process

The order to restore a backup is this (we take as example the restore of the backup #5 on the node id #3):

In the first node, we execute this in the Linux console:

ndb_restore -b 5 -n 3 -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-5

And we get the following exit:

Backup Id = 5
Nodeid = 3
backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-5
Ndb version in backup files: Version 5.0.51

In the second and consecutive nodes, it will be similar, but without the “-m” parameter.

ndb_restore -b 5 -n 4 -r /var/lib/mysql-cluster/BACKUP/BACKUP-5

The options that will be given to it are detailed next:

  • -b: Shows the backup number.
  • -n: shows the specific node (that could be seen in the manager with a "show").
  • -m: shows that the cluster meta data should be restored.
  • -r: shows that data should be restored in the cluster.

After this, you should put the path to the directory ( put the path in the backup we have put in the -b)

Cluster Logs

The MySQL cluster provides two kinds of logs.

The Cluster log

Includes the events generated by each node of the cluster. It is the most recommended log to see if something fails, so it includes the information of the whole cluster.

By default this log is at the directory /var/lib/mysql-cluster/ndb_1_cluster.log

An example of this kind of logs is this:

2009-05-26 11:56:59 [MgmSrvr] INFO     -- Node 5: mysqld --server-id=0
2009-05-26 12:14:32 [MgmSrvr] INFO     -- Mgmt server state: nodeid 6 reserved for ip 10.1.1.220, m_reserved_nodes 0000000000000062.
2009-05-26 12:14:32 [MgmSrvr] INFO     -- Node 6: mysqld --server-id=0
2009-05-26 13:35:47 [MgmSrvr] INFO     -- Mgmt server state: nodeid 6 freed, m_reserved_nodes 0000000000000022.
2009-05-26 13:46:44 [MgmSrvr] INFO     -- Mgmt server state: nodeid 6 reserved for ip 10.1.1.220, m_reserved_nodes 0000000000000062.
2009-05-26 13:46:44 [MgmSrvr] INFO     -- Node 6: mysqld --server-id=0
2009-05-26 13:46:44 [MgmSrvr] INFO     -- Node 2: Node 6 Connected
2009-05-26 13:46:45 [MgmSrvr] INFO     -- Node 3: Node 6 Connected
2009-05-26 13:46:45 [MgmSrvr] INFO     -- Node 3: Node 6: API version 5.0.51
2009-05-26 13:46:45 [MgmSrvr] INFO     -- Node 2: Node 6: API version 5.0.51

The useful information is identified with the words WARNING, ERROR y CRITICAL.

Logs of the Nodes

Each node of the cluster has its own logs, that are divided in two sub-logs. (all logs are at the directory/var/lib/mysql-cluster/).

ndb_X_out.log

The first and most general log is: ndb_X_out.log (being X the node id).This log has the cluster general information and it is like this:


2009-09-29 13:15:51 [ndbd] INFO     -- Angel pid: 30514 ndb pid: 30515
NDBMT: MaxNoOfExecutionThreads=8
NDBMT: workers=4 threads=4
2009-09-29 13:15:51 [ndbd] INFO     -- NDB Cluster -- DB node 3
2009-09-29 13:15:51 [ndbd] INFO     -- mysql-5.1.34 ndb-7.0.6 --
2009-09-29 13:15:51 [ndbd] INFO     -- WatchDog timer is set to 40000 ms
2009-09-29 13:15:51 [ndbd] INFO     -- Ndbd_mem_manager::init(1) min: 4266Mb initial: 4286Mb
Adding 4286Mb to ZONE_LO (1,137151)
NDBMT: num_threads=7
thr: 1 tid: 30520 cpu: 1 OK BACKUP(0) DBLQH(0) DBACC(0) DBTUP(0) SUMA(0) DBTUX(0) TSMAN(0) LGMAN(0) PGMAN(0) RESTORE(0) DBINFO(0) PGMAN(5) 
thr: 0 tid: 30519 cpu: 0 OK DBTC(0) DBDIH(0) DBDICT(0) NDBCNTR(0) QMGR(0) NDBFS(0) TRIX(0) DBUTIL(0) 
thr: 2 tid: 30521 cpu: 2 OK PGMAN(1) DBACC(1) DBLQH(1) DBTUP(1) BACKUP(1) DBTUX(1) RESTORE(1) 
thr: 3 tid: 30522 cpu: 3 OK PGMAN(2) DBACC(2) DBLQH(2) DBTUP(2) BACKUP(2) DBTUX(2) RESTORE(2) 
thr: 4 tid: 30523 cpu: 4 OK PGMAN(3) DBACC(3) DBLQH(3) DBTUP(3) BACKUP(3) DBTUX(3) RESTORE(3) 
thr: 6 tid: 30515 cpu: 6 OK CMVMI(0) 
thr: 5 tid: 30524 cpu: 5 OK PGMAN(4) DBACC(4) DBLQH(4) DBTUP(4) BACKUP(4) DBTUX(4) RESTORE(4) 
saving 0x7f6161d38000 at 0x994538 (0)
2009-09-29 13:15:53 [ndbd] INFO     -- Start initiated (mysql-5.1.34 ndb-7.0.6)
saving 0x7f61621e8000 at 0x9ab2d8 (0)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer

ndb_X_error.log

The second kind of log is the cluster error log that is named: ndb_X_error.log (being X the node id). In this log we have the errors that are made in the cluster and that link us to another log created at a higher leve of debug.

Here we see the exit of a error log file linked to another trace log:


Current byte-offset of file-pointer is: 1067                      

Time: Friday 9 October 2009 - 12:57:13
Status: Temporary error, restart node
Message: Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s) (Arbitration error)
Error: 2305
Error data: Arbitrator decided to shutdown this node
Error object: QMGR (Line: 5300) 0x0000000e
Program: ndbmtd
Pid: 30515
Trace: /var/lib/mysql-cluster/ndb_3_trace.log.1 /var/lib/mysql-cluster/ndb_3_trace.log.1_t1 /var/lib/mysql-cluster/ndb_3_
Time: Tuesday 24 November 2009 - 12:01:59
Status: Temporary error, restart node
Message: Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s) (Arbitration error)
Error: 2305
Error data: Arbitrator decided to shutdown this node
Error object: QMGR (Line: 5300) 0x0000000a
Program: /usr/sbin/ndbmtd
Pid: 10348
Trace: /var/lib/mysql-cluster/ndb_3_trace.log.2 /var/lib/mysql-cluster/ndb_3_trace.log.2_t1 /var/lib/mysql-c 

As we can see it leaves a trace in the following files: /var/lib/mysql-cluster/ndb_3_trace.log.2, /var/lib/mysql-cluster/ndb_3_trace.log.2_t1, ...

We can see a piece of one of these files and see how it is:

--------------- Signal ----------------
r.bn: 252 "QMGR", r.proc: 3, r.sigId: -411879481 gsn: 164 "CONTINUEB" prio: 0
s.bn: 252 "QMGR", s.proc: 3, s.sigId: -411879485 length: 3 trace: 0 #sec: 0 fragInf: 0
H'00000005 H'00000002 H'00000007
--------------- Signal ----------------
r.bn: 253 "NDBFS", r.proc: 3, r.sigId: -411879482 gsn: 164 "CONTINUEB" prio: 0
s.bn: 253 "NDBFS", s.proc: 3, s.sigId: -411879492 length: 1 trace: 0 #sec: 0 fragInf: 0
Scanning the memory channel every 10ms

It is easy to monitor these logs with Pandora itself doing searches of the words WARNING y CRITICAL.

General Procedures

The management individual procedures of each kind module are given in the first place, and later the start and stop procedure for the cluster.

Cluster Manager Process Management

As root:

To start the cluster manager:

/etc/init.d/cluster_mgmt start

To check that it is running:

/etc/init.d/cluster_mgmt status

To stop the Manager process:

/etc/init.d/cluster_mgmt stop

Nodes Management from the Manager

We enter in the shell of the cluster Manager with:

ndb_mgm

We stop the node that we want with:

2 stop

Being the "2" the ID of the node to stop.

To start a node we will use the order:

2 start

Data Node Management with the start scripts

As root:

To start a data node

/etc/init.d/cluster_node start

to stop a data node:

/etc/init.d/cluster_node stop

To start a data node:


Template warning.png

This operation delete the node data of the cluster and restart the redologs and could require a recovery from the backup

 


/etc/init.d/ndbmtd initial

SQL Nodes Management with Starting Scripts

The SQL nodes are managed in the same way that a MySQL server that is not in cluster, through the starting script /etc/init.d/mysql

To start as many SQL nodes as the /etc/my.cnf file indicates.

/etc/init.d/mysql start

To stop as many SQL nodes as the /etc/my.cnf indicates.

/etc/init.d/mysql stop

Launching of a node manually if it is down. If a node downs we should start it manually from the command line following this sequence: First we need to be sure that there is no instance of the Node running:

ps -fea | grep -v grep | grep ndbmtd

Or also:

/etc/init.d/cluster_node status


If the command shows any ndbmtd process running, we should check the losgs to see why even with the process running it has been considered as down.

To start the node we use:

/etc/init.d/cluster_node start

Creating Backups from the Command Line

This is the method for creating a backup manually from the command line:

ndb_mgm -e "START BACKUP”

The backups are kept in:

/var/lib/mysql-cluster/BACKUP

The script of the daily backup is in the Annex 1.

Restoring Backups from the Command Line

Once in the Node of which we want to restore the backup:

ndb_restore -b X -n Y -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-X

The “X” should be replaced by the number of the backup that you want to change and the "Y" by the number of the Node in which we are.

Procedure of Total Stop of the Cluster

Before stopping the cluster, you should do a backup of it, following the procedure previously defined or using the backup script described in the Annex 1.

Once we have finish the backup, it is also recommended to stop the Pandora FMS servers before stopping the cluster.

With all the necessary preparations done, the cluster will be stopped from the manager with the order SHUTDOWN.From the console:

ndb_mgm

ndbm_mgm> SHUTDOWN

Or also from the command line:

ndb_mgm -e SHUTDOWN

This will stop the management nodes and the cluster data ones, and the SQL (ore API) nodes stop separately, as we have said before.

Procedure to Start the Cluster

The start of the complete cluster is an operation that should be checked and while it is being done you should check the cluster main log and check that all has worked right.

When all the nodes are stopped, we should start first the main manager (the one of pandoradbhis), showing it the cluster configuration file.

Using the starting script.

/etc/init.d/cluster_mgmt start

Or also from the command line.

/usr/sbin/ndb_mgmd –config-file=/var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster

Next we start the secondary manager of the cluster ( the one of pandora2) giving the connection string and its node id the main manager.

Using the starting script.

/etc/init.d/cluster_mgmt start

Or also from the command line

/usr/sbin/ndb_mgmd -c pandoradbhis –-ndb-nodeid=2 –configdir=/var/lib/mysql-cluster


At this point it is possible to connect to any of the two managers and show the status with a SHOW, but it is important to show that at this moment of the process the starting, the manager nodes do not see each other so they communicate through the data nodes and because of this any of them will show a different exit in which the only connected node of the cluster is the manager node itself.

Once the 2 manager nodes have been started, we can start launching the 2 data nodes (both in pandoradb1 and in pandoradb2) as it has been shown before, for example with the starting script:

/etc/init.d/cluster_node start

The process for starting the data nodes is slow and has several stages that could be followed in the cluster log.

While doing this you should start the SQL and API nodes (both in pandoradb1 as inpandoradb2)as we have said before.

/etc/init.d/mysql start

Once all the starting orders have been given, you should check in the cluster log that the starting is completed without any error. At the end you could see that all the servers are connected form the manager with the SHOW command.

ndb_mgm -e SHOW

And seeing that all the started nodes are connected.

Appendix. Examples of Configuration Files

/etc/mysql/ndb_mgmd.cnf

File of the Cluster Manager. The secondary manager gets the configuration from the primary one (that should be active when the secondary is started),but this file should be in both nodes.

# MySQL Cluster Configuration file
# By Pablo de la Concepcion Sanz <pablo.concepcion@artica.es>
# This file must be present on ALL the management nodes
# in the directory /var/lib/mysql-cluster/
# For some of the  parameters there is an explanation of the
# possible values that the parameter can take following this
# format:
# ParameterName (MinValue, MaxValue) [DefaultValue]

##########################################################
# MANAGEMENT NODES                                       #
# This nodes are the ones running the management console #
##########################################################
# More info at:
# http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbd-definition.html
# Common configuration for all management nodes:

[ndb_mgmd default]

# This parameter is used to define which nodes can act as arbitrators.
# Only management nodes and SQL nodes can be arbitrators.
# ArbitrationRank can take one of the following values:
#    * 0: The node will never be used as an arbitrator.
#    * 1: The node has high priority; that is, it will be preferred
#         as an arbitrator over low-priority nodes.
#    * 2: Indicates a low-priority node which be used as an arbitrator
#         only if a node with a higher priority is not available
#         for that purpose.
#
# Normally, the management server should be configured as an
# arbitrator by setting its ArbitrationRank to 1 (the default for
# management nodes) and those for all SQL nodes to 0 (the default
# for SQL nodes).
ArbitrationRank=1

# Directory for management node log files
datadir=/var/lib/mysql-cluster

# Using 2 management servers helps guarantee that there is always an
# arbitrator in the event of network partitioning, and so is
# recommended for high availability. Each management server must be
# identified by a HostName. You may for the sake of convenience specify
# a node ID for any management server, although one will be allocated
# for it automatically; if you do so, it must be in the range 1-255
# inclusive and must be unique among all IDs specified for cluster
# nodes.

[ndb_mgmd]

id=1
# Hostname or IP address of management node

hostname=10.1.1.230


[ndb_mgmd]
id=2
# Hostname or IP address of management node
hostname=10.1.1.220


#################
# STORAGE NODES #
#################
# More info at:
# http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbd-definition.html

# Options affecting ndbd processes on all data nodes:
[ndbd default]

# Redundancy (number of replicas):
# Using 2 replicas is recommended to guarantee availability of data;
# using only 1 replica does not provide any redundancy, which means
# that the failure of a single data node causes the entire cluster to
# shut down. We do not recommend using more than 2 replicas, since 2 is
# sufficient to provide high availability, and we do not currently test
# with greater values for this parameter.

NoOfReplicas=2

# Directory for storage node trace files, log files, pid files and error logs.
datadir=/var/lib/mysql-cluster



### Data Memory, Index Memory, and String Memory ###
# This parameter defines the amount of space (in bytes) available for storing
# database records. The entire amount specified by this value is allocated in
# memory, so it is extremely important that the machine has sufficient
# physical memory to accommodate it.
# DataMemory (memory for records and ordered indexes) (recomended 70% of RAM)
# DataMemory antes 22938MB (recomended 70% of RAM)
DataMemory=4096MB

# IndexMemory (memory for Primary key hash index and unique hash index)
# Usually between 1/6 or 1/8 of the DataMemory is enough, but depends on the
# number of unique hash indexes (UNIQUE in table def)
# Also can be calculated as 15% of RAM
# IndexMemory antes 4915MB
IndexMemory= 512MB

# This parameter determines how much memory is allocated for strings
# such as table names
#  * A value between 0 and 100 inclusive is interpreted as a percent of the
#    maximum default value (wich depends on a number of factors)
#  * A value greater than 100 is interpreted as a number of bytes.
StringMemory=25

### Transaction Parameters ###
# MaxNoOfConcurrentTransactions (32,4G) [4096]
# Sets the number of parallel transactions possible in a node
#
# This parameter must be set to the same value for all cluster data nodes.
# This is due to the fact that, when a data node fails, the oldest surviving
# node re-creates the transaction state of all transactions that were ongoing
# in the failed node.
#
# Changing the value of MaxNoOfConcurrentTransactions requires a complete
# shutdown and restart of the cluster.
# MaxNoOfConcurrentTransactions antes 4096
MaxNoOfConcurrentTransactions=8192

# MaxNoOfConcurrentOperations (32,4G) [32k]
# Sets the number of records that can be in update phase or locked
# simultaneously.
MaxNoOfConcurrentOperations=10000000

# MaxNoOfLocalOperations (32,4G)
# Recomentded to set (110% of MaxNoOfConcurrentOperations)
MaxNoOfLocalOperations=11000000

### Transaction Temporary Storage ###
# MaxNoOfConcurrentIndexOperations (0,4G) [8k]
# For queries using a unique hash index, another temporary set of operation
# records is used during a query's execution phase. This parameter sets the
# size of that pool of records. Thus, this record is allocated only while
# executing a part of a query. As soon as this part has been executed, the
# record is released. The state needed to handle aborts and commits is handled
# by the normal operation records, where the pool size is set by the parameter
# MaxNoOfConcurrentOperations.

#
# The default value of this parameter is 8192. Only in rare cases of extremely
# high parallelism using unique hash indexes should it be necessary to increase
# this value. Using a smaller value is possible and can save memory if the DBA
# is certain that a high degree of parallelism is not required for the cluster.
MaxNoOfConcurrentIndexOperations=8192

# MaxNoOfFiredTriggers (0,4G) [4000]
# The default value is sufficient for most situations. In some cases it can
# even be decreased if the DBA feels  certain the need for parallelism in the
# cluster is not high.
MaxNoOfFiredTriggers=4000

# TransactionBufferMemory (1k,4G) [1M]
# The memory affected by this parameter is used for tracking operations fired
# when updating index tables and reading unique indexes. This memory is used to
# store the key and column information for these operations. It is only very
# rarely that the value for this parameter needs to be altered from the default.
TransactionBufferMemory=1M

### Scans and Buffering ###

# MaxNoOfConcurrentScans (2,500) [256]
# This parameter is used to control the number of parallel scans that can be
# performed in the cluster. Each transaction coordinator can handle the number
# of parallel scans defined for this parameter. Each scan query is performed
# by scanning all partitions in parallel. Each partition scan uses a scan
# record in the node where the partition is located, the number of records
# being the value of this parameter times the number of nodes. The cluster
# should be able to sustain MaxNoOfConcurrentScans scans concurrently from all
# nodes in the cluster.
MaxNoOfConcurrentScans=400

# MaxNoOfLocalScans (32,4G)
# Specifies the number of local scan records if many scans are not fully
# parallelized. If the number of local scan records is not provided, it is
# calculated as the product of MaxNoOfConcurrentScans and the number of data
# nodes in the system. The minimum value is 32.
# MaxNoOfLocalScans antes 32
MaxNoOfLocalScans=6400

# BatchSizePerLocalScan (1,992) [64]
# This parameter is used to calculate the number of lock records used to
# handle concurrent scan operations.
#

# The default value is 64; this value has a strong connection to the
# ScanBatchSize defined in the SQL nodes.
BatchSizePerLocalScan=512

# LongMessageBuffer (512k,4G) (4M)
# This is an internal buffer used for passing messages within individual nodes
# and between nodes. Although it is highly unlikely that this would need to be
# changed, it is configurable. In MySQL Cluster NDB 6.4.3 and earlier, the
# default is 1MB; beginning with MySQL Cluster NDB 7.0.4, it is 4MB.
# LongMessageBuffer antes 32M
LongMessageBuffer=4M

### Logging and Checkpointing ###

# Redolog
# Set NoOfFragmentLogFiles to 6xDataMemory [in MB]/(4 *FragmentLogFileSize [in MB]
# The "6xDataMemory" is a good heuristic and is STRONGLY recommended.
# NoOfFragmentLogFiles=135
NoOfFragmentLogFiles=300

# FragmentLogFileSize (3,4G) [16M]
# Size of each redo log fragment, 4 redo log fragment makes up on fragment log
# file. A bigger Fragment log file size thatn the default 16M works better with

# high write load and is strongly recommended!!
# FragmentLogFileSize=256M
FragmentLogFileSize=16M

# By default, fragment log files are created sparsely when performing an
# initial start of a data node â that is, depending on the operating system
# and file system in use, not all bytes are necessarily written to disk.
# Beginning with MySQL Cluster NDB 6.3.19, it is possible to override this
# behavior and force all bytes to be written regardless of the platform
# and file system type being used by mean of this parameter.
# InitFragmentLogFiles takes one of two values:

#  * SPARSE. Fragment log files are created sparsely. This is the default value

#  * FULL. Force all bytes of the fragment log file to be written to disk.

# InitFragmentLogFiles (SPARSE,FULL) [SPARSE]

InitFragmentLogFiles=FULL

# This parameter sets a ceiling on how many internal threads to allocate for
# open files. Any situation requiring a change in this parameter should be
# reported as a bug.
MaxNoOfOpenFiles=80

# This parameter sets the initial number of internal threads to allocate for
# open files.
InitialNoOfOpenFiles=37

# MaxNoOfSavedMessages [25]
# This parameter sets the maximum number of trace files that are kept before
# overwriting old ones. Trace files are generated when, for whatever reason,
# the node crashes.
MaxNoOfSavedMessages=25



### Metadata Objects ###
# MaxNoOfAttributes (32, 4294967039) [1000]
# Defines the number of attributes that can be defined in the cluster.
#MaxNoOfAttributes antes 25000
MaxNoOfAttributes=4096

# MaxNoOfTables  (8, 4G) [128]
# A table object is allocated for each table and for each unique hash
# index in the cluster. This parameter sets the maximum number of table
# objects for the cluster as a whole.
MaxNoOfTables=8192

# MaxNoOfOrderedIndexes (0, 4G) [128]
# Sets the total number of hash indexes that can be in use in the system
# at any one time
#MaxNoOfOrderedIndexes antes 27000
MaxNoOfOrderedIndexes=2048
#MaxNoOfUniqueHashIndexes: Default value 64 Each Index 15 KB per node
#MaxNoOfUniqueHashIndexes antes 2500
MaxNoOfUniqueHashIndexes=1024
# MaxNoOfTriggers (0, 4G) [768]
# This parameter sets the maximum number of trigger objects in the cluster.
#MaxNoOfTriggers antes 770
MaxNoOfTriggers=4096

### Boolean Parameters ###

# Most of this parameters can be set to true (1 or Y) or false (0 or N)

# LockPagesInMainMemory (0,2) [0]
# On Linux and Solaris systems, setting this parameter locks data node
# processes into memory. Doing so prevents them from swapping to disk,
# which can severely degrade cluster performance.
# Possible values:
#       * 0: Disables locking. This is the default value.
#       * 1: Performs the lock after allocating memory for the process.
#       * 2: Performs the lock before memory for the process is allocated.
LockPagesInMainMemory=1
# This parameter specifies whether an ndbd  process should exit or perform
# an automatic restart when an error condition is encountered.
StopOnError=1
# This feature causes the entire  cluster to operate in diskless mode.
# When this feature is enabled, Cluster online backup is disabled. In
# addition, a partial start of the cluster is not possible.
Diskless=0
# Enabling this parameter causes NDBCLUSTER to try using O_DIRECT
# writes for local checkpoints and redo logs; this can reduce load on
# CPUs. We recommend doing so when using MySQL Cluster NDB 6.2.3 or
# newer on systems running Linux kernel 2.6 or later.
ODirect=1
# Setting this parameter to 1 causes backup files to be compressed. The
# compression used is equivalent to gzip --fast, and can save 50% or more
# of the space required on the data node to store uncompressed backup files
CompressedBackup=1
# Setting this parameter to 1 causes local checkpoint files to be compressed.
# The compression used is equivalent to gzip --fast, and can save 50% or
# more of the space required on the data node to store uncompressed
# checkpoint files
CompressedLCP=1

### Controlling Timeouts, Intervals, and Disk Paging ###

# Most of the timeout values are specified in milliseconds. Any exceptions
# to this are mentioned where applicable.
# TimeBetweenWatchDogCheck (70,4G) [6000]
# To prevent the main thread from getting stuck in an endless loop at some
# point, a âwatchdogâ
                     # the number of milliseconds between checks. If the process remains in the
# same state after three checks, the watchdog thread terminates it.
TimeBetweenWatchDogCheck=40000
# TimeBetweenWatchDogCheckInitial (70,4G) [6000]
# This is similar to the TimeBetweenWatchDogCheck parameter, except that
# TimeBetweenWatchDogCheckInitial controls the amount of time that passes
# between execution checks inside a database node in the early start phases
# during which memory is allocated.
TimeBetweenWatchDogCheckInitial=60000
# StartPartialTimeout (0,4G) [30000]
# This parameter specifies how long the Cluster waits for all data nodes to
# come up before the cluster initialization routine is invoked. This timeout
# is used to avoid a partial Cluster startup whenever possible.
#
# This parameter is overridden when performing an initial start or initial
# restart of the cluster.
#
# The default value is 30000 milliseconds (30 seconds). 0 disables the timeout,
# in which case the cluster may start only if all nodes are available.
StartPartialTimeout=30000
# StartPartitionedTimeout (0, 4G) [60000]
# If the cluster is ready to start after waiting for StartPartialTimeout
# milliseconds but is still possibly in a partitioned state, the cluster waits
# until this timeout has also passed. If StartPartitionedTimeout is set to 0,
# the cluster waits indefinitely.
#
# This parameter is overridden when performing an initial start or initial
# restart of the cluster.
StartPartitionedTimeout=60000
# StartFailureTimeout (0, 4G) [0]
# If a data node has not completed its startup sequence within the time
# specified by this parameter, the node startup fails. Setting this
# parameter to 0 (the default value) means that no data node timeout
# is applied.
StartFailureTimeout=1000000
# HeartbeatIntervalDbDb (10,4G)[1500]
# One of the primary methods of discovering failed nodes is by the use of
# heartbeats. This parameter states how often heartbeat signals are sent
# and how often to expect to receive them. After missing three heartbeat
# intervals in a row, the node is declared dead. Thus, the maximum time
# for discovering a failure through the heartbeat mechanism is four times
# the heartbeat interval.
# This parameter must not be changed drastically
HeartbeatIntervalDbDb=2000
# HeartbeatIntervalDbApi (100,4G)[1500]
# Each data node sends heartbeat signals to each MySQL server (SQL node)
# to ensure that it remains in contact. If a MySQL server fails to send
# a heartbeat in time it is declared âdead,â
                                            # transactions are completed and all resources released. The SQL node
# cannot reconnect until all activities initiated by the previous MySQL
# instance have been completed. The three-heartbeat criteria for this
# determination are the same as described for HeartbeatIntervalDbDb.
HeartbeatIntervalDbApi=3000
# TimeBetweenLocalCheckpoints (0,31)[20] Base-2 Logarithm
# This parameter is an exception in that it does not specify a time to
# wait before starting a new local checkpoint; rather, it is used to
# ensure that local checkpoints are not performed in a cluster where
# relatively few updates are taking place. In most clusters with high
# update rates, it is likely that a new local checkpoint is started
# immediately after the previous one has been completed.
#
# The size of all write operations executed since the start of the
# previous local checkpoints is added. This parameter is also exceptional
# in that it is specified as the base-2 logarithm of the number of 4-byte
# words, so that the default value 20 means 4MB (4 Ã 220) of write
# operations, 21 would mean 8MB, and so on up to a maximum value of 31,
# which equates to 8GB of write operations.
# All the write operations in the cluster are added together.
TimeBetweenLocalCheckpoints=20
# TimeBetweenGlobalCheckpoints (10,32000)[2000]
#  When a transaction is committed, it is committed in main memory in all
# nodes on which the data is mirrored. However, transaction log records
# are not flushed to disk as part of the commit. The reasoning behind this
# behavior is that having the transaction safely committed on at least two
# autonomous host machines should meet reasonable standards for durability.
#
# It is also important to ensure that even the worst of cases â a complete
# crash of the cluster â is handled properly. To guarantee that this happens,
# all transactions taking place within a given interval are put into a global
# checkpoint, which can be thought of as a set of committed transactions that
# has been flushed to disk. In other words, as part of the commit process, a
# transaction is placed in a global checkpoint group. Later, this group's log
# records are flushed to disk, and then the entire group of transactions is
# safely committed to disk on all computers in the cluster.
TimeBetweenGlobalCheckpoints=2000
# TimeBetweenEpochs (0,32000)[100]
# This parameter defines the interval between synchronisation epochs for MySQL
# Cluster Replication.
TimeBetweenEpochs=100
# TransactionInactiveTimeout (0,32000)[4000]
# This parameter defines a timeout for synchronisation epochs for MySQL Cluster
# Replication. If a node fails to participate in a global checkpoint within
# the time determined by this parameter, the node is shut down.
TransactionInactiveTimeout=30000

# TransactionDeadlockDetectionTimeout (50,4G)[1200]

# When a node executes a query involving a transaction, the node waits for
# the other nodes in the cluster to respond before continuing. A failure to
# respond can occur for any of the following reasons:
#       * The node is âdeadâ
#       * The node requested to perform the action could be heavily overloaded.
# This timeout parameter states how long the transaction coordinator waits
# for query execution by another node before aborting the transaction, and
# is important for both node failure handling and deadlock detection.
TransactionDeadlockDetectionTimeout=1200
# DiskSyncSize (32k,4G)[4M]
# This is the maximum number of bytes to store before flushing data to a
# local checkpoint file. This is done in order to prevent write buffering,
# which can impede performance significantly. This parameter is NOT
# intended to take the place of TimeBetweenLocalCheckpoints.
DiskSyncSize=4M
# DiskCheckpointSpeed (1M,4G)[10M]
# The amount of data,in bytes per second, that is sent to disk during a
# local checkpoint.
DiskCheckpointSpeed=10M
# DiskCheckpointSpeedInRestart (1M,4G)[100M]
# The amount of data,in bytes per second, that is sent to disk during a
# local checkpoint as part of a restart operation.
DiskCheckpointSpeedInRestart=100M
# ArbitrationTimeout (10,4G)[1000]
# This parameter specifies how long data nodes wait for a response from
# the arbitrator to an arbitration message. If this is exceeded, the
# network is assumed to have split.
ArbitrationTimeout=10

### Buffering and Logging ###

# UndoIndexBuffer (1M,4G)[2M]
# The UNDO index buffer, is used during local checkpoints. The NDB storage
# engine uses a recovery scheme based on checkpoint consistency in
# conjunction with an operational REDO log. To produce a consistent
# checkpoint without blocking the entire system for writes, UNDO logging
# is done while performing the local checkpoint.

# This buffer is 2MB by default. The minimum value is 1MB, which is
# sufficient for most applications. For applications doing extremely
# large or numerous inserts and deletes together with large
# transactions and large primary keys, it may be necessary to
# increase the size of this buffer. If this buffer is too small,
# the NDB storage engine issues internal error code 677 (Index UNDO
# buffers overloaded).
# IMPORTANT: It is not safe to decrease the value of this parameter
# during a rolling restart.
UndoIndexBuffer=2M
# UndoDataBuffer (1M,4G)[16M]
# This parameter sets the size of the UNDO data buffer, which performs
# a function similar to that of the UNDO index buffer, except the UNDO
# data buffer is used with regard to data memory rather than index memory
# If this buffer is too small and gets congested, the NDB storage
# engine issues internal error code 891 (Data UNDO buffers overloaded).
# IMPORTANT: It is not safe to decrease the value of this parameter
# during a rolling restart.

UndoDataBuffer=16M
# RedoBuffer (1M,4G)[32M]
# All update activities also need to be logged. The REDO log makes it
# possible to replay these updates whenever the system is restarted.
# The NDB recovery algorithm uses a âfuzzyâ
                                           # together with the UNDO log, and then applies the REDO log to play
# back all changes up to the restoration point.
# If this buffer is too small, the NDB storage engine issues error
# code 1221 (REDO log buffers overloaded).
# IMPORTANT: It is not safe to decrease the value of this parameter
# during a rolling restart.
RedoBuffer=32M
#

## Logging ##

#
# In managing the cluster, it is very important to be able to control
# the number of log messages sent for various event types to stdout.
# For each event category, there are 16 possible event levels (numbered
# 0 through 15). Setting event reporting for a given event category to
# level 15 means all event reports in that category are sent to stdout;
# setting it to 0 means that there will be no event reports made in
# that category.
# More info at:
# http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-log-events.html
#
# LogLevelStartup (0,15)[1]
# The reporting level for events generated during startup of the process.
LogLevelStartup=15
# LogLevelShutdown (0,15)[0]
# The reporting level for events generated as part of graceful shutdown
# of a node.

LogLevelShutdown=15
# LogLevelStatistic (0,15)[0]
# The reporting level for statistical events such as number of primary
# key reads, number of updates, number of inserts, information relating
# to buffer usage, and so on.
LogLevelStatistic=15
# LogLevelCheckpoint (0,15)[0]
# The reporting level for events generated by local and global checkpoints.
LogLevelCheckpoint=8
# LogLevelNodeRestart (0,15)[0]
# The reporting level for events generated during node restart.
LogLevelNodeRestart=15
# LogLevelConnection (0,15)[0]
# The reporting level for events generated by connections between cluster
# nodes.
LogLevelConnection=0
# LogLevelError (0,15)[0]
# The reporting level for events generated by errors and warnings by the
# cluster as a whole. These errors do not cause any node failure but are
# still considered worth reporting.

LogLevelError=15

# LogLevelCongestion (0,15)[0]
# The reporting level for events generated by congestion. These errors do
# not cause node failure but are still considered worth reporting.
LogLevelCongestion=0
# LogLevelInfo (0,15)[0]
# The reporting level for events generated for information about the general

# state of the cluster.
LogLevelInfo=3
# MemReportFrequency (0,4G)[0]
# This parameter controls how often data node memory usage reports are recorded
# in the cluster log; it is an integer value representing the number of seconds
# between reports.
# Each data node's data memory and index memory usage is logged as both a
# percentage and a number of 32 KB pages of the DataMemory and IndexMemory.
# The minimum value in which case memory reports are logged only when memory
# usage reaches certain percentages (80%, 90%, and 100%)

MemReportFrequency=900
# When a data node is started with the --initial, it initializes the redo log
# file during Start Phase 4. When very large values are set for
# NoOfFragmentLogFiles, FragmentLogFileSize, or both, this initialization can
# take a long time. StartupStatusReportFrequency configuration parameter
# make reports on the progress of this process to be logged periodically.
StartupStatusReportFrequency=30


### Backup Parameters ###

# This section define memory buffers set aside for execution of
# online backups.
# IMPORTANT: When specifying these parameters, the following relationships
# must hold true. Otherwise, the data node will be unable to start:
#       * BackupDataBufferSize >= BackupWriteSize + 188KB
#       * BackupLogBufferSize >= BackupWriteSize + 16KB
#       * BackupMaxWriteSize >= BackupWriteSize
#
# BackupReportFrequency (0,4G)[0]
# This parameter controls how often backup status reports are issued in
# the management client during a backup, as well as how often such reports
# are written to the cluster log. BackupReportFrequency represents the time
# in seconds between backup status reports.
BackupReportFrequency=10

# BackupDataBufferSize (0,4G)[16M]
# In creating a backup, there are two buffers used for sending data to the
# disk. The backup data buffer is used to fill in data recorded by scanning
# a node's tables. Once this buffer has been filled to the level specified
# as BackupWriteSize (see below), the pages are sent to disk. While
# flushing data to disk, the backup process can continue filling this
# buffer until it runs out of space. When this happens, the backup process
# pauses the scan and waits until some disk writes have completed freed up
# memory so that scanning may continue.
BackupDataBufferSize=16M

# BackupLogBufferSize (0,4G)[16M]
# The backup log buffer fulfills a role similar to that played by the backup
# data buffer, except that it is used for generating a log of all table
# writes made during execution of the backup. The same principles apply for
# writing these pages as with the backup data buffer, except that when
# there is no more space in the backup log buffer, the backup fails.
# The default value for this parameter should be sufficient for most
# applications. In fact, it is more likely for a backup failure to be
# caused by insufficient disk write speed than it is for the backup
# log buffer to become full.
# It is preferable to configure cluster nodes in such a manner that the
# processor becomes the bottleneck rather than the disks or the network
# connections.

BackupLogBufferSize=16M

# BackupMemory (0,4G)[32]
# This parameter is simply the sum of BackupDataBufferSize and
# BackupLogBufferSize.
BackupMemory=64M

# BackupWriteSize (2k,4G)[256k]

# This parameter specifies the default size of messages written to disk
# by the backup log and backup data buffers.
BackupWriteSize=256K
# BackupMaxWriteSize (2k,4G)[1M]
# This parameter specifies the maximum size of messages written to disk
# by the backup log and backup data buffers.
BackupMaxWriteSize=1M
# This parameter specifies the directory in which backups are placed
# (The backups are stored in a subdirectory called BACKUPS)
BackupDataDir=/var/lib/mysql-cluster/


### Realtime Performance Parameters ###


# This parameters are used in scheduling and locking of threads to specific
# CPUs on multiprocessor data node hosts.
# NOTE: To make use of these parameters, the data node process must be run as
# system root.
# Setting these parameters allows you to take advantage of real-time scheduling
# of NDBCLUSTER threads (introduced in MySQL Cluster NDB 6.3.4) to get higher
# throughput.

# On systems with multiple CPUs, these parameters can be used to lock
# NDBCLUSTER
# threads to specific CPUs
# LockExecuteThreadToCPU (0,64k)
# When used with ndbd, this parameter (now a string) specifies the ID of the
# CPU assigned to handle the NDBCLUSTER  execution thread. When used with
# ndbmtd, the value of this parameter is a comma-separated list of CPU IDs
# assigned to handle execution threads. Each CPU ID in the list should be
# an integer in the range 0 to 65535 (inclusive)
# The number of IDs specified should match the number of execution threads
# determined by MaxNoOfExecutionThreads
LockExecuteThreadToCPU=0,1,2,3,4,5,6,7
# RealTimeScheduler (0,1)[0]
# Setting this parameter to 1 enables real-time scheduling of NDBCLUSTER
# threads
RealTimeScheduler=1
# SchedulerExecutionTimer (0,110000)[50]
#  This parameter specifies the time in microseconds for threads to be
# executed in the scheduler before being sent. Setting it to 0 minimizes
# the response time; to achieve higher throughput, you can increase the
# value at the expense of longer response times.
# The default is 50 ÎŒsec, which our testing shows to increase throughput
# slightly in high-load cases without materially delaying requests.

SchedulerExecutionTimer=100
# SchedulerSpinTimer (0,500)[0]
# This parameter specifies the time in microseconds for threads to be executed
# in the scheduler before sleeping.
SchedulerSpinTimer=400
#Threads
# MaxNoOfExecutionThreads (2,8)
# For 8 or more cores the recomended value is 8
MaxNoOfExecutionThreads=8

# Options for data node "A":
[ndbd]
id=3
hostname=10.1.1.215         # Hostname or IP address

# Options for data node "B":
[ndbd]
id=4
hostname=10.1.1.216         # Hostname or IP address

#######################################
# SQL NODES (also known as API NODES) #
#######################################

# Common SQL Nodes Parameters

[mysqld default]
# This parameter is used to define which nodes can act as arbitrators.
# Only management nodes and SQL nodes can be arbitrators.
# ArbitrationRank can take one of the following values:
#    * 0: The node will never be used as an arbitrator.
#    * 1: The node has high priority; that is, it will be preferred
#         as an arbitrator over low-priority nodes.
#    * 2: Indicates a low-priority node which be used as an arbitrator
#         only if a node with a higher priority is not available
#         for that purpose.
#
# Normally, the management server should be configured as an
# arbitrator by setting its ArbitrationRank to 1 (the default for
# management nodes) and those for all SQL nodes to 0 (the default
# for SQL nodes).

ArbitrationRank=2



# BatchByteSize (1024,1M) [32k]

# For queries that are translated into full table scans or range scans on

# indexes, it is important for best performance to fetch records in properly
# sized batches. It is possible to set the proper size both in terms of number
# of records (BatchSize) and in terms of bytes (BatchByteSize). The actual
# batch size is limited by both parameters.
# The speed at which queries are performed can vary by more than 40% depending
# upon how this parameter is set
# This parameter is measured in bytes and by default is equal to 32KB.
BatchByteSize=32k
# BatchSize (1,992) [64]
# This parameter is measured in number of records.
BatchSize=512
# MaxScanBatchSize (32k,16M) [256k]
# The batch size is the size of each batch sent from each data node.
# Most scans are performed in parallel to protect the MySQL Server from
# receiving too much data from many nodes in parallel; this parameter sets
# a limit to the total batch size over all nodes.

MaxScanBatchSize=8MB

# SQL node options:
[mysqld]
id=11
# Hostname or IP address
hostname=10.1.1.215
[mysqld]
id=12
# Hostname or IP address
hostname=10.1.1.216

# Extra SQL nodes (also used for backup & checks)
[mysqld]
id=13
[mysqld]
id=14
[mysqld]
id=15
[mysqld]
id=16
[mysqld]
id=17
[mysqld]
id=18


##################
# TCP PARAMETERS #
##################
[tcp default]



# Increasing the sizes of these 2 buffers beyond the default values
# helps prevent bottlenecks due to slow disk I/O.
SendBufferMemory=3M

ReceiveBufferMemory=3M

/etc/mysql/my.cf

Configuration file of the SQL Nodes (that are also the NDB nodes).


# MySQL SQL node config 
# =====================
# Written by Pablo de la Concepcion, pablo.concepcion@artica.es
#
# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
skip-locking
key_buffer_size = 4000M
table_open_cache = 5100
sort_buffer_size = 64M
net_buffer_length = 512K
read_buffer_size = 128M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 64M

query_cache_size = 256M
query_cache_limit = 92M

#slow_query_log = /var/log/mysql/mysql-slow.log
max_connections = 500
table_cache = 9060


# Thread parameters
thread_cache_size = 1024
thread_concurrency = 64
thread_stack = 256k

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

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
# 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

# The safe_mysqld script
[safe_mysqld]
log-error       = /var/log/mysql/mysqld.log
socket          = /var/lib/mysql/mysql.sock

[mysqldump]
socket          = /var/lib/mysql/mysql.sock
quick
max_allowed_packet = 64M

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

[myisamchk]
key_buffer_size = 10000M
sort_buffer_size = 20M
read_buffer = 10M
write_buffer = 10M

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin

#log        = /var/log/mysqld_multi.log
# user       = multi_admin
# password   = secret

# If you want to use mysqld_multi uncomment 1 or more mysqld sections
# below or add your own ones.

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!
# [mysqld1]
# port       = 3306
# datadir    = /var/lib/mysql
 pid-file   = /var/lib/mysql/mysqld.pid
# socket     = /var/lib/mysql/mysql.sock
# user       = mysql


# Cluster configuration
#       by Pablo de la Concepcion <pablo.concepcion@artica.es>


# Options for mysqld process:
[mysqld]

# Run NDB storage engine
ndbcluster

# Location of management servers
ndb-connectstring="10.1.1.215:1186;10.1.1.216:1186"

# Number of connections in the connection pool, the config.ini file of the
# cluster have to define also [API] nodes at least for each connection.
ndb-cluster-connection-pool=3

# Forces sending of buffers to NDB  immediately, without waiting
# for other threads. Defaults to ON.
ndb-force-send=1

# Forces NDB to use a count of records during SELECT COUNT(*) query planning
# to speed up this type of query. The default value is ON. For faster queries
# overall, disable this feature by setting the value of ndb_use_exact_count
# to OFF.
ndb-use-exact-count=0

#  This variable can be used to enable recording in the MySQL error log
# of information specific to the NDB storage engine. It is normally of
# interest only when debugging NDB storage engine code.
# The default value is 0, which means that the only NDB-specific
# information written to the MySQL error log relates to transaction
# handling. If the value is greater than 0 but less than 10, NDB table
# schema and connection events are also logged, as well as whether or
# not conflict resolution is in use, and other NDB errors and information.
# If the value is set to 10 or more, information about NDB internals, such
# as the progress of data distribution among cluster nodes, is also
# written to the MySQL error log.

ndb-extra-logging=00

# Determines the probability of gaps in an autoincremented column.
# Set it to 1 to minimize this. Setting it to a high value for
# optimization â makes inserts faster, but decreases the likelihood
# that consecutive autoincrement numbers will be used in a batch
# of inserts. Default value: 32. Minimum value: 1.

ndb-autoincrement-prefetch-sz=256

engine-condition-pushdown=1

# Options for ndbd process:

[mysql_cluster]
# Location of management servers (list of host:port separated by ;)

ndb-connectstring="10.1.1.230:1186;10.1.1.220:1186"

/etc/cron.daily/backup_cluster

NOTE: as it is a cluster, the mysldump is not reliable because the writting is distributed and the coherence could not be warranted. Though it is not recommended, and it is preferable to do a complete backup of the cluster (see the following section), you could try to get a valid backup if you limit the writting in the cluster (stopping the pandora servers) and in the mode single user (ver http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-single-user-mode.html ).


This backup script does the backup through the "secure" system (command START BACKUP) from the cluster management console.

#!/bin/bash

LOG_TEMPORAL=/tmp/mysql_cluster_backup_script.log

#Directorios de los Backups

DIR_NODO3=/var/lib/mysql-cluster/BACKUPS/Nodo_03
DIR_NODO4=/var/lib/mysql-cluster/BACKUPS/Nodo_04

# Se lanza el backup y se espera a que se complete
/usr/bin/ndb_mgm -e "START BACKUP WAIT COMPLETED" > $LOG_TEMPORAL
echo "Procesando Log $LOG_TEMPORAL"
NUM_BACKUP=`grep Backup $LOG_TEMPORAL | grep completed | awk '{print $4}'`
echo "Procesando backup $NUM_BACKUP"

# Se copian por scp los backups
scp -i /root/.ssh/backup_key_rsa -r root@10.1.1.215:/var/lib/mysql-cluster/BACKUP/BACKUP-$NUM_BACKUP/ $DIR_NODO3 >>$LOG_TEMPORAL 2>> /var/lib/mysql-cluster/BACKUPS/logs/backup_$NUM_BACKUP.err

scp -i /root/.ssh/backup_key_rsa -r root@10.1.1.216:/var/lib/mysql-cluster/BACKUP/BACKUP-$NUM_BACKUP/ $DIR_NODO4 >>$LOG_TEMPORAL 2>> /var/lib/mysql-cluster/BACKUPS/logs/backup_$NUM_BACKUP.err

#Se almacena el log
mv $LOG_TEMPORAL  /var/lib/mysql-cluster/BACKUPS/logs/backup_$NUM_BACKUP.log

	Para programar este script diariamente debemos poner la siguiente linea en el fichero 	/etc/crontab (Esto hará un backup diario a las 5 de la mañana)

00 5   * * *   root    /tmp/backup_cluster

/etc/init.d/cluster_mgmt

Template warning.png

This script is slightly different in the secondary cluster management console (different parameters in DAEMON_PARAMETERS)

 



#!/bin/bash
# Copyright (c) 2005-2009 Artica ST
#
# Author: Sancho Lerena <slerena@artica.es> 2006-2009
#
# /etc/init.d/cluster_mgmt
#
# System startup script for MYSQL Cluster Manager
#
### BEGIN INIT INFO
# Provides:       cluster_mgmt
# Required-Start: $syslog cron
# Should-Start:   $network cron
# Required-Stop:  $syslog
# Should-Stop:    $network
# Default-Start:  2 3 5
# Default-Stop:   0 1 6
# Short-Description: MySQL Cluster Management console startup script
# Description:    See short description
### END INIT INFO

export PROCESS_DAEMON=ndb_mgmd
export PROCESS_PARAMETERS="--config-file=/var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster"

# Uses a wait limit before sending a KILL signal, before trying to stop
# Pandora FMS server nicely. Some big systems need some time before close
# all pending tasks / threads.

export MAXWAIT=300

# Check for SUSE status scripts
if [ -f /etc/rc.status ]
then
        . /etc/rc.status
        rc_reset
else
        # Define rc functions for non-suse systems, "void" functions.
        function rc_status () (VOID=1;)
        function rc_exit () (exit;)
        function rc_failed () (VOID=1;)

fi

# This function replace pidof, not working in the same way in different linux distros

function pidof_process () (
        # This sets COLUMNS to XXX chars, because if command is run
        # in a "strech" term, ps aux don't report more than COLUMNS
        # characters and this will not work.
        COLUMNS=400
        PROCESS_PID=`ps aux | grep "$PROCESS_DAEMON $PROCESS_PARAMETERS" | grep -v grep | tail -1 | awk '{ print $2 }'`
        echo $PROCESS_PID
)

# Main script

if [ `which $PROCESS_DAEMON | wc -l` == 0 ]
then
        echo "Server not found, please check setup and read manual"
        rc_status -s
        rc_exit
fi

case "$1" in
        start)
                PROCESS_PID=`pidof_process`
                if [ ! -z "$PROCESS_PID" ]
                then
                        echo "Server is currently running on this machine with PID ($PROCESS_PID). Aborting now..."
                        rc_failed 1
                        rc_exit
                fi

                $PROCESS_DAEMON $PROCESS_PARAMETERS
                sleep 1

                PANDORA_PID=`pidof_process`

                if [ ! -z "$PANDORA_PID" ]
                then
                        echo "Server is now running with PID $PANDORA_PID"
                        rc_status -v
                else
                        echo "Cannot start Server. Aborted."
                        rc_status -s
                fi
        ;;

        stop)
                PANDORA_PID=`pidof_process`
                if [ -z "$PANDORA_PID" ]
                then
                        echo "Server is not running, cannot stop it."
                        rc_failed
                else
                        echo "Stopping Server"
                        kill $PANDORA_PID
                        COUNTER=0

                        while [  $COUNTER -lt $MAXWAIT ]
                        do
                                PANDORA_PID=`pidof_process`
                                if [ -z "$PANDORA_PID" ]
                                then
                                        COUNTER=$MAXWAIT
                                fi
                                COUNTER=`expr $COUNTER + 1`
                                sleep 1
                        done

                        # Send a KILL -9 signal to process, if it's alive after 60secs, we need
                        # to be sure is really dead, and not pretending...
                        if [ ! -z "$PANDORA_PID" ]
                        then
                                kill -9 $PANDORA_PID
                        fi
                        rc_status -v
                fi
        ;;
        status)
                PANDORA_PID=`pidof_process`
                if [ -z "$PANDORA_PID" ]
                then
                        echo "Server is not running."
                        rc_status
                else
                        echo "Server is running with PID $PANDORA_PID."
                        rc_status
                fi
        ;;
  force-reload|restart)
                $0 stop
                $0 start
                ;;
  *)
                echo "Usage: server { start | stop | restart | status }"
                exit 1
esac
rc_exit

/etc/init.d/cluster_node


#!/bin/bash
# Copyright (c) 2005-2009 Artica ST
#
# Author: Sancho Lerena <slerena@artica.es> 2006-2009
#
# /etc/init.d/cluster_node
#
# System startup script for MYSQL Cluster Node storage
#
### BEGIN INIT INFO
# Provides:       cluster_node
# Required-Start: $syslog cron
# Should-Start:   $network cron
# Required-Stop:  $syslog
# Should-Stop:    $network
# Default-Start:  2 3 5
# Default-Stop:   0 1 6
# Short-Description: MySQL Cluster Node startup script
# Description:    See short description
### END INIT INFO

export PROCESS_DAEMON=ndb_ndb
export PROCESS_PARAMETERS="-d"

# Uses a wait limit before sending a KILL signal, before trying to stop
# Pandora FMS server nicely. Some big systems need some time before close
# all pending tasks / threads.

export MAXWAIT=300

# Check for SUSE status scripts
if [ -f /etc/rc.status ]
then
        . /etc/rc.status
        rc_reset
else
        # Define rc functions for non-suse systems, "void" functions.
        function rc_status () (VOID=1;)
        function rc_exit () (exit;)
        function rc_failed () (VOID=1;)

fi

# This function replace pidof, not working in the same way in different linux distros

function pidof_process () (
        # This sets COLUMNS to XXX chars, because if command is run
        # in a "strech" term, ps aux don't report more than COLUMNS
        # characters and this will not work.
        COLUMNS=400
        PROCESS_PID=`ps aux | grep "$PROCESS_DAEMON $PROCESS_PARAMETERS" | grep -v grep | tail -1 | awk '{ print $2 }'`
        echo $PROCESS_PID
)

# Main script

if [ `which $PROCESS_DAEMON | wc -l` == 0 ]
then
        echo "Server not found, please check setup and read manual"
        rc_status -s
        rc_exit
fi

case "$1" in
        start)
                PROCESS_PID=`pidof_process`
                if [ ! -z "$PROCESS_PID" ]
                then
                        echo "Server is currently running on this machine with PID ($PROCESS_PID). Aborting now..."
                        rc_failed 1
                        rc_exit
                fi

                $PROCESS_DAEMON $PROCESS_PARAMETERS
                sleep 1

                PANDORA_PID=`pidof_process`

                if [ ! -z "$PANDORA_PID" ]
                then
                        echo "Server is now running with PID $PANDORA_PID"
                        rc_status -v
                else
                        echo "Cannot start Server. Aborted."
                        rc_status -s
                fi
        ;;

        stop)
                PANDORA_PID=`pidof_process`
                if [ -z "$PANDORA_PID" ]
                then
                        echo "Server is not running, cannot stop it."
                        rc_failed
                else
                        echo "Stopping Server"
                        kill $PANDORA_PID
                        COUNTER=0

                        while [  $COUNTER -lt $MAXWAIT ]
                        do
                                PANDORA_PID=`pidof_process`
                                if [ -z "$PANDORA_PID" ]
                                then
                                        COUNTER=$MAXWAIT
                                fi
                                COUNTER=`expr $COUNTER + 1`
                                sleep 1
                        done

                        # Send a KILL -9 signal to process, if it's alive after 60secs, we need
                        # to be sure is really dead, and not pretending...
                        if [ ! -z "$PANDORA_PID" ]
                        then
                                kill -9 $PANDORA_PID
                        fi
                        rc_status -v
                fi
        ;;
        status)
                PANDORA_PID=`pidof_process`
                if [ -z "$PANDORA_PID" ]
                then
                        echo "Server is not running."
                        rc_status
                else
                        echo "Server is running with PID $PANDORA_PID."
                        rc_status
                fi
        ;;
  force-reload|restart)
                $0 stop
                $0 start
                ;;
  *)
                echo "Usage: server { start | stop | restart | status }"
                exit 1
esac
rc_exit

Go back to Pandora FMS documentation index