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.
Next you need to use the GRANT SQL command to set up the MySQL user account.
Finally, use the account’s name and password to make connections to the MySQL server.
Please note that MySQL user accounts are different from UNIX/Linux login accounts. 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.
Procedure for setting up a MySQL user account
Login in as mysql root user (at shell prompt type following command:):
$ mysql -u root -p
$ mysql -u root -h myserver-sever.com -p
Create a new mysql database called demo
mysql> CREATE DATABASE demo;
mysql> USE demo;
mysql> GRANT ALL ON demo.* TO user1@localhost IDENTIFIED BY 'mypassword';
Note: GRANT ALL means all privileges i.e. user is permitted do anything. She can read, modify or delete data, but only on tables in the demo database. She cannot access any other database.
How do I connect to MySQL server using user1 account?
User user1 can connect to mysql server demo database using following command:
$ mysql -u user1 -p demo
$ mysql -u user1 -h mysql.server.com -p demo
- -u user1: MySQL Username
- -h : MySQL server name (default is localhost)
- -p : Prompt for password
- demo: demo is name of mysql database (optional)