Learning MySQL (2007)

Part I. Introduction

Chapter 3. Using the MySQL Monitor

MySQL has a client-server architecture; clients connect to the server to perform database operations such as reading or storing data. There are many MySQL clients available, including some that have graphical interfaces. You can also develop your own clients. The standard MySQL command-line client or “monitor” program provided by MySQL AB is the client you’ll probably use the most often. The monitor allows you to control almost all aspects of database creation and maintenance using SQL and the custom MySQL extensions to SQL.

In this chapter, we’ll examine how to start the monitor and how to run commands through the monitor either interactively or in batch mode. We’ll describe how you can access the inbuilt MySQL help functions, and how to test your MySQL setup using the sample databases from the book web site. We’ll also take a quick look at a couple of graphical tools that you can use instead of the monitor.

Starting the Monitor

The monitor program is called simply mysql and is found in a directory with the other MySQL programs. The exact location depends on your operating system and how you chose to install MySQL; we considered some of these in Error Message About MySQL Executable Programs Not Being Found or Recognized,” in Chapter 2.

If your MySQL server isn’t already running, start it using the appropriate procedure for your setup as discussed in Chapter 2. Now, follow these steps to start the monitor and connect to your MySQL server as the MySQL administrator (the MySQL root user) by typing this from the command line:

$ mysql --user=root

If you followed our instructions in Chapter 2, the MySQL root account is protected by the password you chose earlier, and so you’ll get a message saying that you’ve been denied access to the server. If your server has a password, you should specify the password as follows:

$ mysql --user=root --password=the_mysql_root_password

If you get a message from the operating system saying that it can’t find the MySQL program, you’ll need to specify the full path to the mysql executable file as discussed in Error Message About MySQL Executable Programs Not Being Found or Recognized.”

If you used a nonstandard socket file when starting your server, you’ll need to provide the details to any MySQL client programs you run, including mysql. For example, you might type:

$ mysql \

 --socket=server_socket \

 --user=root \

 --password=the_mysql_root_password

If you’re trying to connect to a MySQL server on a different computer or a nonstandard port, you should specify these when starting the monitor:

$mysql \

 --host=server_host_name \

 --port=server_port \

 --user=root \

 --password=the_mysql_root_password

We list a few more options to the monitor program at the end of this chapter.

Most of the other MySQL programs we’ll describe in this book take the same port and socket options to identify the server to connect to, and the same user and password options to identify and authenticate the MySQL user.

If all goes well, you’ll get the monitor’s mysql> prompt:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 456 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

You can now type in commands that MySQL understands. To start things off, ask the server what version of MySQL it is:

mysql> SELECT VERSION();

+------------+

| VERSION()  |

+------------+

| 5.0.22 |

+------------+

1 row in set (0.03 sec)

You’ll almost certainly be using a different version number from the one we’re using. Now ask the server to list all the databases that it has:

mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| mysql    |

| test     |

+----------+

2 rows in set (0.00 sec)

You may see different results on your MySQL server. There are two databases here; the mysql database is used to store information about user access privileges, and the test database is an empty scratch space for experimentation. Not terribly exciting, but you’ll remedy this situation as you progress through this book!

Style, Case, and Semicolons

When interacting with a MySQL server, you’ll use a combination of SQL keywords, MySQL proprietary commands, and names of databases and database components. We follow common convention and use a style to make it easier to distinguish between components of an SQL query. We always show SQL statements and keywords in capitals, such as SELECT or FROM. We also show the MySQL monitor’s proprietary SQL commands—such as USE—in uppercase. We always enter database components—such as database, table, and column names—in lowercase. This makes our SQL more readable and easier to follow in source code and books.

MySQL isn’t fussy about whether you enter SQL or the monitor’s proprietary statements in uppercase or lowercase. For example, SELECT, select, Select, and even SeLeCt are equivalent. However, depending on your platform, MySQL can be fussy about database and table names. For example, under Windows, MySQL isn’t fussy at all (because Windows itself isn’t fussy about the filenames that store those structures), while on Mac OS X its fussiness depends on what underlying filesystem you use to store disk files. Linux and Unix systems observe the difference between uppercase and lowercase strictly. A reliable approach is to adopt the convention of using lowercase for all database, table, and column names. You can control how MySQL manages different case behavior using an option when you start the MySQL server, mysqld, but we don’t recommend you do this, and we don’t discuss it further in this book.

