Monday, January 31, 2022

Migrate Azure MySQL Database to Amazon RDS for MySQL Using MySQL Replication

Its Nightmare Story struggled a lot to reduce the cut over time with Azure MySQL to AWS Migration MySQL Migration.

Tested Methods, 

  1. Backup and Restore - Its took 6 hours to complete
  2. AWS DMS - Text Data truncated and timestamp column data was 6 hours behind from the source

But Nothing helped due to Bugs, features unavailable etc.., But by default replication helped us to work. 

Two types replication available in Azure MySQL ,

1.GTID based Replication

    This is we already discussed with multiple topics in our blog for GCP cloud, also we will explain how to configure GTID based replication from Azure MySQL to AWS RDS in next blog

MySQL GTID Replication and lower_case_table_names

Multi Source Replication from GCP Cloud MySQL to Percona XtraDB Cluster

2.Binlog position-based replication

    We are performing binlog position-based replication to cut over the application in this blog

Environment Setup 

Below POC environment setup done for testing 
  • Azure MySQL 5.6.4 General Purpose instance launched with minimal configuration
  • AWS RDS for MySQL 5.7 launched with customized parameter group 
  • Network connectivity between Azure PaaS and AWS PaaS Service

Once environment is up and running, modify the below server parameters in both side 

Below values needs to update in Both environments, 

Azure MySQL: 

  • replicate_wild_ignore_table=mysql.%,information_schema.%,performance_schema.%,sys.%
  • binlog_row_image = FULL
  • binlog_expire_logs_seconds = 1440


  • time_zone = Same as Azure MySQL
  • binlog_format = ROW
  • collation and character_set parameters = Same as Azure MySQL

Now both server is ready to configure the replication

Pre-Steps to configure the replication

1.Let’s verify and take a note of binlog position in Azure MySQL   

 mysql> show master status.


| File                        | Position    | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |


| mysql-bin.00865 | 52884741       |              |                  |                   |


1 row in set (0.00 sec)

2.Create a replication user with necessary privileges

CREATE ‘repluser’@’%’ IDENTIFIED BY ‘datablogs123’.



GRANT SELECT, CONNECT ON *.* TO ‘repluser’@’%’;


3.Using mysqldump start the schema backup and followed by data backup 

mysqldump -h -u repluser@datablogs -p datablogsprod --no-data --routines --triggers --events > datablogsprodmeta.sql

mysqldump -h -u repluser@datablogs -p datablogsprod --routines --triggers --events --skip-add-drop-table  --max_allowed_packet=1024M --single-transaction  > datablogsproddata.sql

4.Once backup is completed restore the data into MySQL RDS , 

mysql -h -u repluser -p datablogsprod  < datablogsprodmeta.sql

mysql -h -u repluser -p datablogsprod  --max_allowed_packet=1024M  < datablogsproddata.sql

Configuring the replication 

In Amazon MySQL set the external master with the help of Step 1 binlog file name and binlog position 

CALL mysql.rds_stop_replication;

CALL mysql.rds_reset_external_master;

CALL mysql.rds_set_external_master ('', 3306, 'repluser@datablogs', 'repluser', 'mysql-bin.00865', 52884741, 0);

CALL mysql.rds_start_replication;

Once executed start the replication and check the slave status in RDS ,

    mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master_User: repluser@datablogs Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.00865 Read_Master_Log_Pos: 52884741 Relay_Log_File: relaylog.000004 Relay_Log_Pos: 273 Relay_Master_Log_File: mysql-bin.00865 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: datablogsprod Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,innodb_memcache.cache_policies,mysql.rds_history,innodb_memcache.config_options,mysql.rds_configuration,mysql.rds_replication_status Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 52884741 Relay_Log_Space: 19742 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1053643462 Master_UUID: 787858b4-a021-11ea-b894-97f71069e028 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

Post Validation and troubleshooting steps of Azure MySQL Replication

Validate the data and tables counts between Azure MySQL and MySQL RDS 

If we get any errors related with system schema in the Azure use the following method to fix it , we got same error like below ,

Can't find 'mysql.__firewallrules__' doesnt exist 

We can skip the error with below commands in MySQL RDS , 

CALL mysql.rds_stop_replication;
CALL mysql.rds_skip_repl_error;
CALL mysql.rds_start_replication;

