✂️ MySQL

Small information nuggets and recipies about MySQL

(most recent on top)

Show storage engine information (per table)

show engines;
select table_name, engine
from information_schema.tables
where table_schema = 'dbname';

Drop all tables

… execute the command below and then run the generated statements

select concat('drop table if exists ', table_name, ' cascade;')
from information_schema.tables
where table_schema = 'database_name';

Recreate a blank database

drop schema dbname;
create database dbname 
default character set utf8 collate utf8_general_ci;

Show default character set

show variables like 'char%';
show variables like 'coll%';

Change the database default character set

alter database dbname 
default character set utf8 collate utf8_general_ci;

Change table character set

… for each table

alter table tablename 
convert to character set utf8 collate utf8_general_ci;

Show permissions for a user

show grants for username@localhost;

Show list of databases in server

show databases;

Change permissions on a database

  • Stop using FLUSH PRIVILEGES – I used to be a MySQL DBA for Hire
    • No need for doing flush privileges when using “higher level” commands
    • Only when directly manipulating the permissions tables with “low level” commands (insert, update, delete) does the flush privileges command need to be done to reload the permissions copy MySQL keeps cached in memory
grant all privileges on `dbname`.* to 'username'@'localhost';
revoke all privileges on `dbname`.* from 'username'@'localhost';

List users in the database

select user, host, password from mysql.user;

Rename a user

rename user 'oldname'@'localhost' to 'newname'@'localhost';