php_mysql_apache [Electronic resources] نسخه متنی

Using the SHOW Command

There are several different uses of the SHOW command, which will produce output displaying a great deal of useful information about your MySQL database, users, and tables. Depending on your access level, some of the SHOW commands will not be available to you or will provide only minimal information. The root-level user has the capability to use all the SHOW commands, with the most comprehensive results.

The common uses of SHOW include the following, which you'll soon learn about in more detail:

SHOW GRANTS FOR user
SHOW DATABASES [LIKE 
something ]
SHOW [OPEN] TABLES [FROM 
database_name ] [LIKE 
something ]
SHOW CREATE TABLE 
table_name 
SHOW [FULL] COLUMNS FROM 
table_name  [FROM 
database_name ] [LIKE 
something ]
SHOW INDEX FROM 
table_name  [FROM 
database_name ]
SHOW TABLE STATUS [FROM 
db_name ] [LIKE 
something ]
SHOW STATUS [LIKE 
something ]
SHOW VARIABLES [LIKE 
something ]

The SHOW GRANTS command will display the privileges for a given user at a given host. This is any easy way to check on the current status of a user, especially if you have a request to modify a user's privileges. With SHOW GRANTS, you can check first to see that the user doesn't already have the requested privileges. For example, see the privileges available to the joeuser user:

mysql> show grants for joe@localhost; 
+------------------------------------------------------------+
| Grants for joeuser@localhost                               |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'joeuser'@'localhost'
IDENTIFIED BY PASSWORD '34f3a6996d856efd'                    |
| GRANT ALL PRIVILEGES ON testDB.* TO 'joeuser'@'localhost'  |
+------------------------------------------------------------+

If you're not the root-level user or the joeuser user, you'll get an error. Unless you're the root-level user, you can see only the information relevant to your user. For example, the joeuser user isn't allowed to view information about the root-level user:

mysql> show grants for root@localhost; 
ERROR 1044: Access denied for user:'joeuser@localhost' to database 'mysql'

Be aware of your privilege level throughout the remainder of this chapter. If you are not the root-level user, some of these commands will not be available to you or will display only limited information.

Retrieving Information About Databases and Tables

You've used a few of the basic SHOW commands earlier in this book to view the list of databases and tables on your MySQL server. As a refresher, the SHOW DATABASES command does just thatit lists all the databases on the MySQL server:

mysql> show databases; 
+-------------------+
| Database          |
+-------------------+
| testDB            |
| mysql             |
+-------------------+
2 rows in set (0.00 sec)

After you've selected a database to work with, you can also use SHOW to list the tables in the database. In this example, we're using testDB (your table listing may vary):

mysql> show tables; 
+---------------------+
| Tables_in_testDB    |
+---------------------+
| grocery_inventory   |
| email               |
| master_name         |
| myTest              |
| testTable           |
+---------------------+
5 rows in set (0.01 sec)

If you add OPEN to your SHOW TABLES command, you will get a list of all the tables in the table cache, showing how many times they're cached and in use:

mysql> SHOW OPEN TABLES; 
+--------------------------+--------------------+
| Open_tables_in_testDB    | Comment            |
+--------------------------+--------------------+
| grocery_inventory        | cached=1, in_use=0 |
| email                    | cached=1, in_use=0 |
| testTable                | cached=1, in_use=0 |
| master_name              | cached=1, in_use=0 |
| myTest                   | cached=1, in_use=0 |
+--------------------------+--------------------+
5 rows in set (0.00 sec)

Using this information in conjunction with the FLUSH TABLES command you learned earlier in this chapter will help keep your database running smoothly. If SHOW OPEN TABLES shows that tables are cached numerous times, but aren't currently in use, go ahead and use FLUSH TABLES to free up that memory.

Retrieving Table Structure Information

A very helpful command is SHOW CREATE TABLE, which does what it sounds likeit shows you the SQL statement used to create a specified table:

