Securing MySQL Replication Traffic

If you ever find the need to replicate MySQL traffic up to a service such as Amazon AWS or want to replicate from site-to-site without establishing a VPN tunnel, you can secure the traffic using SSL.  I have found this to be especially helpful in the case of configuring AWS instances for replication.

The current MySQL server RPM packages available from Percona and others have SSL support built in, you simply need to configure it.

Create Certificates

You’ll need to have OpenSSL installed to generate these certificates.

Make sure you utilize a unique common name (CN) on each of the generated certificates, otherwise they will not work!

UPDATE! : Make sure the MySQL User has permissions to walk the SSL Certificate folder!!!  This would be a chmod +x on the folder.  Granting the execute bit on a folder allows the user or group to ‘walk’ the folder, IE, see it’s contents! chown/chmod 750 the folder, chmod 640 the certs/keys, and chown root:mysql the keys as well.


#Make directory
mkdir -p /etc/mysql/newcerts && cd /etc/mysql/newcerts

#Generate CA Certs
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

#Generate Server Certs
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

#Generate Client Certs
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

#Verify the Certificates
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

Place Certificates on MySQL Servers

Place the certificates on each MySQL Server participating in replication. Make sure you create the folder /etc/mysql/certs on each server first.

 #On each server
mkdir -p /etc/mysql/certs
#Copy certs to all servers using rsync or scp (See Example Below)
scp /etc/mysql/newcerts/ca-cert.pem root@
scp /etc/mysql/newcerts/server-cert.pem root@
scp /etc/mysql/newcerts/server-key.pem root@
scp /etc/mysql/newcerts/client-cert.pem root@
scp /etc/mysql/newcerts/client-key.pem root@

Edit Configuration Files (my.cnf)

On the master server, make the following edits to my.cnf.  You should be familiar with configuring MySQL replication before attempting to add SSL to the configuration.  Your server-id and other variables will likely vary depending on your environment and how you have your existing replication set up.

#Configure Master
mkdir -p /data/mysql/binlog/ && chown mysql:mysql /data/mysql/binlog/

#Edit my.cnf


On the slave server, your configuration should look something like this.

#Configure Slave
mkdir -p /data/mysql/relaylog/ && chown mysql:mysql /data/mysql/relaylog/

#Edit my.cnf
relay_log           = /data/mysql/relaylog/mysql-relay-bin
relay_log_index     = /data/mysql/relaylog/mysql-relay-bin.index
relay_log_space_limit = 1000M

Finalize Configuration and Activate Replication

Now comes the fun part, actually making the replication happen.

Start the master server, create the slave user, and execute a mysqldump if you haven’t already.

#Create Slave User on Master server
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;

#do a MySQL Backup
# 1. Flush Tables with Read Lock;
# 2. In a new SSH session, Execute Backup with mysqldump, xtrabackup, or ndb_mgm if using MySQL Cluster
# 3. show master status \G
# 4. Unlock tables;

Start the slave, restore the backup and configure the Master settings.

#First, restore the backup of the master.  Then, execute a CHANGE MASTER command, paying special attention to the SSL settings.

CHANGE MASTER TO MASTER_HOST='', MASTER_PORT=3306, MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=123, MASTER_SSL=1, MASTER_SSL_CERT='/etc/mysql/certs/client-cert.pem', MASTER_SSL_KEY='/etc/mysql/certs/client-key.pem';

#Activate the slave

#Check Slave Status

At this point, you have configured MySQL replication with SSL. You should further protect your replication traffic with firewall settings limiting connections only from authorized source IP’s.

Setting Auto Increments

On another note, if you are looking to configure MySQL multi-master replication, whether it be MySQL or MySQL Cluster, you need to know how to make use of autoincrement values to prevent row collision.

These values are specified in my.cnf as auto_increment_increment and auto_increment_offset.  The increment value should be set to the number of instances you intend to run, for example, auto_increment_increment=2 for a co-master situation.  On the first server, you would set auto_increment_offset=1 and the second server auto_increment_offset=2.

In a MySQL Cluster environment with multiple master clusters, you would follow the same suit.  Set the increment to the number of master clusters.  In a circular replication, three master cluster deployment, you would set auto_increment_increment=3 on all mysqld nodes in all three clusters.  In cluster one, you would set all mysqld nodes with auto_increment_offset=1.  Cluster two would utilize auto_increment_offset=2, and cluster three would  utilize auto_increment_offset=3.

