Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I currently have a standard local LAMP setup, (2 web servers, master/slave MySQL, load balancer). This is severing me well locally, but my global users experience slowness.

I want to setup some amazon ec2 nodes in a few continents. The web part isn't an issue as i can deploy code to all the geographic web servers. However, they all need data from the database to function.

What is the best way to have one database design for all the geographic locations? I was thinking of having local databases at each location and have it replicate to the other locations. Writes from one location don't need to be read from another location immediately, but I definitely want speed to its local database.

How would I do this without conflicting primary keys and such?

Is there a better architectural design?

Thanks for your help.

share|improve this question
You should consider the lower hanging fruits first - are you using a globally distributed CDN like CloudFront to serve CSS, JavaScript, images, etc.? –  ceejayoz Aug 14 '13 at 1:44
add comment

2 Answers

Step 1: Setup MySQL Master/Slave replication with SSL: Launch Two boxes 64 bit EC2 instance with MySQL Database on EC2. The entire setup is done inside AWS Classic cloud and not Amazon VPC. Master MySQL Amazon EC2 is in US-EAST (Master US-East-1a) region and One Amazon EC2 for MySQL Slave in Asia Pacific Singapore region (ap-southeast-1a)

Step 2: Configure the following steps on MySQL Master & Slave Amazon EC2's To enable SSL, Edit the my.cnf file

  vim /etc/my.cnf

Add a line with the word ssl to the [mysqld] section:
ssl Restart MySQL

  mysql> show variables like '%ssl%';
  | Variable_name | Value |
  | have_openssl  | YES   |
  | have_ssl      | YES   |
  | ssl_ca        |       |
  | ssl_capath    |       |
  | ssl_cert      |       |
  | ssl_cipher    |       |
  | ssl_key       |       |

7 rows in set (0.00 sec)

The response shows that now SSL is enabled.

Step 3: Steps to create SSL Certificates on MySQL EC2 Now we need to create the CA, server and client certificates that we need for the SSL connections. Create these certificates in the directory /etc/mysql/certs/

Step 3.1 : On MySQL Master EC2 - create CA/Server/Client Certificate: mkdir -p /etc/mysql/certs cd /etc/mysql/certs openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Create server certificate: openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem - set_seial 01 > server-cert.pem

Create client certificate:

  openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
  openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

NOTE: Please use “Unique Common Names” while creating certificates otherwise SSL will not work. Add the following ssl-ca, ssl-cert, and ssl-key lines under [mysqld] section in /etc/my.cnf


Restart MySQL to load the SSL configurations

Step 3.2 : Configure the certificates on MySQL Slave EC2 Copy the following certificate files to MySQL Slave EC2 using SCP to a new directory called /etc/mysql/certs/ca-cert.pem,client-cert.pem,client-key.pem,server-cert.pem,server-key.pem Add the following in MySQL Slave /etc/my.cnf under [mysqld]


Restart MySQL to load the SSL configuration.

Step 4: Configuring replication settings Step 4.1: On MySQL Master Amazon EC2 Add the following in /etc/my.cnf under [mysqld] server_id = 1 log_bin = /var/log/mysql/mysql-bin.log #log_bin_index = /var/log/mysql/mysql-bin.log.index max_binlog_size = 100M expire_logs_days = 1

Restart MySQL

  service mysqld restart

Execute the following in MySQL EC2 shell terminal of Master:
Note:The REQUIRE SSL string is optional; if you leave it out, slave_user will be allowed to connect through encrypted and also non encrypted connections. If you use REQUIRE SSL, then only encrypted connections are allowed between slave and master.

  $ mysql -u root -p
  mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user1'@'%' IDENTIFIED BY 'mysql_mslave1' REQUIRE SSL;
  mysql> show master status;
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  | mysql-bin.000001 |      335  |              |                  |
  1 row in set (0.00 sec)

  mysql> unlock tables;
  mysql> quit

Step 4.2:On MySQL Slave EC2 instance : Edit the my.cnf file server-id = 2 log_bin = /var/log/mysql/mysql-bin.log #log_bin_index = /var/log/mysql/mysql-bin.log.index max_binlog_size = 100M expire_logs_days = 1

