Table & Server Administration Statements

This section of our documentation of MySQL explains SQL statements for administering of MySQL databases. Basically, this section covers the SQL statements used when one is in the mode of managing a database system, not creating databases. This mode is a fairly distinct mindset and sometimes the responsibility of different persons from those who manipulate the data itself.

Below is a listing of related SQL statements followed by related clauses, with brief explanations of each one's purpose. Click on an SQL statement to see a more detailed explanation including examples of the statement's use.

ANALYZE TABLE

Use this MySQL statement to store information that can be useful later when the MySQL optimizer chooses the order for consulting indexes during a query.

BACKUP TABLE

This MySQL statement makes a backup copy of a MyISAM table. However, it has been deprecated because it does not work reliably.

CACHE INDEX

This MySQL statement tells MySQL to cache the given indexes to a specific index cache, which can be created with the SET GLOBAL statement. This MySQL statement is used only on MyISAM tables.

CHECK TABLE

Use this MySQL statement to check tables for errors; as of version 5.1.9 of MySQL, it works with the MyISAM, InnoDB, ARCHIVE, and CSV storage engines. If errors are discovered, you should run the REPAIR TABLE statement to repair the table.

CHECKSUM TABLE

This MySQL statement returns a MyISAM table's live checksum value, a value that can be optionally maintained to improve a table's repairability.

CONNECTION_ID( )

This MySQL function returns the MySQL connection or thread identification number for the MySQL session.

FLUSH

Use this MySQL statement to clear temporary caches in MySQL.

GET_LOCK( )

This MySQL function attempts to get a lock on the name given in the first argument.

IS_FREE_LOCK( )

Use this MySQL function to determine whether the name of the lock given in parentheses is free and available as a lock name.

IS_USED_LOCK( )

This MySQL function determines whether the name given is already in use as a lock name.

KILL

Use this MySQL statement to terminate a client connection to MySQL. You can use the SHOW PROCESSLIST statement to obtain a connection thread identifier for use in this statement.

LOCK TABLES

Use this MySQL statement to lock the given tables for exclusive use by the current connection thread. There are a few different types of locks that may be used: one allow users to continue to retrieve data, but not change data; another locks all other users out, totally.

mysqlbug

This script is used for reporting bugs to MySQL developers at Oracle.

mysqlslap

This utility is used to emulate a load of multiple clients on the server to check timing of the system.

OPTIMIZE TABLE

Use this MySQL statement to optimize the data contained in a table. Optimization is useful when many rows have been deleted from a table. It's also useful to run this statement periodically with a table that contains several variable-character-width columns (i.e., VARCHAR, BLOB, and TEXT columns)

RELEASE_LOCK( )

This MySQL function releases a lock created by GET_LOCK().

REPAIR TABLE

Use this MySQL statement to repair corrupted MyISAM tables.

RESTORE TABLE

This MySQL statement restores a table that was saved to the filesystem by the BACKUP TABLE statement, which is deprecated.

SHOW ENGINE

Use this MySQL statement to display details of the status of a given storage engine. This statement provides information on table and record locks for transactions, waiting locks, pending requests, buffer statistics and activity, and logs related to the engine.

SHOW ENGINES

Use this MySQL statement to list the storage engines (types of tables) available for the installation of MySQL you're using.

SHOW OPEN TABLES

Use this MySQL statement to display a list of tables that are open; that is to say, in the table cache. The list does not include any temporary tables.

SHOW PLUGINS

Use this MySQL statement to display a list of plugins on the server.

SHOW PROCESSLIST

This statement displays a list of connection threads running on the MySQL server. You'll need this if one of them is locked and slowing down the system.

SHOW SCHEMAS

This MySQL statement is synonymous with SHOW DATABASES. See the description of that statement for more information and examples.

SHOW STATUS

This MySQL statement displays status information and variables from the server.

SHOW TABLE STATUS

This MySQL statement displays status information on a set of tables from a database.

SHOW TABLE STATUS

This MySQL statement displays status information on a set of tables from a database.

SHOW VARIABLES

This MySQL statement displays the system variables for the MySQL server.

UNLOCK TABLES

Use this MySQL statement to unlock tables that were locked by the current connection thread with the LOCK TABLES statement.