Principal goal to setup clustering MySQL is to have redundancy – you server and purposes will run easily even when one server goes down.
Notice: For higher efficiency it’s best to have a third server as a administration node however this may be shut down after the cluster begins. Additionally observe that shutting down the administration server will not be beneficial (see the additional notes on the backside of this doc for extra data). You cannot run a MySQL Cluster with simply two Devoted servers And have true redundancy.
It’s doable to arrange the cluster on two Devoted Servers you’ll not get the flexibility to “kill” one server and for the cluster to proceed as regular. For this you want a 3rd server operating the administration node.
Now beneath I had given the instance for 3 servers:
mysql1.area.com – 192.168.0.1
mysql2.area.com – 192.168.0.2
mysql3.area.com – 192.168.0.3
Servers 1 and a pair of would be the two that find yourself “clustered”. This may be good for 2 servers behind a load balancer or utilizing spherical robin DNS and is an efficient substitute for replication. Server 3 must have solely minor modifications made to it and does NOT require a MySQL set up. It may be a low-end machine and will be finishing up different duties.
STAGE 1: Set up MySQL on the primary two servers:
Full the next steps on each mysql1 and mysql2:
cd /usr/native/
dev.mysql.com/get/Downloads/MySQL-4.1/mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz/
from/signal42.com/mirrors/mysql/
groupadd mysql
useradd -g mysql mysql
tar -zxvf mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz
rm mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz
ln -s mysql-max-4.1.9-pc-linux-gnu-i686 mysql
cd mysql
scripts/mysql_install_db –user=mysql
chown -R root .
chown -R mysql knowledge
chgrp -R mysql .
cp support-files/mysql.server /and so on/rc.d/init.d/
chmod +x /and so on/rc.d/init.d/mysql.server
chkconfig –add mysql.server
Don’t begin MySQL but.
STAGE 2: Set up and configure the administration server
You want the next information from the bin/ of the mysql listing: ndb_mgm and ndb_mgmd. Obtain the entire mysql-max tarball and extract them from the bin/ listing.
mkdir /usr/src/mysql-mgm
cd /usr/src/mysql-mgm
dev.mysql.com/get/Downloads/MySQL-4.1/mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz/
from/www.signal42.com/mirrors/mysql/
tar -zxvf mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz
rm mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz
cd mysql-max-4.1.9-pc-linux-gnu-i686
mv bin/ndb_mgm .
mv bin/ndb_mgmd .
chmod +x ndb_mg*
mv ndb_mg* /usr/bin/
cd
rm -rf /usr/src/mysql-mgm
You now have to arrange the config file for this administration:
mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
vi [or emacs or any other editor] config.ini
Now, insert the next (altering the bits as indicated):
[NDBD DEFAULT]
NoOfReplicas=2
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Managment Server
[NDB_MGMD]
HostName=192.168.0.3 # the IP of THIS SERVER
# Storage Engines
[NDBD]
HostName=192.168.0.1 # the IP of the FIRST SERVER
DataDir= /var/lib/mysql-cluster
[NDBD]
HostName=192.168.0.2 # the IP of the SECOND SERVER
DataDir=/var/lib/mysql-cluster
# 2 MySQL Purchasers
# I personally go away this clean to permit fast modifications of the mysql purchasers;
# you possibly can enter the hostnames of the above two servers right here. I recommend you dont.
[MYSQLD]
[MYSQLD]
Now, begin the administration server:
ndb_mgmd
That is the MySQL administration server, not administration console. It’s best to due to this fact not anticipate any output (we are going to begin the console later).
STAGE 3: Configure the storage/SQL servers and begin MySQL
On every of the 2 storage/SQL servers (192.168.0.1 and 192.168.0.2) enter the next (altering the bits as applicable):
vi /and so on/my.cnf
Enter i to go to insert mode once more and insert this on each servers (altering the IP tackle to the IP of the administration server that you simply arrange in stage 2):
[mysqld]
ndbcluster
ndb-connectstring=192.168.0.3 # the IP of the MANAGMENT (THIRD) SERVER
[mysql_cluster]
ndb-connectstring=192.168.0.3 # the IP of the MANAGMENT (THIRD) SERVER
Now, we make the info listing and begin the storage engine:
mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
/usr/native/mysql/bin/ndbd –initial
/and so on/rc.d/init.d/mysql.server begin
When you have accomplished one server now return to the beginning of stage 3 and repeat precisely the identical process on the second server.
Notice: it’s best to ONLY use –initial in case you are both ranging from scratch or have modified the config.ini file on the administration.
STAGE 4: Test its working
Now you can return to the administration server (mysql3) and enter the administration console:
/usr/native/mysql/bin/ndb_mgm
Enter the command SHOW to see what’s going on. A pattern output seems like this:
[root@mysql3 mysql-cluster]# /usr/native/mysql/bin/ndb_mgm
— NDB Cluster — Administration Consumer —
ndb_mgm> present
Linked to Administration Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=2 @192.168.0.1 (Model: 4.1.9, Nodegroup: 0, Grasp)
id=3 @192.168.0.2 (Model: 4.1.9, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.3 (Model: 4.1.9)
[mysqld(API)] 2 node(s)
id=4 (Model: 4.1.9)
id=5 (Model: 4.1.9)
ndb_mgm>
In the event you see
not related, accepting join from 192.168.0.[1/2/3]
within the first or final two traces they you might have an issue. Please e-mail me with as a lot element as you may give and I can attempt to discover out the place you might have gone improper and alter this HOWTO to repair it.
In case you are OK to right here it’s time to take a look at MySQL. On both server mysql1 or mysql2 enter the next instructions: Notice that we now have no root password but 해외서버.
mysql
use take a look at;
CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
INSERT INTO ctest () VALUES (1);
SELECT * FROM ctest;
It’s best to see 1 row returned (with the worth 1).
If this works,which can in all probability occur, go to the opposite server and run the identical SELECT and see what you get. Insert from that host and return to host 1 and see if it really works. If it really works then congratulations.
The ultimate take a look at is to kill one server to see what occurs. When you have bodily entry to the machine merely unplug its community cable and see if the opposite server retains on going nice (attempt the SELECT question). In the event you dont have bodily entry do the next:
ps aux | grep ndbd
You get an output like this:
root 5578 0.0 0.3 6220 1964 ? S 03:14 0:00 ndbd
root 5579 0.0 20.4 492072 102828 ? R 03:14 0:04 ndbd
root 23532 0.0 0.1 3680 684 pts/1 S 07:59 0:00 grep ndbd
On this case ignore the command “grep ndbd” (the final line) however kill the primary two processes by issuing the command kill -9 pid pid:
kill -9 5578 5579
Then attempt the choose on the opposite server. While you’re at it run a SHOW command on the administration node to see that the server has died. To restart it, simply situation
ndbd
Notice: no –initial!
Additional notes about setup
I strongly advocate that you simply learn all of this (and bookmark this web page). It should virtually actually prevent quite a lot of looking.
The Administration Server
I strongly advocate that you don’t cease the administration server as soon as it has began. That is for a number of causes:
* The server would possibly hardly require and take any server sources
* If a cluster falls over, you need to have the ability to simply ssh in and sort ndbd to stat it. You’ll not wish to begin messing round with one other server
* You want the administration server up If you wish to take backups
* The cluster log is distributed to the administration server so to verify what’s going on within the cluster or has occurred since final this is a vital instrument
* All instructions from the ndb_mgm shopper is distributed to the administration server and thus no administration instructions with out administration server.
* The administration server is required in case of cluster reconfiguration (crashed server or community break up). Within the case that it isn’t operating, “split-brain” situation will happen. The administration server arbitration position is required for any such setup to supply higher fault tolerance.
