A Web Developer's Diary

MySQL database replication using SSL on Ubuntu 12.04

7/15/2012

3 Comments

 
This tutorial describes how to set up database replication for MySQL using SSL on Ubuntu 12.04. There are some tutorials out there, but I found out they are a little outdated and don't work as well any more (at least not for Ubuntu specifically). So I decided to write down my experience for other developers who find them selves setting up MySQL database replication to make their lives easier.

First, we start with setting up the master server. Last, we set up the slave server to download database changes from the master server. For the purpose of this tutorial we are going to replicate a database called 'exampledb' from a server at master.webdevelopersdiary.com to a server at slave.webdevelopersdiary.com.

Set up the master server

Setting up the master consists of the following steps:
  1. Generate SSL certificates.
  2. Edit MySQL configuration my.cnf.
  3. Restart MySQL server process.
  4. Set up database replication privileges (and force SSL).
  5. Perform initial database backup to start replication from.
  6. Finish up.
The SSL certificates have to be placed in the /etc/mysql/ directory if you want them to work out-of-the-box. If you want to place them elsewhere, you first have to update the MySQL Apparmor settings, or SSL will fail. For the purpose of this tutorial, we will assume all the certificates will be in /etc/mysql/. The MySQL Manual explains very well how to generate the SSL certificates. I copied the steps here for your convenience. First, we generate the CA certificate:
 $ mkdir ~/mysql-tutorial/ && cd ~/mysql-tutorial/
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 1000 \
-key ca-key.pem -out ca-cert.pem
Then create the server certificate, remove its passphrase and sign it:
 $ openssl req -newkey rsa:2048 -days 1000 \
-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 1000 \
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Create the client certificate, remove its passphrase and sign it:
 $ openssl req -newkey rsa:2048 -days 1000 \
  -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 1000 \
  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Install the certificates into to /etc/mysql/ directory:
$ sudo cp *.pem /etc/mysql/
Open /etc/mysql/my.cnf and make sure MySQL is listening on all interfaces so that the backup server can reach the database from the outside. Typically, there is a line like:
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
The line starting with “bind-address” should be commented out if present.
Also add the following in the [mysqld] section of the config file:
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = exampledb
And enable SSL by editing:
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem
Close /etc/mysql/my.cnf and restart MySQL to apply changes to the config by executing the command:
$ sudo service mysql restart
Next, we create a database dump of the database we want to replicate (exampledb). During the dump, we need to lock the database to determine the exact position from which we need to start replicating (corresponding to the database dump). So we write down the log ‘position’ column from which to start replicating on the slave.

Login to the mysql server (located at master.webdevelopersdiary.com) as root from command line (or use a web-based admin tool like phpMyAdmin to execute the required commands):
$ mysql -h localhost -u root -p
Execute the following SQL commands:
GRANT REPLICATION SLAVE ON *.*
TO 'slave_user'@'slave.webdevelopersdiary.com'
IDENTIFIED BY 'slave_password'
REQUIRE SSL;
FLUSH PRIVILEGES;
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Write down the output of the last statement, you're going to need it later. An example output looks like:
+------------------+----------+-----------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000002 | 1337 | exampledb | |
+------------------+----------+-----------------+------------------+
1 row in set (0.00 sec)
Write down the output. Bring the MySQL console to the background (press CTRL+Z) and make the database dump:
$ mysqldump -h localhost -u root -p --opt exampledb > ~/mysql-tutorial/exampledb.sql
$ fg
`fg` returns you to the MySQL shell, execute the following SQL to finish up:
UNLOCK TABLES;
quit;
Copy exampledb.sql, ca-cert.pem, client-cert.pem and client-key.pem from the ~/mysql-tutorial/ directory to the slave server located at slave.webdevelopersdiary.com. Convince yourself there is no firewall running that is blocking SQL connections from the outside. That's it for the master server.

Set up the slave server

The steps for the slave server are:
  1. Setup the SSL certificates.
  2. Edit MySQL server configuration my.cnf.
  3. Restart MySQL server.
  4. Configure and start slave server.
  5. Finish up.

