MySQL real high availability cluster

Based on the above MySQL Cluster To achieve complete MySQL database backup and read/write at the same time. Now we want to achieve high availability, through an IP address can access the MySQL of each node at the same time. Achieve real high availability.

The above two data nodes and SQL nodes are used for LVS + heartbeat to achieve high availability! Install ipvsadm heartbeat on the two servers respectively. I will use the yum source to install it here!

1. Configure two servers/etc/hosts files.

My

127.0.0.1 localhost. localdomain localhost
192.168.0.118 MGM
192.168.0.146 node1
192.168.0.221 node1

2,ClusterSoftware Installation
Install with two servers:
Yum-y install ipvsadm # ipvs Manager
Yum-y install Libnet # Library File
Yum-y install e2fsprogs # Library File
Yum-y install heartbeat # Linux-ha

Modprobe softdog # load the software dog driverProgram

2-copy the configuration file
CP/usr/share/doc/heartbeat-2.1. */ha. Cf/etc/ha. d/
CP/usr/shares/doc/heartbeat-2.1. */authkeys/etc/ha. d/
CP/usr/share/doc/heartbeat-2.1. */haresources/etc/ha. d/

Drp1 = 192.168.0.180
Dip2 = 192.168.0.181
VIP = 192.168.0.20.
Real = 192.168.0.146
Real = 192.168.0.221

Configure ha. Cf authkeys haresources

1. The content of the HA. cf file is as follows:

Logfile/var/log/ha-Log
Logfacility local0
Keepalive 2
Deadtime 30
Warntime 10
Initdead 120
Udpport 694
Ucast eth0 192.168.0.221 // backup machine. The address of the master server is 10.
Auto_failback on
Watchdog/dev/Watchdog
Node node1
Node node2
Ping 192.168.0.118
Respawn hacluster/usr/lib/heartbeat/ipfail
Apiauth ipfail gid = haclient uid = hacluster

2. authkeys content is as follows:

#
# Authentication file. Must be mode 600
#
#
# Must have exactly one auth directive at the front.
# Auth send authentication using this method-ID
#
# Then, list the method and key that go with that method-ID
#
# Available methods: CRC sha1, MD5. CRC doesn't need/want a key.
#
# You normally only have one authentication method-ID listed in this file
#
# Put more than one to make a smooth transition when changing auth
# Methods and/or keys.
#
#
# Sha1 is believed to be the "best", MD5 next best.
#
# CRC adds no security, packet t from packet uption.
# Use only on physically secure networks.
#
Auth 1
1 CRC
#2 sha1 hi!
#3 MD5 hello!

Change permission to 600

Chmod 600 authkeys

3. Modify the haresources file and add content in the last line.

Node1 ipaddr: 192.168.0.20./ 24/eth0: 0 ldirectord

Now, heartbeat configuration is complete.

Configure ldirectord

Checktimeout = 3
Checkinterval = 1
# Fallback = 127.0.0.1: 80
Autoreload = Yes
Logfile = "/var/log/ldirectord. log"
Logfile = "local0"
Emailalert = "liuguan269@163.com"
Emailalertfreq= 3600
Emailalertstatus = all
Quiescent = Yes

# Sample configuration for a MySQL virtual service.
Virtual = 192.168.0.20.: 3306
# Real = master.up.com-> slave.up.com: 3306 Gate 10
Real = 192.168.0.146: 3306 Gate 10
Real = 192.168.0.221: 3306 Gate 10
# Fallback = 127.0.0.1: 3306
Service = MySQL
Scheduler = WRR
# Persistent = 600
# Netmask = 255.255.255.255.255
Protocol = TCP
Checktype = negotiate
Login = "client"
Passwd = "password123"
Database = "cluster"
Request = "select I from test"
Receive = "233" // optional

4. Enable ip_forward forwarding

Modify

Change 0 to 1.

# Controls IP packet forwarding
Net. ipv4.ip _ forward = 1

Run

Sysctl-P

Bytes ----------------------------------------------------------------------------------------

5. Disable ARP scripts and use them on the member machine.

Switchdr

The content is as follows:

#! /Bin/sh

# Description: Close lo0 and arp_ignore

VIP = 192.168.0.20.

./Etc/init. d/functions

Case "$1" in

Stop)

Echo 0>/proc/sys/NET/IPv4/CONF/All/arp_announce # Allow ARP to parse virtual IP addresses

;;

Start)

Echo "Start Real Server"

Ifconfig lo: 0 $ VIP netmask 255.255.255.255 broadcast $ VIP up

/Sbin/route add-host $ VIP Dev lo: 0

Echo "1">/proc/sys/NET/IPv4/CONF/LO/arp_ignore

