If you need to perform real-time ALTER TABLE processes on MySQL (InnoDB, TokuDB) tables, a great tool for the job is the Percona Toolkit. The Percona Toolkit includes a utility (pt-online-schema-change) to perform such a process, without write-locking tables, or having to manually create a temporary table and triggers to synchronize the data during the … Continue reading Alter mysql tables on the fly, without locking them
This is only to remember what I did to migrate from InnoDB to TokuDB on a MariaDB cluster. TokuDB settings are specific to my environment, so please don’t ask me what/how/why. root@dbrfoo:~# mysql -u root -p MariaDB [(none)]> INSTALL SONAME ‘ha_tokudb’; Query OK, 0 rows affected (0.07 sec) MariaDB [(none)]> show engines; +——————–+———+—————————————————————————-+————–+——+————+ | Engine … Continue reading MariaDB 10.0: How to migrate from InnoDB to TokuDB
Could happen to have a mysql database with a lot of tables and we want dump all of them, but we also want to make the logical dump process faster. Some of you could say that mydumper is the solution. It could be. But what if we don’t have the permission to install it or whatever? … Continue reading Dumping multiple mysql tables at once
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. [codesyntax lang=”bash”] for i … Continue reading Kill all mysql queries having query time greater than 1 minute
The data: select id, name from Object where name like “dbrferrari%”; +—–+————–+ | id | name | +—–+————–+ | 8 | dbrferrari5 | | 9 | dbrferrari6 | | 25 | dbrferrari1 | | 26 | dbrferrari2 | | 35 | dbrferrari3 | | 64 | dbrferrari4 | | 80 | dbrferrari7 | | 99 … Continue reading Natural Sorting in MySQL
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
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
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
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
I assume you you have a high availability (master – slave topology) setup. I will be brief and technical on this one :) Context: dbfoo1 is master and dbfoo2 is slave – dbfoo1(M) – dbfoo2(S) dbfoo2 has to be promoted to master – dbfoo2(M) – dbfoo1(S) note that in some cases the slave requires a … Continue reading mysql: Promoting a slave to master