How to set Environment Variables and Establish DB connection from Command Prompt

Till now we have been issuing SQL queries using the default MySQL Command Line Client which comes handy on installing MySQL. Even to execute a simple SQL query we have been using MySQL Command Line Client.

Don’t you think that there has to be some better option to work on SQL queries rather than invoking MySQL Command Line Client always? 

Well there are several better DB clients available in the market today which are both open source and proprietary. Some of these DB clients even guide you in writing SQL queries. But for beginners I don’t recommend this shortcut :)

Let us go with the hard way but still we will try to take out the dependency of MySQL Command Client and try to make better use of the Windows Command Prompt to issue SQL queries. You heard me right, we can use Windows Command Prompt to establish MySQL Database Connection and to issue SQL queries.

The simple way would be to traverse to the MySQL/Bin directory from Command Prompt and start using it as shown below.

Second approach would be to take out the above location dependency. No matter where your command prompt is pointing to; you should still be able to establish MySQL connection and start issuing SQL queries.

This can be achieved by Setting the Environment variables which will determine the path to look in for MySQL program. This allows you to connect and play around with your MySQL database by issuing queries from the system command prompt independent of its location.

All you have to do is Go to the Control Panel, Double Click on System Icon. Go to the Advanced Tab
Click on Environment Variables

Under System Variables, Select Path and Click on Edit as shown below.


















Now add your MySQL installed directory location and Click on OK to finish.

Now go to Windows Run prompt and type cmd to invoke the command prompt. For example, If I want to establish Database connection from C: Drive instead from the installed directory, I need to traverse to the C drive in command prompt and Type the following to establish MySQL database connection.

• mysql -h localhost -p -u root testers_db 

(The arguments -h used to provide machine information where MySQL server is running, if it is locally installed provide it as localhost if not provide the IP address. -p to prompt for a password, -u to provide the user information and at the end if you are aware of which database to connect provide the database name. In the above example DB name is testers_db)

Database connection can also be established from command prompt by using the below commands.

• mysql –p –u root

Provide the appropriate password when prompted. After establishing connection to select a database (Ex: testers_db) type the following.
use testers_db;

• mysql --user = root --password = admin

• mysql --user = root --password = admin testers_db

Note: Providing DB name is optional  and  it is always better to provide -p to prompt password rather than exposing the password directly.

Let me know if you face any issues in setting the environment variables. I shall be happy to help you also do let me know your opinion in the form of comments.

Technorati Tags: Setting Environment Variables,Invoke mysql from Command line.