Archive for January 2015

Missing InnoDB information_schema Views in MariaDB

While working on a MariaDB 10.0.14 instance today I noticed the INNODB_% tables were missing from information_schema. I could tell the InnoDB plugin was loaded. ?View Code MYSQLSHOW PLUGINS; Name Status Type Library License InnoDB ACTIVE STORAGE ENGINE ha_innodb.so GPL Checking the documentation I could see that these views are supplied by the innodb plugin itself. […]

Spotting missing indexes for MariaDB & MySQL

Here’s a query I use for MySQL / MariaDB to spot any columns that might need indexing.  It uses a bunch of information_schema views to flag any columns that end with the characters “id” that are not indexed. This tests that the column is located at the head of the index through the ORDINAL_POSITION clause. So if […]

Moving an InnoDB Database with FLUSH TABLES .. FOR EXPORT

If we wanted to move a large InnoDB database our options were very limited. Essentially we had to mysqldump the single database or move the entire tablespace. I did have an idea for moving a single InnoDB database by copying files but only ever tried it out with TokuDB. This method worked but seemed to […]

A MariaDB Multi-Master setup example

Here’s a very quick example for how to setup Multi-Master replication with MariaDB.  It’s light on detail here to focus only on the multi-master aspects of the setup. Have a good read of the documentation before attempting this. This example also uses GTIDs so you’ll need some understanding of these as well. The example here is pretty […]

Identify Cross Database Foreign Keys

I’ve blogged before about cross-database foreign keys and what I think of them. I had a developer wanting to check for such references between two databases today. Here’s what I came up with to do this… ?View Code MYSQLSELECT t.NAME AS TABLE_NAME, i.NAME AS INDEX_NAME, f.* FROM information_schema.`INNODB_SYS_FOREIGN` f INNER JOIN information_schema.`INNODB_SYS_INDEXES` i ON i.`NAME` […]