Point In Time Recovery - is a nightmare for DBAs if the MySQL clusters are self managed. It was 10PM, after had my dinner I was simply watching some shows in YouTube. And my phone was ringing, the customer on other side. Due to some bad queries, one of the main table get updated without where clause. Then suddenly everyone joined the call and asking me to bring the data back. That day it took 6 to 8 Hours to bring the data. Yes, every DBAs will do one or two biggest mistakes. In my carrier I would say this was that day. So here is my MySQL PITR the fastest way with DevOps.

Where I failed in this DR setup?

• PITR starts with last full backup + binlogs
• I missed in my backup script to add --master-data, So I don’t know how to start applying binlogs.
• No Delay replica. I got the call within 10mins when the data has been messed up. But all of my replicas are real time sync. Its affected all of …

This is my next blog series. Im going to write about how I automated many complex tasks in MySQL with Rundeck. In my last series, I have explained RunDeck basics. You can find those articles here. In this blog Im writing about how I automated MySQL archive for multiple tables in one Rundeck job. Challeange …

The post MySQL With DevOps 1 – Automate Database Archive appeared first on SQLgossip.

This is my next blog series. Im going to write about how I automated many complex tasks in MySQL with Rundeck. In my last series, I have explained RunDeck basics. You can find those articles here. In this blog Im writing about how I automated MySQL archive for multiple tables in one Rundeck job.

Challeange with Replication:

My MySQL database setup has 1 Master 4 Read Replica and the 3’rd replica is an intermediate Master for Replica 4. I don’t want to archive this data on Replica 3 and 4. Because these replicas are using for generating historical reports also some internal application.

Disable Log-Bin:

To prevent archive data on Replica 3 and 4, I decided to disable binlog on my archive session. But another challenge is, it won’t replicate to Replica 1 and 2. So my final solution is Archive the data on Master, then execute the …

While managing multi servers in a single place, we need a secure authentication method which includes SSH Keys, Passwords and etc. RunDeck is having a great feature called Key Storage. RunDeck Key Storage is a secure and encrypted place for storing confidential contents. Its using HashiCorp Vault for this. Its already enabled by default. So …

The post RunDeck Series 5 – Encrypt Key Files And Passwords In RunDeck appeared first on SQLgossip.

While managing multi servers in a single place, we need a secure authentication method which includes SSH Keys, Passwords and etc. RunDeck is having a great feature called Key Storage. RunDeck Key Storage is a secure and encrypted place for storing confidential contents. Its using HashiCorp Vault for this. Its already enabled by default. So we just upload our keys and creating encrypted passwords.

Encrypting PEM Files:

• Click the Gear Icon and Go to Key Storage.

• Then you can select that you want to store Key files or Password.

• Key Type: Public Key
• Then you can upload your Public Key file or just copy the file contents and paste it.
• Storage path: Keys/ is the default storage location for all encrypted files. But we can make folders inside the keys/. (Eg: …

I have done many automations MySQL Automations with Rundeck. This blog series will explain about the DevOps In MySQL with Rundeck. Rundeck is one of my favourite Automation tools. Here we are going to see how can we install and configure rundek on a CentOS server with mysql as a backend. Even I like Jenkins, …

The post RunDeck Series 1 – Install And Configure RunDeck 3.0 On CentOS 7 appeared first on SQLgossip.