MySQL BLACKHOLE Engine as Replication Filter



Today, I am going to tell very interesting use-case where we have used Blackhole engine as replication filter.

We have an Aurora Cluster(let's call it C1) where multiple db's are hosted and multiple applications are writing data into it. While in another project one application wanted to read the data from one of the db's hosted on aurora cluster C1 & this new project is hosted into another account.

Now the challenge is we don't want self hosted db which supports replication filters(replicate-do-db) and bring only one db while wanted to use aurora only to in the new project as per the company standards, But problem is Aurora DB doesn't support native replication filters So we were not able to setup replication.
To solve this problem we tried multiple approaches:

 Approach 1: Introducing intermediate Slave with replication filters, But using this approach we were introducing more infra and node management and that too self hosted will DB.

 Approach 2: Use AWS DMS service which is able to solve our problem easily, But it became quite costly.

 Approach 3: Use Blackhole engine and replicate only desired DB. The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result. This approach is quite cool which is using MySQL native replication and not introducing any new tool in between so less management of infra and nodes.
We followed below steps to setup:

 Step 1: Create a new aurora cluster(let's call it C2) from C1 cluster snapshots while don't setup replication from C1.

 Step 2: Take full DB dump of DB we want to restore and schema dump for rest of the DB's from C2 and restore on the cluster where new project has to be pointed.

 Step 3: alter all the tables and set engine to blackhole except DB we want to replicate.

 Step 4: Start replication from co-ordinates found during aurora creation.

And we are done.

Comments

Post a Comment

Popular posts from this blog

Shell/Bash Commands Execution from MySQL Client or Stored Procedure/function

Image
Today I am going to explain ways to execute shell or bash commands from mysql clients or stored procedure and function. There are basically 2 method to do so: Method 1: Use MySQL Client inbuilt feature  To run single command: \! command or system command. eg \! uptime or system command  To get terminal \! bash or \! sh Method 2: Deploy external plugin ( lib_mysqludf_sys ) Step 1: Download lib_mysqludf_sys from github: git clone https://github.com/mysqludf/lib_mysqludf_sys.git Step 2: Install libmysqlclient15-dev, for Ubuntu you can use: apt-get install libmysqlclient15-dev Step 3: Note down o/p of: mysql -uroot -pxxxx -e "select @@plugin_dir;" Step 4: Change directory to git clone dir. cd lib_mysqludf_sys/ Step 5: Compile and put plugin in plugin dir gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o {value from step 3}lib_mysqludf_sys.so -fPIC Step 6: Create necessary functions for lib_mysqludf_sys: mysql -uroo
Read more

How to remove/deregister an instance from PMM?

Image
We all need a monitoring system like pmm to monitor our database. Let me give a brief about how pmm works. So PMM uses prometheus to store metrics/graphs and grafana to display them. As seen in the architecture diagram prometheus uses consul to get the list of host to scrap metrics. More information on architecture and installation are available in docs. We were facing issues related to down hosts and if setup alerts in grafana for down host and host is terminated it continuously throws alerts. So problem was how can we remove/deregister instances from pmm. We can access consul UI using below link: http://PMM-SERVER/consul/#/dc1/ Previously there was option available on this ui to deregister host. while in the new releases of consul it has been removed see link . But now we can use below procedure from command line to deregister hosts from consul. Get list of nodes: curl -s 'http://USER:PASSWORD@PMM-SERVER-IP/v1/internal/ui/nodes?dc=dc1' | python -mjso
Read more

Azure VM Application Consistent MySQL DB Disk Snapshots

Image
Backup of Database is the pillar of our system which is necessary and mandatory to provide us data incase of crash, new machine provisioning and many other scenarios listed here .  As part of the backup process, a snapshot is taken, and the data is transferred to the Recovery Services vault with no impact on production workloads. The snapshot provides different levels of consistency, as described below: 1. Application-consistent: App-consistent backups capture memory content and pending I/O operations. App-consistent snapshots use a VSS writer (or pre/post scripts for Linux) to ensure the consistency of the app data before a backup occurs. When you're recovering a VM with an app-consistent snapshot, the VM boots up. There's no data corruption or loss. The apps start in a consistent state. 2. File-system consistent: File-system consistent backups provide consistency by taking a snapshot of all files at the same time. When you're recovering a VM with a file-system cons
Read more