How to install MySQL NDB Cluster on Linux

As discussed in this previous article about MySQL NDB Cluster overview, it is built on the NDBCLUSTER storage engine for MySQL and provides high availability and scalability. This article explains installation of MySQL NDB Cluster on Linux environment. The detailed documentation is available at the official MySQL Developer Documentation page.
To avoid a single point of failure, the example cluster is installed on separate hosts that represent each of the four nodes needed to run the cluster. So this is a four node, four host cluster that we are setting up. Instead of different hosts, virtual hosts also can be used for this setup. Each node/host is assigned separate IP addresses on the Ethernet Network as follows:
Management node (mgmd) –
SQL node (mysqld) –
Data node “A” (ndbd) –
Data node “B” (ndbd) –
NDB Cluster setup on multi-computer Ethernet network
Installing the NDB Cluster Binary Release
Install the NDB Cluster binary from the MySQL NDB Cluster download page. At the time of this article, NDB Cluster 7.5 binary archive is available as mysql-cluster-gpl-7.5.8-linux-i686-glibc23.tar.gz. Download it to /var/tmp.
SQL Node setup
A user named mysql on group mysql is needed on the system. Verify these are present after login as root user and if not, manually create them.
Unpack the archive to /usr/local and create a symbolic link named mysql to the mysql directory found inside the package.
shell> cd /var/tmp
shell> tar -C /usr/local -xzvf mysql-cluster-gpl-7.5.8-linux2.6.tar.gz
shell> ln -s /usr/local/mysql-cluster-gpl-7.5.8-linux2.6-i686 /usr/local/mysql
Now the symbolic link mysql points to the NDB Cluster Directory mysql-cluster-gpl-7.5.8-linux2.6-i686 inside /usr/local.
After changing to the mysql directory, initialize the MySQL installation (mysqld) by creating the data directory and populating the mysql system table.
With –initialize option a random password will be generated to the MySQL root user. If no password generation is needed during this stage, use –initialize-insecure option instead of –initialize and set password later.  The database base directory and data directory also can be specified during initialization:
shell> mysqld –initialize –user=mysql
Set permission for root and mysql users over the database directories and assign the base directory to the group mysql:
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .
In order for mysqld to start during system startup, add the startup script to /etc/rd.d/init.d scripts, make it executable and add it as startup service.
shell> cp support-files/mysql.server /etc/rc.d/init.d/
shell> chmod +x /etc/rc.d/init.d/mysql.server
shell> chkconfig –add mysql.server
Thus mysql.server starts the SQL node. For each host machine containing SQL nodes, above steps are to be repeated.
Data node setup
Data nodes do not need the mysqld binary. To setup the data node, either the single-threaded NDB Cluster data node executable ndbd or multi-threaded executable ndbmtd is required. These binaries are also included in the NDB Cluster binary downloaded above. Download and unpack the binary in the data node host. The data node binaries will be inside the bin directory of the unpacked cluster binary directory. From the downloaded location (assuming as /var/tmp), move the data node binaries to the /usr/local/bin location for binaries. Perform the below operations as root user.
shell> cd /var/tmp
shell> tar -zxvf mysql-5.7.19-ndb-7.5.8-linux-i686-glibc23.tar.gz
shell> cd mysql-5.7.19-ndb-7.5.8-linux-i686-glibc23
shell> cp bin/ndbd /usr/local/bin/ndbd
shell> cp bin/ndbmtd /usr/local/bin/ndbmtd
Delete the directory that contains the unpacked NDB cluster archive.
Now make both the data node binaries (ndbd and ndbmtd) executable:
shell> cd /usr/local/bin
shell> chmod +x ndb*
Repeat the above steps for each data node host. Note that the MySQL data directory is /usr/local/mysql/data.
Management node setup
Management node does not need the mysqld binary. It needs only the NDB Cluster management server ndb_mgmd. The management client named ndb_mgm is highly desirable to use the management node. Both binaries are located in the bin folder inside the NDB Cluster binary downloaded (inside /var/tmp) as mentioned above. As root user, unpack the cluster binary, copy the management node and client binaries to /ust/local/bin and make them executable.
shell> cd /var/tmp
shell> tar -zxvf mysql-5.7.19-ndb-7.5.8-linux2.6-i686.tar.gz
shell> cd mysql-5.7.19-ndb-7.5.8-linux2.6-i686
shell> cp bin/ndb_mgm* /usr/local/bin
Delete the directory that contains the unpacked NDB cluster archive.
Now make both the management node and client binaries executable:
shell> cd /usr/local/bin
shell> chmod +x ndb_mgm*
NDB Cluster Initial Configuration
After downloading and installing the SQL, Data and Management nodes, now it is time to configure the NDB Cluster.
Data and SQL Nodes Configuration
Each of the data and SQL nodes need the configuration file my.cnf to specify the connection string to connect to the management node and another parameter that enables the NDBCLUSTER storage engine on this MySQL Server. Create the my.cnf file, if not already exists, or open with vi editor and add the configuration parameters.
shell> vi /etc/my.cnf
Below shown is the part of the my.cnf file that holds the NDB Cluster related parameters.
# Options for mysqld process:
ndbcluster # run NDB storage engine
# Options for NDB Cluster processes:
ndb-connectstring= # location of management server
Note: the IP address of management node above is as per this example setup only and needs to be replaced by the actual IP address in your case.
In our example, these my.cnf settings are to be added to Data Node A, Data Node B and SQL Node hosts.
In our example, these my.cnf settings are to be added to Data Node A, Data Node B and SQL Node hosts.
Management Node Configuration
The Management Node needs a config.ini file that specifies the following information:
1. How many replicas (data nodes) to be managed in the cluster.
2. How much memory is to be allocated for data and indexes on each data node.
3. IP address or Domain Names (with DNS Resolution) of Data and SQL Nodes in the cluster.
4. Data directories of each Data Node to save database data.
Create the configuration directory for the Management Node and create config.ini file inside it, running as root user.
shell> mkdir /var/lib/mysql-cluster
shell> cd /var/lib/mysql-cluster
shell> vi config.ini
For our example setup, the config.ini of Master Node should read as shown below after writing the settings:
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataMemory=80M # How much memory to allocate for data storage
IndexMemory=18M # How much memory to allocate for index storage
                  # For DataMemory and IndexMemory, we have used the
                  # default values. Since the “world” database takes up
                  # only about 500KB, this should be more than enough for
                  # this example NDB Cluster setup.
ServerPort=2202 # This the default value; however, you can use any
                  # port that is free for all the hosts in the cluster
                  # Note1: It is recommended that you do not specify the port
                  # number at all and simply allow the default value to be used
                  # instead
                  # Note2: The port was formerly specified using the PortNumber
                  # TCP parameter; this parameter is no longer available in NDB
                  # Cluster 7.5.
# Management process options:
HostName= # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files
[ndbd el_id=’59a5076ed1de1′]
# Options for data node “A”:
                                # (one [ndbd el_id=’59a5076ed1e2b’] section per data node)