Echo "2">/proc/sys/NET/IPv4/CONF/LO/arp_announce

Echo "1">/proc/sys/NET/IPv4/CONF/All/arp_ignore

Echo "2">/proc/sys/NET/IPv4/CONF/All/arp_announce

Sysctl-P

;;

*)

Echo "Usage: switchdr {START | stop }"

Exit 1

Esac

6. Copy all files to another backup server.

SCP switchdr 192.168.0.221:/etc/init. d/
SCP-r/etc/ha. d 192.168.0.221:/etc
SCP/etc/sysconfig/ipvsadm 192.168.0.221:/etc/sysconfig/

Note that the HA. cf file of the backup Node

Change ucast eth0 192.168.0.181

Ucast eth0 192.168.0.180

I am connecting to the database on computer 118

[Root @ MGM yum. Repos. d] #/usr/local/MySQL/bin/MySQL-u client-p-h 192.168.0.133
Enter password:
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 5 to server version: 5.1.5-alpha-max

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| CC |
| Cluster |
| Huzi |
| MySQL |
| Ndb_rjfs |
| Test |
+ -------------------- +
7 rows in SET (0.01 Sec)

Mysql>

Start a shell to connect to the database.

[Root @ MGM yum. Repos. d] #/usr/local/MySQL/bin/MySQL-u client-p-h 192.168.0.133
Enter password:
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 5 to server version: 5.1.5-alpha-max

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Cluster |
| Huzi |
| MySQL |
| Test |
+ -------------------- +
5 rows in SET (0.00 Sec)

Mysql>

View connection status

[root @ huzi ha. d] # ipvsadm-l-N
IP Virtual Server version 1.2.1 (size = 4096)
prot localaddress: Port sched1_flags
-> remoteaddress: port forward weight activeconn inactconn
TCP 192.168.0.133: 3306 WRR
-> 192.168.0.221: 3306 Route 10 0 0
-> 192.168.0.146: 3306 route 0 0 0

IP Virtual Server version 1.2.1 (size = 4096)
Prot localaddress: Port sched1_flags
-> Remoteaddress: port forward weight activeconn inactconn
TCP 192.168.0.133: 3306 WRR
-& Gt; 192.168.0.221: 3306 Route 10 0 1
-& Gt; 192.168.0.146: 3306 Route 10 0 1
[Root @ huzi ha. d] #

[Root @ stu253 etc] #/usr/local/MySQL/bin/MySQL-u client-p-h 192.168.0.133
Enter password:
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 620 to server version: 5.1.5-alpha-max

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> show
-> Databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Cluster |
| Huzi |
| MySQL |
| Test |
+ -------------------- +
5 rows in SET (0.01 Sec)

Mysql> exit
Bye
[Root @ stu253 etc] #/usr/local/MySQL/bin/MySQL-u client-p-h 192.168.0.133
Enter password:
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 2 to server version: 5.1.5-alpha-max

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Cluster |
| Huzi |
| MySQL |
| Ndb_rjfs |
+ -------------------- +
5 rows in SET (0.04 Sec)

Mysql> exit
Bye
[Root @ stu253 etc] #

[Root @ node1 ha. d] # ipvsadm-l-N-C
[Root @ huzi ha. d] # ipvsadm-l-N-C
Ipvs connection entries
Pro expire State Source virtual destination
TCP established 192.168.0.253: 8232 192.168.0.133: 3306 192.168.0.221: 3306
TCP established 192.168.0.253: 8234 192.168.0.133: 3306 192.168.0.221: 3306

Successfully executed: ha + MySqlClusterApplication successful

Use NDB management nodes to view the connection status of SQL nodes

[Root @ MGM yum. Repos. d] #/usr/local/MySQL/bin/ndb_mgm
-- NDB Cluster -- management client --
Ndb_mgm> show
Connected to management server at: MGM: 1186
Cluster configuration
---------------------
[Ndbd (NDB)] 2 node (s)
Id = 1 @ 192.168.0.146 (Version: 5.1.5, nodegroup: 0, Master)
Id = 2 (not connected, accepting connect from node2)

[Ndb_mgmd (MGM)] 1 node (s)
Id = 3 @ 192.168.0.118 (Version: 5.1.5)

[Mysqld (API)] 3 node (s)
Id = 4 @ 192.168.0.221 (Version: 5.1.5)
Id = 5 @ 192.168.0.146 (Version: 5.1.5)
Id = 6 (not connected, accepting connect from any host)

Ndb_mgm>

After two days of practice, there is no problem! I used virtual machines to complete all of them. There was a problem with NDB! The NDB is reinitialized. no previous data (tables stored in NDB ). makes me not dare to use NDB .. implement the MySQL master-master synchronization in another day, so that the above is also made highly available!