Using MySQL with the Command Line Client

Published on November 2nd, 2009 by admin

The command line client is one of those tools this is used heavily by professionals and often neglected by beginners. Here is a great primer to get you familiar with the client and impress your friends :)

What you will need

What is the command line client?

Command Line Client

The command line client is a program that allows you to work with your operating system via the use of scripts. You can use it to do practically anything in the operating environment including creating files and folders, executing programs and running MySQL scripts :) .

Why use it?

The client liberates you from your dependence on a graphical user interface (such as PHPMyAdmin) and gives you much more flexibility to work. Also, in a production environment you might not always have access to PHPMyAdmin so it’s very useful when you need to work with the SQL

Setup the Server

I use windows as my primary production environment, therefore I’m not going to show you how to setup a server on Mac or Linux. Fortunately, the concept applies to all three operating systems with small variations in the syntax and procedure. Please do a search in google for more information about those platforms.

Download and Install WAMP

There are two great prepackaged builds for implementing your own development server, WAMP and XXAMP. I’ve tried them both over the years and prefer WAMP. Feel free to use what you like though.

1. Download the WAMP Server Here

2. Double click the downloaded file and install the server. It should be pretty straightforward.

Configure the environment variables

Now with your server installed we’re going to configure the environment variables so it’s easy to access your MySQL server in the client.

If we didn’t do this we would have to go through these two steps before we can start using the server.

  • We would first have to use the cd command to change to the server location of my mysql install.
  • Then we could finally start using the mysql to start using the server.

Here’s what it would look like in the client.

Command Line Client Without Environment Variables

I don’t know about you but I’m not going to be able to remember that path every time I log in.

So let’s make it a little easier. . .

1. In Windows go to Control Panel > System and Security > System > Advanced System Settings

2. On the Advanced tab, click the Environment Variables button.

environment_vars_button

3. Select Path from the list of System Variables

4. Click Edit… you will see the following pop up.

Environment Variables

5. At the end of the string in Variable Value add the path to the mysql bin where you installed your MySQL server.

For example, I would add:

C:\Server\bin\mysql\mysql5.1.36\bin

note: Be sure and separate each entry with a semicolon (;). And don’t add a space between them!

If done correctly that variable should look similar to this:

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Server\bin\mysql\mysql5.1.36\bin

6. Press Ok until you exit out of all the windows.

Your environment variables are set up!

Create a shortcut on your desktop

Next we’re going to create a convenient way to start the client and begin using MySQL right away!

1. Create a shortcut to the client by searching for cms in the Start Menu, and right clicking it.

create-shortcut

2. With the shortcut created on your desktop, right click it and choose Properties

3. From the Shortcut tab change the Start in: string to the directory where you serve your web files.

start-in

The Start In variable makes it much easier to work with files on your web server. All files that you access are in relation to that directory.

4. Next go to the layout tab and apply the following settings. It makes it easier to work with the client from this size.

Window Size

5. Press OK

The client is configured and ready to go!

Now lets do some SQL . .

Leave a Reply




Popular Posts

Posts By Category

Supported by