When inside the MySQL command shell, you can use several different commands that share the prefix SHOW, to get various bits of information about the current state of the database. The following informations describe some of the more common SHOW commands.

SHOW COLUMNS

The SHOW COLUMNS command returns a listing of all the columns in a table, and their attributes. The general format of SHOW COLUMNS is as follows:

SHOW COLUMNS FROM [table] FROM [database]

If you want to get a list of all the columns in the New_Vehicle table, you could use the following:

SHOW COLUMNS FROM New_Vehicles FROM VehicleInventory;

Which returns the following:

+--------------------+----------------------+----------+-------+-------------+---------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+----------+-------+-------------+----------------------+
| vehicle_id | int(11) | | PRI | NULL | auto_increment |
| model_id | int(11) | YES | | NULL | |
| price | decimal(10,2) | YES | | NULL | |
| color | varchar(200) | YES | | NULL | |
| description | text | YES | | NULL | |
| modelyear | int(11) | YES | | NULL | |
+---------------------+--------------------+---------+-------+-------------+-----------------------+

In the return output, the Field column contains the name of each column, Type shows the column data type, Null specifies whether or not NULL values are allowed in the column, Key indicates what key or index types each column belongs to, Default shows any specified default column value, and Extra shows any special treatment given to each column. Note that this command is functionally identical to MySQL's DESCRIBE [table] command.

SHOW CREATE TABLE

You might want to learn what command could be used to create a given table. For that, you have the SHOW CREATE TABLE command:

SHOW CREATE TABLE [table name]

To get the create command that would create your New_Vehicles table, send:

SHOW CREATE TABLE New_Vehicles G

Running this command against the VehicleInventory database gives you this:

*************************** 1. row ***************************
Table: New_Vehicles

Create Table: CREATE TABLE `New_Vehicles` (

`vehicle_id` int(11) NOT NULL auto_increment,

`model_id` int(11) default NULL,

`price` decimal(10,2) default NULL,

`color` varchar(200) default NULL,

`description` text,

`modelyear` int(11) default NULL,

PRIMARY KEY (`vehicle_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

This whole CREATE TABLE command can then be entered in a fresh database to create an exact copy of the structure of the New_Vehicles table.
 

SHOW DATABASES

The SHOW DATABASES command does exactly what it looks like -- it shows all databases in the system that the currently logged-in account has access to. Simply run the command:

SHOW DATABASES;

And it will return a list of the databases accessible by the current user:

+------------------------+
| Database |
+------------------------+
| VehicleInventory |
| mysql |
+------------------------+

SHOW GRANTS

The SHOW GRANTS command lists all access privileges given to a specific user account. For example, to see all the access granted to the testuser1 account in the VehicleInventory database, use the following:

SHOW GRANTS FOR `testuser1'@'localhost' G

Which returns this:

*************************** 1. row ***************************
Grants for testuser1@localhost: GRANT USAGE ON *.* TO `testuser1'@'localhost'

IDENTIFIED BY PASSWORD `*E69570F2322D3DC1F956C48199FEB21FF2D7D984'

*************************** 2. row ***************************
Grants for testuser1@localhost: GRANT ALL PRIVILEGES ON `vehicleinventory`.* TO

`testuser1'@'localhost'

*************************** 3. row ***************************
Grants for testuser1@localhost: GRANT SELECT, SELECT (description), INSERT

(description), UPDATE (description) ON `vehicleinventory`.`used_vehicles` TO

`testuser1'@'localhost'

Database Maintenance

You've created your Vehicle Inventory database, put it in production, set up searching, and the client is now pleased with the outcome. Time to sit back and relax, right?
Not quite. There's another thing you might want to consider, to help minimize the cost of a catastrophic loss of the production databases. What you need are backups.

Creating Backups

Backups in MySQL are very easy to perform, thanks to a great set of client tools that come with the sysem. To create your backup, you can use the mysqldump utility. The first thing you need to do is exit the MySQL client, if needed, and find yourself at your standard shell prompt:

mysql> exit

Once you're back at a command prompt, it's a simple matter of invoking the mysqldump command-line tool. For most database backups, the following generic format will work:

mysqldump --opt databasename > backupfile.sql

Here you're calling the mysqldump command and telling it to dump the database named databasename into a file called backupfile.sql. Also present is the --opt option, which tells the backup utility to use a set of common options that output a format that can help MySQL restore the file more accurately.

For more mysqldump options, type man mysqldump at your command prompt.

To back up the vehicle inventory database, use the following:

mysqldump --opt VehicleInventory > vi_backup.sql

As with the mysql command-line client, if you need to supply a username and password, make sure you use -u and -p accordingly before the name of the database.

You now have a full database-creation script in the file vi_backup.sql. You can now archive this file as needed, restore it to a different server, burn it to a CD or DVD, or anything else you prefer.

Restoring Databases from Backups

In the event that a server crashes, or gets hacked, or you just want a copy of the production database for testing in a development environment, you're going to have to know how to restore your database back-ups. In MySQL, it's extremely simple, as you can use the MySQL command-line client, like so:

mysql databasename < backupfile.sql

So to restore the VehicleInventory database, use this:

mysql VehicleInventory < vi_backup.sql

Note that the name of the destination database for your restoration must be an actual database that already exists in the system. If you want to restore a backup to a new table, just log into the mysql client, create an empty database with the name you want, exit the client, and then restore your backup as desired.