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
Take snapshot of current production instance
Copy snapshot with enabled encryption
Restore the snapshot with different instance identifier
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
Step 2 : Make a copy of snapshot with encryption enabled option
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
Step 3 : We are ready with encryption enabled snapshot copy
Step 4 : Restore rdssnapshotwithencryption
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
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
Step 7 : Instance identifier change
Change instance identifier name to _old for unencrypted instance , and change encrypted instance name to production name
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
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
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
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)
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 AmazonS3FullAccessAmazonRedshiftFullAccess , 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 ,
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
Launched three separate docker containers ( Query Exporter , Prometheus and PMM Server ) for each components . Added additional Dashboards in PMM Server for testing purpose
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
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
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
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
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
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