Working with MySQL

Now that you’ve installed MySQL, set the MySQL root password. (It’s blank by default.) Failure to do so will leave your MySQL server, and ultimately your entire machine, open to malicious attacks. To do this, run the following command from the command line and substitute your preferred password:

 > mysqladmin -u root password sc00bI 

Once you’ve done this, the mysql command will start a command-line client program that lets you issue SQL commands to the database server. When administering MySQL, you should use the root admin user, so issue the following to ensure that MySQL prompts you for your password:

 mysql -u root -p 

You should see some status messages about the version of MySQL and a prompt such as in Figure B-1.

Figure B-1: The MySQL command-line tool

You can use the \q or exit command to exit from the MySQL client.

Now you should create a database. Run the following at the MySQL prompt:

 mysql> CREATE DATABASE catalog; 

To add some data to the catalog database, start by typing the following at the prompt:

 mysql> USE catalog;  Database changed 

You’re now in the catalog database, which has no tables and no data. Type the following at the prompt:

 mysql> CREATE TABLE product (      -> prodid int not null,      -> prodname varchar(30)      -> ); 

The -> prompt means you haven’t finished a SQL statement. This isn’t a problem, because SQL statements can span multiple lines.

If you see a prompt such as '>, it means you have an opening apostrophe that hasn’t been closed. Similarly, "> means you have an opening quote mark that hasn’t been closed. Apostrophes must always balance out and so must quote marks.

To view details of your new table, type the following:

 mysql> DESCRIBE product;  +----------+-------------+------+-----+---------+-------+  | Field    | Type        | Null | Key | Default | Extra |  +----------+-------------+------+-----+---------+-------+  | prodid   | int(11)     |      |     | 0       |       |  | prodname | varchar(30) | YES  |     | NULL    |       |  +----------+-------------+------+-----+---------+-------+  2 rows in set (0.10 sec) 

To insert a row of data into your table, type the following:

 mysql> INSERT INTO product VALUES (      -> 1,      -> 'Yo-Yo'      -> ); 

Now that you have some data in your database, run a SELECT command to access it.

 mysql> SELECT * FROM product;  +--------+----------+  | prodid | prodname |  +--------+----------+  |      1 | Yo-Yo    |  +--------+----------+  1 row in set (0.02 sec) 

This is quite a labor-intensive process. However, there is a better way. Save all the previous commands in a file called mysqlTest.sql, as shown in Listing B-1. The addition of the EXISTS commands ensure that you can change and reuse this script without having to alter the database beforehand.

Listing B.1: mysqlTest.sql

 CREATE DATABASE IF NOT EXISTS catalog;  USE catalog;  DROP TABLE IF EXISTS product;  CREATE TABLE product (    prodid int not null,    prodname varchar(30)  );  DESCRIBE product;  INSERT INTO product VALUES (    1,    'Yo-Yo'  );  SELECT * FROM product; 

You can use this file in two ways. The first is to direct the mysql binary to use it as an input.

 > mysql -u root -p < ./scripts/3316/mysqlTest.sql 

This will produce the required results, in that the table will be created and the data will be entered. However, the DESCRIBE and SELECT commands won’t produce very nice output.

The second way to use the file is from within the mysql binary.

 mysql> \. ./scripts/3316/mysqlTest.sql 

The following are the results:

 mysql> \. ./scripts/3316/mysqlTest.sql  Database changed  Query OK, 0 rows affected (0.01 sec)  Query OK, 0 rows affected (0.07 sec)  +----------+-------------+------+-----+---------+-------+  | Field    | Type        | Null | Key | Default | Extra |  +----------+-------------+------+-----+---------+-------+  | prodid   | int(11)     |      |     | 0       |       |  | prodname | varchar(30) | YES  |     | NULL    |       |  +----------+-------------+------+-----+---------+-------+  2 rows in set (0.01 sec)  Query OK, 1 row affected (0.00 sec)  +--------+----------+  | prodid | prodname |  +--------+----------+  |      1 | Yo-Yo    |  +--------+----------+  1 row in set (0.00 sec) 

As you can see, each command runs in turn.

To give users access to your database, you can use the mysql binary to grant access privileges. The following command grants the user matthewm read-only (SELECT) access to all the tables in the catalog database when connecting from localhost.

 mysql> GRANT SELECT ON catalog.*      -> TO 'matthewm'@'localhost'      -> IDENTIFIED BY 'm00die'; 

catalog.* indicates all tables within the catalog database. To grant access to a single table, you can use catalog.product. The user being granted privileges has the form user'@'hostname where the host name may be replaced by % to indicate all hosts.