But in MySQL RDS Row by row only errors will be skip and it will take long time to do it , follow the below method and check the binlog position until for the DML , DDL queries are captured in binlog 

SHOW BINLOG EVENTS IN 'mysql-bin.00865' FROM 52884567 LIMIT 10 \G

Note : Based on the error modify the binlog file and position in above command

Once found the binlog position repeat the configuring replication step

Now All set for Azure to AWS data sync , now its ready for application cutover with zero downtime !!!


Friday, January 21, 2022

Export/Import Data on Amazon Oracle RDS using Data Pump utility

Its easy to refresh the schemas in Oracle RDS , We can do one time for Testing or Development Purpose 

But if its needs to run on daily basis , here is the automated code for achieve the solution  

GitHub Link for Source Code :

Sunday, January 16, 2022

Upgrade Oracle Apex 5.1.4 to Oracle Apex 21.1.3 in AWS RDS

Its quick story and easy steps too to upgrade Oracle Apex in AWS RDS . But dependent ORDS (Oracle Rest Data Service) in-place upgrade little difficult task , we will solve that in next blog 

Environment Setup ,

1.Oracle RDS launched with 12.1.0 Engine version

C:\Users\datablogs>aws rds describe-db-instances
“DBInstances”: [
“DBInstanceIdentifier”: “datablogs-db”,
“DBInstanceClass”: “db.t3.medium”,
“Engine”: “oracle-se2”,
“DBInstanceStatus”: “available”,
“MasterUsername”: “dataadmin”,
“DBName”: “ORCL”,
“Endpoint”: {
“Address”: “”,
“Port”: 1521,
“HostedZoneId”: “Z2R2ITUGPM61AM”
"EngineVersion": ""

2.Create an option group and add APEX option value as 5.1.4 . Attached the option group into oracle 12c RDS 

C:\Users\datablogs>aws rds describe-option-groups --option-group-name oraapex
 “OptionGroupsList”: [
 “OptionGroupName”: “oraapex”,
 “OptionGroupDescription”: “oraapex”,
 “EngineName”: “oracle-se2”,
 “MajorEngineVersion”: “12.1”,
 “Options”: [
 “OptionName”: “APEX”,
 “OptionDescription”: “Oracle Application Express Runtime Environment”,
 “Persistent”: false,
 “Permanent”: false,
 “OptionVersion”: “5.1.4.v1”,
 “OptionSettings”: [],
 “DBSecurityGroupMemberships”: [],
 “VpcSecurityGroupMemberships”: []

By default , depends on the Apex version oracle RDS will be create the databases name as like version name ( Exp., Oracle Apex 5.1.4 - APEX_050100 . Oracle Apex 21.1.3 - APEX_210100 )

3.Once we launched the Oracle RDS , we can check Apex version using below command ,

Select comp_name,status,version from dba_registry order by comp_name;

Note : We can keep Oracle Apex schemas in the default user ( APEX_050100 ) else we can create separate schema and maintain it . But even if you upgrade major version database schema wont affect in oracle Apex .

4.Assume we are having workspaces and application connected with the current version

In current version 442 tables available , we are going to upgrade to Oracle 19c

5.Repeat Step 2 , Create an option group and add APEX option value as 21.1.3 . Attached the option group into oracle 19c RDS and begin the upgrade in console

5.1.Modify the Oracle instance in AWS RDS Console

5.2.Modify the additional options with new option group

6.Upgraded the Oracle RDS to 19c

C:\Users\datablogs>aws rds describe-db-instances
 “DBInstances”: [
 “DBInstanceIdentifier”: “datablogs-db”,
 “DBInstanceClass”: “db.t3.medium”,
 “Engine”: “oracle-se2”,
 “DBInstanceStatus”: “available”,
 “MasterUsername”: “dataadmin”,
 “DBName”: “ORCL”,
 “Endpoint”: {
 “Address”: “”,
 “Port”: 1521,
 “HostedZoneId”: “Z2R2ITUGPM61AM”
"EngineVersion": ""
C:\Users\datablogs>aws rds describe-option-groups --option-group-name ora19apex
 “OptionGroupsList”: [
 “OptionGroupName”: “oraapex”,
 “OptionGroupDescription”: “oraapex”,
 “EngineName”: “oracle-se2”,
 “MajorEngineVersion”: “12.1”,
 “Options”: [
 “OptionName”: “APEX”,
 “OptionDescription”: “Oracle Application Express Runtime Environment”,
 “Persistent”: false,
 “Permanent”: false,
 “OptionVersion”: “21.1.v1”,
 “OptionSettings”: [],
 “DBSecurityGroupMemberships”: [],
 “VpcSecurityGroupMemberships”: []

7.Repeat Step 3 , we have upgraded to Oracle Apex 21.1.3

As we mentioned in previous note in Step 3 , new default schema created for latest version and existing schema tables didn't have any impact

Its easy task in AWS RDS to upgrade Oracle Apex , But in the next blog we will in-place upgrade of ORDS in detail

Thanks for Reading !!!

Wednesday, December 29, 2021

Capture Basic MySQLRemoteServerDetails

Basic Requirement to get the below details from MySQL Remote Host Using Python

  1. OS Version
  2. Find Physical host / VMWare
  3. MySQL Port
  4. Total RAM / Total CPU and Cores
  5. How many Database and Size ?
  6. Disk space

# Required libraries for program
import paramiko
from mysql.connector import connect, Error

# Required libraries to connect
client = paramiko.SSHClient()

# Remote server details to enter
print('Enter the RemoteHost Login Details')
re_host_IP = input("RemoteHost IP: ")  # If any IP works , pass that to RemoteHost IP
re_host_user = input("RemoteHost Username: ")
re_host_password = input("RemoteHost Password: ")  # If no password for AWS machine just enter to fill the next details
re_host_key_file = input(
    "RemoteHost keyfile name: ")  # If no keys just enter to move next step . We have tested AWS machine , so its required to pass pem key file to connect .

    # Remote Host Server Connectivity
    client.connect(re_host_IP, username=re_host_user, password=re_host_password, key_filename=re_host_key_file)
    stdin, stdout, stderr = client.exec_command('cat /proc/version')
    print('Remote Server OS and Version')
    print('Remote Server Total CPU and Cores')
    stdin, stdout, stderr = client.exec_command('lscpu')
    print('Total CPU/Core count for Remote server: ', stdout.readlines()[3])
    print('Connected Server Total RAM in GB')
    stdin, stdout, stderr = client.exec_command('vmstat -s')
    print('Total RAM for Remote server: ', stdout.readlines()[0])
    print('Connected Server Total Disk Spaces')
    stdin, stdout, stderr = client.exec_command('df -h --total')
    print('Total Disk for Remote server: ',

    # MySQL Basic Details code below
    with connect(
            user=input("Enter Remote MySQL Username: "),
            password=input("Enter Remote MySQL Password: "),
    ) as connection:
        get_database_host = "select @@hostname"
        with connection.cursor() as cursor:
            database_host = cursor.fetchone()
            print("Successfully connected Remote MySQL Server :", database_host[0])
        print(database_host[0], 'Database Name and Size in MB')
        get_database_size = "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;"
        with connection.cursor() as cursor:
            database_size = cursor.fetchall()
            for row in database_size:
                dbname, dbsize = row
                print('{} - {}'.format(dbname, dbsize))
        select_port = 'SELECT @@port'
        with connection.cursor() as cursor:
            database_port = cursor.fetchone()
            print('Database Port :')
            print(database_host[0], 'Server MySQL Port is :', database_port[0])

except Error as e:

Wednesday, October 14, 2020

RDS Snapshot Share with Automated or Manual Backups ?

Objective :

Launch production server clone in to another AWS account using RDS snapshots

We can do ?

There are multiple ways to do RDS data extract in AWS . We can take using manual snapshot , automated snapshot , export data to S3 and AWS backup service

What we can do most of time ?

To create clone of production server , it is easy way to create snapshot and launch new instance

But , Our Requirement to copy the snapshot in to different AWS account and launch copy of production .

Mostly we try with RDS automated snapshots to copy or share into different account . But in this case we can't share automated snapshots to different account

Automated snapshots

We can export data to S3 as well , but its required some efforts as well .

So possibilities is to take a manual snapshot and share into different account like below steps . Below screenshot refers manual snapshot of MySQL RDS in source AWS account

Source AWS account

Snapshot sharing into target AWS account

share snapshot to target account

Shared RDS snapshot on target AWS account

target AWS account

Then we can proceed to launch clone of production instance into different account .

Monday, August 3, 2020

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


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 :