Thursday, 10 January 2013

How to Connect to XAMPP/MySQL using Windows CMD [Command Prompt]

Work with MySQL terminalOn our previous post, we have discussed about how to install LAMP Environment on your Windows PC easily using XAMPP. If you are a MySQL coder, or wish to learn it, then probably you have came to know about the MySQL terminal [or simply a command prompt in Windows]. Basically it is the best MySQL tool for any newbie user. You have type all your SQL Queries here manually and this way you can actually learn faster.
But opening the MySQL prompt can be a little tricky in Windows PC. As just typing “mysql” in command prompt does not work… So here is a complete guide to easily open MySQL terminal from Windows Command Prompt.

#1: Install XAMPP and locate installation Directory:

First you need to install XAMPP on your Windows PC. You can install MySQL alone if you want, but I shall recommend XAMPP for a better and easier usage. Simply follow the previous post to install it. AlsoNote down the installation directory.
The default installation directory is “C:\XAMPP\”. It may vary for your customized installation. So assuming this, try to locate this folder:
C:\xampp\mysql\bin\” or “~Installation Folder~\xampp\mysql\bin
where ~installation Folder~ is obviously the folder where you have selected to install XAMPP.
You should be able to find a file named, mysql.exe . This is the main MySQL Terminal.Locate mysql.exe fileIf you simply open it by double clicking then although a MySQL Terminal will be launched, but you won’t actually be able to modify your database, as in this way MySQL terminal does not get connected to the DB server host!
Just follow the instruction below to open the terminal properly…

#2: Open MySQL terminal from Windows Command Prompt(cmd):

  • Go to Start Menu > All Programs > Accessories > Command Prompt;
  • This will pop up the Windows CMD.
  • Now type in the following command and hit enter:
    CD C:\xampp\mysql\bin
    Change the working directory using cd
  • Note that if you have any other installation directory, then you need to replace C:\XAMPP accordingly. Rest the Folder structure …\mysql\bin\ remains the same.
  • Now type the following command on the cmd prompt:
    mysql.exe –h localhost –u root -p
  • MySQL will start and will prompt you for a password. Leave it blank and hit enter.Launch MySQL with proper parametersThis should bring you the MySQL terminal.
Now write and run the SQL Query languages and do whatever you wish to. Below is a process to add shortcut to the MySQL terminal.

#3: Adding shortcut using environment variables:

We will now add a environment variable to windows so that we can open the mysql.exe terminal window anywhere from command prompt without changing the working directory. Just follow the steps below:
  • On Windows 7 / Vista:Starting Environment VariablesGo to Control Panel > System > Advanced System Settings > Advance Tab > Environment Variable;
  • On Windows XP: Go to Control Panel > System > Advanced Tab > Environment Variable;
  • Now under “User Variables for ~UserName~” Click on “New” button.
  • Under “Variable Name” type: mysql
  • Under “Variable Value” type: C:\xampp\mysql\bin\mysql.exe
  • Obviously you need to change the location of …/xampp if you have any other installation directory.
  • Now Save everythingsetting the environment variablesand close the window.
You are done with creating the shortcuts… To launch MySQL all you need to is:
  • Open up Command Prompt;
  • Type:
    %mysql% –h localhost –u root -p
    and hit enter (As shown on the first image). Give blank password as before and you are set to go…