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
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
It looks like Marvel is generating some data everyday. Is there a way to reduce the amount of data generated by marvel? The short answer to the above question is Yes! [codesyntax lang=”bash”] curator –host 127.0.0.1 show indices –older-than 30 –time-unit days –timestring ‘%Y.%m.%d’ –prefix .marvel curator –host 127.0.0.1 close indices –older-than 30 –time-unit days … Continue reading Marvel indices taking lot of space? Delete indices older than 7 days!
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 don’t know about other distributions, but I know that the debug symbols were stripped from Debian/Ubuntu packages. If there are some crashes reported I won’t be able to fully analyze them. The only way to fix this problem is to build the packages again. [codesyntax lang=”bash”] git clone https://github.com/MariaDB/server.git cd server/ git branch -a … Continue reading How to add debug symbols for MariaDB Debian/Ubuntu packages
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