AWS RDS Encryption

What is RDS Encryption ?

We can encrypt Amazon RDS DB instances and snapshots with enabling encryption option for our any amazon RDS instance . It means data is encrypted at rest including underling storage of DB instances , its read replicas , snapshots and automated backups

Amazon RDS encrypted DB instances use the industry standard AES-256 encryption algorithm to encrypt your data on the server that hosts your Amazon RDS DB instances. You don't need to modify your database client applications to use encryption.

For encrypted and unencrypted DB instances, data that is in transit between the source and the read replicas is encrypted, even when replicating across AWS Regions

Possibilities to encrypt RDS Instance :

Using snapshot copy option we have to enable encryption for RDS snapshot , and restore the snapshot with new instance

1. Take snapshot of current production instance
2. Copy snapshot with enabled encryption
3. Restore the snapshot with different instance identifier
4. Rename the current production instance identifier to proddb-old

Once renamed production instance , rename instance identifier of encrypted RDS to current production name

Steps to Encrypt RDS instance : 

Step 1 : Take a snapshot of unencrypted RDS instance with any specified name

Take a snapshot

Step 2 : Make a copy of snapshot with encryption enabled option

copy snapshot

Here , we can add default master key else if any consumer keys available use the same

Note : Encrypted read replicas must be encrypted with the same key as the source DB instance when both are in the same AWS Region

encryption enable

Step 3 : We are ready with encryption enabled snapshot copy

snapshots

Step 4 : Restore rdssnapshotwithencryption

restore snapshot

We have launch the instance identifier with rdssnapshotwithencryption

Step 5 : Except Naming convention and other network parameters should be same as unencrypted RDS instance

DB Instance identifier : encrypted-instance

Virtual private cloud (VPC) : Same like unencrypted instance  

new instance launch

VPC security group : Same like unencrypted instance

DB instance class : Same like unencrypted instance

Step 6 : Once instance has been launched , verify the configuration of encryption

configuration validation

Step 7 : Instance identifier change

Change instance identifier name to _old for unencrypted instance , and change encrypted instance name to production name

identifier change

Step 8 : verify the application connectivity with encrypted instance

If anything goes wrong , we just need to rename the instance Identifier name in max 2 minutes without major downtime

References :

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html

Cassandra Multi-Node Cluster setup in Ubuntu 16.04

Sharing simple steps to configure Cassandra cluster in few minutes ,

Prerequisite :

Install Java ( Jre ) from oracle site and verify java version , But our installing step will be take care of java installation as well

java --version

Installing Cassandra repository :

What are repositories ?

"Authentication keys" are usually obtained from the maintainer of the software repository. The maintainer will often place a copy of the authentication key on a public key server such as www.keyserver.net. The key can then be retrieved using the command.

Install the Cassandra repository into /etc/apt/sources.list.d/cassandra.sources.list using below command

Depends on requirement , update the 36x values into below command

echo "deb http://www.apache.org/dist/cassandra/debian 36x main" | sudo tee -a /etc/apt/sources.list.d/cassandra.sources.list

if we didn't know the public key to authenticate for download packages , just run apt-get update commands and get the authentication key for Cassandra software

root@datablogs:~# sudo apt-get update
Hit:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial InRelease
Hit:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates InRelease
Hit:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports InRelease
Get:5 http://security.ubuntu.com/ubuntu xenial-security InRelease [109 kB]
Hit:6 http://apt.postgresql.org/pub/repos/apt xenial-pgdg InRelease
Get:4 https://downloads.apache.org/cassandra/debian 36x InRelease [267 B]
Ign:4 https://downloads.apache.org/cassandra/debian 36x InRelease
Get:7 https://downloads.apache.org/cassandra/debian 36x/main amd64 Packages [678 B]
Fetched 113 kB in 1s (79.6 kB/s)
Reading package lists... Done
W: GPG error: https://downloads.apache.org/cassandra/debian 36x InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 749D6EEC0353B12C
W: The repository 'http://www.apache.org/dist/cassandra/debian 36x InRelease' is not signed.
N: Data from such a repository can't be authenticated and is therefore potentially dangerous to use.
N: See apt-secure(8) manpage for repository creation and user configuration details.

Here , 749D6EEC0353B12C is the public key . Add the public key to install Cassandra ,

root@datablogs:~# sudo apt-key adv --keyserver pool.sks-keyservers.net --recv-key 749D6EEC0353B12C
Executing: /tmp/tmp.HUY7Ncexdr/gpg.1.sh --keyserver
pool.sks-keyservers.net
--recv-key
749D6EEC0353B12C
gpg: requesting key 0353B12C from hkp server pool.sks-keyservers.net
gpg: key 0353B12C: public key "T Jake Luciani <jake@apache.org>" imported
gpg: Total number processed: 1
gpg:               imported: 1  (RSA: 1)

