Tag Archives: mysql

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

Topology refactoring using classic MySQL replication commands – version 1

A week ago I wrote a post about promoting a mysql slave as master. Today I will write topology refactoring using classic replication commands. Context: Initial state: dbfoo1(M) – dbfoo2(MS) – dbrfoo3(S) Desired state: dbfoo1(M) – dbfoo2(S) \ dbfoo3(S) Let’s get to work: stop slave on dbfoo3 MariaDB [(none)]> stop slave; check the “freezing point” … Continue reading Topology refactoring using classic MySQL replication commands – version 1

Relay log read failure: Could not parse relay log event entry

It happened today… why? Well, I was testing parallel replication (slave_parallel_threads) and after setting the number of threads to zero and when started the slave I got this wonderful error message: MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master.example.com Master_User: user Master_Port: 3306 Connect_Retry: 60 … Continue reading Relay log read failure: Could not parse relay log event entry

mysql: preload InnoDB buffer pool

Why?! To avoid a lengthy warmup period after restarting the server, particularly for instances with large InnoDB buffer pools. Things to be added to /etc/mysq/my.cnf: # Shortened warm-up times with a preloaded InnoDB buffer pool innodb_buffer_pool_dump_at_shutdown=ON innodb_buffer_pool_load_at_startup=ON Basically when MySQL receives the TERM signal will dump the InnoDB buffer pool /data/db/mysql/ib_buffer_pool and it will load it … Continue reading mysql: preload InnoDB buffer pool