MySQL cluster Series

MySQL cluster ( Cluster) Series (1. Dual-host high availability)I. Introduction
This document describes how to install and configure a MySQL cluster based on two servers. In addition, the MySQL cluster can continue to run when any server encounters a problem or goes down. Add the following (keepalived + LVS + MySQL cluster document) to achieve high availability and load balancing of MySQL Dual Servers.
Installation environment and software package:
VMware Workstation 5.5.3
Mysql-5.2.3-falcon-alpha.tar.gz
Gentos 2006.1
Server1: 192.168.1.111
Server2: 192.168.1.110

2. Install MySQL on Server 1 and Server 2
Perform the following steps on Server 1 and Server 2
# Music mysql-5.2.3-falcon-alpha.tar.gz/tmp/package
# Cd/tmp/package
# Groupadd MySQL
# Useradd-G MySQL
# Tar-zxvf mysql-5.2.3-falcon-alpha.tar.gz
# Rm-F mysql-5.2.3-falcon-alpha.tar.gz
# Music mysql-5.2.3-falcon-alpha MySQL
# Cd MySQL
#./Configure -- prefix =/usr -- With-extra-charsets = Complex -- With-plugin-ndbcluster -- With-plugin-partition -- With-plugin-Innobase
# Make & make install
# Ln-S/usr/libexec/ndbd/usr/bin
# Ln-S/usr/libexec/ndb_mgmd/usr/bin
# Ln-S/usr/libexec/ndb_cpcd/usr/bin
# Ln-S/usr/libexec/mysqld/usr/bin
# Ln-S/usr/libexec/mysqlmanager/usr/bin
# Mysql_install_db -- user = MySQL

3. install and configure nodes
Perform the following steps on Server 1 and Server 2
1. Configuration Management node configuration file:
# Mkdir/var/lib/MySQL-Cluster
# Cd/var/lib/MySQL-Cluster
# Vi config. ini
Add the following content to config. ini:
[Ndbd default]
Noofreplicas = 2
Maxnoofconcurrentoperations = 10000
Datamemory = 80 m
Indexmemory = 24 m
Timebetweenwatchdogcheck= 30000
Datadir =/var/lib/MySQL-Cluster
Maxnooforderedindexes = 512
[Ndb_mgmd default]
Datadir =/var/lib/MySQL-Cluster
[Ndb_mgmd]
Id = 1
Hostname = 192.168.1.111
[Ndb_mgmd]
Id = 2
Hostname = 192.168.1.110
[Ndbd]
Id = 3
Hostname = 192.168.1.111
[Ndbd]
Id = 4
Hostname = 192.168.1.110
[Mysqld]
[Mysqld]
[TCP default]
Portnumber = 63132

2. Configure the general my. CNF file, which is used by mysqld, ndbd, and ndb_mgmd.
# Vi/etc/My. CNF
Add the following content to my. CNF:
[Mysqld]
Default-storage-engine = ndbcluster avoid adding engine = ndbcluster to SQL statements.
Ndbcluster
NDB-connectstring = 192.168.1.111, 192.168.1.110
[Ndbd]
Connect-string = 192.168.1.111, 192.168.1.110
[Ndb_mgm]
Connect-string = 192.168.1.111, 192.168.1.110
[Ndb_mgmd]
Config-file =/var/lib/MySQL-cluster/config. ini
[Mysql_cluster]
NDB-connectstring = 192.168.1.111, 192.168.1.110

After saving and exiting, start the management node server1 as follows:
# Ndb_mgmd -- ndb_nodeid = 1
Start the management node server2 as follows:
# Ndb_mgmd -- ndb_nodeid = 2