HostName= # Hostname or IP address
NodeId=2 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node’s data files
[ndbd el_id=’59a5076ed1e68′]
# Options for data node “B”:
HostName= # Hostname or IP address
NodeId=3 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node’s data files
# SQL node options:
HostName= # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)
Note: the IP addresses are for this example setup only and need to be replaced by your actual IP addresses or Domain names.
The above are the minimal settings for the my.cnf and config.ini files.
Initial Startup of the NDB Cluster
The nodes must be started in the following order:
1. Management Nodes
2. Data Nodes
3. SQL Nodes.
Start the management node process ndb_mgmd in the management node host. During the initial startup, the node must be supplied as parameter, the location of its configuration file, config.ini, through the option -f or –config-file:
shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini
Next start the data nodes, ndbd processes, at the data node hosts:
shell> ndbd
Now start the SQL node process, mysqld, on the SQL node host:
shell> cd mysql_installation_directory
shell> bin/mysqld_safe &
shell> mysql.server start
In the example, mysql_installation_directory is /usr/local/mysql
If the setup is correct and everything has gone well, the cluster will be operational now. Use the ndb_mgm client to test the cluster. A running cluster should produce the below output for this example:
shell> ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
[ndbd(NDB)] 2 node(s)
id=2 @ (Version: 5.7.19-ndb-7.5.8, Nodegroup: 0, *)
id=3 @ (Version: 5.7.19-ndb-7.5.8, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @ (Version: 5.7.19-ndb-7.5.8)
[mysqld(API)] 1 node(s)
id=4 @ (Version: 5.7.19-ndb-7.5.8)
Here the [mysqld(API) ] reference is for the SQL Node process mysqld and it is acting as the NDB Cluster API node to accept data access requests from clients.
Now you can create tables using the NDBCLUSTER storage engine:
CREATE TABLE tbl_name (col_name column_definitions) ENGINE=NDBCLUSTER;
Alter existing tables to NDBCLUSTER storage engine, if any:
To shutdown the cluster, issue below command on the management node host:
shell> ndb_mgm -e shutdown
To restart the cluster, issue below command on the management node host:
shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini
In data node hosts:
shell> ndbd
In SQL node hosts, from the mysql installation directory:
shell> bin/mysqld_safe &
shell> mysql.server start

Quest Software released Toad Edge to Simplify MySQL Database Development

Open Source database management systems are increasing their market share dramatically over the last couple of years. Market Experts like Gartner are predicting a market shift from commercial, closed-source DBMSs to Open Source DBMSs in the coming years. According to Gartner, by 2018, more than 70 percent of in-house deployments will be on open source DMBSs and 50 percent of existing RDBMS deployments will have been converted or start the process.
To address this space, Toad Edge – a new commercial database management tool is being released by Quest Software – a global System and Security management software provider, to simplify MySQL development and administration. It is part of the Toad Product Family – a database management toolset by Quest that enabled data analysts, DBAs and database developers to manage relational and non-relational databases using SQL. Toad is said to contribute in data technology management by automating processes, reduce development time, lowers development cost and enhancing performance and scalability.
Toad Edge currently supports MySQL RDBMS development, management and administration by providing the following valuable services:
1. Minimize the MySQL Learning Curve by utilizing a lightweight, intuitive IDE and automation facilities of Toad.
2. Simplifies MySQL development and administration.
3. Ensures quality and maintainability of code.
4. Supports DevOps workflows using the Jenkins Continuous Integration Plugin.
Features of Toad Edge
1. Object Explorer and User Manager – allows efficient management of database objects and user privileges.
2. SQL Worksheet with Content Assist for writing complex SQL queries rapidly and with ease.
3. SQL Query Monitor that displays running queries to enable creation of export scripts and logs easily.
4. Quick Import/Export of data using a variety of formats – SQL, CSV, ZIP archives etc.
5. Schema Comparison and Synchronization – for databases and creates change scripts for source and destination databases and exports to a file or SQL Worksheet.
6. Database Snapshot allows taking current state database snapshot to compare and synchronize it with another active database (replicated or clone).
7. Advanced MySQL JSON Browser and Editor for editing JSON scripts using a data browser that visualizes scripts segments and help to find and fix errors.
8. Supports both Windows and Mac OS.
9. Expandability to include open source, on-site or cloud databases like MySQL, Amazon RDS MySQL etc.
10. Enterprise Technical Support along with self-help tools.
IT Architecture

Symptoms of a bad IT Architecture

How significant is the IT Architecture for your business
Every organization, SMBs to Enterprises, relies upon their IT architecture for running the business, its workflows, planning, processes, policies, principles, operations, services, projects and to fulfill their obligation towards value proposition. It is not merely the data storage and management system, as existed till the late 80’s but incorporates the complete domain of business management as of now. Right from the aim, planning, policies and principles, the IT architecture starts to play a significant and profound role in the organization’s existence, operations and development.
Your IT architecture coordinates and interlinks the organization and its management functions by providing the platform, tools and interface for organizational foundation, functioning, administration, and development. It enforces the standards and regulations upon the processes and workflows to produce the desired outcome. It controls and regulates the functioning by providing tools and interface for monitoring, analyzing and decision making. It provides historical and statistical data with analytical tools for planning and implementation and at the same time providing resources for the expansion and development of the organization.
What is needed in the IT Architecture?
The below elements or components are crucial for the IT architecture:
1. Back office
2. Operations
3. Project execution
4. Public interface
5. Data management
6. Infrastructure
There are many architecture implementation models adopted by various organizations. Some select a particular architecture after detailed studies and research while some architecture will evolve with time and experience. In any case, since IT architecture is the backbone and powering factor of the organizational realm, any discrepancies in the selection, configuration, implementation and management of the IT architecture will produce negative signs and side effects to the smooth functioning of the organization.
An IT architecture can be bad or the domain related synonyms as applicable to it, like non-scalable, outdated, redundant, inconsistent, unreliable, etc. depending upon the environment that demands from it. Whatever the technology be, if your IT architecture is just not suitable for your needs, it will start producing warning signs for your business.
What are the symptoms of a bad IT Architecture?
Frequent Malfunctioning and Increased Downtime
Is your IT staff busy running around departments responding to case reports and troubleshooting? Are there frequent system crashes and your staff is stuck with an abrupt system shutdown or crash? Or are they not getting the data and functionality they need for their duties? Obviously this is a clear and concise indication of an outdated or mis-configured IT architecture.
Redundant Interfaces
If the number of interfaces is not proportionate to data view and management requirements, it means you are forced to do the same action across different screens on the same data. You are wasting the operational time as well as computing resources. A poorly designed CRM or Finance Software can create this issue and it may be on a certain point of time that you have detected it. It indicates either lack of horizontal scalability or poor design patterns.
Data Redundancy
It depends upon the data and entity relationship design of your IT architecture that decides how much data redundancy you are experiencing either visible or invisible. Copying databases or data tables across departmental databases create extreme amount of data duplication and redundancy. If you HR could not find an interface to review the salary hike pattern of an employee through the synchronized but logically separated data views of the HR and Finance departments, it indicates that a data copy mechanism is in use to provide this data to the HR. This is a clear case of Data Redundancy and associated overheads in data synchronization, storage issues etc.
Data Inconsistency
It is related to data synchronization across the organizational departments. Many cases are reported where the organizational data is either corrupted or not vertically scalable due to missing data entities or units. For organizations that heavily depend upon traditional and in-house data management systems, this risk is high due to the lack of adequate human, hardware and software resources.
Lack of features and tools
Is your CRM not allowing or integrating with your Email Marketing Software to send marketing and other types of emails to your customers? Are you unable to export data in bulk? Is your DBA struggling with growing slow query log and underperforming database? Are you lacking a security system to filter your incoming and outgoing emails? Is your disk storage congesting with incremental backups? All these indicate that your IT architecture is hungry for additional functionality and capabilities. Ignoring this scarcity will be a huge road block between your business and its growth.
Complex Workflow
Organizations now depend heavily on networked (web based or LAN) applications for managing their customer relations, marketing, finance and accounts, communication, commerce, stock management, data management etc. When the integration of these applications is not done properly, or if they are not compatible with each other, it results in complex workflows. This complexity will be experienced in the form of multiple and redundant data processing tasks, lack of tools to process data, rigid and tedious procedures to apply when it comes to combine data from various departments etc. Employees will start feeling the overhead and unnecessary extra effort will be spent on otherwise easy processes. The main culprit here is an architecture that lacked proper planning and selection of compatible applications or failure to select a business software suite matching the organization’s requirements. Foreseeing the business and data growth also contributes to this complexity.
Communication and Collaboration bottlenecks
Communication is not only IMs, telephony, Voice/Video Conferences, but for the current world it extends to collaborative sharing of resources like files, media and even a code base. If you are facing issues with concurrent file editing, version control, task management, multiplexed resource sharing etc. then these are signs of your IT architecture lacking the most critical feature of Collaborative Sharing. Managers need to have multi-channels of inward and outward communication and broadcasting facilities to ensure processes and work-flows are executed flawlessly.
High Costs
When you face a situation to purchase a costly hardware or software to add additional functionality to your IT architecture, it points to its weakness in scalability with cost-affordable commodity hardware. This is a prominent sign of your IT architecture needing a revamp. Additionally the consultation and human resources expenses starting from recruitment to salary and perks to manage highly proprietary application suite further complicates the issue.
Little Customizable
Are you forced to be satisfied with what your IT architecture is offering and your requirement for a flexible work-flow is to be disregarded due to the complexity or non-feasibility in customizing the components of your IT architecture, then it is good time for initiating a re-thinking. Customization is one of the promising features any IT architecture should offer and that are your right rather than a privilege.
Customer Dissatisfaction
The million, if not a billion dollar concern – how your IT architecture is affecting your deliverability and customer satisfaction? Are they forced to wait for a manual approval of an order or transaction they have placed online? Are your embedded business rules are outdated and you are not able to detect or if so, not able to enhance it? Then customer dissatisfaction is obvious. Other features that you may look as being available in your IT architecture are continuous delivery and agility, improved customer participation and transparency in processes, easy and minimum levels of work-flows, smart and pro-active customer support not only through conventional means, but also through live chat, document sharing, transfer of issues to appropriate departments, track-able support tickets etc. are must haves for your IT architecture.
Where are we now?
Not only for SMBs who have resource and budget constraints, in today’s enterprise scenario, even corporations are forced to save every penny in the business model. It is not the actual budget that matters, but whether you will get the ROI and QOS you are looking for your investment. Your IT architecture today have a horizon full of options to support your business with the tools and suites that changes your business environment to a highly productive, collaborative, controlled, and dynamic one.
Data Management and computing are domains where technology has grown enormously. The enterprise service architecture based technologies are making life simpler and richer. If you need a scalable and fully managed database management system to store all your data, your biggest asset, then there are the DBaaS and highly scalable cluster systems like the MySQL Cluster. Managed microservices, cloud based SaaS technologies for communication, collaboration and sharing offer enormous quantity of availability and scalability. Virtualization and Containerization has emerged as powerful alternatives for costly hardware changes. You need not house any hardware in-house that adds to the cost of server maintenance and security, but can purchase the highly scalable, rapidly provisioned computing and data nodes deployed through the cloud and placed in data centers across the world. There is no such thing like one-time investment and periodic investments for upgrades; you need to pay only for what you need at the current moment and foreseeable future.
It is the matter of choices and drive for embracing technology for nourishing your business and productivity through wise decisions. And available for you is a broader spectrum of options, to be picked up and added to your IT architecture shopping cart. What you are supposed to get from this most modern and sophisticated infrastructure is efficiency and growth, with the least effort and cost. And you will start the process of continuous evolution again only to be the best than the previous you.
mysql ndb cluster

An Overview of MySQL NDB Cluster

The “Shared Nothing” Architecture and Database Sharding
It is a distributed computing architecture with interconnected, but independent computing nodes with their own memory and disk storage that are not shared. Compared to a centralized controller based distributed architecture, the SN architecture eliminates presence of a “single point of failure”. An SN architecture offers great scalability by adding as many nodes as wanted. The data is distributed or partitioned among the different nodes that run on separate machines. To respond to user’s queries, some kind of coordination protocol is being used. This is also called database sharding, where the data is horizontally partitioned. A database shard that contains a partition will be holding on a separate database server instance to spread load and to eliminate failures. In horizontal partitioning, table rows are separated, rather than as columns in vertical partitioning or normalization. Each data set will be part of the shard that is distributed across different database servers and also at different physical locations, if needed.
MySQL Cluster and NDB Cluster
A MySQL Cluster is based on the shared-nothing clustering and database sharding architecture. It is built on the NDB or NDBCLUSTER storage engine for MySQL DBMS. MySQL Cluster provides high availability and scalability, high throughput with low latency, etc. It’s architecture is a real-time distributed, multi-master, ACID compliant and transactional, with no single point of failure. It has adopted the horizontally scalable auto-sharding on in-expensive commodity hardware and serves for intense workload read/write operations through both SQL and NoSQL queries.
MySQL Cluster is built on top of NDB Cluster while NDB Cluster is an independent MySQL storage engine named NDBCLUSTER (In-Memory type) that stores tables of rows as in database sharding. It can be accessed using the C++ based NDB API by any application. For the NDB Cluster, MySQL Server Instance is an API process that accesses the NDB Cluster using its API, along with other API processes that can access it, using API formats like Memcached, Node.JS, Java, HTTP/REST, etc. MySQL Cluster adds additional capabilities to the NDB Cluster, like: SQL processing, application connectors like JDBC/ODBC, Table Joins Mechanism, User authentication and authorization and Asynchronous data replication.
The NDB Cluster eliminates the “single point of failure” using the shared-nothing system in which each node has its own memory and disk. Network shares and file systems are not used by NDB Cluster. In technology documentations, the term NDB refers to the part of the setup related to storage engine and MySQL NDB Cluster refers to the integration of MySQL Server(s) with NDB storage engine. In an NDB Cluster there are multiple hosts that run their own node processes. These processes include MySQL server to access NDB data, data nodes for storing data, management servers and other co-ordination processes.

NDB Cluster Components
Image courtesy of MySQL Cluster Documentation – 
MySQL Cluster Manager
MySQL Cluster Manager is responsible for creating, administering, monitoring and managing and MySQL Cluster setup. It is used to automate the common management tasks like scaling through node allocation, upgrades, configuration management, backup and restore. It manages both the MySQL server nodes and data nodes.
In the MySQL Cluster, tables are stored in data nodes, instead of the MySQL server nodes. This data can be accessed using SQL through the MySQL Server and also by using NoSQL API. For this MySQL Cluster Libraries are there that can be used by any application to access the cluster data. The client libraries are available in the following formats:
1. Memcached
2. Node.js / JavaScript
3. Java and JPA
5. NDB API (C++)
MySQL Cluster Nodes
There are 3 types of nodes or processes in a MySQL Cluster:
1. Data node (ndbd/ndbmtd process) – The database tables are automatically sharded across the data nodes. Data nodes are programmed to handle replication, load balancing and failover.
2. Cluster manager node (ndb_mgmd process) – Configures, administers, monitors and perform other management activities like start/restart, arbitration etc.
3. Application node or SQL node (mysqld process) – This MySQL Server process connects to all data nodes for data access, reads and writes. This node is optional and other applications can access the cluster data directly through the NDB API.
Each node will run on separate hosts, either physical or VM or cloud instance.
Working of the MySQL Cluster
MySQL Cluster uses synchronous replication to commit data transactions across the multi-masters in the cluster. This is in contrast to the asynchronous MySQL replication. To ensure availability, replicas of the data, usually two, are created or mirrored and synchronously replicated to the nodes. This ensures fast failover and data loss protection. Sometimes nodes/clusters are hosted in geographically distant data centers and asynchronous data replication is used. This is for ensuring high data security, disaster recovery and to avoid latency by serving from the data center closest to the user.
The individual nodes can independently started/stopped/restarted and can re-join to the system after a failover. For making configuration changes and upgrades that are common to all nodes, a mechanism called Rolling Restarts is used in which all nodes are restarted in turn. Backup and restore of the MySQL Cluster is managed by the NDB Cluster Management client and the ndb_restore program included in the CDB Cluster Software. NDB Cluster nodes use TCP/IP over standard 100 Mbps pr faster hardware for inter-node communication.
Logs and Check Points
The NDB Cluster classifies events based upon categories like startup, shutdown etc), priority and severity. There are two types of event logs used by the cluster:
1. Cluster log – to log events for the cluster as a whole.
2. Node log – separate logs maintained by each nodes.
Nodes save data to the disk and the transaction is committed by adding a date/time checkpoint to identify last modified point. There are two types of checkpoints by which consistency of cluster data is maintained.
1. Local Checkpoint (LCP) – checkpoint specific to each node’s data saving activity. These depend upon the data activity involved with that node as well as on the cluster.
2. Global Checkpoint (GCB) – It occurs every few seconds after synchronizing transactions across all nodes and re-do log (logs data changes) is flushed to disk.
database backup

