How do I create a user account on MySQL database server?
When you try to access MySQL database server from client such as mysql or even programming language such as php or perl you need a user account. MySQL has sophisticated user management system that controls who can access server and from which client system. It uses special tables in mysql database. In order to create a new user account you need a MySQL root account password. You need to use the GRANT SQL command to set up the MySQL user account.
| Tutorial details | |
|---|---|
| Difficulty level | Easy |
| Root privileges | No |
| Requirements | None |
| Est. reading time | 2 minutes |
Please note that MySQL root user account is different from UNIX/Linux root login account. For example, the MySQL root user and the Linux/Unix root user are separate and have nothing to do with each other, even though the username is the same in each case.
Setup a root user password
To setup root password for first time, use mysqladmin command at shell prompt as follows:$ mysqladmin -u root password NEWPASSWO
If you want to change or update a root user password, then you need to use the following command:$ mysqladmin -u root -p'oldpassword' password newpass
Procedure for setting up a MySQL user account
Login in as mysql root user. At shell prompt type the following command:
$ mysql -u root -p
OR
$ mysql -u root -h your-mysql-server-host-name -p
Create a new mysql database called demo. Type the following command at mysql> prompt:
mysql> CREATE DATABASE demo;
Create a new user called user1 for database called demo:
mysql> GRANT ALL ON demo.* TO user1@localhost IDENTIFIED BY 'mypassword';
How do I connect to MySQL database demo using user1 account?
User user1 can connect to demo database using the following shell command:
$ mysql -u user1 -p demo
OR
$ mysql -u user1 -h your-mysql-server-host-name-here -p demo
Where,
- -u user1 : MySQL Username
- -h : MySQL server name (default is localhost)
- -p : Prompt for password
- demo: demo is name of mysql database (optional)
See also
- Man pages: mysqladmin(1)