Archive for the ‘MySQL’ Category

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 […]

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` […]

mysqldump: backing up specific tables

Here’s a quick example of how to backup specific MySQL / MariaDB tables and piping to xz to compress… ?View Code BASHmysqldump -h 127.0.0.1 -P3306 mysql user tables_priv | xz –compress -9 > /db_dumps/3104/mysqldump_tables_example.sql.xz Here’s how to decompress the resulting file. Note xz will delete the input file unless you specifiy –keep on the command […]

Bash script to execute a MariaDB query multiple times

This simple bash script will execute a query 100 times against a MySQL instance. It also uses the time command to report how long the entire process took. I use this for some very simple bench-marking. The query used here creates a temporary table and inserts 100K rows into it. You need the sequence engine […]