Logging in to MySQL as Root

To communicate with the MySQL db engine running in the computer’s memory as DOS window in required. This is because the MySQL client software is a character based one.  To open a DOS window in MS. Windows XP do the following:

Start -> Run -> type in cmd and press enter.

A DOS command window opens with a flashing cursor indicating that the system is waiting for your character based commands.

At the command prompt enter:

<Prompt> mysql –uroot –p{root password here}

The command mysql invokes the MySQL character based client software.

1. -u is a flag with which a user name is passed to the MySQL client
2. NOTE: There is no space between the –u and the user name
3. -p is a flag with which the password for the specific user is passed to the MySQL client

The MySQL client software checks the username and password combination.If this is Okay the command prompt changes to {drive letter}:\mysql.  

This indicates that the operating system is no longer listening to you but it is the MySQL client that is ready and waiting for your commands.

Setting Up the Root Password in MySQL:

Whenever MySQL is installed an Administrator's login is created automatically. On Windows the administrator’s is accessed via a password set at installation time.  The administrator's login name is root.  

Setting the root Password

This can be done by using the mysqladmin utility or by directly updating the user table in the mysql database.

Using mysqladmin to set the root password

The mysqladmin utility can be used to set up passwords for any MySQL user account. In this case root’s password is being set.

The following is the syntax used for setting a password using mysqladmin:

Syntax:
<System Prompt> mysqladmin -u <username> password <user defined password>

Example:
mysql> mysqladmin -u root password abR!abr#
(Do set a password of at least 8 alphanumeric characters for security purposes).

The system prompt returns indicating that the root password has been set.

Re-Setting the root Password

If the root password is already assigned (as in case of Windows) and the password needs to be changed then issue the following command.

Syntax:
<System Prompt> mysqladmin -u <username> --password=<old password> password <new user defined password>

Example:
mysql>mysqladmin -u root --password= abR!abr#  password xyR!xy#

Logging Into MySQL after Setting the Root Password

Syntax:
<System Prompt>mysql -u <user name> -password <user defined password>

Example:
mysql>mysql -u root --password= xyR!xy#

NOTE: This is definitely an unsafe way to pass the root password at login time. Anyone who is looking at the terminal can read the root password and then use it later to get complete control of the MySQL database.

A better way of logging is as shown in Diagram 1.

The use of the switch -p informs the mysql executable to prompt for a password. This password cannot be seen when it is being keyed in.

<System Prompt> mysql -u root –p
Enter Password: <Here key in the root password set earlier>

Diagram1: Logging in via root

• Next >