Place all the .pem files you copied from master server into the directory /etc/mysql/ on the slave server. Start of your favourite editor and edit /etc/mysql/my.cnf and add the following lines:
server-id=2
replicate-do-db=exampledb
Save and close /etc/mysql/my.cnf and restart the MySQL server. Then start up the MySQL console:
$ sudo service mysql restart
$ mysql -h localhost -u root -p
Execute following SQL to import database and start slave. Make sure you replace the correct values at MASTER_HOST, MASTER_USER, MASTER_PASSWORD, MASTER_LOG_FILE (from earlier written down value) and MASTER_LOG_POS (also from earlier written down value).
STOP SLAVE;
CREATE DATABASE IF NOT EXISTS exampledb;
USE exampledb;
SOURCE /path/to/exampledb.sql;

CHANGE MASTER TO
MASTER_HOST='master.webdevelopersdiary.com',
MASTER_USER='slave_user',
MASTER_PASSWORD='slave_password',
MASTER_CONNECT_RETRY=60,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1337,
MASTER_SSL=1,
MASTER_SSL_CA='/etc/mysql/ca-cert.pem',
MASTER_SSL_CERT='/etc/mysql/client-cert.pem',
MASTER_SSL_KEY='/etc/mysql/client-key.pem';
START SLAVE;
SHOW SLAVE STATUS \G;
That's it, database replication should be up and running!
3 Comments

Lightweight self-contained database: SQLite vs. H2 database vs. MySQL embedded

7/8/2012

1 Comment

 
SQLite is a popular choice for PHP programmers to kick-start their development. It's easy, self-contained, requires no configuration, no server and all the data is kept neatly in just one file. Then later on, the application is commonly migrated to a production DBMS (e.g. MySQL). There have not been any alternatives for SQLite when developing in PHP with MySQL as a target platform. But then again, why would you want an alternative for SQLite? Well, to name a few reasons: MySQL compatibility and support for hash indices. And that's not all! Keep reading to see why.

When looking for alternatives to SQLite, I came across two suitable candidates: H2 database engine and a MySQL embedded version. My criteria were that the alternative should be as easy to use as SQLite and compatible with MySQL, based on two reasons. First, I wanted it to be compatible with MySQL so that I can keep using that easy self-contained database for development even after the application has gone into production. Second, an easy to setup database is great for bringing new developers up to speed with development as fast as possible.

Let's start with the good stuff. Below is a comparison of SQLite, H2 database engine and MySQL embedded version.
Comparison of self-contained PHP development databases.
Compare SQLite H2 database engine MySQL Embedded
Footprint 350KiB ~1MB <2MB
License Public domain Dual: Modified MPL 1.1 / EPL 1.0 (commercial friendly) GPL 2.0 (only commercial friendly if not redistributed)
Self-contained ✔ ✔ ✔
Single file ✔ ✔ ✖
Serverless ✔ ✔ ✖
Server-mode ✖ ✔ ✔
Zero-configuration ✔ ✔ ✔
Transactions ✔ ✔ ✔
Indices ✔ (B-tree, R-tree, full-text) ✔ (B-tree, tree, hash, full-text) ✔ (B-tree, R-tree, hash, full-text)
MySQL compatibility ✖ ✔ (but not 100%) ✔
Compatibility with other DBMS ✖ ✔ MySQL, PostgreSQL, Oracle, MSSQL, DB2, HSQLDB and Derby ✖
Encryption ✖ ✔ ✖
In-memory databases ✔ ✔ ✔ (MEMORY storage engine)
To me it looks like H2 database is the easiest to manage. It requires no server and everything is put neatly in one file, which makes back-upping and sharing databases among developers easier. It even has a nice extra feature over MySQL: encryption of data files (though I don't see a direct need for that during development for me personally).

I have experimented with PHP and H2 before and ran into some limitations (Quercus' MySQL driver doesn't play well yet with H2's MySQL compatibility mode so I have to use the Quercus' PDO driver instead). I haven't tried embedding MySQL yet, which is what I am going to try next.

The embedded version of MySQL has the big benefit of being 100% MySQL compatible, but it also has an uncertainty. I am not sure if it's okay to redistribute my application (containing embedded MySQL) among developers. Another limitation could be that I would have to buy a commercial license for MySQL should I choose to distribute embedded MySQL to customers (vs. customers setting up their own MySQL server). Though, it is okay to use MySQL embedded version for a website hosted by myself (it's not technically distributed in that case).

For now, I have not decided yet whether I will stick with H2 or switch to the embedded version of MySQL. I am going to give both a try and I will keep you updated via this blog!
1 Comment

Good to know: how to properly store date and time values in MySQL

6/30/2012

