1.4 Starting and Terminating mysql

1.4.1 Problem

You want to start and stop the mysql program.

1.4.2 Solution

Invoke mysql from your command prompt to start it, specifying any connection parameters that may be necessary. To leave mysql, use a QUIT statement.

1.4.3 Discussion

To start the mysql program, try just typing its name at your command-line prompt. If mysql starts up correctly, you'll see a short message, followed by a mysql> prompt that indicates the program is ready to accept queries. To illustrate, here's what the welcome message looks like (to save space, I won't show it in any further examples):

% mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18427 to server version: 3.23.51-log

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

mysql>

If mysql tries to start but exits immediately with an "access denied" message, you'll need to specify connection parameters. The most commonly needed parameters are the host to connect to (the host where the MySQL server runs), your MySQL username, and a password. For example:

% mysql -h localhost -p -u cbuser
Enter password: cbpass

In general, I'll show mysql commands in examples with no connection parameter options. I assume that you'll supply any parameters that you need, either on the command line, or in an option file so that you don't have to type them each time you invoke mysql.

If you don't have a MySQL username and password, you need to obtain permission to use the MySQL server.

The syntax and default values for the connection parameter options are shown in the following table. These options have both a single-dash short form and a double-dash long form.

As the table indicates, there is no default password. To supply one, use --password or -p, then enter your password when mysql prompts you for it:

%

mysql -p

If you like, you can specify the password directly on the command line by using either -ppassword (note that there is no space after the -p) or --password=password. I don't recommend doing this on a multiple-user machine, because the password may be visible momentarily to other users who are running tools such as ps that report process information.

If you get an error message that mysql cannot be found or is an invalid command when you try to invoke it, that means your command interpreter doesn't know where mysql is installed. 

To terminate a mysql session, issue a QUIT statement:

mysql> QUIT

You can also terminate the session by issuing an EXIT statement or (under Unix) by typing Ctrl-D.

The way you specify connection parameters for mysql also applies to other MySQL programs such as mysqldump and mysqladmin. For example, some of the actions that mysqladmin can perform are available only to the MySQL root account, so you need to specify name and password options for that user:

% mysqladmin -p -u root shutdown
Enter password:

1.3 Creating a Database and a Sample Table

1.3.1 Problem

You want to create a database and to set up tables within it.

1.3.2 Solution

Use a CREATE DATABASE statement to create a database, a CREATE TABLE statement for each table you want to use, and INSERT to add records to the tables.

1.3.3 Discussion

The GRANT statement used in the previous section defines privileges for the cookbook database, but does not create it. You need to create the database explicitly before you can use it. This section shows how to do that, and also how to create a table and load it with some sample data that can be used for examples in the following sections.

After the cbuser account has been set up, verify that you can use it to connect to the MySQL server. Once you've connected successfully, create the database. From the host that was named in the GRANT statement, run the following commands to do this (the host named after -h should be the host where the MySQL server is running):

% mysql -h localhost -p -u cbuser
Enter password: cbpass
mysql> CREATE DATABASE cookbook;
Query OK, 1 row affected (0.08 sec)

Now you have a database, so you can create tables in it. Issue the following statements to select cookbook as the default database, create a simple table, and populate it with a few records:^[1]

If you don't want to enter the complete text of the INSERT statements (and I don't blame you),. And if you don't want to type in any of the statements.

mysql> USE cookbook;
mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('octopus',0,8);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);

The table is named limbs and contains three columns to records the number of legs and arms possessed by various life forms and objects. (The physiology of the alien in the last row is such that the proper values for the arms and legs column cannot be determined; NULL indicates "unknown value.")

Verify that the table contains what you expect by issuing a SELECT statement:

mysql> SELECT * FROM limbs;
+--------------+------+------+
| thing        | legs | arms |
+--------------+------+------+
| human        |    2 |    2 |
| insect       |    6 |    0 |
| squid        |    0 |   10 |
| octopus      |    0 |    8 |
| fish         |    0 |    0 |
| centipede    |  100 |    0 |
| table        |    4 |    0 |
| armchair     |    4 |    2 |
| phonograph   |    0 |    1 |
| tripod       |    3 |    0 |
| Peg Leg Pete |    1 |    2 |
| space alien  | NULL | NULL |
+--------------+------+------+
12 rows in set (0.00 sec)

At this point, you're all set up with a database and a table that can be used to run some example queries.