Note: There is a warning prompt at startup.
Cluster configuration warning:
Arbitrator with ID 1 and DB node with ID 3 on same host 192.168.1.111
Arbitrator with ID 2 and DB node with ID 4 on same host 192.168.1.110
Running Arbitrator on the same host as a database node may
Cause complete cluster shutdown in case of host failure.
The arbitrator of nodes 1, 3, 2, and 4 may cause the entire cluster to fail. (Don't worry about it)

4. initialize the Cluster
In server1
# Ndbd -- nodeid = 3 -- Initial
In server2
# Ndbd -- nodeid = 4 -- iniitial
Note: The -- initial parameter is required only when ndbd is started for the first time or after config. INI is modified!

5. Check the working status
Start the Management Terminal on any machine:
# Ndb_mgm
Enter the show command to view the current working status: (The following is an example of status output)
-- NDB Cluster -- management client --
Ndb_mgm> show
Connected to management server at: 192.168.1.111: 1186
Cluster configuration
---------------------
[Ndbd (NDB)] 2 node (s)
Id = 3 @ 192.168.1.111 (Version: 5.2.3, nodegroup: 0, Master)
Id = 4 @ 192.168.1.110 (Version: 5.2.3, nodegroup: 0)
[Ndb_mgmd (MGM)] 2 node (s)
Id = 1 @ 192.168.1.111 (Version: 5.2.3)
Id = 2 @ 192.168.1.110 (Version: 5.2.3)
[Mysqld (API)] 2 node (s)
Id = 5 (not connected, accepting connect from any host)
Id = 6 (not connected, accepting connect from any host)
Ndb_mgm>

If there is no problem above, add mysqld (API) now ):
Note: This document does not set the root password for MySQL. We recommend that you set the root password for MySQL of server1 and server2.
In server1:
# Mysqld_safe -- ndb_nodeid = 5 -- user = MySQL &
In server2:
# Mysqld_safe -- ndb_nodeid = 6 -- user = MySQL &
# Ndb_mgm-e show
The information is as follows:
Connected to management server at: 192.168.1.111: 1186
Cluster configuration
---------------------
[Ndbd (NDB)] 2 node (s)
Id = 3 @ 192.168.1.111 (Version: 5.2.3, nodegroup: 0, Master)
Id = 4 @ 192.168.1.110 (Version: 5.2.3, nodegroup: 0)
[Ndb_mgmd (MGM)] 2 node (s)
Id = 1 @ 192.168.1.111 (Version: 5.2.3)
Id = 2 @ 192.168.1.110 (Version: 5.2.3)
[Mysqld (API)] 4 node (s)
Id = 5 @ 192.168.1.111 (Version: 5.2.3)
Id = 6 @ 192.168.1.110 (Version: 5.2.3)

OK, you can test it:
In server1
#/Usr/local/MySQL/bin/MySQL-u root-P
> Create Database AA;
> Use AA;
> Create Table ctest (I INT );
> Insert into ctest () values (1 );
> Select * From ctest;
We can see 1 row returned information (return value 1 ).
If the above is normal, switch to server2 and observe the effect. If the operation succeeds, execute insert in server2 and return to server1 to check whether the operation is normal.
If no problem exists, congratulations!

Vi. Destructive Testing
Unplug the network cable of Server 1 or server 2 (ifconfig eth0 down) and check whether the server in the other cluster works normally (you can use select to query and test ). After testing, re-insert the network cable.
Note: The test result is invalid before any read/write operations are performed on the cluster, because after the cluster is started, only a few empty directories are created under/var/lib/MySQL-cluster, if the entire storage (ndbd) node is disabled.
You can also test it as follows: On server1 or server2:
# Ps aux | grep ndbd
All ndbd processes are displayed:
Root 5578 0.0 0.3 6220 1964? S ndbd
Root 5579 0.0 20.4 492072 102828? R ndbd
Root 23532 0.0 0.1 3680 684 pts/1 s grep ndbd
Then, kill an ndbd process to destroy the MySQL Cluster Server:
# Kill-9 5578 5579
Then use the SELECT query test on another cluster server. In addition, execute the show command on the management terminal of the Management node server to view the State of the damaged server.
After the test is complete, you only need to restart the ndbd process of the damaged server:
# Ndbd -- ndb_nodeid = ID of the storage Node
Note! As mentioned above, the -- inparameters parameter is not required!
Now, the configuration of the MySQL dual-host cluster is complete!MySQL cluster (Cluster) Series(2.Adding nodes online-Online hotplugin)MySQL cluster (cluster) Series (2. Adding nodes online rather than online-Online hotplugin)