The auto_increment_increment and auto_increment_offset values can be changed on the fly using  SET GLOBAL.  Make sure you set these values correctly and you utilize the auto_increment function on non-unique tables!


Online Upgrade from ESXi 5.0 to 5.1 Using Host Profiles and CLI

On a standalone ESXi box or in a small environment not running vSphere Update Manager, the best way to update your hosts is via SSH CLI.  I recently discovered a way to use this patching method to perform a version upgrade for 5.0 to 5.1 using host profiles rather than using the interactive installer, aka the install CD.  VMware hosts their standard host profiles on the web, so these can be utilized to perform the 5.0 to 5.1 upgrade with minimal downtime; all that you’ll have to do is apply the profile and reboot, just like a normal patch. Remember to migrate any important VM’s off the host in question prior to running these operations.

1. Enable HTTP Client Firewall Exception

esxcli network firewall ruleset set -e true -r httpClient 

2. List Profiles

esxcli software sources profile list -d 

Continue reading

How to Empty the Active Directory Recycling Bin

One of the coolest new features in Server 2008 R2 and 2012 is the ability to recover deleted Active Directory objects. Your forest functional level must be at least 2008 R2 in order to activate this feature. Server 2008 R2 introduced the AD Administrative Center which provides a nice GUI to restore deleted objects after activated.  However, eventually one might want to permanently empty the contents of this recycling bin. There is no way to do this via the GUI. The following PowerShell line will do just that.

Get-ADObject -Filter 'isDeleted -eq $true -and Name -like "*DEL:*"' -IncludeDeletedObjects | Remove-ADObject -Confirm:$false

If you want to see what it is going to remove, only use the Get-ADObject section of the command prior to the pipe. Remove -Confirm:$false if you want to see and confirm each deletion individually. Only run this command in the form seen here if you are absolutely sure you want to empty the trash!

Apple Profile Manager – Mountain Lion Migration

Recently I had the pleasure of migrating an OSX Lion server to Mountain Lion.  It’s primary function was an MDM server for Apple devices.  Basically, the upgrade process involves upgrading to Mountain Lion followed by installing the updated Lion Server app.

Primarily, my interactions with the Apple Server have been for Profile Manager functionality.  In Lion, the Profile Manager utilized a PostgreSQL backend with a datastore located in /usr/share/devicemgr/backend/.  iOS applications and other push to device material were located in the ‘/backend/file_store/’ directory named as their MD5 checksum equivalent.  Logs for the devicemgr service were located in the ‘/backend/logs/’ directory.

In Mountain Lion Server, what used to be located in /usr/share/ is now packed into the Server Application itself.  For example, the same ‘/devicemgr/backend/’ is now located at ‘/Applications/’.  The iOS applications and other push material are now located at ‘/var/devicemgr/ServiceData/Data/FileStore/’.

This knowledge is critical if you encounter an issue with the Profile Manager; there is not much info to go on if you have a problem.  In Lion, I had seen cases where the Profile Manager would for an unknown reason delete applications I was trying to push, causing managed devices to be unable to receive the application.

In the case of Mountain Lion Server, I encountered the following issue with devices post-upgrade when trying to upload an updated version of an application.

