Create New MySQL User and set Privileges

Introduction

This guide will show you how to make a new user and assign them all privileges to a specified database.

This is not a unique tutorial but is mainly intended as a quick reference for the author. Hope you find it useful though!

Steps

Create new database user along with their password. This will apply to all databases they access.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Give user all privileges to a particular database

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Necessary to apply the changes.

FLUSH PRIVILEGES;

References

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

MySQL List all Databases, List all Tables in a Database, List all columns in a Table (Ubuntu 14.04)

Running MySQL with super user privileges
mysql -u root -p

List all databases
SHOW DATABASES;

List Tables in a Specified Database
SHOW TABLES FROM databasename;

List Columns in a Specified Table from a Specified Database
SHOW COLUMNS FROM dbname.tablename;

Setup MySQL for development (Ubuntu 14.04)

Since I have repeatedly had to do this I figured it’s time to make a guide for it. In case the other ones disappear on the INTERNET. Some of the parts require some testing.

Italicized fields means you have to fill in whatever string you want in that field.

    Ubuntu 14.04

Installation (From what I recall)
sudo apt-get update
sudo apt-get install mysql-server

You will be prompted during the installation to set a password for the super user.

Running MySQL with super user privileges
mysql -u root -p
You will be prompted for a username and password

Create a New Database
CREATE DATABASE databasename;

Create a New User
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Enlist user to a specific database
GRANT ALL PRIVILEGES ON databasename.* TO 'newuser'@'localhost';
You can further specify the tables you want to give the user but I assume that most developers would be looking for that.

The privilege changes you made won’t be applied until you do the following
FLUSH PRIVILEGES;