Installing Apache Cassandra :

Start updating the repository and install Cassandra cluster , Java Packages included with installation

root@datablogs:~# sudo apt-get update
Hit:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial InRelease
Hit:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates InRelease
Hit:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports InRelease
Get:5 http://security.ubuntu.com/ubuntu xenial-security InRelease [109 kB]
Hit:6 http://apt.postgresql.org/pub/repos/apt xenial-pgdg InRelease
Get:4 https://downloads.apache.org/cassandra/debian 36x InRelease [267 B]
Fetched 112 kB in 1s (95.7 kB/s)
Reading package lists... Done
root@datablogs:~# sudo apt-get install cassandra
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  ca-certificates-java fontconfig-config fonts-dejavu-core java-common libavahi-client3 libavahi-common-data libavahi-common3 libcups2 libfontconfig1 libjpeg-turbo8
  libjpeg8 liblcms2-2 libnspr4 libnss3 libnss3-nssdb libopts25 libpcsclite1 libpython-stdlib libpython2.7-minimal libpython2.7-stdlib libxi6 libxrender1 libxtst6 ntp
  openjdk-8-jre-headless python python-minimal python2.7 python2.7-minimal x11-common
Suggested packages:
  cassandra-tools default-jre cups-common liblcms2-utils pcscd ntp-doc libnss-mdns fonts-dejavu-extra fonts-ipafont-gothic fonts-ipafont-mincho fonts-wqy-microhei
  fonts-wqy-zenhei fonts-indic python-doc python-tk python2.7-doc binutils binfmt-support
The following NEW packages will be installed:
  ca-certificates-java cassandra fontconfig-config fonts-dejavu-core java-common libavahi-client3 libavahi-common-data libavahi-common3 libcups2 libfontconfig1
  libjpeg-turbo8 libjpeg8 liblcms2-2 libnspr4 libnss3 libnss3-nssdb libopts25 libpcsclite1 libpython-stdlib libpython2.7-minimal libpython2.7-stdlib libxi6
  libxrender1 libxtst6 ntp openjdk-8-jre-headless python python-minimal python2.7 python2.7-minimal x11-common
0 upgraded, 31 newly installed, 0 to remove and 9 not upgraded.
Need to get 63.1 MB of archives.
After this operation, 167 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libjpeg-turbo8 amd64 1.4.2-0ubuntu3.4 [111 kB]
Get:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 x11-common all 1:7.7+13ubuntu3.1 [22.9 kB]
Get:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libxtst6 amd64 2:1.2.2-1 [14.1 kB]
.....
.....
.....

Configuring Apache Cassandra :

Needs to configure required variables in cassandra.yaml file ,

cluster_name: Name of the cluster

cluster_name: 'Test Cluster' ( Should be same in all Cassandra nodes ) 

-seeds: This is a comma-delimited list of the IP address of each node in the cluster

- seeds: "172.31.x.20,172.31.x.21,172.31.x.22,172.31.x.23,172.31.x.24,172.31.x.25"

listen_address: This is IP address that other nodes in the cluster will use to connect to this one. It defaults to localhost and needs changed to the IP address of the node

listen_address: 10.225.x.20 ( Cassandra Node 1 Address )

rpc_address: This is the IP address for remote procedure calls. It defaults to localhost. If the server’s host name is properly configured, leave this as is. Otherwise, change to server’s IP address or the loop back address (127.0.0.1)

rpc_address: 10.225.x.20 ( Cassandra Node 1 Address )

Once updated variables , restart Cassandra cluster in each nodes

Cluster status :

Verify Cassandra running status using below command ,

nodetool status 

[SQLState XX000] ERROR: User arn:aws:redshift:ap-south-1:316720341464:dbuser:redshift-cluster-1/analytics is not authorized to assume IAM Role arn:aws:iam::323232323:role/aws-access-for-redshift

While Configure Data Migration Service between Redshift and any kind of heterogeneous source , we come across these issues often .

Error looks like an IAM policy is missing for attached , so we straightly move into IAM console granted AmazonS3FullAccess AmazonRedshiftFullAccess , then restart failed DMS tasks .

But no luck , still we are getting same issue

When we looking into Redshift IAM role , customer have been added custom roles with permissions . so it is missing trusted entities for both redshift.amazonaws.com and dms.amazonaws.com

