MySQL Master-Master Replication, Heartbeat, DRBD, Apache, PHP, Varnish MegaHOWTO

I created this HOWTO while building a new development environment today. The intention is to take a single Apache2/Varnish/MySQL environment and scale it to two servers, with one effectively a “hot-standby” – increase redundancy and continuity whilst maintaining current performance. This HOWTO is based on Linux Debian-76-wheezy-64-minimal 3.2.0-4-amd64 #1 SMP Debian 3.2.60-1+deb7u3 x86_64

Our current server has IP 192.168.201.1/24 and our new server has IP 192.168.201.7.

Section #1: Set up MySQL Master/Master Replication


First, we’ll set up MySQL master to master replication. In this configuration, data can be written and read from either host. Bear in mind that issues may exist with autoincrement fields when written to at the same time. There are other caveats with replication so ensure to research them along with how to deal with corruption and repair before considering this setup for a live application. Also be sure to be using the same version of MySQL on both servers – this may not always be necessary, however unless you are very familiar with any changes between versions, not doing so could spell disaster.

The current working server (192.168.201.1) was installed using:

apt-get install mysql-server

No additional configuration changes have been made to date aside from the creation of databases, tables, data and user accounts.

First, edit /etc/mysql/my.cnf. Your bind_address will be set by default to 127.0.0.1. You’ll need to change this to bind to the IP that you expect MySQL to communicate over. 0.0.0.0 can also be used to specify all IPs on all interfaces. Please note the security implications of the IP that you open MySQL up on. Additional firewalling measures may need to be considered. Scroll down until reaching the server-id line, and modify/uncomment the lines that follow:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema
binlog_ignore_db        = performance_schema

In my case, I’ve chosen to ignore the 3 default databases, as I don’t want them replicated (user accounts mainly)

Now restart mysql:

/etc/init.d/mysql restart

Now, using log into the console:

mysql -u root -p

And lock the database tables with:

mysql> FLUSH TABLES WITH READ LOCK;
 Query OK, 0 rows affected (0.08 sec)

Now review and take note of the server’s master status:

 

mysql> SHOW MASTER STATUS\G
 *************************** 1. row ***************************
 File: mysql-bin.000002
 Position: 84637
 Binlog_Do_DB:
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
 1 row in set (0.00 sec)

Take note of the file ‘mysql-bin.000002’ and position ‘84637’

Keep this session open, or the table lock will be dropped. In a new window, dump the database server’s data for all tables aside from any that were specified to be ignored in the configuration above:

mysqldump -u root -p --add-drop-database --databases mydatabase test myapplication test2 > dump.sql
gzip dump.sql

Once complete, return to the MySQL console window, unlock the tables, and create a replication account for the new server to use.

UNLOCK TABLES;
GRANT REPLICATION SLAVE ON *.* to 'replication'@'192.168.201.7' IDENTIFIED BY 'YOUR_PASSWORD';
FLUSH PRIVILEGES;
\q

Now, transfer the database dump to the new server:

scp dump.sql.gz root@192.168.201.7:~/

Now, switch to the new server – in our case, 192.168.201.7:

MySQL was again installed with default configuration, by:

apt-get install mysql-server

Import the data into this MySQL instance:

cd /root
gunzip dump.sql.gz
mysql -u root -p < ~/dump.sql

Now edit /etc/mysql/my.cnf on the new server:

Use the same settings from the first server, above, but change server-id from 1 to 2:

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema
binlog_ignore_db        = performance_schema
/code]

Now restart mysql:


/etc/init.d/mysql restart

Once MySQL has restarted, log on to the database console with:

mysql -u root -p

Now, we'll create a replication user account for the initial server to connect to this server with, and at the same time, we'll make the replication connection to the initial server. Note the MASTER_LOG_FILE and MASTER_LOG_POS as noted from the initial server originally:

GRANT REPLICATION SLAVE ON *.* to 'replication'@'192.168.201.1' IDENTIFIED BY 'ANOTHER_PASSWORD';
FLUSH PRIVILEGES;
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.201.1', MASTER_USER='replication', MASTER_PASSWORD='YOUR_PASSWORD', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=84637;
START SLAVE;
SHOW SLAVE STATUS\G;

If everything has gone to plan, many lines of output will be returned, including the following:

(...)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(...)

Excellent! Now on this new server, we need to get the current master file and position, again, taking note of the values returned:

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 420
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
1 row in set (0.00 sec)

Now back to primary server, using the file and position logged from the secondary server above:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.201.7', MASTER_USER='replication', MASTER_PASSWORD='ANOTHER_PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=420;
START SLAVE;
SHOW SLAVE STATUS\G;

