Headlines
Loading...


How-To-Create-User-In-MySQL

Create User In MySQL 

Syntax of the Create User statement:


CREATE USER  [ IF NOT EXISTS ] account name IDENTIFIED BY 'password' ;

In the syntax first we mention account after the key word CREATE USER. The account name consists of two parts First is username and second is hostname separated by @ sign.

username@hostname

Hostname part in account name is optional if we removed it then the user can connect from any host. Account without hostname is

username@%

If username and hostname contain special characters like space or - etc then you need to quote both separately.

'username'@'hostname'

IF NOT EXISTS option is create USER only if users not exist.

Note: CREATE USER statement create a new user without any privileges. By using GRANT statement you grant the privilege to user.


Create User In MySQL 


1). By using MySQL client tool connect to MySQL server.

mysql  -u  root  -p

2). Enter the password of root account and hit enter button.

Enter password: ********

3). Now show users from current MySQL server by using

mysql>  select   user  from  mysql.user;

4). Now create new user called as alex.

mysql> create user alex@localhost identified by 'secure1pass !' ;

5). Show all users again by using

mysql>  select   user  from  mysql.user;

Now you see the user alex in user list. The user alex is successfully created.

6). Open new session and login MySQL again as alex.

mysql  -u  alex  -p

Now enter the password for user alex and hit enter button.


7). Show the databases that alex has access:

mysql> show databases;

Here is the list of databases that alex can access:

| Database                       |
|  information_schema | 
 1 row in set (0.01 sec)

8). Go to the session of the user root and create a new database called bobdb:

mysql> create database alexdb;

9). Select the database alexdb:

mysql> use alexdb;

10). Create a new table called lists:

mysql> create table lists( 
-> id int auto_increment primary key, 
-> todo varchar(100) not null, 
-> completed bool default false);

Note that when you press Enter, instead of showing the mysql> command, the mysql tool shows the -> that accepts new clause of the statement.

11). Grant all privileges on the alexdb to alex:

mysql> grant all privileges on alexdb.* to alex@localhost;

Note that you will learn how to grant privileges to a user in the GRANT tutorial.

 12).Go to the alex’s session and show databases:

mysql> show databases;

Now, alex can see the alexdb:

+----------------------------------+ 
| Database                      | 
+----------------------------------+ 
| alexdb                           |
| information_schema | 
+----------------------------------+ 
2 rows in set (0.00 sec)

13). Select the database alexdb:

mysql> use alexdb;

show the tables from the alexdb database:

mysql> show tables;

The user alex can see the lists table:

+----------------------------+ 
| Tables_in_alexdb | 
+----------------------------+ 
| lists                         | 
+----------------------------+ 
1 row in set (0.00 sec)

14). Insert a row into the lists table:

mysql> insert into lists(todo) values('Learn MySQL');

15). Query data from the lists table:

mysql> select * from lists;

This is the output:

+----+--------------------------+------------------+ 
| id | todo                     | completed | 
+----+--------------------------+------------------+ 
| 1  | Learn MySQL     | 0                  | 
+----+--------------------------+------------------+ 
1 row in set (0.00 sec)

So the user alex can do everything in the alexdb database.

16). Disconnect from the MySQL Server from both sessions:

mysql> exit

In this tutorial, we learned how to use the MySQL CREATE USER to create a new user in the database server.





0 Comments: