1.6 Protecting Option Files

1.6.1 Problem

Your MySQL username and password are stored in your option file, and you don't want other users reading it.

1.6.2 Solution

Change the file's mode to make it accessible only by you.

1.6.3 Discussion

If you use a multiple-user operating system such as Unix, you should protect your option file to prevent other users from finding out how to connect to MySQL using your account. Use chmod to make the file private by setting its mode to allow access only by yourself:

% chmod 600 .my.cnf

1.7 Mixing Command-Line and Option File Parameters

1.7.1 Problem

You'd rather not store your MySQL password in an option file, but you don't want to enter your username and server host manually.

1.7.2 Solution

Put the username and host in the option file, and specify the password interactively when you invoke mysql; it looks both in the option file and on the command line for connection parameters. If an option is specified in both places, the one on the command line takes precedence.

1.7.3 Discussion

mysql first reads your option file to see what connection parameters are listed there, then checks the command line for additional parameters. This means you can specify some options one way, and some the other way.

Command-line parameters take precedence over parameters found in your option file, so if for some reason you need to override an option file parameter, just specify it on the command line. For example, you might list your regular MySQL username and password in the option file for general purpose use. If you need to connect on occasion as the MySQL root user, specify the user and password options on the command line to override the option file values:

To explicitly specify "no password" when there is a non-empty password in the option file, use -p on the command line, and then just press Return when mysql prompts you for the password:

%

mysql -p

1.5.1 Problem

You don't want to type connection parameters on the command line every time you invoke mysql.

1.5.2 Solution

Put the parameters in an option file.

1.5.3 Discussion

To avoid entering connection parameters manually, put them in an option file for mysql to read automatically. Under Unix, your personal option file is named .my.cnf in your home directory. There are also site-wide option files that administrators can use to specify parameters that apply globally to all users. You can use /etc/my.cnf or the my.cnf file in the MySQL server's data directory. Under Windows, the option files you can use are C:\my.cnf, the my.ini file in your Windows system directory, or my.cnf in the server's data directory.

The following example illustrates the format used to write MySQL option files:

# general client program connection options
[client]
host=localhost
user=cbuser
password=cbpass

# options specific to the mysql program
[mysql]
no-auto-rehash
# specify pager for interactive mode
pager=/usr/bin/less

This format has the following general characteristics:

• Lines are written in groups. The first line of the group specifies the group name inside of square brackets, and the remaining lines specify options associated with the group. The example file just shown has a [client] group and a [mysql] group. Within a group, option lines are written in name=value format, where name corresponds to an option name (without leading dashes) and value is the option's value. If an option doesn't take any value (such as for the no-auto-rehash option), the name is listed by itself with no trailing =value part.

• If you don't need some particular parameter, just leave out the corresponding line. For example, if you normally connect to the default host (localhost), you don't need any host line. If your MySQL username is the same as your operating system login name, you can omit the user line.

• In option files, only the long form of an option is allowed. This is in contrast to command lines, where options often can be specified using a short form or a long form. For example, the hostname can be given using either -h hostname or --host=hostname on the command line; in an option file, only host=hostname is allowed.

• Options often are used for connection parameters (such as host, user, and password). However, the file can specify options that have other purposes. The pager option shown for the [mysql] group specifies the paging program that mysql should use for displaying output in interactive mode. It has nothing to do with how the program connects to the server.

• The usual group for specifying client connection parameters is [client]. This group actually is used by all the standard MySQL clients, so by creating an option file to use with mysql, you make it easier to invoke other programs such as mysqldump and mysqladmin as well.

• You can define multiple groups in an option file. A common convention is for a program to look for parameters in the [client] group and in the group named after the program itself. This provides a convenient way to list general client parameters that you want all client programs to use, but still be able to specify options that apply only to a particular program. The preceding sample option file illustrates this convention for the mysql program, which gets general connection parameters from the [client] group and also picks up the no-auto-rehash and pager options from the [mysql] group. (If you put the mysql-specific options in the [client] group, that will result in "unknown option" errors for all other programs that use the [client] group and they won't run properly.)

• If a parameter is specified multiple times in an option file, the last value found takes precedence. This means that normally you should list any program-specific groups after the [client] group so that if there is any overlap in the options set by the two groups, the more general options will be overridden by the program-specific values.

• Lines beginning with # or ; characters are ignored as comments. Blank lines are ignored, too.

• Option files must be plain text files. If you create an option file with a word processor that uses some non-text format by default, be sure to save the file explicitly as text. Windows users especially should take note of this.

• Options that specify file or directory pathnames should be written using / as the pathname separator character, even under Windows.

If you want to find out which options will be taken from option files by mysql, use this command:

% mysql --print-defaults

You can also use the my_print_defaults utility, which takes as arguments the names of the option file groups that it should read. For example, mysql looks in both the [client] and [mysql] groups for options, so you can check which values it will take from option files like this:

% my_print_defaults client mysql

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.