How To Set Up A Load-Balanced MySQL Cluster - Part 7

6.4 Create A Database Called ldirector

Next we create the ldirector database on our MySQL(和PHP搭配之最佳组合) cluster nodes sql1.example.com and sql2.example.com. This database will be used by our load balancers to check the availability of the MySQL(和PHP搭配之最佳组合) cluster nodes.

sql1.example.com:

MySQL(和PHP搭配之最佳组合) -u root -p
GRANT ALL ON ldirectordb.* TO 'ldirector'@'%' IDENTIFIED BY 'ldirectorpassword';
FLUSH PRIVILEGES;
CREATE DATABASE ldirectordb;
USE ldirectordb;
CREATE TABLE connectioncheck (i INT) ENGINE=NDBCLUSTER;
INSERT INTO connectioncheck () VALUES (1);
quit;

sql2.example.com:

MySQL(和PHP搭配之最佳组合) -u root -p
GRANT ALL ON ldirectordb.* TO 'ldirector'@'%' IDENTIFIED BY 'ldirectorpassword';
FLUSH PRIVILEGES;
CREATE DATABASE ldirectordb;
quit;

6.5 Prepare The MySQL(和PHP搭配之最佳组合) Cluster Nodes For Load Balancing

Finally we must configure our MySQL(和PHP搭配之最佳组合) cluster nodes sql1.example.com and sql2.example.com to accept requests on the virtual IP address 192.168.0.105.

sql1.example.com / sql2.example.com:

apt-get install iproute

Add the following to /etc/sysctl.conf:

sql1.example.com / sql2.example.com:

vi /etc/sysctl.conf

# Enable configuration of arp_ignore option
net.ipv4.conf.all.arp_ignore = 1

# When an arp request is received on eth0, only respond if that address is
# configured on eth0. In particular, do not respond if the address is
# configured on lo
net.ipv4.conf.eth0.arp_ignore = 1

# Ditto for eth1, add for all ARPing interfaces
#net.ipv4.conf.eth1.arp_ignore = 1


# Enable configuration of arp_announce option
net.ipv4.conf.all.arp_announce = 2

# When making an ARP request sent through eth0 Always use an address that
# is configured on eth0 as the source address of the ARP request.  If this
# is not set, and packets are being sent out eth0 for an address that is on
# lo, and an arp request is required, then the address on lo will be used.
# As the source IP address of arp requests is entered into the ARP cache on
# the destination, it has the effect of announcing this address.  This is
# not desirable in this case as adresses on lo on the real-servers should
# be announced only by the linux-director.
net.ipv4.conf.eth0.arp_announce = 2

# Ditto for eth1, add for all ARPing interfaces
#net.ipv4.conf.eth1.arp_announce = 2

sysctl -p

Add this section for the virtual IP address to /etc/network/interfaces:

sql1.example.com / sql2.example.com:

vi /etc/network/interfaces

auto lo:0
iface lo:0 inet static
  address 192.168.0.105
  netmask 255.255.255.255
  pre-up sysctl -p > /dev/null

ifup lo:0