ProfileManager[217] <Error>: Caught unhandled exception undefined method `get_all_devices’ for nil:NilClass at …’

To me, this sounded like a nonexistent remnant of Lion was being referenced to.  To some people, this might sound like a good time to reset the Profile Manager with the script.  However, this would require you to rejoin all devices to the MDM.  In this case, there was only a single application which the MDM was being used to deploy, so I figured I would try clearing the Postgres tables containing the application information and see what happened.  After running the following commands, I was able to upload my application and push without the ‘undefined method’ error as show above.

sudo psql –U _postgres –d device_management
DELETE from public.ios_applications; SELECT setval('ios_applications_id_seq', 1); DELETE from ios_application_library_item_relations; SELECT setval('ios_application_library_item_relations_id_seq', 1);
serveradmin stop devicemgr
serveradmin start devicemgr

Branch Office Connections – GRE over IPSec VPN

While studying for the CCNP Route exam, I noticed that GRE Tunneling and IPSec were mentioned as topics, however configuration of the two was never really covered in the certification guide.  In addition, I did not see any labs involving this type of scenario, so I decided to create my own.  Hopefully this provides some insight to others about how to configure GRE Tunnels over IPsec.  Don’t feel bad if this takes you a while, setting up this scenario and writing this up took a whole Saturday afternoon.  The configuration files are at the bottom of the post.

IPSec can secure data in transit and provides authentication that it came from a trusted source.

To route from the branch office to the enterprise core, you can use either static routes or a routing protocol. To secure traffic over the internet, IPSec can be used.  To utilize a routing protocol to connect securely to the core with IPsec, it must be combined with a GRE tunnel which supports the broadcasts/multicasts necessary for proper IGP operation.

GRE Tunnels

  • Act link a point-to-point link from a Layer 3 perspective.
  • Supports many passenger protocols, including IPv4.
  • Encapsulates/forwards broadcasts and multicasts, therefore supporting IPv4 IGP’s.
  • GRE tunnels can run through IPsec tunnels.

Implementing Branch Office GRE over IPSec VPN

Branch Office GRE/IPSec VPN (R2-R4) & Private Serial (R3-R4)

Here, we will utilize split-tunnel at the Branch Office to ensure that only traffic destined for the Core will be encrypted over the GRE IPSec tunnel. A default route is configured on R2 and R4 to ISP1. Additionally, ISP1 has been configured with a loopback to test split-tunnel functionality.

All routers (except ISP1 obviously) will participate in EIGRP AS 1. Each participating router shall be configured with the network and no auto-summary commands.

Configure the GRE Tunnels on R2 and R4

The tunnel source is the internet-facing interface, in this case fa0/0 with the IP of on R2 and fa0/1 with the IP of on R4.
When associating a tunnel source with an interface, if the associated interface goes down, so does the tunnel.  If you have redundant connections, use a loopback as a source instead.

R2 – R4 GRE Tunnel Configuration

First, configure the GRE tunnel. After doing this, you should see your EIGRP neighbors go up.

In a real world situation, you’d probably also want to configure the tunnel with Path MTU Detection.  This will mitigate fragmentation issues that could cause your routers to drop CEF and use process switching.  Enable PMTUD at each tunnel endpoint with tunnel path-mtu-discovery.



R2 GRE Route-Map

Here, we will configure a route-map to prevent a default route from ever being advertised over the GRE tunnel. The only way for R4 to ever learn R2’s default route should be via the serial connection to R3, and it should only be utilized if R4’s fa0/0 interface goes down. We could also utilize this to block other networks from being advertised over the GRE tunnel by modifying the ACL.

Now, you can see that R4 only learns a backup Default-Route through R3. Previously, it learned it through the tunnel as well, which would be silly to utilize and might break split-tunnel if the static default-route was lost for some reason.

Encrypt GRE Tunnel traffic using IPSec

We will now encrypt traffic traversing the GRE tunnel using IPsec. We must first define an ACL to catch the traffic we wish to encrypt, which will be GRE traffic from our GRE source-interface destined for the GRE tunnel destination.

R2 IPsec Policy Creation

ACL (Catch GRE to R4)


ISAKMP Key & IPSec Transform Set (Transport Mode)

Keep in mind that transport mode is used only because we are not traversing NAT, we are going from R2 to R4’s public IP for both IPSec and GRE Tunnels.   Tunnel mode is required if the GRE destination is different from the IPSec destination.  If the endpoints were each behind a NAT router, the situation requires a combination of NAT-T and Tunnel Mode.  Tunnel mode adds an extra 20 bytes to the overall packet size. NAT-T also adds an extra 20 bytes per packet and encapsulates the IPSec traffic into UDP packets over port 4500.

Crypto Map Configuration

Here, we configure the Crypto Map to R4. We will use the previously created GRE catching ACL.

Apply Crypto Map to GRE Tunnel

The Crypto Map should be applied to the physical exit interface. Cisco recommends applying it to the logical tunnel interface as well.

R4 IPsec Policy Creation

We will follow the same procedure utilized on R2 and reverse the IP addresses.

ACL (Catch GRE to R2)


ISAKMP Key & IPSec Transform Set (Transport Mode)

Crypto Map Configuration

Here, we configure the Crypto Map to R4. We will use the previously created GRE catching ACL.

Apply Crypto Map to GRE Tunnel

Verify IPsec and Split-Tunnel Functionality

You can utilize Wireshark to verify only ESP traffic traverses between the GRE endpoints. I found that the Crypto Map did not need to be applied to the logical Tunnel 0 interface to function, but only the physical exit interface. I am not sure why Cisco recommends applying it to both.

You can see that the Crypto traffic for Tunnel 0 and FastEthernet 0/0 is identical. I am not sure if running the crypto map VPN on both the Tunnel 0 and FastEthernet 0/0 is causing excess encryption overhead, or if it only encrypts the traffic once.

After disabling Crypto Map VPN on the Tunnel 0 interface, I confirmed via Wireshark that the GRE traffic continued to be encrypted. A show crypto ipsec sa detail shows that the traffic continues to be encrypted as well.

Verify Split-Tunnel

To test this, I will simply ping a simulated public internet address of from the inside interface of R4.


We have implemented remote branch office with redundant connectivity to the core; a leased line and a secured backup utilizing GRE over IPsec via the public Internet. Our branch office is now able to participate in the EIGRP routing process over either link.  If you wanted to use the IPsec VPN as your primary connection to HQ, modify the bandwidth value of the Tunnel 0 interfaces to make the EIGRP metric more preferable than the serial route.  By default, tunnel interfaces seem to be given a bandwidth value of only 9kbps.  The default delay value of tunnel interfaces is high as well.  Tweak the bandwidth and delay values to achieve desired results.

Normal Conditions

Downed Serial Link to HQ

Configuration Files

ISP1 Configuration

R1 Configuration

R2 Configuration

R3 Configuration

R4 Configuration

Cisco IP SLA

So while studying for CCNP Route, I came across the concept of IP SLA and utilizing probes to check availability of next-hop addresses prior to instating them inside a route-map.  An objective was to have two ISP’s listed in a route-map statement, and set all traffic originating from the local router with a next-hop IP of ISP1 as long as it was deemed reachable by the IP SLA tracker probe status.


The Configuration (Cisco 2691 – IOS ADVENTERPRISE 12.4(15)T14)

Define IP SLA Policy
PolicyRouter(config)#ip sla 1
PolicyRouter(config-ip-sla)icmp-echo source-interface Serial0/0
PolicyRouter(config-ip-sl.a-echo)#timeout 1000
PolicyRouter(config-ip-sla-echo)#frequency 3

Start Probe
PolicyRouter(config)#ip sla schedule 1 start-time now life forever

Track Probe Status
PolicyRouter(config)#track 1 rtr 1 reachability

PolicyRouter(config)#ip access-list extended ROUTER
PolicyRouter(config-ext-nacl)#permit ip any any

Route Map
PolicyRouter(config)#route-map ROUTER-TRAFFIC 10
PolicyRouter(config-route-map)#match ip address ROUTER
PolicyRouter(config-route-map)#set ip next-hop verify-availability 10 track 1
PolicyRouter(config-route-map)#set ip next-hop

Apply Policy-Based Routing to Locally Generated Traffic
PolicyRouter(config)#ip local policy route-map ROUTER-TRAFFIC


Route Map Status

Traceroute with Both ISP’s Reachable

 Traceroute with ISP1 Down

The result was good.  The next probe was working and traffic was being routed fine.  The fail-over to ISP2 upon failure of Serial0/0 also works fine.

The Problem

When Serial0/0 comes back on-line, the PBR sticks with the route through ISP2.  After looking at the route map again after the failover and the restoration of Serial0/0, I saw that the tracker was still registering the next-hop as down.

I did some testing and noticed that pings to would fail.  This is because of the route-map applied to locally generated traffic.  The locally-generated ICMP-echo probes were being set with the route-map’s active next-hop interface, sending the probe incorrectly to ISP2.  For the purpose of the probe, it is necessary the probe traffic to be permitted to travel to the correct next-hop of ISP1 regardless of where other traffic is destined.


To correct this issue, I created an ACL to catch the probe traffic and inserted a sequence above the current statement in the route-map.  This sequence allows the probe traffic to utilize the correct destination rather than be affected by the fail-over next-hop functionality.

PolicyRouter(config)#ip access-list extended RTR_PING_S0/0
PolicyRouter(config-ext-nacl)#permit icmp host host echo

Route Map
PolicyRouter(config)#route-map ROUTER-TRAFFIC 5
PolicyRouter(config-route-map)#match ip address RTR_PING_S0/0


This fix resulted in next-hop redundancy including preempting when the specified next-hop ISP comes back on-line.  This could be applied to a router with more ISP connections, so long as an ACL and route-map are created with respect to the necessary traffic-flow characteristics.

NOTE: Be very careful applying route-maps to locally-generated traffic.  You must be aware of the implications to router functionality.