I. Introduction
This document describes how to design a MySQL cluster, create a cluster template, and effectively avoid MySQL restrictions. (This article is for the module of Management 2, storage 4, and data 8)
Installation environment and software package:
VMware Workstation 5.5.3
Mysql-5.2.3-falcon-alpha.tar.gz
Gentos 2006.1
(Multiple IP addresses per Nic)
Server1: 192.168.1.111 (ndb_mgmd, id = 1)
Server1: 192.168.1.112 (ndbd, id = 3)
Server1: 192.168.1.113 (ndbd, id = 4)
Server2: 192.168.1.110 (ndb_mgmd, id = 2)
Server2: 192.168.1.109 (ndbd, id = 5)
Server2: 192.168.1.108 (ndbd, id = 6)

2. Install MySQL on Server 1 and Server 2
Perform the following steps on Server 1 and Server 2
# Music mysql-5.2.3-falcon-alpha.tar.gz/tmp/package
# Cd/tmp/package
# Groupadd MySQL
# Useradd-G MySQL
# Tar-zxvf mysql-5.2.3-falcon-alpha.tar.gz
# Rm-F mysql-5.2.3-falcon-alpha.tar.gz
# Music mysql-5.2.3-falcon-alpha MySQL
# Cd MySQL
#./Configure -- prefix =/usr -- With-extra-charsets = Complex -- With-plugin-ndbcluster -- With-plugin-partition -- With-plugin-Innobase
# Make & make install
# Ln-S/usr/libexec/ndbd/usr/bin
# Ln-S/usr/libexec/ndb_mgmd/usr/bin
# Ln-S/usr/libexec/ndb_cpcd/usr/bin
# Ln-S/usr/libexec/mysqld/usr/bin
# Ln-S/usr/libexec/mysqlmanager/usr/bin
# Mysql_install_db -- user = MySQL

3. install and configure nodes
Perform the following steps on Server 1 and Server 2
1. Configuration Management node configuration file:
# Mkdir/var/lib/MySQL-Cluster
# Cd/var/lib/MySQL-Cluster
# Vi config. ini
Add the following content to config. ini:
[Ndbd default]
Noofreplicas = 4 (this article has four storage nodes)
Maxnoofconcurrentoperations = 10000
Datamemory = 80 m
Indexmemory = 24 m
Timebetweenwatchdogcheck= 30000
Datadir =/var/lib/MySQL-Cluster
Maxnooforderedindexes = 512
[Ndb_mgmd default]
Datadir =/var/lib/MySQL-Cluster
[Ndb_mgmd]
Id = 1
Hostname = 192.168.1.111
[Ndb_mgmd]
Id = 2
Hostname = 192.168.1.110
[Ndbd]
Id = 3
Hostname = 192.168.1.112
[Ndbd]
Id = 4
Hostname = 192.168.1.113
[Ndbd]
Id = 5
Hostname = 192.168.1.109
[Ndbd]
Id = 6
Hostname = 192.168.1.108
[Mysqld]
[Mysqld]
[Mysqld]
[Mysqld]
[Mysqld]
[Mysqld]
[Mysqld]
[Mysqld] A total of eight mysqld Definitions
[TCP default]
Portnumber = 63132

2. Configure the general my. CNF file, which is used by mysqld, ndbd, and ndb_mgmd.
# Vi/etc/My. CNF
Add the following content to my. CNF:
[Mysqld]
Default-storage-engine = ndbcluster avoid adding engine = ndbcluster to SQL statements.
Ndbcluster
NDB-connectstring = 192.168.1.111, 192.168.1.110
[Ndbd]
Connect-string = 192.168.1.111, 192.168.1.110
[Ndb_mgm]
Connect-string = 192.168.1.111, 192.168.1.110
[Ndb_mgmd]
Config-file =/var/lib/MySQL-cluster/config. ini
[Mysql_cluster]
NDB-connectstring = 192.168.1.111, 192.168.1.110