Move into Trust relationships tab in custom role and edit trust relationship as below ,

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "redshift.amazonaws.com",
          "dms.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

after adding trusted entities

Attached policies for DMS tasks

Once we update trust policy , able to load data between MySQL to Redshift

Remember to troubleshoot DMS tasks in depth , always enable detailed debug in cloudwatch logs

Monitoring Data Growth With Query Exporter + Prometheus + Grafana

In this experimental scenario , gathering the table growth statistics using query exporter and loading the data into time series Prometheus database . Grafana dashboards shows the growth in minutes basis

Table growth statistics gathered from localhost mysql instance for testing purpose

Frequently receiving questions like , how to we monitor the data growth of each critical tables. We can have some statistics about data growth and add convenient space for future

Launched three separate docker containers ( Query Exporter , Prometheus and PMM Server ) for each components . Added additional Dashboards in PMM Server for testing purpose

Docker Containers

Prometheus and Query Exporter Configuration files mapped with localhost volume like below ,

docker run -p 9560:9560/tcp -v "$PWD/config.yaml:/config.yaml" --rm -it adonato/query-exporter:latest -- /config.yaml

docker run -p 9090:9090 -v /tmp/prometheus.yml:/etc/prometheus/prometheus.yml prom/prometheus

config.yaml ( Query Exporter configuration file ) :

databases:
  datablogs:
    dsn: mysql://queryexporter:test123@172.31.90.32:3306/test
    keep-connected: true

metrics:
  metric1:
    type: gauge
   # type: counter
    description: A sample gauge

queries:
  query1:
    interval: 3s
    databases: [datablogs]
    metrics: [metric1]
    sql: SELECT TABLE_ROWS as metric1 FROM information_schema.tables WHERE table_schema = 'test' and table_name='actor';

prometheus ( Prometheus configuration file ) :

global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'queryexporter'
    scrape_interval: 5s
    static_configs:
      - targets: ['172.17.0.3:9560']

Both Query Exporter and Prometheus docker container started and we have to allow appropriate ports to access the web console through browser

AWS Console Inbound Rules

Lets verify the Query Exporter and Prometheus running status

Query exporter metrics

Prometheus targets status

Query Exporter is gathering the statistics and loading the data into Prometheus database . Then

Add the Prometheus data source into Grafana

Using the collected metrics display the dashboards on every minutes

Monitoring Dashboards

References :

Query Exporter : https://github.com/albertodonato/query-exporter

PMM Server : https://www.percona.com/doc/percona-monitoring-and-management/2.x/install/docker-setting-up.html

Prometheus : https://www.digitalocean.com/community/tutorials/how-to-install-prometheus-using-docker-on-ubuntu-14-04

Multi Source Replication from GCP Cloud MySQL to Percona XtraDB Cluster

It is easy to migrate the MySQL databases from one cloud provider to another cloud provider , but without downtime is little difficult to migrate the 1 TB of data . Using GTID based replication is easy to achieve in few hours of effort

Before go into detail , we will go through explanations of each things,

What is Cloud MySQL ?

Google Cloud Managed service for MySQL Database servers . It is supporting MySQL 5.6 and 5.7 with first and second generation instances . First and Second generation differs with allocating RAM and Storage

What is Percona xtradb cluster ?

Synchronous multi-master replication using percona MySQL server and percona xtrabackup using galera Library . As recommended it should contains odd number of nodes and same data spanned with all nodes

What is multi-source replication ?

MySQL multi-source replication enables a replication slave to receive transactions from multiple immediate masters in parallel

In this scenario , we have to migrate more than 3 GCP Cloud MySQL Managed instances into

Percona Galera Cluster . Before proceed to migrate the data , it is requires to compare MySQL 5.7 and Percona MySQL 5.7

https://www.percona.com/doc/percona-server/5.7/feature_comparison.html

So that we can avoid impact after migrating the data into new server

Instructions follow to setup multi source replication ,

• In GCP MySQL Instance , take a user database backup from each instances separately through mysqldump or using export in GCP GUI Console and move to backup files to destination server using gsutil or scp utility

mysqldump --user=root --password --host=gcpinstance1 sourcedb1 > mysqldump1.sql

mysqldump --user=root --password --host=gcpinstance2 sourcedb2 > mysqldump2.sql

• Before goes to next steps , we have to ready with three node percona XtraDB cluster environment before proceeds with next steps
• In Percona XtraDB Cluster end , need to modify below server variables in my.cnf file for GTID based replication and restart mysql service

[mysqld]
server-id=[SERVER_ID]
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
replicate-ignore-db=mysql
binlog-format=ROW
log_bin=mysql-bin
expire_logs_days=1
read_only=ON

