MySQL - Bag Of Tricks

Introduction

This document has many useful command.

MySql Kill users

You can use the following syntax to lookup users within a database

SHOW PROCESSLIST;
SELECT group_concat(concat('KILL ',id,';') SEPARATOR ' \n') FROM information_schema.processlist WHERE `db` LIKE 'ashe%' AND `user` ='kiwilive';

Then cut and paste into a query window

Get all database names from a full dump file

grep -E "^-- Current Database:" all.sql

You can do the extract this way with one line if you have a prefix of the database names

awk '/^-- Current Database: `valley_/ { out = substr($4, 2, length($4)-2) ".sql"; print "Extracting to " out } out { print > out } /^-- Current Database: `[^v]/ { out = "" }' all.sql