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 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 cluster nodes.

sql1.example.com:

mysql -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 -u root -p

GRANT ALL ON ldirectordb.* TO 'ldirector'@'%' IDENTIFIED BY 'ldirectorpassword';

FLUSH PRIVILEGES;

CREATE DATABASE ldirectordb;

quit;

6.5 Prepare The MySQL Cluster Nodes For Load Balancing

Finally we must configure our MySQL 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