Archive for the ‘MySQL’ Category

MySQL 5.7: root password is not in mysqld.log

I came across this issue today when working on an ansible playbook with MySQL 5.7. Old habits die hard and I was still trying to use mysql_install_db to initialise my instance. It seems a few others have been doing the same. The effect of using mysql_install_db in more recent version of MySQL is that we end […]

MariaDB: subquery causes table scan

I got asked today to look at some slow queries on a MariaDB 10 instance. Here are the anonymized results of the investigation I did into this and how I solved the issue… There were a bunch of queries, expected to run very fast, that were taking 10-15 seconds to execute. The queries were all […]

Recover a single table from a mysqldump

I needed to recover the data, from a single table, from a mysqldump containing all the databases from an entire instance. A quick google yielded this result. This produced a nifty little sed one-liner… sed -n -e ‘/CREATE TABLE.*your_table_name/,/CREATE TABLE/p’ mysqldump_file.sql > your_table_name.sql I also wanted to import the data into a different table. Again […]

TokuDB file & table sizes with information_schema

Here’s a few queries using the information_schema.TokuDB_fractal_tree_info to get the on disk size in MB for TokuDB tables. This first one will sum up the on disk size for tables using the TokuDB engine. SELECT table_schema, table_name, SUM(ROUND(bt_size_allocated / 1024 / 1024, 2)) AS table_size_mb FROM information_schema.`TokuDB_fractal_tree_info` WHERE table_schema = ‘database_name’ GROUP BY table_schema, table_name; […]

The behavior of gtid_strict_mode in MariaDB

GTIDs in MariaDB are a nice improvement to replication and make fail-over a simpler process. I struggled a little with the explanation of gtid_strict_mode and what to expect. So I thought I’d run through a simple scenario to make my own understanding clear. In this scenario I have created two instances of MySQL; primary and secondary. […]