After saving and exiting, start the management node server1 as follows:
# Ndb_mgmd -- ndb_nodeid = 1
Start the management node server2 as follows:
# Ndb_mgmd -- ndb_nodeid = 2
4. initialize the Cluster
In server1
# Ndbd -- bind_address = 192.168.1.112 -- nodeid = 3 -- Initial
# Ndbd -- bind_address = 192.168.1.113 -- nodeid = 4 -- Initial
In server2
# Ndbd -- bind_address = 192.168.1.109 -- nodeid = 5 -- Initial
# Ndbd -- bind_address = 192.168.1.108 -- nodeid = 6 -- Initial
Note: The -- initial parameter is required only when ndbd is started for the first time or after config. INI is modified!

5. Check the working status
Start the Management Terminal on any machine:
# Ndb_mgm
Enter the show command to view the current working status: (The following is an example of status output)
-- NDB Cluster -- management client --
Ndb_mgm> show
Connected to management server at: 192.168.1.111: 1186
Cluster configuration
---------------------
[Ndbd (NDB)] 4 node (s)
Id = 3 @ 192.168.1.111 (Version: 5.2.3, nodegroup: 0, Master)
Id = 4 @ 192.168.1.110 (Version: 5.2.3, nodegroup: 0)
Id = 5 @ 192.168.1.109 (Version: 5.2.3, nodegroup: 0)
Id = 6 @ 192.168.1.108 (Version: 5.2.3, nodegroup: 0)
[Ndb_mgmd (MGM)] 2 node (s)
Id = 1 @ 192.168.1.111 (Version: 5.2.3)
Id = 2 @ 192.168.1.110 (Version: 5.2.3)
[Mysqld (API)] 8 node (s)
Id = 7 (not connected, accepting connect from any host)
Id = 8 (not connected, accepting connect from any host)
Id = 9 (not connected, accepting connect from any host)
Id = 10 (not connected, accepting connect from any host)
Id = 11 (not connected, accepting connect from any host)
Id = 12 (not connected, accepting connect from any host)
Id = 13 (not connected, accepting connect from any host)
Id = 14 (not connected, accepting connect from any host)
Ndb_mgm>

If there is no problem above, add mysqld (API) now ):
Note: This document does not set the root password for MySQL. We recommend that you set the root password for MySQL of server1 and server2.
In server1:
# Mysqld_safe -- ndb_nodeid = 7 -- user = MySQL &
In server2:
# Mysqld_safe -- ndb_nodeid = 8 -- user = MySQL &
# Ndb_mgm-e show
The information is as follows:
Connected to management server at: 192.168.1.111: 1186
Cluster configuration
---------------------
[Ndbd (NDB)] 4 node (s)
Id = 3 @ 192.168.1.111 (Version: 5.2.3, nodegroup: 0, Master)
Id = 4 @ 192.168.1.110 (Version: 5.2.3, nodegroup: 0)
Id = 5 @ 192.168.1.109 (Version: 5.2.3, nodegroup: 0)
Id = 6 @ 192.168.1.108 (Version: 5.2.3, nodegroup: 0)
[Ndb_mgmd (MGM)] 2 node (s)
Id = 1 @ 192.168.1.111 (Version: 5.2.3)
Id = 2 @ 192.168.1.110 (Version: 5.2.3)
[Mysqld (API)] 4 node (s)
Id = 5 @ 192.168.1.111 (Version: 5.2.3)
Id = 6 @ 192.168.1.110 (Version: 5.2.3)
Id = 7 (not connected, accepting connect from any host)
Id = 8 (not connected, accepting connect from any host)
Id = 9 (not connected, accepting connect from any host)
Id = 10 (not connected, accepting connect from any host)
Id = 11 (not connected, accepting connect from any host)
Id = 12 (not connected, accepting connect from any host)
OK, you can test it:
In server1
#/Usr/local/MySQL/bin/MySQL-u root-P
> Create Database AA;
> Use AA;
> Create Table ctest (I INT );
> Insert into ctest () values (1 );
> Select * From ctest;
We can see 1 row returned information (return value 1 ).
If the above is normal, switch to server2 and observe the effect. If the operation succeeds, execute insert in server2 and return to server1 to check whether the operation is normal.
If no problem exists, congratulations! Now the template is created.
Note: During the template creation process, the first read/write operation is completed for the cluster. If you skip the test (skip read/write), perform the following operations before the cluster does not work collaboratively, the following operations are meaningless. After the cluster is started, only a few empty directories are created under/var/lib/MySQL-cluster.