There are some restrictions on what characters and words you can use in your database, table, and other names. For example, you can’t have a column named from or select (in any mix of uppercase and lowercase). These restrictions are mostly obvious, since they apply to reserved keywords that confuse MySQL’s parser. We discuss the characters that can and can’t be used in Chapter 6.

You’ll notice that we terminate all SQL statements with the semicolon character (;). This tells MySQL that we’ve finished entering a statement and that it should now parse and execute it. This gives you flexibility, allowing you to type in a statement over several lines. For example, the following statement works fine:

mysql> SELECT User,Host

    -> FROM user;

+------+--------------------------+

| User | Host                     |

+------+--------------------------+

|      | localhost                |

| root | localhost                |

|      | saied-ltc.cs.rmit.edu.au |

| root | saied-ltc.cs.rmit.edu.au |

+------+--------------------------+

4 rows in set (0.00 sec)

We often use this style in this book, because it helps long statements fit in the margins of a page. Notice that the monitor shows you a different prompt (->) to indicate that it’s waiting for you to enter more of your SQL statement or to type in a semicolon.

In fact, you can add whitespace—such as space and tab characters—anywhere between the components of a statement to improve its formatting, and we often do this in our longer statements. Of course, because whitespace separates keywords and values, you can’t add space within the keywords or values themselves; for example, if you type the keyword SELECT as SEL ECT, you’ll get an error.

In contrast to SQL statements, you can’t span the MySQL monitor’s own commands over more than one line. This is because the semicolon isn’t actually required for these, and just pressing the Enter key has the same effect. For example, the USE command tells MySQL that you want to use a particular database. The following works fine:

mysql> USE test

Database changed

However, if you try to span the command over more than one line, you won’t get far:

mysql> USE

ERROR:

USE must be followed by a database name

The Monitor Help

The monitor has a handy HELP command that you can use to get more information on the monitor commands or SQL syntax. If you type HELP and press the Enter key, you’ll get a list of commands the monitor understands:

mysql> HELP

For information about MySQL products and services, visit:

 http://www.mysql.com/

For developer information, including the MySQL Reference Manual, visit:

 http://dev.mysql.com/

To buy MySQL Network Support, training, or other products, visit:

 https://shop.mysql.com/

List of all MySQL commands:

Note that all text commands must be first on line and end with ';'

