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 InformationThe 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 VariablesTo 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:
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 InformationYou 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 InformationYou 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 TablesYou 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.) |