6 Comments

 
MySQL has two native field types that can store information about both date and time: timestamp and datetime. The major differences between timestamp and datetime field types are:
  1. timestamp can hold values only in the range '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC while datetime can hold any value in the range '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
  2. timestamp is the only mysql field type that stores values relative to a timezone (namely UTC), datetime does not store according to a time zone!

This has some serious implications. First, timestamp can only be used where the supported value range is sufficient. Second, for scenarios where you would want to log, compare or perform other tasks with date and time, datetime is not reliable if you adhere to your server time zone settings because datetime does not store the time zone information. An example to illustrate this unreliability:

Suppose you are logging transactions of some sort to your MySQL database, neatly adding the date and time at which the transaction took place, relying on the server time zone settings. Another process is monitoring the transactions (e.g. is client X not exceeding Y transactions per hour, whether transactions result in insufficient account balance, etc.). Now suppose the time zone of the server changes, this could be because of any reason:
  1. Some countries have daylight savings time, if your server is configured to that country's particular timezone, your server automatically changes time zone upon entering and leaving daylight savings time.
  2. The server administrator manually changes the time zone, e.g. because the server moved physically, perhaps the change is by accident or he/she decided it was better to change to a time zone that doesn't have daylight savings time ;-)
  3. A bug in the system software changes the time zone by accident.

To stick with the daylight savings example, let's say the clock gets rewound one hour. Transactions are still happily being inserted into the database using datetime. Now, according to the transaction log, customers are making twice the amount of transactions during this hour. But wait?! Weren't we keeping an eye on the maximum amount of transactions per hour of clients? It looks like some clients that are operating at only half of their capacity suddenly exceed the maximum transaction limit during that hour, uh oh!

How will MySQL know whether it was 2:30 AM before or after daylight saving time when you use timestamp? The answer is: MySQL doesn't know! E.g. 28 October 2012 2:30 AM in either CEST (UTC+2:00) or CET (UTC+1:00) are both stored as unix timestamp value 1351387800 (= "2012-10-28 02:30 CET" = "2012-10-28 01:30 UTC"). As we will see soon, this is documented behaviour. It's not possible to insert unix timestamps directly into a timestamp field, so you are required to use a text representation of the date and time you are trying to store (e.g. "2012-10-28 2:30", NOW(), CURRENT_TIMESTAMP). MySQL takes this string and converts it into an unix timestamp using UNIX_TIMESTAMP(), this is where the point where 28 October 2012 2:30 AM CEST and CET both get mapped to 1351387800. One suggested work-around I came accross doesn't work in my boundary case, confirming what the manual says:

 mysql> SELECT FROM_UNIXTIME(@ts := 1351387800) = FROM_UNIXTIME(@ts - 3600);
+--------------------------------------------------------------+
| FROM_UNIXTIME(@ts := 1351387800) = FROM_UNIXTIME(@ts - 3600) |
+--------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

So how can we store our dates and times without being affected by this? The answer lies in designing your database (and application). Trusting your server's time zone settings and MySQL's time zone conversion abilities is a bad idea. Instead, use datetime fields and store UTC formatted values only.  You can always get the current date and time in UTC via UTC_TIMESTAMP(), e.g.:

 mysql> SELECT UNIX_TIMESTAMP(), UTC_TIMESTAMP(), NOW();
+------------------+---------------------+---------------------+
| UNIX_TIMESTAMP() | UTC_TIMESTAMP() | NOW() |
+------------------+---------------------+---------------------+
| 1351384200 | 2012-10-28 00:30:00 | 2012-10-28 02:30:00 |
+------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Alternatively, you could store unix timestamps in unsigned int columns. But then you can not reliably use all those useful documented MySQL date and time functions, and, you would have to write your own date and time calculations using math in queries which can get messy.

A disadvantage of storing all values in a datetime field is that you cannot make use anymore of the CURRENT_TIMESTAMP default value. But then again, if you need to store values outside the timestamp range or need 100% reliability, that might be one of your lesser concerns.

Conclusion
Use UTC formatted date and time values within MySQL and your application for reliability    . Store all your date and time values in UTC format in a datetime column.
6 Comments

    Author

    Blog about random challenges of a web developer.

    Archives

    April 2015
    May 2013
    January 2013
    July 2012
    June 2012

    Categories

    All
    Aws
    Cdn
    Code Igniter
    Css
    H2database
    Jamp
    Java
    Javascript
    Magento
    Maven
    Mysql
    Opencl
    Php
    Play-framework
    Quercus
    Scala
    Ubuntu

    RSS Feed

Powered by Create your own unique website with customizable templates.