MySQL create new user

20.Dec.2010

Lets say you need to create a new user for you database, but you don’t have any tool that could help you. If you do have SSH, than you can use CLI for MySQL to create new user. Copy following snippets in order to create new user for your MySQL database.

First you need to login to MySQL:

mysql --user="admin-username" --password="admin-password"

Then, just do the following:

CREATE USER 'new-username'@'localhost' IDENTIFIED BY 'new-password';
GRANT ALL ON *.* TO 'new-username'@'localhost' WITH GRANT OPTION;

There is a list of privileges that you can grant to a user:

PrivilegeMeaning
ALL [PRIVILEGES]Sets all simple privileges except GRANT OPTION
ALTEREnables use of ALTER TABLE
CREATEEnables use of CREATE TABLE
CREATE TEMPORARY TABLESEnables use of CREATE TEMPORARY TABLE
DELETEEnables use of DELETE
DROPEnables use of DROP TABLE
EXECUTENot implemented
FILEEnables use of SELECT … INTO OUTFILE and LOAD DATA INFILE
INDEXEnables use of CREATE INDEX and DROP INDEX
INSERTEnables use of INSERT
LOCK TABLESEnables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESSEnables the user to see all processes with SHOW PROCESSLIST
REFERENCESNot implemented
RELOADEnables use of FLUSH
REPLICATION CLIENTEnables the user to ask where slave or master servers are
REPLICATION SLAVENeeded for replication slaves (to read binary log events from the master)
SELECTEnables use of SELECT
SHOW DATABASESSHOW DATABASES shows all databases
SHUTDOWNEnables use of MySQLadmin shutdown
SUPEREnables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the MySQLadmin debug command; allows you to connect (once) even if max_connections is reached
UPDATEEnables use of UPDATE
USAGESynonym for ââ?¬Å?no privilegesââ?¬Â?
GRANT OPTIONEnables privileges to be granted