?         (\?) Synonym for `help'.

clear     (\c) Clear command.

connect   (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new

               delimiter.

edit      (\e) Edit command with $EDITOR.

ego       (\G) Send command to mysql server, display result vertically.

exit      (\q) Exit mysql. Same as quit.

go        (\g) Send command to mysql server.

help      (\h) Display this help.

nopager   (\n) Disable pager, print to stdout.

notee     (\t) Don't write into outfile.

pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print     (\p) Print current command.

prompt    (\R) Change your mysql prompt.

quit      (\q) Quit mysql.

rehash    (\#) Rebuild completion hash.

source    (\.) Execute an SQL script file. Takes a file name as an argument.

status    (\s) Get status information from the server.

system    (\!) Execute a system shell command.

tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.

use       (\u) Use another database. Takes database name as argument.

charset   (\C) Switch to another charset. Might be needed for processing binlog with

               multi-byte charsets.

warnings  (\W) Show warnings after every statement.

nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

Depending on the version of the monitor that you’re using, you may see a different list. The characters in the parentheses indicate a shortcut for each command. You typed in the USE command earlier in this chapter to change to the test database. From the list, you can see a short description of this command and also see that you can type \u instead of USE.

Let’s look at another entry on this list. The monitor has command completion, just like the Linux and Mac OS X shells, and like the Windows command prompt. You can press the Tab key to complete SQL statements and table and attribute names. If not all the options you expect are shown, you can update the internal list of expansions (“rebuild the completion hash”) by typing rehash (or using the shortcut characters \#) and pressing the Enter key.

Using the help function of the monitor, you can also get help on how to interact with the MySQL server. To see a table of contents for the help documentation, type HELP Contents:

mysql> HELP Contents

You asked for help about help category: "Contents"

For more information, type 'help <item>', where <item> is one of the following

categories:

 Account Management

 Administration

 Data Definition

 Data Manipulation

 Data Types

 Functions

 Functions and Modifiers for Use with GROUP BY

 Geographic Features

 Language Structure

 Storage Engines

 Stored Routines

 Table Maintenance

 Transactions

 Triggers

You may see more or less help content depending on the help files that have been installed with your server. You can get information on individual topics by typing in the HELP command followed by the topic name. For example, to get information on data manipulation, you would type:

mysql> HELP Data Manipulation

You asked for help about help category: "Data Manipulation"

For more information, type 'help <item>', where <item> is one of the following

topics:

 CACHE INDEX

 ...

 DELETE

 ...

 EXPLAIN

 ...

 INSERT

 ...

 SELECT

 ...

 SHOW

 ...

 SHOW CREATE DATABASE

 ...

 SHOW CREATE TABLE

 ...

 SHOW DATABASES

 ...

 SHOW GRANTS

 ...

 SHOW STATUS

 ...

 SHOW TABLES

 ...

 UPDATE

We’ve omitted some items to keep the output short.

You can request further information on any of the items by typing HELP followed by the appropriate keywords. For example, for information on the SHOW DATABASES command, you’d type:

mysql> HELP SHOW DATABASES

Name: 'SHOW DATABASES'

Description:

Syntax:

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']

SHOW DATABASES lists the databases on the MySQL server host. You see

only those databases for which you have some kind of privilege, unless

you have the global SHOW DATABASES privilege. You can also get this

list using the mysqlshow command.

If the server was started with the --skip-show-database option, you

cannot use this statement at all unless you have the SHOW DATABASES

privilege.

SHOW SCHEMAS can be used as of MySQL 5.0.2

Running the Monitor in Batch Mode

The MySQL monitor can be used in interactive mode or in batch mode. In interactive mode, you type in SQL queries or MySQL commands such as SHOW DATABASES at the MySQL prompt, and view the results.

In batch mode, you tell the monitor to read in and execute a list of commands from a file. This is useful when you need to run a large set of operations—for example, when you want to restore a database from a backup file. It’s also useful when you need to run a particular sequence of operations frequently; you can save the commands in a file and then tell the monitor to read in the file whenever you need it.

The examples we’ve presented earlier in this chapter, and most of the examples in this book, show the monitor being used in interactive mode. Let’s look at an example for batch mode. Say you have a text file called count_users.sql containing the SQL commands:

use mysql;

SELECT COUNT(*) FROM user;

This script tells MySQL that you want to use the mysql database, and that you want to count all the users who have accounts on the MySQL server (we’ll explain the syntax of the SELECT command in Chapter 5).

You can run all the commands in this file using the SOURCE command:

mysql> SOURCE count_users.sql

Database changed

+----------+

| count(*) |

+----------+

| 4        |

+----------+

1 row in set (0.00 sec)

If the count_users.sql file isn’t in the current directory, you should give the full path to the file—for example, /home/adam/Desktop/count_users.sql or C:\count_users.sql. Alternatively, from the command line, you can use the less-than (<) redirection operator followed by the filename:

$ mysql --user=root --password=the_mysql_root_password < count_users.sql

count(*)

4

Loading the Sample Databases

To get a working sample database that you can play with, start by visiting the book’s web site and downloading the music database file music.sql from the sample databases section.

To load the file into your server, you need to use the SOURCE command and specify where MySQL can find the music.sql file. For example, this might be ~/music.sql or ~/Desktop/music.sql on a Linux or Mac OS X system, or C:\Documents and Settings\my_windows_login_name\Desktop\music.sql on a Windows system.

Once you run the SOURCE command, you should see some reassuring messages flash by:

mysql> SOURCE path_to_music.sql_file;

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

...

You can now see if the database is there by using the SHOW DATABASES command:

mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| music    |

| mysql    |

| test     |

+----------+

3 rows in set (0.00 sec)

mysql>

We’ll see how to use this database in future chapters.

Repeat this process for the two additional sample database files, flight.sql and university.sql, that are available from the book’s web site. Finally, you can leave the MySQL monitor by typing quit:

mysql> quit

MySQL Monitor Program Options

The monitor program can take several parameters; the ones you’ll need most frequently are:

host

The host the server is running on; you can leave this out if the server is running on the same host as the client (localhost).

user

The username to use when connecting to the MySQL server. This bears no relation to the username the server is running under, or to your Linux or Mac OS X username. If you don’t provide a username with this option, the monitor uses a default value; this default username is your machine account name on a Linux or Mac OS X system, and ODBC on a Windows system.

password

The password of this user. If you don’t provide the password parameter, no password is supplied to the server. This is fine if there is no password stored for that user, but if the user does have a password, the connection will fail:

$ mysql --user=the_username

ERROR 1045 (28000): Access denied for user 'the_username'@'localhost'

  (using password: NO)

If you include the password option but don’t specify a password, the client will prompt you for a password after you press the Enter key. If the user has no password, pressing the Enter key will work; otherwise, the connection will fail again:

$ mysql --user=the_username --password

Enter password:

ERROR 1045 (28000): Access denied for user 'the_username'@'localhost'

  (using password: NO)

If you provide an incorrect password, or you don’t have permission to access a specified database, MySQL will note this in the error message:

$ mysql --user=the_username --password=wrong_password

Enter password:

ERROR 1045 (28000): Access denied for user 'the_username'@'localhost'

  (using password: YES)

If you specify the correct password at the Enter password: prompt, or if you specify the correct password on the command line when starting the monitor, the connection will succeed:

$ mysql --user=the_username --password=the_password

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 169 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Some users prefer not to specify the password on the command line because suppressing the password guarantees the password won’t be displayed in the operating system process table or command history. Under all operating systems we’ve tested, the password is hidden and can’t be seen using operating system utilities to view running processes. However, the password may be stored in your command-line history, which other users may be able to access.

database

The database to use. This saves you from having to type USE the_database_name after the MySQL monitor starts. You can also simply omit the database option and just add the name of the database you want to use at the end of the mysql command.

safe-updates

Most experienced MySQL users can remember occasions where they’ve accidentally deleted all the data in a table by issuing a DELETE FROM table_name command, forgetting to add a limiting condition.

The safe-updates option prevents you from doing this by requiring you to provide a key constraint to DELETE and UPDATE, or to use a LIMIT clause. For example:

mysql> DELETE FROM user;

ERROR 1175 (HY000): You are using safe update mode and you tried to update

  a table without a WHERE that uses a KEY column

We’ll explain these commands in later chapters.

Let’s look at a couple of examples. First, let’s say you want to connect to the server running on the same machine you’re working on (localhost), as the MySQL user root, and with the password the_mysql_root_password. You want to use the database music, so you would type:

$ mysql --user=root --password=the_mysql_root_password --database=music

Now for a more complex example: say you’re working on the host sadri.learningmysql.com, and wish to use the Moodle database on the MySQL server listening to port 57777 on the host zahra.learningmysql.com. For this MySQL server, you have the MySQL account name moodleuserand the password moodlepass. You would type the command (all on one line):

$ mysql \

  --host=zahra.learningmysql.com \

  -port=57777 \

  --user=moodleuser \

  --password=moodlepass \

  --database=Moodle

We’ll look at how to create and manage users in Chapter 9.

Instead of specifying options on the command line, you can list them in the mysql section of an options file. You can also store your password in an options file to avoid typing it in every time you start the monitor. We discuss how to do this in Chapter 11.

Graphical Clients

Before we end this chapter, let’s have a quick look at two graphical clients that you can use in place of the monitor.

The MySQL Administrator program is a graphical tool that you can download as part of the MySQL GUI Tools Bundle from the MySQL AB downloads page at http://dev.mysql.com/downloads. This program allows you to perform most database administration from within a graphical environment, as shown in Figure 3-1.

The MySQL Administrator graphical MySQL administration tool

Figure 3-1. The MySQL Administrator graphical MySQL administration tool

The MySQL Query Browser program is also available for download from the same web page. This allows you to run SQL queries from within a graphical environment, and view the results. A sample query is shown in Figure 3-2.

The MySQL Query Browser graphical MySQL client

Figure 3-2. The MySQL Query Browser graphical MySQL client

Together, these tools replace an older program known as the MySQL Control Center mysqlcc. In this book, we focus on doing things using the monitor; once you understand the way MySQL works, you’ll find it easy to use other clients such as these.

Exercises

1.    What do we mean when we say that MySQL has a client-server architecture?

2.    Use the monitor help to look up information on the SELECT statement. (We’ll discuss SELECT in detail in Chapter 5.)

3.    What is the difference between using the monitor in interactive mode and using the monitor in batch mode?

4.    What do the monitor user, password, and database options do?