MariaDB Backup and Restore with mysqldump

In MariaDB both logical and physical backups are possible. Logical backups are made up of SQL statements that actually performed CREATE, SELECT, UPDATE, INSERT, etc. operations on the database and Physical backups are copying the data and index files that store the table information including structure and data. While logical backups are highly portable, physical backups are less portable and compatible between not only different hardware, OS, etc. but also between different storage engines of the same DBMS. This is due to the difference in file structure used by different intra-database storage engines and between different OS, hardware, drivers etc.
Performing logical backup using mysqldump tool

mysqldump is the favorite DBA tool for creating quick backups in different output formats such as SQL, CSV, XML, etc. Triggers are dumped as part of the table structure while stored procedures, events and views can be included in the dump file (output backup file) using mysqldump options like –routines and –events. 
Typically the username and password are specified as options along with mysqldump:
shell> mysqldump –u user_name –p[password el_id=’599c0ffea9f20′] db_name [tbl_name …] > backup-file.sql
To compress the dump file using pipe:
shell> mysqldump –u user –p[password el_id=’599c0ffea9f5d’] dbname | gzip -9 > backupfile.sql.gz
To decompress before restore, use gunzip backupfile.sql.gz.
Below are the basic usages of mysqldump to backup tables, single database and all databases:

shell> mysqldump [options el_id=’599c0ffeaa64a’] db_name [tbl_name …] > backup-file.sql
shell> mysqldump [options el_id=’599c0ffeaa696′] –databases db_name … > backup-file.sql
shell> mysqldump [options el_id=’599c0ffeaa6d0′] –all-databases > backup-file.sql
To restore a dump file into a database:
shell> mysql -u user_name –p[password] db_name < backup-file.sql
Note: a CREATE DATABASE statement will be included only if –databases option is included.
Below are some default options that are needed to perform a valid backup.

