Book Image

High Availability MySQL Cookbook

By : Alexander Davies
Book Image

High Availability MySQL Cookbook

By: Alexander Davies

Overview of this book

High Availability is something that all web sites hope to achieve, especially those that are linked to big companies.MySQL, an open source relational database management system (RDBMS), can be made highly available to protect from corruption, hardware failure, software crashes, and user error. Running a MySQL setup is quite simple. Things start getting complex when you start thinking about the best way to provide redundancy. There are a large number of techniques available to add 'redundancy' and 'high availability' to MySQL, but most are both poorly understood and documented.This book will provide you with recipes showing how to design, implement, and manage a MySQL Cluster and achieve high availability using MySQL replication, block level replication, shared storage, and the open source Global File System (GFS).This book covers all the major techniques available for increasing availability of your MySQL databases. It demonstrates how to design, implement, troubleshoot and manage a highly available MySQL setup using any one of several techniques, which are shown in different recipes. It is based on MySQL Cluster 7.0, MySQL (for non clustered recipes) 5.0.77, and CentOS / RedHat Enterprise Linux 5.3.The book starts by introducing MySQL Cluster as a technology and explaining how to set up a simple cluster. It will help you to master the options available for backing up and restoring a file in the MySQL Cluster. By following the practical examples in this book, you will learn how to manage the MySQL Cluster. Further, we will discuss some troubleshooting aspects of the MySQL Cluster.We also have a look at achieving high availability for MySQL databases with the techniques of MySQL Replication, block level replication, shared storage (a SAN or NAS), and DRBD.Finally, you will learn the principles of Performance tuning and tune MySQL database for optimal performance.
Table of Contents (17 chapters)
High Availability MySQL Cookbook
Credits
About the Author
About the Reviewers
Preface
Base Installation
Index

Starting a management node


In this recipe, we will start the management node installed in the previous recipe, and then use the management client to confirm that it has properly started.

How to do it…

The first step is to create the data directory for the management node that you defined in config.ini file as follows:

[root@node5 mysql-cluster]# mkdir -p /usr/local/mysql-cluster

Now, change the directory to it and run the management node process (ndb_mgmd), telling it which configuration file to use:

[root@node5 mysql-cluster]# cd /usr/local/mysql-cluster
[root@node5 mysql-cluster]# ndb_mgmd  --config-file=config.ini 
2009-06-28 22:14:01 [MgmSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.34 ndb-7.0.6
2009-06-28 22:14:01 [MgmSrvr] INFO     -- Loaded config from '//mysql-cluster/ndb_1_config.bin.1'

Finally, check the exit code of the previous command (with the command echo $?). An exit code of 0 indicates success:

[root@node5 mysql-cluster]# echo $?
0

If you either got an error from running ndb_mgmd or the exit code was not 0, turn very briefly to the There's more… section of this recipe for a couple of extremely common problems at this stage.

Note

Everything must run as root, including the ndbd process. This is a common practice; remember that the servers running MySQL Cluster should be extremely well protected from external networks as anyone with any access to the system or network can interfere with the unencrypted and unauthenticated communication between storage nodes or connect to the management node. In this book, all MySQL Cluster tasks are completed as root.

Assuming that all is okay, we can now run the MySQL Cluster management client, ndb_mgm. This will be the default, connecting to a management client running on the local host on port 1186. Once in the client, use the SHOW command to show the overall status of the cluster:

[root@node5 mysql-cluster]# ndb_mgm
-- NDB Cluster -- Management Client –

And have a look at the structure of our cluster:

ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 (not connected, accepting connect from 10.0.0.1)
id=4 (not connected, accepting connect from 10.0.0.2)

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

[mysqld(API)] 4 node(s)
id=11 (not connected, accepting connect from 10.2.0.2)
id=12 (not connected, accepting connect from 10.2.0.3)
id=13 (not connected, accepting connect from any host)
id=14 (not connected, accepting connect from any host)

This shows us that we have two storage nodes (both disconnected) and four API or SQL nodes (both disconnected). Now check the status of node ID 1 (the management node) with the <nodeid> STATUS command as follows:

ndb_mgm> 1 status
Node 1: connected (Version 7.0.6)

Finally, exit out of the cluster management client using the exit command:

ndb_mgm> exit

Congratulations! Assuming that you have no errors here, you now have a cluster management node working and ready to receive connections from the SQL and data nodes which are shown as disconnected.

There's more…

In the event that your cluster fails to start, a couple of really common causes have been included here:

If the data directory does not exist, you will see this error:

[root@node5 mysql-cluster]# ndb_mgmd
2009-06-28 22:13:48 [MgmSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.34 ndb-7.0.6
2009-06-28 22:13:48 [MgmSrvr] INFO     -- Loaded config from '//mysql-cluster/ndb_1_config.bin.1'
2009-06-28 22:13:48 [MgmSrvr] ERROR    -- Directory '/var/lib/mysql-cluster' specified with DataDir in configuration does not exist.
[root@node5 mysql-cluster]# echo $?
1

In this case, make sure that the directory exists:

[root@node5 mysql-cluster]# mkdir –p /var/lib/mysql-cluster

If there is a typo in the configuration file or if the cluster cannot find the config.ini file you may see this error:

[root@node5 mysql-cluster]# ndb_mgmd  --config-file=config.ini
2009-06-28 22:15:50 [MgmSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.34 ndb-7.0.6
2009-06-28 22:15:50 [MgmSrvr] INFO     -- Trying to get configuration from other mgmd(s) using 'nodeid=0,localhost:1186'...

At this point ndb_mgmd will hang. In this case, kill the ndb_mgmd process (Ctrl + C or with the kill command) and double-check the syntax of your config.ini file.