database strategies for microservices

Last blog I have talked about the problem of database lockup but how can we solve it?

Shared Tables: Shared Tables could be a easy to go and a dirty solution that is very common. But be aware that it is high maintenance.
Using mysql we can use FEDERATED ENGINE (http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html) to do this. We have to create a federated table based on the table at another remote location that we want.

CREATE TABLE remote_user (
  username varchar(20) NOT NULL,
  password varbinary(20) NOT NULL,
  PRIMARY KEY(username)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:password@someip:port/db/user’;

Database View : A database view is a comparatively better approach when the cases are simple because it allows another representation of database model which is more suitable. Most amazing thing about database view is that it supports wide range of databases. But for heavy use cases we can see performance issues. While considering database view we must ensure that both of the databases can connect with each other without any network or firewall issue. Most of the database views are read only, updating them according to need might get tricky.

CREATE TABLE federated_table (
    [column definitions go here]
)
ENGINE=FEDERATED
CONNECTION='mysql://username:password@someip:port/db/user’;

Triggers:
Database triggers might come handy where one database operation will trigger another database update. We can bind to AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers.

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);
END

Data Virtualization: When we are dealing with micro services possibly some of our databases are running using Mysql while other services are running other DBMS. In that case Data Virtualization strategy is necessary. One open source data virtualization platform is Teiid. But when dealing with data virtualization strategy we must know that if we are dealing with stale data or not, as it will have serious performance issue as it will add another hop as the data is not being accessed directly from database.

Event sourcing: Rather then making database operatins we can consider designing it as a stream of events that goes one after another through as message broker. So it does not matter how many users are accessing your database it will never lock up your database but it would take more time to process the data.

Change Data Capture: Another approach is to use Change Data Capture (CDC), is an integration strategy that captures the changes that are being made to a data and makes them available as a sequence of events in other databases that needs to know about these changes. It can be implemented using Apache Kafka, Debezium and so on.

Running a on premise local mysql replica with AWS RDS Aurora master

To solve our problem we are running a hybrid cloud. Few of our services are running on cloud and some of our services are running in premise locally in our country where we have our users and where AWS does not provide service. To able to do that we need a database replica that has read facility.

We need to creating replica user:

CREATE USER 'replica'@'%' IDENTIFIED BY 'slavepass'; 
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

Then create a new DB Cluster parameter group and set binlog_format to MIXED. Modify the Aurora cluster and select the custom parameter group. Restart your db to apply those changes. Now if you run following command you will be able to see the bin log file name and position.

show master status

Now we need to dump our master user data to sql dump so that we can feed our slave database.

mysqldump --single-transaction --routines --triggers --events -h XXX.azhxxxxxx2zkqxh3j.us-east-1.rds.amazonaws.com -u bhuvi –-password='xxx' my_db_name > my_db_name.sql

It can be GB to TB of data depending on your database size. So it will take time to download.

Run follwoing to know your mysql configuration file:

mysqld --help -verbose | grep my.cnf

For me it is /usr/local/etc/my.cnf

vi /usr/local/etc/my.cnf

and change server-id to:

 [mysqld] server-id = 2

now lets import these data into our mysql.

mysql -u root –-password='xxx' my_db_name < my_db_name.sql

Now we need to let our slave database know who is the master:

CHANGE MASTER TO  
MASTER_HOST = 'RDS END Point name',  
MASTER_PORT = 3306,  
MASTER_USER = '',  
MASTER_PASSWORD = '',  
MASTER_LOG_FILE='',  
MASTER_LOG_POS=;

Now we need to start the slave.

start slave;