-add-drop-table: drop table if exists before a CREATE TABLE statement.
–add-locks: add table lock statements with INSERT statements so that during restore, INSERTS become fast.
–create-options: to include all options related to table creation with CREATE TABLE statements.
–disable-keys: Keys are not created during CREATE TABLE and key checks are not performed during INSERTs (non-unique indexes only) to speed up INSERTs. After completing all INSERTs, indexes (keys) are created.
–extended-insert: to use multiple rows in one INSERT statement to speed up INSERTs and to reduce dump file size.
–lock-tables: to lock all tables before creating the INSERT statements to create the table dumps.
–quick: to speed up dumping on larger tables. Tells mysqldump to read and dump one row at a time rather than dumping the entire row set, reading from memory and placing in buffer prior to insertion.
–set-charset: to use the default charset for the tables.
The above options are grouped in the –opt option and this group is the default option set with a mysqldump command if no other options are specified. These options and the option group are set as default to make the dumping faster and to create a dump file that can be restored quickly.

These default options can be negated or skipped by specifying corresponding skip options.
–skip-add-locks option disables –add-locks option.
–skip-create-option disables –create-options option.
–skip-disable-keys option disables –disable-keys
To apply the default option group –opt with some individual group options skipped, use appropriate skip options along with –opt.
Example: to skip extended inserts and quick options.

