MySQL Replication is a feature of the MySQL database that allows you to create a live copy of a database.

Benefits of scaling-out a MySQL database using replication:

* Backup; If one database burns down, all recent data lives on.
* Performance; Reads and Writes can be spread out across multiple machines.
* Robustness; In the event of a problem with one database, you can choose to switch it out.

In this post, I'd like to cover popular MySQL replication setups we use here at SPI, explaining the pros and cons of each one.

Master - Slave

One MySQL master, handling read and writes
One or more MySQL slaves, handling reads only



Pros:

+ Most simple to setup
+ Logically separating reads (slave) from writes (master), translates to improved read throughput. No waiting for locks on reads.

Cons:

+ If Master is down, Slave doesn't automatically take over. There are a few hacks to make this setup work properly, by using DRBD or other home-grown solutions.

Master - Master

Two MySQL masters, each handling both reads and writes.



Pros:

+ Robust. If one database is down, the application can continue functioning normally, with HAProxy (or another MySQL load balancing solution) routing all queries to the other master.
+ Ability to spread-out writes across several machines.
+ Supports scaling outside of a single geographic location.

Cons:

+ More challenging to setup. You have to properly configure /etc/my.cnf, giving each database it's own ID and using different auto-increment offsets.
+ Stateful. If clientA starts a database session with masterA, you have to ensure clientA will continue hitting masterA on future queries. (Simple round robin load balancing doesn't work with a master-master setup)
+ Need to continually monitor replication validity. Can't afford for either one of the masters to fall-back or miss any data.

Circular Replication

Three (or more) MySQL databases, typically all setup as master-master-master, with each node capable of handling reads and writes.



Pros:

+ All benefits of a master-master setup, with improved performance and reliability.

Cons:

+ Initial setup can be tricky if you don't know what you're doing.
+ If replication breaks (i.e. one of the databases stops functioning), the replication chain stops and restarting it requires manual intervention.

Replication on the same machine

Two mysqld instances running on the same machine.

This configuration comes in handy when you need to connect two circular replications together, overcoming MySQL's limitation where each node can only be the slave of one master.

Consider two separate circular replications:

A => B => C => A
D => E => F => D

A is slave of B, B is slave of C and C is slave of A.
D is slave of E, E is slave of F and F is slave of D.

If we need to connect these two circles together, let's say at points A<->D, we will have to setup A as a slave of both B and D. D will become a slave of both A and E.

This is accomplished by running another instance of mysql on machines A and D. The other instance runs on a different port pointing to the same data directory.

Pros:

+ All benefits of circular replication, with better performance, ability to easily support remote geographic locations and slow networks.

Cons:

+ Can be tricky to setup, especially when using InnoDB. Start here.

The SPI Shopping Cart platform, allows you to customize system email messages and look&feel so that it best fits your organization.

Between Order Confirmations, Commission Notifications, Payment Alerts and User-events (Forgot Password, Signup etc), there are well over 100 system emails that can all be customized with your branding and your unique look&feel.

In order to ensure your affiliates, customers and leads, are receiving system messages that look good and deliver your message eloquently, it is often helpful to get blind-copied on all of those emails.

Here's how to set this up:

Step 1: Setup a new gmail.com email address. You're going to need a new @gmail.com email address, because you don't want all those messages coming in to your main gmail account.

Write down the new email address you selected and memorize the password.

Step 2: Login to your SPI account, click on System Messages and then select the 'Settings' option from the left.

Step 3: Enter the new gmail email address you created



That's it. From now on, you will receive a copy of all system messages delivered to your customers, leads and affiliates.

Check your new gmail account from time to time, to monitor your event-driven outgoing communications.

This can help you in continually customizing messages, updating content and enhancing look & feel as needed.

Today we completed upgrading our WYSIWYG (What You See Is What You Get) HTML editor across the entire SPI platform.

Affected services:
* Email Marketing
* Autoresponder
* Surveys
* FAQ
* Help desk
* Projects Manager
* System Branding
* Product Manager



The new editor loads faster, offers a wider featureset and is a lot less buggy than the one we were previously using.

The only feature we miss with this new editor is "Auto link" - having the editor automatically convert google.com to google.com.

We're working on a plugin to make that happen.

Mercurial is great distributed version control system that we all use and love here at SPI.

If you're not familiar with distributed version control systems, the key thing to understand is that, unlike a central repository, Mercurial pulls all revisions to your local machine, so that you have the entire project history from day one.

This allows you to check-in / check-out / revert / branch all locally, without having to interface with a remote server repository until you're ready to push changes to the server.

Working on a Windows machine, with the server running on Linux/FreeBSD, introduces an interesting problem of "Case Folding Collision".

What is Case Folding Collision?

Linux/FreeBSD are case sensitive file systems. This means that hello.gif and HELLO.gif are two different files.

Windows on the other hand, is a case insensitive file system. You can't have both hello.gif and HELLO.gif in the same directory on a Windows machine.

Using Mercurial to pull/push between a Linux/FreeBSD and your local Windows machine, becomes problematic when you have multiple files with the same name different capitalization in the same folder.

Pulling changes from Mercurial aborts with an aggressive error, like


How to fix Mercurial Case Folding Collision

On the Linux/FreeBSD machine, we have to enter Mercurial "debug mode", go back to the bad version that introduced the files with same name different capitalization, delete them in that old revision and then go back to the current revision.

Here's how it's done:

Step 1: Enter Mercurial debug mode


The revision should be the one you are attempting to update to.


Step 2: Remove rogue files

Now we want to remove all the files in error.

You can use 'hg manifest tip' to check for the files in error.


Step 3: Commit changes


Step 4: Commit again (incase we had multiple heads)


Step 5: Go back to tip


Note: Often when dealing with case folding collision, you'll have more than one rogue file. Make sure you delete ALL of them on step 2, so that you don't have to repeat the process multiple times.

View 2 Comment(s)

Want to find out if your server is under DoS attack?

Or maybe you want to optimize your architecture, understanding the network footprint of each of your servers?

Using a combination of handy shell commands, it's easy to construct a list of all IP addresses connected to your server, sorted by count:

On FreeBSD:

On Linux:

Sample output:

Blocking offenders is as easy as:

On Linux:

On FreeBSD:


View 4 Comment(s)

Affiliate Summit Las Vegas is the largest gathering of affiliates, merchants and pay-by-performance networks.



SoftwareProjects is sending a small team of rainmakers to meet and network with our respected clients and partners.

We will be blogging live from the event, covering session highlights here on SoftwareProjects.com

If you'd like to get together and learn why more than 3,000 businesses in 14 countries rely on SoftwareProjects when it comes to Shopping Carts, Product Launches, PPC, Email Marketing and Software Development, please drop a comment here or email us to setup a meeting.

See you in Vegas!