6. Back up the corresponding NDB _ * _ fS under/var/lib/MySQL-cluster/for future use.
VII. Principles:
Ndbd -- Initial: initial cluster structure. In the official documents, after adding a node to require bakcup, restore mainly needs to initialize storage nodes. Both Management Nodes and data nodes can be implemented by cluster restart.
The storage node is only valid, so the entire cluster can work normally. Therefore, after the template is complete, unused storage nodes can be used as faulty nodes. When enabled, the storage node can synchronize data through its internal storage node.
Disadvantages: When a cluster is started, each unused storage node waits for 60 seconds by default, resulting in a longer start time for the cluster. You can set the parameter to a small value, which is not recommended.
8. Implementation:
Needless to say, the management node is enough, mainly for the storage node.
Follow the steps above to build a server and copy the corresponding NDB _ * _ fS to/var/lib/MySQL-cluster/. The machine IP address conforms to config. the definition in ini. Okay, you can enable it. It's easy.
For existing designs, you can refer to this document when adding and modifying nodes according to official documents to reserve idle nodes to facilitate future work.MySQL cluster (Cluster) Series(3.lvs+ keeplived + MySQL cluster)I. Introduction
This document aims to introduce how to use LVS + keepalived to achieve high availability and load balancing of MySQL cluster. This article adds LVS + keepalived based on 1-Article (dual-host high availability), which is suitable for clusters with more nodes with slight modifications.
Installation environment and software package:
VMware Workstation 5.5.3
Mysql-5.2.3-falcon-alpha.tar.gz
Gentos 2006.1
Ipvsadm-1.24.tar.gz
Keepalived-1.1.13.tar.gz
Linux-2.6.20.3.tar.bz2
Iproute2-2.6.15-060110.tar.gz
Server1: 192.168.1.111 (ndb_mgmd, id = 1)
Server2: 192.168.1.110 (ndb_mgmd, id = 2)

II ~ Step 6, see article 1 (dual-host high availability)