shell> mysqldump –opt –skip-extended-insert –skip-quick db_name > backup-file.sql
The above statement can be replaced by:
shell> mysqldump –skip-extended-insert –skip-quick db_name > backup-file.sql
Since –opt is ON by default.
To skip the entire –opt:
shell> mysqldump –skip-opt db_name > backup-file.sql
To skip –opt, but at the same time use some of the default options, for example:
–add-drop-table and –disable-keys, use:
shell> mysqldump –skip-opt –add-drop-table –disable-keys db_name > backup-file.sql
The order of parameters is important in selective enabling/disabling of options. In the above statement, instead of specifying–skip-opt in the beginning, if it is included at the end, it will skip all –opt group options including the –add-drop-table and –disable-keys, that precedes the skip opt.
Another option group is –compact that includes the below options:

–skip-add-drop-table, –skip-add-locks, –skip-comments, –skip-disable-keys and –skip-set-charset
This option group disables structure comments, header and footer statements like DROP TABLE IF EXISTS, enables key building and checks, add table locks etc. These are intended for a debugging operation to analyze and troubleshoot and database.
Below are some options that have importance in specific scenarios:

–debug-check: before exiting, mysqldump will check memory and prints file usage stats.
–debug-info: before exiting, print debug information
–log-error=file_name: to log all errors and warnings encountered during the dumping operation, in the specified file name.
–log-queries: if logging is turned on, the slow queries will be recorded in the slow query log and general queries in query log.
Master Slave Replication