Again, the following should be returned within the output to confirm success:

(...)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(...)

Congratulations, your master to master replication is now up and running.

Section #2: Set up DRBD Primary/Slave


DRBD has a range of use cases. In our case, we are going to replicate data between two machines at block level. The aim is to have two mirrored web partitions.

Before starting, we'll need a dedicated DRBD partition on disk of identical size on both hosts. If they are not evenly matched, the available space on the DRBD partition will be limited to that of the smaller partition. In my case, I have a dedicated 20GB partition formatted as ext3 and mounted in /var/www on the primary server.

First, we'll tune the kernel parameters for DRBD on both servers by adding the following code to /etc/sysctl.conf:

net.ipv4.tcp_no_metrics_save = 1
net.core.rmem_max = 33554432
net.core.wmem_max = 33554432
net.ipv4.tcp_rmem = 4096 87380 33554432
net.ipv4.tcp_wmem = 4096 87380 33554432
vm.dirty_ratio = 10
vm.dirty_background_ratio = 4

Next, DRBD needs to be installed:

apt-get install drbd8-utils ntp

Now create entries in /etc/hosts containing a hostname and IP for both servers:

192.168.201.7   b1
192.168.201.1   www1

On both machines, create /etc/drbd.d/r0.res, editing IPs, hostnames and partition names as required:

resource r0 {
  on b1 {
    device    /dev/drbd0;
    disk      /dev/xvda4;
    address   192.168.201.7:7789;
    meta-disk internal;
  }
  on www1 {
    device    /dev/drbd0;
    disk      /dev/xvda3;
    address   192.168.201.1:7789;
    meta-disk internal;
  }
}

Now, the original /dev/xvda3 and /dev/xvda4 must be unmounted if they've been mounted before moving further. On each host, run:

modprobe drbd
echo drbd >> /etc/modules
drbdadm create-md r0
drbdadm up r0

Now, the primary server must be selected. If both machines have empty or identical partitions, it makes no difference which is selected, although only one must be selected. In my case, www1 contains valuable data, and b1 is new and empty. On www1, I execute:

drbdadm -- --overwrite-data-of-peer primary r0

WARNING! Running this command on the wrong server will wipe the peer's contents and synchronize the wrong way!

Now, we can view the process of the data synchronization:

root@b1:/etc/drbd.d# cat /proc/drbd
version: 8.3.11 (api:88/proto:86-96)
srcversion: F937DCB2E5D83C6CCE4A6C9
0: cs:SyncTarget ro:Secondary/Primary ds:Inconsistent/UpToDate C r-----
ns:0 nr:219492 dw:219492 dr:0 al:0 bm:13 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:20752384
[>....................] sync'ed:  1.1% (20264/20480)Mfinish: 4:23:47 speed: 1,300 (1,280) want: 250 K/sec

root@www1:/var# cat /proc/drbd
version: 8.3.11 (api:88/proto:86-96)
srcversion: F937DCB2E5D83C6CCE4A6C9
0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----
ns:238692 nr:0 dw:100 dr:243861 al:7 bm:14 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:20733184
[>....................] sync'ed:  1.2% (20244/20480)Mfinish: 4:23:33 speed: 1,292 (1,280) K/sec

Your DRBD link is now up and running, and the secondary is mirroring data from the primary.

Now, edit /etc/fstab on the primary server and change the existing partition (/dev/xvda3 in my case) to /dev/drbd0. The new DRBD device can now be mounted with:

mount /dev/drbd0

And www1 now has it's /var/www partition back and operational - even while the synchronization is in progress.

Note that this partition can not be mounted on both the primary and secondary simultaneously. Only the primary server can mount the resource. Now, while DRBD does support primary-primary mode, DO NOT consider using that in this case. Our partition is ext3, which is not a distributed filesystem. Mounting and writing to the same ext3 filesystem on two machines simultaneously is going to corrupt it badly.

If you only want to use DRBD as a secondary backup which will need to be mounted and activated manually, you need not implement heartbeat below.

Section #3: Set up Apache2, PHP and Varnish


This is probably the easiest part. The packages can be installed with:

apt-get install apache2 libapache2-mod-php5 php5-mysql varnish libapache2-mod-rpaf

Apache2, PHP and Varnish can be customized and optimized extensively, however this HOWTO lays out the basic functioning configuration.

Eaccelerator can optionally be installed at this stage for PHP caching.

We'll need to change Apache's listening port from 80 to 8000 as Varnish will handle requests on port 80.

Edit /etc/apache2/ports.conf changing the first two lines to:

NameVirtualHost *:8000
Listen 8000

