Using the Mysql Client
Once you have successfully installed and started MySQL, you need some sort of way to interact with it. Whereas mysqld is the MySQL server that manages all the data, you need a client application that will talk to mysqld. The most common way to interface with mysqldbesides using a programming languageis to use the mysql client(or mysql monitor, as it is also called). This application can be used to connect to mysqld running on the same machine, or even on another. Most of the examples throughout the rest of this book will be accomplished via mysql (the lowercase word mysql will refer to the client, as opposed to MySQL, which refers to the software as a whole).
The mysql client can take several arguments up front, including the user name, password, and hostname (computer name). You establish these arguments like so:
mysql -u user name -p -h hostname
The -p option will cause mysql to prompt you for the user's password, just as the mysqladmin tool does. You can specify the password on this line if you prefer, by typing it after the -p prompt, but it will be visible, which is less secure.
Within the mysql client, practically every statement (or SQL command) needs to be terminated by a semicolon. This means that you can continue the same statement over several lines to facilitate typing. With this in mind, you will also see a few different prompts when using the interface, as illustrated in Figure 2.30 and listed in Table 2.1.
Figure 2.30. The mysql client will indicate what it thinks you are doing by using different prompts.
Table 2.1. These prompts, also represented in Figure 2.30, are used to clue you in as to what the mysql client is expecting.
mysql Client Prompts
Continuing a command
Need to finish a single quote
Need to finish a double quote
Need to finish a backtick
Need to finish a comment
As a quick demonstration of working with the mysql client, I will show you how to access mysql, select a database to use, and quit the application. As always, the MySQL server must be running to connect to it from the mysql client.
Before going through the steps, there's an alternative beginning for Windows users. The latest versions of MySQL have created a shortcut to the mysql client. You can access it by selecting Start > All Programs > MySQL > MySQL Server 5.0 > MySQL Command Line Client. This was created during the installation process, but the particulars may differ, depending upon your version of MySQL. If you have this shortcut, you can follow it and skip ahead to Step 4.
To use the mysql client:
Log in to your system from a command-line interface.
Move to the MySQL installation directory.
Steps 1 and 2 should be old hat for you by now.
Enter the following text (Figure 2.31
Figure 2.31. Invoke the mysql client, which will be the most commonly used method to access databases throughout this book.
The -h hostname
argument described previously is optional, and I tend to leave it off unless I cannot get into mysql
otherwise. If you set a password for the root
user, as detailed earlier in this chapter, you can use the root
username and password now.
At the prompt, enter the user's password.
If you used root
in Step 3 or used the Windows Start menu trick, you would enter the root
user's password here.
If you ever want to connect to the mysql
client using another username, just replace root
with that username (in Step 3) and enter that user's password at the prompt. On Windows, you'll either need to use a configuration file or manually go through the preceding steps.
Show all the available databases (Figure 2.32
Figure 2.32. The SHOW DATABASES command lists the databases available to the logged-in user.
The SHOW DATABASES
command asks MySQL to list every database which you, the logged-in user, can access. The semicolon that terminates the command is a requirement in the mysql
Select which database you want to use (Figure 2.33
Figure 2.33. The first step you will normally take within the mysql client is to choose the database with which to work.
command tells MySQL which database you want to deal with from here on out (saving typing the database name over and over again later). The mysql_install_db
script run during the installation process creates two starter databasesmysql
If you know in advance which database you will want to use, you can simplify matters by starting mysql
mysql -u username -p databasename
Quit out of mysql.
You can also use the command quit
to leave the client. This stepunlike most other commands you enterdoes not require a semicolon at the end.
On Windows, using the Start Menu shortcut, this should also close the window.
To see what else you can do with the mysql client, type help; at any time (at the mysql prompt, after logging in).
The mysql client makes use of the Unix readline tool, allowing you to use the up and down arrows to scroll through previously entered commands. This can save you oodles of time later on.
You can also expedite your work within the mysql client by pressing the Tab key to complete words (type a single # and press Return to see what words can be completed), using Ctrl+A to move your cursor to the beginning of a line, and using Ctrl+E to move the cursor to the end of the line.
If you are in a long statement and make a mistake, cancel the current operation by typing \c and pressing Return (it must be a lowercase "c"). If mysql thinks a closing single or double quotation mark is missing, you'll need to enter that first.
Depending upon how MySQL is installed on your system, some Windows users can run the mysql clientand other utilities described in this chaptersimply by double-clicking the executable file found within the mysql/bin folder. You can also directly run the mysql client from the Start > Run menu.
To be particularly safe when using mysql, start the application using the --i-am-a-dummy argument. And, no, I am not making this up (the argument limits what you can and cannot do).
The mysql client (or mysql monitor) is just one of many tools you can use to work with MySQL. Although this will be the application used almost exclusively in this book, you don't have to use the mysql client. There are many other programs available, all of which will provide the same functionality but in a different way.
MySQL also makes two related applications, both of which are free. The first is the MySQL Administrator. The second is the MySQL Query Browser. Both offer a functionality that overlaps with the mysql client and with mysqladmin, but they use a nicer graphical interface.
There are also plenty of third-party tools available. Of these, phpMyAdmin is a popular choice (available from www.phpmyadmin.net). It's a Web-based tool, which requires that you also have a Web server and PHP installed.
If none of these are to your liking, just search the Internet (or your favorite download repository) for MySQL applications for your particular platform.