Perform the following steps on Server 1 and Server 2
Install linux-2.6.20.3.tar.bz2 in seven Cores
# Tar xvjf linux-2.6.20.3.tar.bz2-C/usr/src
# Linux-2.6.20.3/CD/usr/src/
# Zcat/proc/config.gz. config
# Make menuconfig
After selecting network packet filtering framework (netfilter) --->
[] TCP: appears under MD5 Signature Option support (rfc2385) (experimental)
IP: Virtual Server Configuration --->
The configurations in netfilter and virtual server are selected based on your needs.
Select IP: Advanced Router
Choose IP: fib lookup algorithm (Choose maid if unsure) (FIG) --->
· IP: Policy Routing
# Make all & make modules_install & make install
# Vi/boot/grub. conf
Title = 2.6.20.3
Kernel/vmlinuz-2.6.20.3 root =/your root device
# Reboot (start the system with a new kernel)
8. Install ipvsadm and keepalived
# Tar-zxvf ipvsadm-1.24.tar.gz-C/tmp/package
# Cd/tmp/package/ipvsadm-1.24
# Make & make install
# Tar-zxvf keepalived-1.1.13.tar.gz-C/tmp/package
# Cd/tmp/package/keepalived-1.1.13
# Vi keepalived/vrrp/vrrp_arp.c
Set 26 # include <Linux/if_packet.h>
27
28/* local primary des */
29 # include "vrrp_arp.h"
30 # include "memory. H"
31 # include "utils. H"
Change
26/* local primary des */
27 # include "vrrp_arp.h"
28 # include "memory. H"
29 # include "utils. H"
30 # include <Linux/if_packet.h>
31
The # include <Linux/if_packet.h> line is moved below.
#./Configure -- prefix =/usr -- With-kernel-Dir =/usr/src/linux-2.6.20.3
# Make & make install
# Vi/etc/init. d/keepalived Add the following content
#! /Sbin/runscript
# Copyright 1999-2004 Gentoo Foundation
# Distributed under the terms of the GNU General Public License v2
# $ Header:/var/cvsroot/gentoo-x86/sys-cluster/keepalived/files/init-keepalived, V 1.3 00:55:17 export ffis exp $
Depend (){
Use Logger
Need net
}
Checkconfig (){
If [! -E/etc/keepalived. conf]; then
Eerror "you need an/etc/keepalived. conf file to run keepalived"
Return 1
Fi
}
Start (){
Checkconfig | return 1
Ebegin "Starting keepalived"
Start-stop-daemon -- start -- quiet -- pidfile/var/run/keepalived. PID/
-- Startas/usr/sbin/keepalived
Eend $?
}
Stop (){
Ebegin "Stopping keepalived"
Start-stop-daemon -- stop -- quiet -- pidfile/var/run/keepalived. PID
Eend $?
}
This is the keepalived script of Gentoo.
# Chmod 755/etc/init. d/keepalived
# RC-update add keepalived default
# Vi/etc/keepalived. conf
! Configuration file for keepalived
Global_defs {
Router_id mysql_cluster
}
Vrrp_sync_group vg1 {(this is the HA part)
Group {
Vi_1
}
}
Vrrp_instance vi_1 {
State master
Interface eth0
Lvs_sync_daemon_interface eth0
Virtual_router_id 1 (here server1 is 1, server2 is 2)
Priority150
Advert_int 1
Authentication {
Auth_type pass
Auth_pass mysqlcluster
}
Virtual_ipaddress {
192.168.1.120
}
}
Virtual_server 192.168.1.120 3306 {(the Server Load balancer section is defined here, using the Dr method)
Delay_loop 6
Lvs_sched wlc
Lvs_method Dr
Persistence_timeout 60
Ha_suspend
Protocol TCP
Real_server 192.168.1.110 3306 {
Weight 1
Tcp_check {
Connect_timeout 10
}
}
Real_server 192.168.1.111 3306 {
Weight 1
Tcp_check {
Connect_timeout 10
}
}
}
9. Start
#/Etc/init. d/keepalived start
# Ip addr list (if iproute2 is not installed, you can use emerge iproute2 for installation. Note that emerge is the fate of Gentoo)
The following information is displayed:
Eth0: <broadcast, multicast, up, 10000> MTU 1500 qdisc pfifo_fast qlen 1000
Link/ether 00: 0C: 29: 6f: F9: 21 brd ff: FF
Inet 192.168.1.111/24 BRD 192.168.1.255 scope global eth0
Inet 192.168.1.120/32 scope global eth0 (this line indicates that the virtual IP Address has taken effect)
Inet6 fe80: 20c: 29ff: fe6f: f921/64 scope Link
Valid_lft forever preferred_lft forever
# Tail/var/log/messages to view more information.
Similar
Keepalived: Starting keepalived v1.1.13 (03/26, 2007)
Keepalived_healthcheckers: Using linkwatch kernel Netlink reflector...
Keepalived_healthcheckers: Registering kernel Netlink Reflector
Keepalived_healthcheckers: Registering kernel Netlink Command Channel
Keepalived_healthcheckers: configuration is using: 9997 bytes
Keepalived: Starting healthcheck child process, pid = 27738
Keepalived_vrrp: Using linkwatch kernel Netlink reflector...
Keepalived_vrrp: Registering kernel Netlink Reflector
Keepalived_vrrp: Registering kernel Netlink Command Channel
Keepalived_vrrp: Registering gratutious ARP shared channel
Keepalived_vrrp: configuration is using: 36549 bytes
Keepalived: Starting vrrp child process, pid = 27740
Keepalived_healthcheckers: Activating healtchecker for service [192.168.1.110: 3306]
Keepalived_healthcheckers: Activating healtchecker for service [192.168.1.111: 3306]
S: Sync thread started: State = Master, mcast_fn = eth0, syncid = 2
Keepalived_vrrp: vrrp_instance (vi_1) Transition to master state
Keepalived_vrrp: vrrp_instance (vi_1) entering master state
Keepalived_vrrp: vrrp_group (vg1) syncing instances to master state
Keepalived_vrrp: Netlink: Skipping nl_cmd MSG...
10. Conclusion
The purpose of the three documents is to consider how to better use MySQL and Linux and related tools from the perspective of MySQL cluster applications. If any errors are found in this document, please leave it blank.