Useful MySQL commands

• Show database sizes

  SELECT table_schema AS DB, SUM(index_length + data_length)/(1024 * 1024) AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;
  

• Common privileges for web application DB user:

  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON databasename.* TO 'userid'@'host' IDENTIFIED BY 'password';

• Export grants in an importable format:

  mysql -B -e 'SELECT DISTINCT CONCAT('\''show grants for `'\'', user, '\''`@`'\'', host, '\''`;'\'') AS query FROM mysql.user;' | grep -v query | mysql