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.
BACKUP TABLE
 This MySQL statement makes a backup copy of a MyISAM table. However, it has been deprecated because it does not work reliably.
SHOW TABLE STATUS
 This MySQL statement displays status information on a set of tables from a database.
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.
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.
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.
SHOW ENGINES
 Use this MySQL statement to list the storage engines (types of tables) available for the installation of MySQL you're using.
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.
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.
RESET
 Use this MySQL statement to reset certain server settings and log files.
SHOW PLUGINS
 Use this MySQL statement to display a list of plugins on the server.
RESTORE TABLE
 This MySQL statement restores a table that was saved to the filesystem by the BACKUP TABLE statement, which is deprecated.
SHOW STATUS
 This MySQL statement displays status information and variables from the server.
SET
 This MySQL statement sets a system or user variable for global or session use.
SHOW TABLE STATUS
 This MySQL statement displays status information on a set of tables from a database.
FLUSH
 Use this MySQL statement to clear temporary caches in MySQL.
SHOW VARIABLES
 This MySQL statement displays the system variables for the MySQL server.
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.
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.
UNLOCK TABLES
 Use this MySQL statement to unlock tables that were locked by the current connection thread with the LOCK TABLES statement.
CONNECTION_ID( )
 This MySQL function returns the MySQL connection or thread identification number for the MySQL session.
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.
GET_LOCK( )
 This MySQL function attempts to get a lock on the name given in the first argument.
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.
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.
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)
IS_USED_LOCK( )
 This MySQL function determines whether the name given is already in use as a lock name.
REPAIR TABLE
 Use this MySQL statement to repair corrupted MyISAM tables.
RELEASE_LOCK( )
 This MySQL function releases a lock created by GET_LOCK().
SHOW SCHEMAS
 This MySQL statement is synonymous with SHOW DATABASES. See the description of that statement for more information and examples.

Need Math Examples

Although we did well in math classes all through school, we have very little experience with the Math Functions of MySQL very little. As a result, we don't have very good examples for them. If you work in science or engineering and have some ideas for easy to understand examples that we can add, please tell us.

MySQL Replication

Whether you’re new to MySQL replication or have been using it for some time, you will find this new book useful. It will help you actually to understand MySQL replication so you can keep it running. It’s especially useful when replication stops and you can’t get it started again.