MySQL Databases

Some MySQL handy oneliners:

• List all users from running instance:

SELECT User, Host, Password FROM mysql.user;

• Create new user called newuser

CREATE USER newuser@localhost IDENTIFIED BY 'superstrongpassword';

• Create new UTF-8 database called mydatabase (at time of writing, 20190214, these settings seem to be the best practice for UTF-8 settings, YMMV, update if needed)

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• Change the encoding of mydatabase (because some software may not support latest best practices, etc)

 ALTER DATABASE mydatabase CHARACTER SET = utf8 COLLATE = utf8_general_ci;

• Give full control of mydatabase to existing newuser

GRANT ALL PRIVILEGES ON mydatabase.* TO newuser@localhost;
FLUSH PRIVILEGES;