Synchronization of two Apache + MySQL servers on FreeBSD

In this review I will talk about the implementation of a cluster consisting of two nodes with the reservation of a popular bundle for the Apache + MySQL + FreeBSD web server (or any Linux).

Briefly about servers. Each server has two network cards. They are connected to one - to the switch, between the second ones there is a short patchcord, in order not to overload our switch with extra traffic.
Accordingly, two network interfaces em0 - for external, rl0 - for replication.
On the first server for the rl0 interface, set IP 192.168.0.1 on the second 192.168.0.2.

1. APACHE
The task is to synchronize the files of virtual hosts.
We will use rsync in conjunction with ssh. To do this, on the first server in the file / etc / ssh / sshd_config we write:

AllowUsers root@192.168.0.2
PermitRootLogin yes

The second is similar:
AllowUsers root@192.168.0.1
PermitRootLogin yes

That is, we allow ssh access to the root user, between servers. For each virtual domain, different users are used, respectively, they have a different UID. Of course, you can also create a separate user for replicating files that can access each user’s files, but it’s simpler to use root, especially since there’s no vulnerability. SSH access to the superuser root has only one internal IP address, which we explicitly indicated in the configuration files.
')
Next, let's allow root access without a password, for this we will generate a pair of keys:
ssh-keygen -t rsa (passphrase )
scp /root/.ssh/id_rsa.pub root@192.168.0.2:/root/.ssh/authorized_keys2

and similarly on the second server:
ssh-keygen -t rsa (passphrase )
scp /root/.ssh/id_rsa.pub root@192.168.0.1:/root/.ssh/authorized_keys2

Next, on the second server, create, for example, in / root / scripts / file, let's call it sync.sh:
/usr/local/bin/rsync -e 'ssh -l root -i /root/.ssh/id_rsa' --progress -lzuogthvr --compress-level=9 --delete-after root@192.168.0.1:/opt/vhosts/sitename.ru/ /opt/vhosts/sitename.ru/ >> /root/logs/sync.sitename

sitename.ru - virtual host name

Enable launch:
chmod +x /root/scripts/sync.sh

Let's write in / etc / crontab:
0 */1 * * * root /root/scripts/sync.sh >/dev/null 2>&1
In my example, synchronization is performed every hour.

2. MySQL
On the first server in the file my.cnf we write the following:
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
slave-compressed = 1
binlog-do-db = base ( , )
replicate-wild-ignore-table=base.chat ( chat – MEMORY TABLE , )

We will perform replication under a separate user, let's call it repluser, allow it access from IP 192.168.0.2 and give global rights SELECT, RELOAD, SUPER, REPLICATION SLAVE.

On the second server in my.cnf:
max-user-connections = 50
master-host = 192.168.0.1
master-user = repluser ( )
master-password = < repluser>
server-id = 2 ( ID !)
replicate-do-db = base ( )

Farther
On the first server we execute:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
See something like this:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000031 | 2073 | base | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Be sure to save the output to a text file! Further:
mysqldump -u root -p base > /root/base.db

mysql> UNLOCK TABLES;

transfer the received dump to the 2nd server.

On the second server:
mysql>CREATE DATABASE base;
mysql> USE base;
mysql> SOURCE /root/base.db ( )

mysql> CHANGE MASTER TO MASTER_LOG_FILE='srv011-bin.000813';
Query OK, 0 rows affected (0.05 sec)
srv011-bin.000813 - what they wrote to the text file

mysql> CHANGE MASTER TO MASTER_LOG_POS=1156293;
Query OK, 0 rows affected (0.05 sec)
1156293 - from the same place

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Replication works! Check the replication status with the command:

mysql> SHOW SLAVE STATUS\G;

3. Heartbeat
Cluster implementation. Since the kernel update method was originally chosen - the binary update, the most convenient CARP option was dropped. CARP is a great tool, unfortunately, not accessible without rebuilding the kernel. So choose a heartbeat - a fairly well-known software, unfortunately ported from linux, which draws a lot of unnecessary dependencies, but it's not so scary.

cd /usr/ports/sysutils/heartbeat
make && make install && make clean portmaster sysutils/heartbeat

Go to the configuration for this on the first server:
/usr/local/etc/ha.d/authkeys:

auth 1
1 crc

/usr/local/etc/ha.d/ha.cf:

crm off
logfile /var/log/heartbeat.log
keepalive 2
deadtime 10
udpport 694
ucast rl0 192.168.0.2
auto_failback on
node srv1.sitename.ru
node srv2.sitename.ru

rl0 - the name of the interface on which we are synchronizing

/usr/local/etc/ha.d/hareresources:
srv1.sitename.ru 212.212.212.212/28/em0
212.212.212.212 - our white IP

Similarly on the second server, except for the IP address in /usr/local/etc/ha.d/ha.cf:
ucast rl0 192.168.0.1

It seems to be all, but in the implementation for FreeBSD an error came out unpleasant, at the start, heartbeat did not want to set the correct subnet mask, and besides, it didn’t know what route was. Fortunately, the solution is quite simple, go to:
/usr/local/etc/ocf/resources.d/heartbeat/IPaddr

Looking for a string
CMD="$IFCONFIG $iface inet $ipaddr netmask 255.255.255.255 alias";;

And change it to:
CMD="$IFCONFIG $iface $ipaddr netmask $netmask broadcast $broadcast; route add default <IP ->";;

Now everything works as it should.

Hearbeat can manage commands:

Make the node enforced primary:
/ usr / local / lib / heartbeat / hb_takeover
Make the node forced to spare:
/ usr / local / lib / heartbeat / hb_standby