MySQL Databases

From Run Your Own
Jump to: navigation, search

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;