mysql> show create table grocery_inventory; 
+--------------------+---------------------------------------------+
| Table              | Create Table
|+-------------------+---------------------------------------------+
| grocery_inventory  | CREATE TABLE 'grocery_inventory' (
'id' int(11) NOT NULL auto_increment,
'item_name' varchar(50) NOT NULL default ",
'item_desc' text,
'item_price' float NOT NULL default '0',
'curr_qty' int(11) NOT NULL default '0',
PRIMARY KEY  ('id')
) TYPE=MyISAM
+--------------------+---------------------------------------------+
1 row in set (0.00 sec)

This is essentially the same information you'd get if you dumped the table schema, but the SHOW CREATE TABLE command can be used quickly if you're just looking for a reminder or a simple reference to a particular table-creation statement.

If you need to know the structure of the table, but don't necessarily need the SQL command to create it, you can use the SHOW COLUMNS command:

mysql> show columns from grocery_inventory; 
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     |      | PRI | NULL    | auto_increment |
| item_name  | varchar(50) |      |     |         |                |
| item_desc  | text        | YES  |     | NULL    |                |
| item_price | float       |      |     | 0       |                |
| curr_qty   | int(11)     |      |     | 0       |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

The SHOW COLUMNS and DESCRIBE commands are aliases for one another and, therefore, do the same thing.

The SHOW INDEX command will display information about all the indexes present in a particular table. The syntax is

SHOW INDEX FROM table_name [FROM database_name]

This command produces a table full of information, ranging from the column name to cardinality of the index. The columns returned from this command are described in Table 28.1.

Another command that produces a wide table full of results is the SHOW TABLE STATUS command. The syntax of this command is

SHOW TABLE STATUS [FROM 
database_name ] LIKE '
something '

This command produces a table full of information, ranging from the size and number of rows to the next value to be used in an auto_increment field. The columns returned from this command are described in Table 28.2.

Retrieving System Status

The SHOW STATUS and SHOW VARIABLES commands will quickly provide important information about your database server. The syntax for these commands is simply SHOW STATUS or SHOW VARIABLESnothing fancy.

There are no less than 54 status variables as the output of SHOW STATUS, but the most useful are

• Aborted_connects
  The number of failed attempts to connect to the MySQL server. Anytime you see an aborted connection, you should investigate the problem. It could be related to a bad username and password in a script, or your number of simultaneous connections could be set too low.

• Connections
  The aggregate number of connection attempts to the MySQL server during the current period of uptime.

• Max_used_connections
  The maximum number of connections that have been in use simultaneously during the current period of uptime.

• Slow_queries
  The number of queries that have taken more than long_query_time, which defaults to 10 seconds. If you have more than one, it's time to investigate your SQL syntax!

• Uptime
  Total number of seconds the server has been up during the current period of uptime.

You can find a comprehensive list of SHOW STATUS variables and an explanation of their values in the MySQL manual, located at http://www.mysql.com/doc/S/H/SHOW_STATUSl.

The SHOW VARIABLES command produces even more results than SHOW STATUSapproximately 82! The variables reported from SHOW VARIABLES control the general operation of MySQL and include the following useful tidbits:

• connect_timeout
  Shows the number of seconds the MySQL server will wait during a connection attempt before it gives up.

• have_innodb
  Will show YES if MySQL supports InnoDB tables.

• have_bdb
  Will show YES if MySQL supports Berkeley DB tables.

• max_connections
  The allowable number of simultaneous connections to MySQL before a connection is refused.

• port
  The port on which MySQL is running.

• table_type
  The default table type for MySQL, usually MyISAM.

• version
  The MySQL version number.

You can find a comprehensive list of the variables returned by the SHOW VARIABLES results and an explanation of their values in the MySQL manual at http://www.mysql.com/doc/S/H/SHOW_VARIABLESl. After you know the values you have, you can change them in your MySQL configuration file or startup command.