Same Changes needs to edit for additional node of percona cluster

• Restore multiple source cloud mysql backup into one percona cluster instance

mysql --user=root --password --host=perconainstanceslave < mysqldump1.sql

mysql --user=root --password --host=perconainstanceslave < mysqldump2.sql

• Once restore is completed , we need to add multiple source of GCP Cloud MySQL instances into single percona cluster instance using below command

CHANGE MASTER TO MASTER_HOST="gcpinstance1", MASTER_USER="slaveuser1",MASTER_PASSWORD="slaveuser1", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

CHANGE MASTER TO MASTER_HOST="gcpinstance2", MASTER_USER="slaveuser2",MASTER_PASSWORD="slaveuser2", MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

One we execute this , we will get below error due to applied transactions exists already in slave server or we will get duplicate transactions issues commonly

Slave_IO_Running: No

Slave_SQL_Running: Yes

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

• So,We need to get gtid_purged values from each separate source backups to ignore the deleted transactions in the binary log and troubleshoot above issues

Get gtid values

cat mysqldump1.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'''

cat mysqldump2.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'''

• To set global GTID_PURGED values , needs to do reset master in percona galera cluster . If server is in cluster not possible to execute reset master command . Needs to disable wsrep_provider variable in wsrep.conf to execute reset master and restart mysql service in slave server

#wsrep_provider = /usr/lib/galera/libgalera_smm.so

• Once mysql service is restarted , we can able to do reset master in percona galera cluster server and set GTID_PURGED values

Set GTID_PURGED Value :

Configure gtid values

Check Show Slave Status G :

Slave Status

We able to see slave server is get synced in few minutes , and seconds_behind_master is 0

Check Show Processlist :

Highlighted the multi master servers ( two servers ) is syncing to slave server

Processlist

Once completed all the steps , enable cluster wsrep_provider variable in wsrep.conf and restart mysql service in slave server

Verify once again the slave status and processlist :)

References :

External Replica Setup : https://cloud.google.com/sql/docs/mysql/replication/configure-external-replica

Get gtid_purged : https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source-provision-slave.html

Set gtid_purged and troubleshoot : https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source-provision-slave.html

MySQL GTID Replication and lower_case_table_names

Error 'Table 'EMPLOYEES.POSITION' doesn't exist' on query. Default database: 'employees'. Query: 'ALTER TABLE EMPLOYEES.POSITION ADD COLUMN phone VARCHAR(15)'

Interesting , table exist on slave server . But we are getting above error frequently and unable to broken the replication because database size is too big

We are having environment as GTID replication setup with windows server (Master) to Ubuntu Linux machine (Slave) . When we dig into all the findings , concluded its may be with case sensitivity issue . Lower_case_table_names variable value is same on both servers . But as per MySQL documents 

Database and table names are not case-sensitive in Windows , but are case-sensitive in most varieties of Unix.Column,index,stored routine, and event names are not case-sensitive on any platform,nor are column aliases

So , decided to tackle the current situation without data loss in slave server . Changing the lower_case_table_names is not best practice and it will corrupt case_sensitive names data files . 

we have stopped the slave , executed all the DML and DDL statements in slave server to match the schema deployments with master server .

And followed GTID replication error skipping process to skip the transactions manually handled in slave servers 

Capture the error GTID with master binlog files

root@master:/var/log/mysql# mysqlbinlog --start-position=904 mysql-bin.000004

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#200102 11:35:24 server id 1 end_log_pos 123 CRC32 0x14c9c2f7 Start: binlog v 4, server v 5.7.28-0ubuntu0.16.04.2-log created 200102 11:35:24

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

fNUNXg8BAAAAdwAAAHsAAAABAAQANS43LjI4LTB1YnVudHUwLjE2LjA0LjItbG9nAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AffCyRQ=

'/*!*/;

# at 904

#200102 12:06:51 server id 1 end_log_pos 969 CRC32 0x3564eba7 GTID last_committed=4 sequence_number=5 rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= '69b98063-2d31-11ea-9586-12b321a8670d:78'/*!*/;

# at 969

#200102 12:06:51 server id 1 end_log_pos 1042 CRC32 0xb527b5d3 Query thread_id=328 exec_time=0 error_code=0

Skipped the binlog position 904 transactions in slave server

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SESSION.GTID_NEXT= '69b98063-2d31-11ea-9586-12b321a8670d:78';

Query OK, 0 rows affected (0.00 sec)

mysql> begin ; commit;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET gtid_next=automatic;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Finally , GTID Replication begins to sync pending SQL threads and upto date the data with Master server !!!