–master-data[=#]: this option is used to create a dump file from a master server for creating a slave server or modify an existing slave for the master server from which the dump is being taken. The binary log position and filename (binary log coordinates) needed for replication slave setup is appended to the output dump file. Setting this option to 1 will add the CHANGE MASTER statement in the dump file, so that the receiving slave will use the parameters for connecting with and replicating the master. An option value of 2 will comment out the above command. Read master-slave replication implementation article here.
shell> mysqldump –master-data=1 –all-databases > dumpfile.sql
–delete-master-logs: this option is used on a master server that has binary logging enabled. This option appends a PURGE BINARY LOGS statement to delete all binary log files older than the log file specified in the PURGE BINARY LOGS command.
    { TO ‘log_name’ | BEFORE datetime_expr }
Deletes all log files older than the file ‘log_name’ or the datetime specified. In effect, –delete-master-logs option enables –master-data to create a dump file for a slave server.
–dump-slave[=value]: this option is used to create a dump file from an existing slave server to create another slave server for the pre-existing master. Value 1 is used to obtain binary log coordinates.
To take an old backup for a slave and replay the old changes since that backup at the master:
shell> mysqldump –all-databases –master-data=2 > all_databases.sql
Creating mysqldump backups for text file importing
–tab=directory_name: this option is used for creating tab-separated text-format (.txt) data files along with the SQL file (.sql) that holds the table’s CREATE TABLE statement in the specified directory name value. So the output will be an SQL file and a TXT file. This text file can be used by the LOAD DATA INFILE statement to dump data into a database table. You must have the FILE permission and mysqldump should be in the same server that runs mysqld and it should have file write permissions on the specified directory. By default, this option places tabs between field values and newline (\n) at the end of line, in the .txt file.
The LOAD DATA INFILE statement imports rows from a text file in to the database at high speed. This text file can be created using mysqldump by adding delimiters and line separators to the rows and fields in the dump file.
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
        [TERMINATED BY ‘string’]
        [[OPTIONALLY] ENCLOSED BY ‘char’]
        [ESCAPED BY ‘char’]
        [STARTING BY ‘string’]
        [TERMINATED BY ‘string’]
    [IGNORE number LINES]
    [SET col_name = expr,…]
When LOAD DATA INFILE statement is used along with the text file that contains the rows, MariaDB identifies fields by:
1. A string that terminates a field, mainly tab or space.
2. (Optionally) enclosed by a char like single or double quotes.
3. Escaped by chars like \t – tab or \s – space.
Individual rows or text file lines are identified by:
1. Starting by a string, generally quotes.
2. Terminated by string, generally newline (\n) and carriage return (\r).
To make such an INFILE, below options are to be used along with mysqldump that works with above –tab option to produce a .txt formatted data file.
–fields-terminated-by=name : used with –tab option to specify a field terminator, usually tab.
–fields-enclosed-by=name : used with –tab option to specify field enclosing string, usually space.
–fields-optionally-enclosed-by=name : same as above, but depends on –fields-terminated-by value.
–fields-escaped-by=name : used with –tab option to specify an escape character (\) for the fields, if they contain instances of tab, newline, or \ that occur within field values.
–lines-terminated-by=name : used with –tab option to specify a string as the end of line (row), generally \r\n.
Hexadecimal codes can also be used to specify the delimiters.
Example: using the hexadecimal 0x0d0 for \r\n (EOL) character as line termination markers
shell> mysqldump –tab=/tmp –fields-terminated-by=’\t’,
         –fields-enclosed-by='”‘ –lines-terminated-by=0x0d0a database1
Transactional Table Backups
When taking backups of transactional tables like InnoDB, the option –single-transaction is used to dump and restore the operation as a single transaction to get a consistent state of the database.
shell> mysqldump –all-databases –single-transaction all_databases.sql

To Organizations – Database is your Biggest Asset

For organizations, small to large, database plays a major role in their IT infrastructure as well as in storing and maintaining the invaluable data assets. These data comprises of the company’s own data plus project data that has high significance in terms of business values. Data makes a company or organization, a working business, and hence it is the biggest asset an organization is expected to take care of, in maintaining the integrity and security of the business itself. Database plays a major role in managing this data, since it is the data warehouse that gives data the capabilities to make the business run, through efficient data storage, maintenance, accessibility, retrieval and security. The DBMS – Database Management System, is a software application that uses hardware and networking for representing, storing, managing, maintaining, serving and securing data – the life energy of any business.
This article discusses some important points organizations need to seriously consider pertaining to their database needs and requirements.


Selecting the database technology
Database technology often is correlated to the overall IT infrastructure of the organization. It depends on the server technology, computing model, networking and distribution demands, scalability and performance requirements etc. Different DBMS products available in the market have their own advantages and disadvantages.
RDBMS is the industry leader for decades, with a solid underlying technology that is based on discrete mathematical principles, the intelligent SQL query language and performance based computing and logical architecture. Each of the major RDBMS systems, like Microsoft SQL Server, Oracle, MySQL, and IBM’s DB2 etc. has their own market share and fan base. This is mainly due to the ecosystem, these systems have nourished over decades, and that comprises of the software versioning and release platforms, support, bug reporting and fixes, announcements, documentation and tutorials, support from resource providers like hosting companies etc.

With the evolution of Big Data applications and massive web-based data requirements, NoSQL emerged as a good choice with its flexible schemas, ability to hold and process bulk amount of data without performance overhead. This is mainly due to the fact that many modern web-based applications have more sparse or redundant data that needs only loose linking. The transactional capabilities, complexity and compatibility considerations required by RDBMS systems are not needed to represent such data and NoSQL is emerged as a great option here. But on the other side it lacks certain features of RDBMS that provides ACID compliance, like transactional integrity, indexing, ease of querying etc.
NoSQL has support for additional data structures like key-value pairs, Documents, Graphs and Wide column store that are used heavily by todays Big Data users, RDBMS has also started providing similar functionality like providing column-store engine to their core.
The In-Memory DBMS improves performance because data is stored in main memory rather than hard disk. Since there is no disk I/O is involved, access latency is highly reduced and other computational requirements for processing disk operations. The disadvantage here obviously is the cost due to the use of costly primary memory.
Another consideration is the computing platform and technology stack – whether you are based on Linux/Unix, Windows or Mainframe. While many of the DBMS solutions are evolving as cross-platform, a prominent percentage of them are still platform dependent. Another factor is the enterprise support needed. While most solutions are open source, an enterprise level support needs you to purchase the commercial version.
Next is the level of implementation that is crucial in terms of cost and scalability. Today, with growing data and storage needs, companies are moving toward the Cloud Based DBaaS providers, also called database outsourcing. Compared to an in-house database server(s) setup, this offers much scalability with reduced cost and resource needs in terms of administration, hardware and software.
Following are other areas to consider during a database decision making process.
Database Design
It is essential for a table or entity to have a unique identifier for each row of data in the form of a primary key. This key is by default a unique constraint also and represents its row uniquely. Without a primary key searches and sorts will take large amount of processing resources. A typical data scheme will have many dependencies and relations among the data entities. This relationship needs to be considered and enforced during database design. For this foreign keys need to be used wherever necessary and its integrity needs to be preserved across tables. There should be indexes applied for columns that need sorting and also are keys for other operations. The number of indexes must be reasonable only because too many indexes can affect performance due to the extra overhead for preserving the index. Periodic monitoring of database performance can greatly assist in identifying useful and unnecessary indexes.
Table or Entity normalization is another feature that must be addressed from the beginning itself. A well normalized database will help the application to issue fast and efficient queries. Such a database can be scaled both horizontally, if more entity relationships are needed. Data must be atomic enough for organizing as a well defined entity relationship. Usage of appropriate data types and correct data sizes are another factor that affect performance and scalability at a table level. For eg: an arbitrary length text column can be assigned the SQL VARCHAR type that grows according to the data in the field, while a fixed length data can be assigned a CHAR type with a fixed size to save space. VARCHAR allows scalability while CHAR provides space optimization. Also it is inefficient to use a DECIMAL or INT with decimal places for an integer data instead of the INT type.
Configure your server properly
However good the database may be, a poorly configured database and system server will produce bottlenecks for productivity and performance. It is important to solve all software dependencies in the system server well before production to avoid crashes during load time. The memory allocation for various storage elements like database files, table space, caches, logs, configuration files etc must be sufficient enough for the smooth operation of the database. Performance statistics and query analysis can be used to optimize and fine tune these configurations throughout the database life cycle.
Backup your database
Your database is a software application that runs on hardware. The very materialistic nature of the database in itself demands solid backup and restore policies. Any point of time, there needs to be a latest possible backup must be available for restoring the database in case of an expected or unexpected emergency. Backups can be automated at system server level by using file backup tools or at database server level using the database backup tools. Scheduling the backup is also important for performance. During backups, sometimes the backup tool may lock tables to prevent changes during backup so that the latest data snapshot can be taken. If this process takes place during a (peak) load time when clients are actively using the database, service denials and application lags can happen, thus degrading the performance. So it is recommended to schedule backup during off-peak time. However for multi-node based database systems like master-slave and clustered systems, this is not an issue, since backup tool runs on a synchronized and separate node while other nodes are available for clients.
Secure your database continuously
Security is the top priority for any database. A breach can occur through many ways, including unauthorized system server access, database server access through over privileged client user, query based attacks etc. The first and foremost place to secure is the system server itself, followed by the database server. In majority of the cases the super admin user named root will be used to install and configure the system. That’s it! Many places are there from which root user can be compromised. The allowed hosts for root in some databases would be ‘%’ meaning ‘any host’. The root user may either be deleted or renamed to some other non ad hoc name that is not familiar to intruders like that the name root does. Next is confining the database users to either the local machine (localhost) or specific client machines/nodes from which database connection is seek.
Next part is restricting the privileges to trivial users to that of READ/WRITE only, so that client applications including that of the hacker do not use database users to perform any DML queries or administrative actions on the database. The GRANT OPTION privilege as well as ALL PRIVILEGES should be restricted to only the genuine administrator of the database. Physical security to your servers comes next, in which the servers are placed in dedicated and secure server rooms with unauthorized access/entry restriction. In case of outsourced database service, any user names supplied to developers/testers etc should be revoked or deleted immediately after the task/project is completed. Majority of DBaaS providers have firewalls to block malicious query injections, DOS attacks, application level intrusions, etc. Your application must be 100% secure to prevent hackers to use it to gain access to the database. Perform security audits periodically to identify the security strength of not only the database but your entire infrastructure.
Perform monitoring, maintenance and optimization rigorously
Monitoring is the great tool that makes you understand the vibes of your database and move along with it. Monitor the database traffic, performance, query cache, slow query log, error log, access log etc. to understand how your database is functioning. Use this information to further optimize and fine tune the database. Occasionally empty the caches and logs and defragment the memory pool to refresh the system. Optimize slow queries for speed and performance. Periodically rebuild indexes or if found of little use, drop less worthy indexes and identify indexes that really boost performance.
Prepare for change and scale out
Every business strives for growth and databases are the primary area that reflects this growth. So from the planning stage itself, leave room for growth and scalability. Prepare for data growth and traffic increase and use resources that allow this expansion or scalability. It is essential that your selected database technology has in-built provisions for vertical scalability. Irrespective of the DBMS, the data must be formatted or backed up in a multi-platform, multi-architecture compatible fashion so that, when time arrives for a major shift, you can carry your data for re-deployment in the new system without any issues. This ensures that the business is capable for growth and the data along with database is always there with the business.

Basic MariaDB Database Administration

MariaDB is an open source RDBMS released under GPL License. Currently there are millions of MariaDB installations across the world. MariaDB’s popularity is due to its speed, security and scalability and capability to handle massive data sets. This article discusses about basic to intermediate MariaDB Administration tasks. More details are available at the MariaDB official knowledgebase.
MariaDB is available for installation in the form of tarballs and binaries for Linux, Solaris and Windows. There are packages for Linux distributions also available at MariaDB Downloads website. MariaDB can also be upgraded from an existing MySQL installation. There are various clients and utilities available for MariaDB configuration, optimization and administration. Some important categories of these clients and utilities are:
1. mysql command line client (SQL shell)
2. Aria table clients and utilities
3. Backup, restore and import clients
4. MyISAM clients and utilities
5. EXPLAIN Analyzer
6. innochecksum
7. mysqladmin
8. mysqlaccess
9. mysqlbinlog
10. mysqlcheck
11. mysqldumpslow
12. mysql_install_db
13. mysql_plugin
14. mysqlreport
15. mysql_secure_installation
16. mysql_setpermission
17. mysqlshow
18. mysqlslap
19. mysql-test
20. mysql_upgrade
Additionally Graphical and Enhanced clients are also available. A few of them are:
Database Workbench, dbForge Studio for MySQL, HeidiSQL, JPDB Admin for MariaDB, MONyog and SQLyog, mycli, phpMyAdmin, Sequel Pro, DBeaver, TOAD for MySQL, etc.
Starting and Stopping MariaDB
The MariaDB Server Binary is called mysqld. There are many ways to start it.
1. Starting from the source directory:
shell > ./mysqld &
2. Use the startup script mysqld_safe, from the MariaDB installation directory:
shell > mysqld_safe &
mysqld_safe starts mysqld.
3. Use the mysql.server startup script located on the /etc/init.d directory to start/stop mysqld:
mysql.server start
mysql.server stop
Once the server is started, the client utility mysql can be used to perform various tasks. To start mysql client and connect to a database:
shell > mysql –user=user_name –password=your_password db_name
Once mysql client starts running the command line prompt changes to mysql >, where you can execute SQL queries.
mysql> SELECT * FROM users where user_name = ‘Robert’
MariaDB User Account Management
1. Create a user from a host if already does not exist:
Query OK, 0 rows affected (0.00 sec)
Valid host names include:
‘sam’@ ‘localhost’ – local server only
‘sam’@ ‘%’ – any host
‘sam’@ ‘190.190.190.%’ – any host from the network mask
‘sam’@ ‘ip_addr’
‘sam’@ ‘host1’ – from the host – host1
2. Create a user, replace if already exists:
CREATE OR REPLACE USER ‘sam’@ ‘localhost’ IDENTIFIED BY ‘password’;
Query OK, 0 rows affected (0.00 sec)
3. Change or alter a user
To force a user to use SSL connections:
Set or restrict connection variables for a user:
4. Change password
SET PASSWORD FOR ‘sam’@’’ = PASSWORD(‘newpassword’);
5. Rename a user
RENAME USER ‘donald’ TO ‘tom’@’localhost’;
6. Grant/Revoke privileges for the user
Granting all privileges:
CREATE USER ‘bob’@’localhost’;
There are different levels of privileges from Global to Procedure privileges.
1. Global privileges
2. Database privileges
3. Table privileges
4. Column privileges
5. Function privileges
6. Procedure privileges
The ALL PRIVILEGES privilege grants all privileges. The clause WITH GRANT OPTION gives the user ability to grant privileges for other user.
Giving all privileges on a specific database db1 only.
GRANT ALL PRIVILEGES ON db1.* to ‘bob’@’localhost;
7. Revoke privileges from a user
8. Remove a user
Query OK, 0 rows affected (0.00 sec)
After all user management operations, the privileges table needs to be reloaded:
MariaDB Backup and Restore
The mysqldump client utility can be used for backup and restore.
Backup to a sql file:
shell> mysqldump db_name > backup-file.sql
Restore the backup
shell> mysql db_name < backup-file.sql
Creating/Selecting/Dropping Databases
create database mydb;
use mydb;
drop database mydb;

Galera Cluster for MySQL and MariaDB

Galera Cluster for MySQL is a MySQL multi-master cluster setup that uses the Galera Replication Plugin. The replication is synchronous so that any changes happened at any one master node is immediately replicated to other master nodes as broadcasted transaction commits. This synchronization provides high-availability, high up-time and scalability. All master nodes in the cluster are available both READS/WRITES. The Galera Replication Plugin provides automatic node control to implement dropping of failed nodes from the cluster and rejoining recovered nodes back to the cluster. This prevents data loss and clients can connect to any node, as decided by the Replication Load Balancer. Since changes are synchronized between all nodes, unlike conventional replication, there is no slave lag, lost transactions and client latencies are kept at a minimum level.

The Galera Replication Plugin
Galera Cluster is implemented by activating the Galera Replication Plugin on the MySQL or MariaDB database server. This transactional replication plugin extends the MySQL replication plugin API and is called the Write-Set Replication API or wsrep API. This API creates the interface between the Galera Replication and the MySQL/MariaDB DBMS Engine. It is responsible for producing the multi-master synchronous replication that is also a certification-based replication. In certification-based replication, the Galera Plugin prepares WRITE transactions also called write-sets in each node, that include database row changes and all locks applied to the database at the time of transaction. This write-set is added to the transaction queue and each node certifies this write-set against other write-sets in the applier queue. This certification causes transaction commit and the WRITES are applied to the nodes’ tablespace. Even though this is a “logical synchronization”, since each node needs to certify the write-set independently, the actual writing and committing to the node’s tablespace is also independent and hence asynchronous.
Synchronous Vs. Asynchronous Replication
1. In synchronous replication, any changes happened in one node is applied to other nodes in the cluster with guarantee as certified transactions. In asynchronous replication, changes in master are replicated to slave nodes only upon request from the slave and hence there is no guarantee for the transaction to occur within a certain time limit. If master crashes or if there is network/propagation delay, the data replication is negatively affected.
2. Synchronous replication provides high-availability, since any node crash will not cause data loss or affect data integrity, since other nodes maintain a consistent replica of data and state.
3. Since the replication is transaction based, they are applied in parallel across all nodes.
However, the extra overhead in the form of complexity and distributed locking for synchronous replication cause delays in the cluster, compared to asynchronous replication.
Replication in the Galera Cluster
The wsrep API that powers Galera Replication Plugin uses the database-state model for implementing replication. The data, READ/WRITE/COMMIT etc. locks at a given point of time is the state of that database. When clients perform READ/WRITES, the database state changes. These state changes are translated as write-sets and recorded by the wsrep API as transactions. The nodes synchronize their states by applying these write-set transactions broadcasted through a transaction queue, in the same serial order. It is the Galera Replication Plugin that manages the write-set certification and its replication across the cluster.
The different components of the Galera Replication Plugin are:
  • Certification Layer: creates write-sets from database state changes, perform certification checks on these write-sets and ensures that the write-sets can be applied to the nodes in the cluster. 
  • Replication Layer: Manages the entire replication protocol implementation and controls the cluster. 
  • Group Communication Framework: Provides a plugin architecture based communication system for the various component groups that connect to the Galera Cluster. 
Advantages of Galera Cluster

  1. High availability due to multiple masters synchronized together.
  2. True multi-master – read/write to any node.
  3. Tightly coupled by synchronization – all nodes maintain same state.
  4. No data loss – changes are transaction committed as and when they occur without delay. So node failures will not affect data integrity.
  5. Automatic node provisioning.
  6. Hot standby – no downtime, since failed nodes are compensated by other nodes.

Database Weekly News

The rise of the vertical database.
Things are really heating up in the database industry; the last ten years have seen an explosion in the number of products and vendors (analyst firm 451 Research now tracks 386 different products on its popular ‘tube-map’ of the database landscape). The rapid evolution of the internet and the move towards data driven decisions across all industries has created a rich environment for database innovation. Read more.
The OpenSource Innovation Revolution.
Back in 2000 most Fortune 1000 companies shunned the idea of utilizing OpenSource technologies. Not that there were many commercially available products to choose from. What most technology managers didn’t realize at the time, was that the OpenSource community was in the midst of a technology innovation revolution.
The first shot of this revolution was fired in 1991 by a young Finnish student named Linus Torvalds. His personal operating system project (Linux) proved to be the foundation and spark that ignited an innovation explosion. In the 24 years since the Linux kernel was introduced, there have been dozens of technologies that have changed the perception and adoption of Open Source within large U.S. businesses. Read more.
Bad News: Your Favorite Database Isn’t the Best.
We decided to answer one of the key questions in the database world: Which is faster, PostgreSQL or MySQL? This analysis has never been possible to do objectively before, but with recent advances, it now is. So we did it. Read more.
SQLite 3.20 Improves Query Planner.
There’s a major upgrade to SQLite, the embeddable SQL database library that is found in many memory constrained gadgets such as cellphones, PDAs, and MP3 players. The new release has an improved query planner, and an extension that suggest tab-completions for interactive user interfaces. Read more.
Rising database security threats require attention — and action.
In the not too distant past, Gartner’s data management analysts didn’t get many inquiries about data security issues from the IT managers and other corporate professionals they advise, but that has changed.
With database security threats taking on new dimensions in the big data era, data management teams are asking more security-related questions, according to Gartner analyst Merv Adrian. The volume is still relatively small — only one-quarter the level of inquiries about the Hadoop processing framework, for example. Read more.


mysql dbaas

MySQL DBaaS Case Study – DrawCoin Creative

About DrawCoin Creative

 is an online multi player game provider that has a vast community of gamers and reviewers spread across the globe. Their CityBuilding multi player game has high requirements for solid, robust and scalable computing and data storage facilities. Since the gamers are from different geographic regions, DrawCoin Creative needed highly distributed clusters of computing and data management nodes. Since their community is expanding they were looking for a highly scalable and fast infrastructure to implement.

The Game
DrawCoin’s multiplayer online CityBuilding game is a blend of rich graphics and realistic actions. Their base modules themselves needed high amount of memory and computing capabilities due to the fact that:
1. The game has about 25 different modes starting from training to Country Projects.
2. There are more than 100 City types with different specification and feature levels.
3. There are more than 500 locations created according to the varying geographical characteristics.
4. There are more than 100 levels of skill ranging from amateur to professional for the gamers.
5. Enormous amount of data is there in the form of player profile, statistics, city/locations/geography/climate details, configurations per player, leaderboards etc.
The Computing Server Part
DrawCoin’s multi player CityBuilding game was developed using different technologies like shell based CGIs, Node.js etc. They are using a distributed computing network with high end specifications for providing the most real-time experience for their gamers. The infrastructure included nodes deployed as clusters with top load balancing schemes.
But when they realized that the threshold of their data storage capabilities were approaching rapidly due to the growing gamer community, they started thinking about a Data Storage Solution that supports the computing infrastructure with the required support and integrity. After doing some research and case studies, they decided to use MySQL based Database as a Service hosted solution. This crucial decision was made mainly because of the capabilities of MySQL and the flexibility and efficiency they realized to achieve from a hosted solution.
The MySQL DBaaS Service Provider
They selected ClusterEngine as their service provider from an initial list of DBaaS providers. ClusterEngine stands distinct from others due to their portfolio, infrastructure, support, technology and flexible on-the-go pricing. The transition from their in-house database server to ClusterEngine’s solution was smoothly handled by ClusterEngine without any downtime.
DrawCoin is offered the cloud based MySQL cluster instances that are deployed in different data centers across the globe. Client API calls are routed by computing server to the nearest data server, thus reducing response time and latency to match the needs of a strategy game. The intelligent load balancing system of ClusterEngine distributed the heavy load evenly to the replicated servers in the cluster.
The Result
The end user experience was amazing according to the feedback received by DrawCoin. The game got top reviews and gamer community started growing rapidly. The in-house engineering team was happy with the High Availability and Prompt Support from the DBaaS provider ClusterEngine. Up-time touched 99.9% with minimal maintenance schedule. The most fascinating experience for DrawCoin was the potential of scalability that they really utilized well. More database instances were allocated upon request, due to the high volume gaming and the cost was still less than that would be in the case of an in-house deployment. They paid for only what they consumed and the company’s revenue clearly indicated the savings that they yielded by this Database Outsourcing.
According to the CTO of DrawCoin Creative – Bum-Erdenehiyn Dorzhgotov:
“We were running in full throttle and our in-house deployment was throwing signs of under-performance with the growing gaming activities, during when we decided to switch to the DBaaS provider ClusterEngine. The migration, otherwise a nightmare, was smoother than what we expected. After the migration and deployment, our computing performance also increased due to the fast response time and geo-location based routing. And when we needed more space and resources, they were just a few mouse clicks away in the control panel provided by ClusterEngine for us. All this came with highly affordable cost and gave us the peace of mind that we re-utilized for concentrating in improvisation of our product.”
The Technical Advantages MySQL DBaaS Provided to DrawCoin
Apart from high availability, scalability and reliable support, ClusterEngine’s MySQL DBaaS provided DrawCoin with the following invaluable advantages.
1. Enhanced security – the cloud based instances are deployed in a highly secure, isolated from public network with firewall protected and restricted IP/Host environment. Any attempt of intentional or unintentional security breach is immediately detected, blocked, recorded and notified.
2. Reliable Backup Schemes – the replicated server architecture and MySQL’s in-built support for high end replication, allowed implementation of a solid and steady backup scheme with zero downtime.
3. Speed and Optimization – the MySQL DBaaS provided Slow Query Log and Performance Monitoring Report helped the engineering team to optimize and fine tune the queries for maximum performance.
4. Analytics Dashboard – the traffic, performance, query logs and other analytics provided in the Dashboard and in the form of detailed reports, enabled DrawCoin to form a research team that efficiently used the reports to formulate and implement strategies for horizontal scaling.
5. The ClusterEngine DBaaS was fully responsible for the maintenance, troubleshooting, configuration and upgrades of the MySQL cloud instances. This allowed DrawCoin to be free from stressful system administration tasks and they got more opportunities and time to focus on the other parts of their infrastructure.
6. The MySQL Cluster Architecture seamlessly blended with DrawCoin’s computing and middle-ware infrastructure to produce astonishing outputs and massive expansion and popularity of the gaming community.
DrawCoin Creative believes, the current popularity and broad gamer base for their CityBuilding game was made possible by the intelligent decision of switching to the MySQL DBaaS provided by ClusterEngine. Developed by one of the world’s largest technology corporation and supported by a 100% reliable service provider – ClusterEngine, this MySQL DBaaS is still playing its crucial role in supporting DrawCoin’s endeavors and contributing to its growth.