How I implemented a multi-master MySQL replication setup using the poor man’s VPN

Posted by & filed under Systems Administration.

The folks at my company and I recently deployed a web application for a client. During initial discussions, we were made aware of the fact that most of the users of the application will be accessing the app from one location. We couldn’t have the application hosted in-premises because there were components of the app that needed to be online all the time and accessible from the Internet. Given the conditions under which hosting the application in-premises will be subjected to, we decided to have the app hosted on a VPS. Performance, however was key and given that the location was likely to have a poor Internet connection, we then decided to have two servers – one for the version that was hosted externally and another for the in-house version. Since the application was essentially the same, we didn’t have to create two application versions but synchronizing the databases was going to be key.

Up until now, I had never implemented a MySQL replication setup before and knew only about one-master-multiple-slaves-type replication using MySQL. I was planning of using PostgreSQL for the multi-master replication setup until I discovered a way to implement multi-master replication in MySQL.

This setup works on the premise that both machines are able to “see” each other – meanining I can connect to the MySQL instance running on each of the machines from the other. In our scenario, we were dealing with a server that was on the Internet (which the local server didn’t have any problems connecting to) and another server that was behind a firewall at the client location – which meant that the Internet server was incapable of connecting to the MySQL instance on the local machine. Also in this case, port forwarding was not an option.

Solution

SSH tunneling is fondly called the poor man’s VPN because with it, you can setup tunnels that will allow you gain access to services running on a local server from the Internet. With a simple:

ssh -R 8000:localhost:8000 username@remoteserver

Issued from a local machine to a remote server with an Internet reachable IP address, a service hosted locally on port 8000 finally becomes accessible from the remote machine. In the case of making MySQL running on a local machine accessible from the remote server, this can be accomplished by using the syntax:

ssh -R 3307:localhost:3306 username@remoteserver

and now you can connect to it from the remote server:

remoteserver username$ mysql -h localhost -P 3307 --protocol=TCP

It’s sometimes essential to specify the connection protocol as MySQL tends to use the UNIX pipe to connect to MySQL by default and that would mean that it will connect to the MySQL server running on the current machine irregardless of the -P option.

I configured autossh to automatically connect to the remote server on startup and reconnect if the connection gets severed for any reason. Because of the unstable Internet connectivity, I configured a cronjob to always attempt to connect to the remote server in the event that it’s unable to do so at startup.

What was left, was to configure the right master settings for each of the servers. The only difference was the addition of master-port in the mysqld configuration file to specify which port to connect to the master. In the mysqld configuration file for the remote server, I set master-port to 3307 and master-host to localhost. On the local server, I configured master-host also to localhost and master-port to 3307. This was for the sake of consistency. So I was able to do this because I had created a tunnel linking port 3307 on the local machine to port 3306 on the remote server and port 3307 on the remote server to 3306 on the local server.

Remote server mysqld configuration

auto_increment_increment = 10
auto_increment_offset = 1
master-host = localhost
master-port = 3307
master-user = <replication user>
master-password = <replication password>

Local server mysqld configuration

auto_increment_increment = 10
auto_increment_offset = 2
master-host = localhost
master-port = 3307
master-user = <replication user>
master-password = <replication password>

After this setup and sorting out some replication-related issues, both servers started synching with each other and it performs pretty well even with a 128k link and with lots of activity on both servers. The only issue I’ve had is with the timestamp fields, they seems to always have the same minute (e.g. 10:02, 23:02, 13:02) and I’ve not been able to figure out why.

Are there other (better) ways this could have been implemented?

Is there still a market for ringtones in Nigeria?

Posted by & filed under Mobile.

I don’t see those ads again; on TV or radio – advertising ringtone downloads and all that. I tried to figure it out. If the venture was profitable, what happened to the industry?

I think it’s not that hard to figure out. First off, I think the pricing of ringtones didn’t adapt quickly to the changes in the market. I’m pretty sure 99% if not 99.999% of all truetones on mobile phones these days are essentially ripped mp3s. Mobile phone users have gotten smart enough to transfer ringtones from whatever source to their mobile phones. That’s not going to change. So the question is, is there still a market for ringtones? My subjective answer is yes.

How? The current view about ringtones in the market needs to change. Ringtones are not the same as full-length music tracks and should not be treated as such. How many ringtones made out of full-length music tracks do you actually get to hear up until the end of the track? At best, you get to hear the first 30 seconds of the track and the most enjoyable parts of the music track do not usually start at the beginning.

My take on ringtones is that they shouldn’t be sold or if they must, then it should be at an afforable price. ₦100 per ringtone doesn’t come across to me as affordable for a majority of mobile phone users. Ringtones could potentially, be used to create awareness or even drive sales for full-length music tracks or albums.

I’m left wondering how the ringtone market is fairing in other parts of the world? What sort of challenges are they facing?

How to hire a programmer when you’re not a programmer

Posted by & filed under Programming.

I read this at the Signal vs Noise blog today and I thought it was worth reblogging with some of my own comments.

Everything Matt mentioned in the post couldn’t be “truer” even in a Nigerian context. I remember back in college when some of my colleagues (who are by the way very good developers) would argue about programming languages. You’ll hear them argue about PHP being better than Java or even Flash or the other way around and you could tell how passionate they are about the tools and languages they use. In my observation, some of the best programmers I’ve met are heavily opinionated about programming languages and this is as a result of how much of that language they know and of course this only comes as a result of a lot of use.

Contributing to open source projects is still a new concept with a lot of Nigerian developers and one of the ways to identify exceptional programmers are by their contributions to open source projects. Of all the points Matt wrote, this is one I really want to see a lot of Nigerian developers doing.

I’ve been working as a freelancer with a client who has been in the IT industry for eons for about a year and one of the things they tell me about good developers is that they know how to communicate. Devs tend to speak geekspeak a lot and for business-types, this makes little or no sense. Your ability to communicate highly complex matters in simple-to-understand language will earn you great points. When next you’ve been told to explain a highly technical feature in your software project, throw away all the jargon and when you’re done writing, ask yourself if your grandmother will understand what you’ve written. If she will, then you’ve done a great job. If not, then rewrite it.

For more points, please read the original post.

Solution: Most programmers cant solve this

Posted by & filed under General.

In my previous post on this, here’s my solution (written in Python):

Like I explained in the previous post on this matter, the most important thing to note is that “divisibility” is determined using the modulus operator.

If x is divisible by y, then x mod y is equal to 0 (zero). In python, the modulus operator is represented by ‘%‘. Hence (not i % 5) returns True if i is divisible by 5.

The second thing to note is that you need to check for the condition where the number is divisible by both 3 and 5 first, before testing individually for either 3 or 5. The reason might not be that obvious but if you test if it is divisible by 3 first, it will pass and skip the condition where both tests are true. Same also applies for testing for divisibility by 5.

Most programmers cant solve this

Posted by & filed under Programming.

Pascal Ehigie Aito wrote an interesting blog post this morning and I’ll suggest visiting his blog to read about the challenge.

For those of you who do take the challenge to write a solution, please leave a comment with a link to your solution. Any programming language is accepted.

Here is a tip: You really need to understand what “divisibility” means in a mathematical context. Hint: it has something to do with modulus. That said, the reason why a programmer wouldn’t be able to solve this problem will have to do with not understanding computation in general.

I’ll be posting and explaining my solution in a few days so watch out for it.