Change any other instances of '80' to '8000' that you find in files under /etc/apache2/sites-enabled/. Custom vhosts can be created now in this directory, using the template:

<VirtualHost *:8000>
ServerName www.domain.com
ServerAlias domain.com
ServerAdmin info@domain.com

DocumentRoot /var/www/domain.com
<Directory /var/www/domain.com>
Options FollowSymLinks
AllowOverride None
Order allow,deny
Allow from all
</Directory>
CustomLog /var/log/apache2/domain.com-custom_log combined
ErrorLog /var/log/apache2/domain.com-error_log
</VirtualHost>

Finally, the new configuration should be tested and then Apache restarted with:

apache2ctl configtest
apache2ctl restart

We now need to configure Varnish. Edit /etc/default/varnish. All lines should be commented/removed, and the active configuration should read:

START=yes
NFILES=131072
MEMLOCK=82000
DAEMON_OPTS="-a :80 \
-T localhost:6082 \
-f /etc/varnish/mysite.vcl \
-S /etc/varnish/secret \
-s malloc,256m"

The memory size passed to -s can be changed as required. Next, create /etc/varnish/mysite.vcl:

backend apache {
.host = "127.0.0.1";
.port = "8000";
.connect_timeout = 10s;
.first_byte_timeout = 15s;
.between_bytes_timeout = 15s;
}

sub vcl_fetch {
remove req.http.X-Forwarded-For;
set req.http.X-Forwarded-For = req.http.rlnclientipaddr;
if (req.url ~ "^/w00tw00t") {
error 403 "Not permitted";
}
return(deliver);
}

sub vcl_deliver {
remove resp.http.X-Varnish;
remove resp.http.Via;
remove resp.http.Age;
remove resp.http.X-Powered-By;
}

Again, this is the most basic caching configuration. Depending on your application, you may have extension, session/cookie or cache age issues to deal with and configure.

Lastly restart Varnish:

/etc/init.d/varnish restart

Apache and Varnish should both be online and you should be able to browse to your web server IP.

Section #4: Set up Heartbeat & Shared IPs


At this point, 'b1' 192.168.201.7 acts as a cold backup server. MySQL is replicated to it, as is our /var/www/ web partition. The partition however is useless to us in secondary mode as we can't mount it. To transform this to a hot standby, we need this server to automatically detect when the master is down, and take over. As the IP differs, and our application code will be looking for a fixed, we'll need to use a shared IP that also gets switched to this server should the master fail. It is this shared IP that the application code will connect to.

Let's use IP 192.168.201.10 as our shared IP. Install heartbeat on both servers:

apt-get install heartbeat

Now, on the primary server (192.168.201.1), edit /etc/ha.d/ha.cf:

keepalive 2
deadtime 20
warntime 10
initdead 120
udpport 694
auto_failback off
ucast eth0 192.168.201.7
node www1 b1

On the secondary server (192.168.201.7):

keepalive 2
deadtime 20
warntime 10
initdead 120
udpport 694
auto_failback off
ucast eth0 192.168.201.1
node www1 b1

Now, we'll need to enter the resources to share. On both nodes, edit /etc/ha.d/haresources. Leave 'www1' in the text below intact on both nodes as it refers to the primary node:

www1 drbddisk::r0 \
        Filesystem::/dev/drbd0::/var/www::ext3 \
        Delay::3::0 \
        IPaddr::192.168.201.10/24/eth0:0

We share the /dev/drbd0 resource, being mounted at /var/www and we also share the 192.168.201.10 IP address.

Lastly, set a shared key in /etc/ha.d/authkeys on both machines:

auth 2
2 sha1 alksfjsdfjhj3vjhkghfjquxrzplfjgs5kjg3g8lshflsjkfh873

And restart heartbeat on both machines:

/etc/init.d/heartbeat restart

Lastly, on www1 take over all resources with:

/usr/share/heartbeat/hb_takeover

Verify that www1 now holds the new shared IP and that DRBD remains mounted and working.

You should be able to run the above takeover on node2 and after a few moments verify that the DRBD partition is now mounted on the secondary node and that the IP has moved over. To test heartbeat, disconnect the LAN cable or shutdown the heartbeat service on the active node with:

/etc/init.d/heartbeat stop

After a short pause, the secondary node should automatically take over resources.

Please post any corrections, comments or questions!

Tags: , , , , , , , , ,

2 Responses to “MySQL Master-Master Replication, Heartbeat, DRBD, Apache, PHP, Varnish MegaHOWTO”

  1. Marco says:

    Hi,

    Great article. A little tip, you can create a better password using the util pwgen.

    Regards,

Leave a Reply