Category Archives: Databases

Custom Elasticsearch template with custom field mapping

Disclaimer: I am writing this post mostly for me to remember how and what I did to solve wrong field mapping in elasticsearch. Quote from Elasticsearch Dynamic Mapping documentation page When Elasticsearch encounters a previously unknown field in a document, it uses dynamic mapping to determine the datatype for the field and automatically adds the … Continue reading Custom Elasticsearch template with custom field mapping

Kill all mysql queries having query time greater than 1 minute

At this point there are two approaches to achieve this. One is using pt-kill from Percona Toolkit, and the other one is to use a bash script with a lot of pipes :) Why would someone use the second approach? I don’t know, perhaps because there is no Percona Toolkit available. If you can/want to … Continue reading Kill all mysql queries having query time greater than 1 minute

mysql hot backup with xtrabackup

I want to backup my databases without downtime… Well, there are couple approaches to this. One of them is to add a standby slave and use LVM snapshot approach. The second approach is to use percona xtrabackup. My problem is that I have some huge databases here, so taking local backups is out of the question. … Continue reading mysql hot backup with xtrabackup

How to recover the space used by UNDO_LOG in InnoDB tablespaces

The panic started when the monitoring server sent an alert about the storage of one of our MySQL servers – saying that the disk is about to get filled. I realized that a a lot of the disk space was used by the InnoDB’s shared tablespace ibdata1. But wait… I have innodb_file_per_table enabled. So, what … Continue reading How to recover the space used by UNDO_LOG in InnoDB tablespaces

Topology refactoring using classic MySQL replication commands – version 2

This one in not very different than Topology refactoring using classic MySQL replication commands – version 1, actually it’s the other way around. Context: Initial state: dbfoo1(M) – dbfoo2(S) \ dbfoo3(S) Desired state: dbfoo1(M) – dbfoo2(MS) – dbrfoo3(S) Let’s get it done: stop slave on dbfoo3 MariaDB [(none)]> stop slave; check the “freezing point” on … Continue reading Topology refactoring using classic MySQL replication commands – version 2