Getting Information About the Server and Databases

The mysqlshow script and SHOW SQL command allow you, as an administrator, to get a lot of information about what is going on with your databases and server.

Retrieving Database Information

The mysqlshow script gives information about databases. If you run it without any parameters, as

 mysqlshow 

it will give you a list of databases accessible to you as a user . This gives the same result as running

 show databases; 

from within the mysql monitor or other user interface.

As with most of the command-line scripts, you can supply mysqlshow with -u and a username and -p to supply that user's password. It also has various useful options. Running

 mysqlshow -help 

will supply you with a full list of these options.

One option is to provide a database name to get more information about a particular database. For example, if you specify a database as follows , you will get a list of the tables in that database:

 mysqlshow u  username  p  database  

You can get more information about the tables by adding --status to the end of this line. Try it for yourself with the employee database, as shown here:

 mysqlshow -u  username  --status employee 

The output is a little hard to read because it's so wide, but it includes information about the storage engine used in each table, how much data is in each table, the current value of any auto-increment column in a table, and the character set used in each table.

You can also use the SQL command SHOW inside your MySQL client to get information about a database and the status of the server. By this stage in this book, you should be familiar with using

 show databases; 

and

 show tables; 

to get information about databases and tables. However, the SHOW statement has a huge number of other options you can use.

You can use

 show columns from  tablename  ; 

to give you the same information you would get from a DESC statement. Similarly, you can use

 show table status 

to get the same information we got from mysqlshow --status .

Viewing Server Status and Variables

To get information about the server and how it's running, we can look at the server status and the values of variables.

To see the status of your MySQL server, you can use either

 SHOW STATUS 

inside MySQL, or

 mysqladmin u  username  p extended-status 

from the command line.

What this mostly gives you is a lot of statistics about what the server has been doing since it was started. You may be interested to look at the values named com_* ”for example, com_select tells you how many select statements have been executed by the server.

Some other particularly interesting values to look at are listed here:

• threads_connected : This is the current number of connections to the server.

• slow_queries : This is the number of queries this server has run that have taken more time than the value of the server variable long_query_time . These queries are logged in the Slow Query Log. We will return to slow queries in Chapter 19, "Optimizing Your Queries."

• uptime : This is how long this server instance has been running in seconds.

To see the values of server variables, you can use

 show variables; 

from inside MySQL or

 mysqladmin u  username  p variables 

from the command line.

The values of most of these variables can be set in your configuration file, from the command line when you start the server, or dynamically inside MySQL using the SET command. Configuration is covered in Chapter 12, "Configuring MySQL," and the use of SET is covered later in this chapter.

Viewing Process Information

You can see what processes are currently running on your server by running the following command inside MySQL:

 show processlist; 

At a minimum you will see information about the query you just typed ( show processlist ).

You can get the same information from the command line using

 mysqladmin u  username  p showprocesslist 

Viewing Grant and Privilege Information

You can see what privileges an individual user has been granted by typing

 show grants for  username  @  host  ; 

This is expressed in terms of a GRANT statement that could be used to reproduce the privileges the user has. For example,

 mysql> show grants for root@localhost; 

on my system will produce the following results:

 +---------------------------------------------------------------------+  Grants for root@localhost                                            +---------------------------------------------------------------------+  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION  +---------------------------------------------------------------------+ 1 row in set (0.40 sec) 

You can also remind yourself what the various privileges are by typing

 show privileges; 

This will give you a reference list of the privileges available on the system.

Viewing Reference Information About Tables

You can see what table types are installed and available by typing

 show table types; 

You can see the create statement that would be needed to create any particular table in a database by typing

 show create table  tablename  ; 

For example, in our sample employee database, typing

 show create table department; 

will give us back this:

 CREATE TABLE 'department' ('departmentID' int(11) NOT NULL auto_increment,   'name' varchar(30) default NULL,   PRIMARY KEY  ('departmentID')) TYPE=InnoDB CHARSET=latin1 

(Note that the column names are quoted to be safe, and the default character set ”which we didn't specify ”is specified here.)