The MySQL database system uses a client-server architecture that centers around the server, mysqld. The server is the program that actually manipulates databases. Client programs don't do that directly; rather, they communicate your intent to the server by means of queries written in Structured Query Language (SQL). The client program or programs are installed locally on the machine from which you wish to access MySQL, but the server can be installed anywhere, as long as clients can connect to it. MySQL is an inherently networked database system, so clients can communicate with a server that is running locally on your machine or one that is running somewhere else, perhaps on a machine on the other side of the planet. Clients can be written for many different purposes, but each interacts with the server by connecting to it, sending SQL queries to it to have database operations performed, and receiving the query results from it.

One such client is the mysql program that is included in MySQL distributions. When used interactively, mysql prompts for a query, sends it to the MySQL server for execution, and displays the results. This capability makes mysql useful in its own right, but it's also a valuable tool to help you with your MySQL programming activities. It's often convenient to be able to quickly review the structure of a table that you're accessing from within a script, to try a query before using it in a program to make sure it produces the right kind of output, and so forth. mysql is just right for these jobs. mysql also can be used non-interactively, for example, to read queries from a file or from other programs. This allows you to use it from within scripts or cron jobs or in conjunction with other applications.

This chapter describes mysql's capabilities so that you can use it more effectively. Of course, to try out for yourself the recipes and examples shown in this book, you'll need a MySQL user account and a database to work with. The first two sections of the chapter describe how to use mysql to set these up. For demonstration purposes, the examples assume that you'll use MySQL as follows:

• The MySQL server is running on the local host.

• Your MySQL username and password are cbuser and cbpass.

• Your database is named cookbook.

For your own experimentation, you can violate any of these assumptions. Your server need not be running locally, and you need not use the username, password, or database name that are used in this book. Naturally, if you don't use MySQL in the manner just described, you'll need to change the examples to use values that are appropriate for your system. Even if you do use different names, I recommend that you at least create a database specifically for trying the recipes shown here, rather than one you're using currently for other purposes. Otherwise, the names of your existing tables may conflict with those used in the examples, and you'll have to make modifications to the examples that are unnecessary when you use a separate database.

1.2 Setting Up a MySQL User Account

1.2.1 Problem

You need to create an account to use for connecting to the MySQL server running on a given host

1.2.2 Solution

Use the GRANT statement to set up the MySQL user account. Then use that account's name and password to make connections to the server.

1.2.3 Discussion

Connecting to a MySQL server requires a username and password. You can also specify the name of the host where the server is running. If you don't specify connection parameters explicitly, mysql assumes default values. For example, if you specify no hostname, mysql typically assumes the server is running on the local host.

The following example shows how to use the mysql program to connect to the server and issue a GRANT statement that sets up a user account with privileges for accessing a database named cookbook. The arguments to mysql include -h localhost to connect to the MySQL server running on the local host, -p to tell mysql to prompt for a password, and -u root to connect as the MySQL root user. Text that you type is shown in bold; non-bold text is program output:

% mysql -h localhost -p -u root
Enter password: ******
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
Query OK, 0 rows affected (0.09 sec)
mysql> QUIT
Bye

After you enter the mysql command shown on the first line, if you get a message indicating that the program cannot be found or that it is a bad command,  when mysql prints the password prompt, enter the MySQL root password where you see the ******. (If the MySQL root user has no password, just press Return at the password prompt.) Then issue a GRANT statement like the one shown.

To use a database name other than cookbook, substitute its name where you see cookbook in the GRANT statement. Note that you need to grant privileges for the database even if the user account already exists. However, in that case, you'll likely want to omit the IDENTIFIED BY 'cbpass' part of the statement, because otherwise you'll change that account's current password.

The hostname part of 'cbuser'@'localhost' indicates the host from which you'll be connecting to the MySQL server to access the cookbook database. To set up an account that will connect to a server running on the local host, use localhost, as shown. If you plan to make connections to the server from another host, substitute that host in the GRANT statement. For example, if you'll be connecting to the server as cbuser from a host named xyz.com, the GRANT statement should look like this:

mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'xyz.com' IDENTIFIED BY 'cbpass';

It may have occurred to you that there's a bit of a paradox involved in the procedure just described. That is, to set up a user account that can make connections to the MySQL server, you must connect to the server first so that you can issue the GRANT statement. I'm assuming that you can already connect as the MySQL root user, because GRANT can be used only by a user such as root that has the administrative privileges needed to set up other user accounts. If you can't connect to the server as root, ask your MySQL administrator to issue the GRANT statement for you. Once that has been done, you should be able to use the new MySQL account to connect to the server, create your own database, and proceed from there on your own.