Restart MySQL service mysqld restart

Execute the following in MySQL EC2 shell terminal of Slave:

 mysql -u root -p
 mysql slave stop;
 mysql> CHANGE MASTER TO MASTER_HOST='ec2-IP-49.compute-1.amazonaws.com',    MASTER_USER='slave_user1', MASTER_PASSWORD='mysql_mslave1', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=107,MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/certs/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/certs/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/certs/client-key.pem';
 mysql> START SLAVE;
 ** there should be a message "Waiting for master to send event"

Step 5: Test for MySQL AWS Inter Region Replication between MySQL Master & Slave: On MySQL Master EC2 instance mysql -u root -pmysql> create database rep_test_US_APAC; mysql> use rep_test_US_APAC; mysql> CREATE TABLE t1(c1 int,c2 var(100));

Note: In MySQL Slave Amazon EC2 in APAC You can find that database and the table is created. Next step, Insert data into the Master MySQL EC2, in few seconds/minutes replication lag check you can find that the data is present on the slave. Stop inserting the records to master and verify that the slave is up to date, Check the replication lag status on Slave EC2 by applying following command

 $ mysql -u root -p -e 'show slave status\G' | grep -i seconds
 Seconds_Behind_Master: 0


Note:It is important that both Slave_IO_Running and Slave_SQL_Running have the value "Yes" in the output (otherwise something went wrong, and you should check your configuration steps again and /var/log/syslog to find out about any errors); as you're using an SSL connection now, you should also find values in the fields Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_Cert, and Master_SSL_Key.

share|improve this answer
Will I be able to write to both databases? If so, when writing to the same table, will I have primary key conflicts if a table is frequently written to? –  tdbui22 Aug 14 '13 at 3:47
You should be writing to single DB and else you will get Duplicate Entry error. Use Slave for Read and Master for insert/updates. –  Abhishek Anand Amralkar Aug 14 '13 at 5:56
Wouldn't that defeat geographic web servers when it's got to travel back to a central database? –  tdbui22 Aug 15 '13 at 12:51
add comment

Prove that network lag is the issue

There are many reasons users experience slowness.

While you could try solving this problem with local MySQL instances in many geographic locations, that setup would be complex (and quite costly too).

If possible, you should investigate other options (such as database and application logic).

Your Application

Have you investigated your application logic?

You may have too many database calls which are slowing your overall application down. Things like database index's, storage engines, database field types and other optimisations should be investigated.

You could take advantage of AWS ElastiCache (http://aws.amazon.com/elasticache/) or use other caching techniques to store local copies of data in your application for short periods of time.

You might also consider using NoSQL for parts of your application if you can't completely replace MySQL.

These sorts of changes can have huge impacts to the relative slowness of your application.

RDS - Relation Database

Amazon have a service called Amazon Relational Database Service (Amazon RDS).


If you must use a relational database you may find RDS useful. In particular the following features may give you the performance increase you're after.

Provisioned IOPS - You can convert from standard storage to Provisioned IOPS storage and get consistent throughput and low I/O latencies.

Read Replicas that can be used in conjunction to gain enhanced database availability

Although these won't help out with network latency, which your users may be experiencing. Before going further you should be sure it's network latency that is the issue (and that the issue isn't just slow ISP or link on the client side).

Other DB Solutions (NoSQL)

AWS have a few more options for you, http://aws.amazon.com/running_databases/

Using SimpleDB or DynamoDB may suit your needs better.


Amazon DynamoDB, a fully managed service that offers extremely fast performance, seamless scalability and reliability, low cost and more.


Amazon SimpleDB, a fully managed service that provides a schemaless database, reliability and more.

share|improve this answer
None of AWS's managed databases are cross-region, which is what the OP is looking to do. –  ceejayoz Aug 14 '13 at 1:44
It's true they're not cross-region however the OP is also looking to reduce slowness which these methods can help tackle. –  Drew Khoury Aug 14 '13 at 2:07
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.