CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

The CREATE USER statement was added in MySQL 5.0.2. This statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new record in the mysql.user table that has no privileges. An error occurs if the account already exists. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account.

The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the PASSWORD keyword. See Section 13.5.1.3, “GRANT Syntax”.

DROP USER user [, user] ...

The DROP USER statement removes one or more MySQL accounts. To use it, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account.

DROP USER as present in MySQL 5.0.0 removes only accounts that have no privileges. In MySQL 5.0.2, it was modified to remove account privileges as well. This means that the procedure for removing an account depends on your version of MySQL.

As of MySQL 5.0.2, you can remove an account and its privileges as follows:

DROP USER user;

The statement removes privilege rows for the account from all grant tables.

In MySQL 5.0.0 and 5.0.1, DROP USER deletes only MySQL accounts that have no privileges. In these MySQL versions, it serves only to remove each account record from the user table. To remove a MySQL account completely (including all of its privileges), you should use the following procedure, performing these steps in the order shown:

Important: DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail. This is by design.

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

object_type =
    TABLE
  | FUNCTION
  | PROCEDURE

with_option =
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to from accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. The REVOKE statement is related and enables administrators to remove account privileges. See Section 13.5.1.5, “REVOKE Syntax”.

MySQL account information is stored in the tables of the mysql database. This database and the access control system are discussed extensively in Chapter 5, Database Administration, which you should consult for additional details.

Important: Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

If the grant tables hold privilege rows that contain mixed-case database or table names and the lower_case_table_names system variable is set to a non-zero value, REVOKE cannot be used to revoke these privileges. It will be necessary to manipulate the grant tables directly. (GRANT will not create such rows when lower_case_table_names is set, but such rows might have been created prior to setting the variable.)

Privileges can be granted at several levels:

The object_type clause was added in MySQL 5.0.6. It should be specified as TABLE, FUNCTION, or PROCEDURE when the following object is a table, a stored function, or a stored procedure.

For the GRANT and REVOKE statements, priv_type can be specified as any of the following:

The EXECUTE privilege is not operational until MySQL 5.0.3. CREATE VIEW and SHOW VIEW were added in MySQL 5.0.1. CREATE USER, CREATE ROUTINE, and ALTER ROUTINE were added in MySQL 5.0.3.

The REFERENCES privilege currently is unused.

USAGE can be specified when you want to create a user that has no privileges.

Use SHOW GRANTS to determine what privileges an account has. See Section 13.5.4.12, “SHOW GRANTS Syntax”.

You can assign global privileges by using ON *.* syntax or database-level privileges by using ON db_name.* syntax. If you specify ON * and you have selected a default database, the privileges are granted in that database. (Warning: If you specify ON * and you have not selected a default database, the privileges granted are global.)

The FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative privileges that can only be granted globally (using ON *.* syntax).

Other privileges can be granted globally or at more specific levels.

The priv_type values that you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, ALTER, CREATE VIEW and SHOW VIEW.

The priv_type values that you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT, and UPDATE.

The priv_type values that you can specify at the routine level are ALTER ROUTINE, EXECUTE, and GRANT OPTION. CREATE ROUTINE is not a routine-level privilege because you must have this privilege to create a routine in the first place.

For the global, database, table, and routine levels, GRANT ALL assigns only the privileges that exist at the level you are granting. For example, GRANT ALL ON db_name.* is a database-level statement, so it does not grant any global-only privileges such as FILE.

MySQL allows you to grant privileges even on database objects that do not exist. In such cases, the privileges to be granted must include the CREATE privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for database objects that are to be created at a later time.

Important: MySQL does not automatically revoke any privileges when you drop a table or database. However, if you drop a routine, any routine-level privileges granted for that routine are revoked.

Note: the ‘_’ and ‘%’ wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a ‘_’ character as part of a database name, you should specify it as ‘\_’ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ....

To accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user value in the form user_name@host_name. If a user_name or host_name value is legal as an unquoted identifier, you need not quote it. However, quotes are necessary to specify a user_name string containing special characters (such as ‘-’), or a host_name string containing special characters or wildcard characters (such as ‘%’); for example, 'test-user'@'test-hostname'. Quote the username and hostname separately.

You can specify wildcards in the hostname. For example, user_name@'%.loc.gov' applies to user_name for any host in the loc.gov domain, and user_name@'144.155.166.%' applies to user_name for any host in the 144.155.166 class C subnet.

The simple form user_name is a synonym for user_name@'%'.

MySQL does not support wildcards in usernames. Anonymous users are defined by inserting entries with User='' into the mysql.user table or by creating a user with an empty name with the GRANT statement:

GRANT ALL ON test.* TO ''@'localhost' ...

When specifying quoted values, quote database, table, column, and routine names as identifiers, using backticks (‘`’). Quote hostnames, usernames, and passwords as strings, using single quotes (‘'’).

Warning: If you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user_name@localhost. Otherwise, the anonymous user account for localhost in the mysql.user table (created during MySQL installation) is used when named users try to log in to the MySQL server from the local machine. For details, see Section 5.8.5, “Access Control, Stage 1: Connection Verification”.

You can determine whether this applies to you by executing the following query, which lists any anonymous users:

SELECT Host, User FROM mysql.user WHERE User='';

If you want to delete the local anonymous user account to avoid the problem just described, use these statements:

DELETE FROM mysql.user WHERE Host='localhost' AND User='';
FLUSH PRIVILEGES;

GRANT supports hostnames up to 60 characters long. Database, table, column, and routine names can be up to 64 characters. Usernames can be up to 16 characters. Note: The allowable length for usernames cannot be changed by altering the mysql.user table, and attempting to do so results in unpredictable behavior which may even make it impossible for users to log in to the MySQL server. You should never alter any of the tables in the mysql database in any manner whatsoever except by means of the procedure prescribed by MySQL AB that is described in Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

The privileges for a table, column, or routine are formed additively as the logical OR of the privileges at each of the privilege levels. For example, if the mysql.user table specifies that a user has a global SELECT privilege, the privilege cannot be denied by an entry at the database, table, or column level.

The privileges for a column can be calculated as follows:

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges

In most cases, you grant rights to a user at only one of the privilege levels, so life is not normally this complicated. The details of the privilege-checking procedure are presented in Section 5.8, “The MySQL Access Privilege System”.

If you grant privileges for a username/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE statement. In other words, GRANT may create user table entries, but REVOKE does not remove them; you must do that explicitly using DROP USER or DELETE.

Warning: If you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is very insecure. As of MySQL 5.0.2, you can enable the NO_AUTO_CREATE_USER SQL mode to prevent GRANT from creating a new user if it would otherwise do so, unless IDENTIFIED BY is given to provide the new user a non-empty password.

If a new user is created or if you have global grant privileges, the user's password is set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, this is replaced by the new one.

Passwords can also be set with the SET PASSWORD statement. See Section 13.5.1.6, “SET PASSWORD Syntax”.

In the IDENTIFIED BY clause, the password should be given as the literal password value. It is unnecessary to use the PASSWORD() function as it is for the SET PASSWORD statement. For example:

GRANT ... IDENTIFIED BY 'mypass';

If you do not want to send the password in clear text and you know the hashed value that PASSWORD() would return for the password, you can specify the hashed value preceded by the keyword PASSWORD:

GRANT ...
IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

In a C program, you can get the hashed value by using the make_scrambled_password() C API function.

If you grant privileges for a database, an entry in the mysql.db table is created if needed. If all privileges for the database are removed with REVOKE, this entry is deleted.

The SHOW DATABASES privilege enables the account to see database names by issuing the SHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the --skip-show-database option.

If a user has no privileges for a table, the table name is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement).

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the GRANT OPTION privilege, because two users with different privileges may be able to join privileges!

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

Be aware that when you grant a user the GRANT OPTION privilege at a particular privilege level, any privileges the user possesses (or may be given in the future) at that level can also be granted by that user to other users. Suppose that you grant a user the INSERT privilege on a database. If you then grant the SELECT privilege on the database and specify WITH GRANT OPTION, that user can give to other users not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can grant INSERT, SELECT, and UPDATE.

For a non-administrative user, you should not grant the ALTER privilege globally or for the mysql database. If you do that, the user can try to subvert the privilege system by renaming tables!

The MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, and MAX_CONNECTIONS_PER_HOUR count options limit the number of queries, updates, and logins a user can perform during any given one-hour period. If count is 0 (the default), this means that there is no limitation for that user.

The MAX_USER_CONNECTIONS count option, implemented in MySQL 5.0.3, limits the maximum number of simultaneous connections that the account can make. If count is 0 (the default), the max_user_connections system variable determines the number of simultaneous connections for the account.

Note: To specify any of these resource-limit options for an existing user without affecting existing privileges, use GRANT USAGE ON *.* ... WITH MAX_....

See Section 5.9.4, “Limiting Account Resources”.

MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the username and password. To specify SSL-related options for a MySQL account, use the REQUIRE clause of the GRANT statement. (For background information on the use of SSL with MySQL, see Section 5.9.7, “Using Secure Connections”.)

There are a number of different possibilities for limiting connection types for a given account:

The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause like this:

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/Email=tonu@example.com'
  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

The AND keyword is optional between REQUIRE options.

The order of the options does not matter, but no option can be specified twice.

When mysqld starts, all privileges are read into memory. For details, see Section 5.8.7, “When Privilege Changes Take Effect”.

Note that if you are using table, column, or routine privileges for even one user, the server examines table, column, and routine privileges for all users and this slows down MySQL a bit. Similarly, if you limit the number of queries, updates, or connections for any users, the server must monitor these values.

The biggest differences between the standard SQL and MySQL versions of GRANT are:

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

The RENAME USER statement renames existing MySQL accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. An error occurs if any old account does not exist or any new account exists. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account.

The RENAME USER statement was added in MySQL 5.0.2.

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts. To use REVOKE, you must have the GRANT OPTION privilege, and you must have the privileges that you are revoking.

For details on the levels at which privileges exist, the allowable priv_type values, and the syntax for specifying users and passwords, see Section 13.5.1.3, “GRANT Syntax”

If the grant tables hold privilege rows that contain mixed-case database or table names and the lower_case_table_names system variable is set to a non-zero value, REVOKE cannot be used to revoke these privileges. It will be necessary to manipulate the grant tables directly. (GRANT will not create such rows when lower_case_table_names is set, but such rows might have been created prior to setting the variable.)

To revoke all privileges, use the following syntax, which drops all global, database-, table-, and column-level privileges for the named user or users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database.

SET PASSWORD [FOR user] = PASSWORD('some password')

The SET PASSWORD statement assigns a password to an existing MySQL user account.

With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.

With a FOR clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

That is equivalent to the following statements:

UPDATE mysql.user SET Password=PASSWORD('newpass')
  WHERE User='bob' AND Host='%.loc.gov';
FLUSH PRIVILEGES;

Note: If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use the preceding SET PASSWORD or UPDATE statement without reading Section 5.8.9, “Password Hashing as of MySQL 4.1”, first. The password format changed in MySQL 4.1, and under certain circumstances it is possible that if you change your password, you might not be able to connect to the server afterward.

You can see which account the server authenticated you as by executing SELECT CURRENT_USER().

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM and BDB. For InnoDB the table is locked with a write lock. This statement works with MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

For more information on how the analysis works withinInnoDB, see Section 14.2.16, “Restrictions on InnoDB Tables”.

MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant.

This statement requires SELECT and INSERT privileges for the table.

ANALYZE TABLE returns a result set with the following columns:

You can check the stored key distribution with the SHOW INDEX statement. See Section 13.5.4.13, “SHOW INDEX Syntax”.

If the table has not changed since the last ANALYZE TABLE statement, the table is not analyzed again.

ANALYZE TABLE statements are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used. This is done so that ANALYZE TABLE statements used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

Note: This statement is deprecated. We are working on a better replacement for it that will provide online backup capabilities. In the meantime, the mysqlhotcopy script can be used instead.

BACKUP TABLE copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. The statement works only for MyISAM tables. It copies the .frm definition and .MYD data files. The .MYI index file can be rebuilt from those two files. The directory should be specified as a full pathname. To restore the table, use RESTORE TABLE.

During the backup, a read lock is held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot (preventing any of them from being changed during the backup operation), issue a LOCK TABLES statement first, to obtain a read lock for all tables in the group.

BACKUP TABLE returns a result set with the following columns:

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

CHECK TABLE checks a table or tables for errors. CHECK TABLE works for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. For MyISAM tables, the key statistics are updated as well.

As of MySQL 5.0.2, CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist.

CHECK TABLE returns a result set with the following columns:

Note that the statement might produce many rows of information for each checked table. The last row has a Msg_type value of status and the Msg_text normally should be OK. If you don't get OK, or Table is already up to date you should normally run a repair of the table. See Section 5.10.4, “Table Maintenance and Crash Recovery”. Table is already up to date means that the storage engine for the table indicated that there was no need to check the table.

The FOR UPGRADE option checks whether the named tables are compatible with the current version of MySQL. This option was added in MySQL 5.0.19. With FOR UPGRADE, the server checks each table to determine whether there have been any incompatible changes in any of the table's data types or indexes since the table was created. If not, the check succeeds. Otherwise, if there is a possible incompatibility, the server runs a full check on the table (which might take some time). If the full check succeeds, the server marks the table's .frm file with the current MySQL version number. Marking the .frm file ensures that further checks for the table with the same version of the server will be fast.

Incompatibilities might occur because the storage format for a data type has changed or because its sort order has changed. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.

Currently, FOR UPGRADE discovers these incompatibilities:

The other check options that can be given are shown in the following table. These options apply only to checking MyISAM tables and are ignored for InnoDB tables and views.

If none of the options QUICK, MEDIUM, or EXTENDED are specified, the default check type for dynamic-format MyISAM tables is MEDIUM. This has the same result as running myisamchk --medium-check tbl_name on the table. The default check type also is MEDIUM for static-format MyISAM tables, unless CHANGED or FAST is specified. In that case, the default is QUICK. The row scan is skipped for CHANGED and FAST because the rows are very seldom corrupted.

You can combine check options, as in the following example that does a quick check on the table to determine whether it was closed properly:

CHECK TABLE test_table FAST QUICK;

Note: In some cases, CHECK TABLE changes the table. This happens if the table is marked as “corrupted” or “not closed properly” but CHECK TABLE does not find any problems in the table. In this case, CHECK TABLE marks the table as okay.

If a table is corrupted, it is most likely that the problem is in the indexes and not in the data part. All of the preceding check types check the indexes thoroughly and should thus find most errors.

If you just want to check a table that you assume is okay, you should use no check options or the QUICK option. The latter should be used when you are in a hurry and can take the very small risk that QUICK does not find an error in the data file. (In most cases, under normal usage, MySQL should find any error in the data file. If this happens, the table is marked as “corrupted” and cannot be used until it is repaired.)

FAST and CHANGED are mostly intended to be used from a script (for example, to be executed from cron) if you want to check tables from time to time. In most cases, FAST is to be preferred over CHANGED. (The only case when it is not preferred is when you suspect that you have found a bug in the MyISAM code.)

EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

Some problems reported by CHECK TABLE cannot be corrected automatically:

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

CHECKSUM TABLE reports a table checksum.

With QUICK, the live table checksum is reported if it is available, or NULL otherwise. This is very fast. A live checksum is enabled by specifying the CHECKSUM=1 table option when you create the table; currently, this is supported only for MyISAM tables. See Section 13.1.5, “CREATE TABLE Syntax”.

With EXTENDED, the entire table is read row by row and the checksum is calculated. This can be very slow for large tables.

If neither QUICK nor EXTENDED is specified, MySQL returns a live checksum if the table storage engine supports it and scans the table otherwise.

For a non-existent table, CHECKSUM TABLE returns NULL and, as of MySQL 5.0.3, generates a warning.

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

This statement requires SELECT and INSERT privileges for the table.

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables.

OPTIMIZE TABLE works only for MyISAM, BDB, and InnoDB tables.

For MyISAM tables, OPTIMIZE TABLE works as follows:

For BDB tables, OPTIMIZE TABLE currently is mapped to ANALYZE TABLE. See Section 13.5.2.1, “ANALYZE TABLE Syntax”.

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

You can make OPTIMIZE TABLE work on other storage engines by starting mysqld with the --skip-new or --safe-mode option. In this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.

OPTIMIZE TABLE returns a result set with the following columns:

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

OPTIMIZE TABLE statements are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword(or its alias LOCAL) is used. This is done so that OPTIMIZE TABLE statements used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
    tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk --recover tbl_name. REPAIR TABLE works for MyISAM and for ARCHIVE tables. See Section 14.1, “The MyISAM Storage Engine”, and Section 14.8, “The ARCHIVE Storage Engine”.

This statement requires SELECT and INSERT privileges for the table.

Normally, you should never have to run this statement. However, if disaster strikes, REPAIR TABLE is very likely to get back all your data from a MyISAM table. If your tables become corrupted often, you should try to find the reason for it, to eliminate the need to use REPAIR TABLE. See Section A.4.2, “What to Do If MySQL Keeps Crashing”, and Section 14.1.4, “MyISAM Table Problems”.

Warning: If the server dies during a REPAIR TABLE operation, it is essential after restarting it that you immediately execute another REPAIR TABLE statement for the table before performing any other operations on it. (It is always a good idea to start by making a backup.) In the worst case, you might have a new clean index file without information about the data file, and then the next operation you perform could overwrite the data file. This is an unlikely but possible scenario.

REPAIR TABLE returns a result set with the following columns:

The REPAIR TABLE statement might produce many rows of information for each repaired table. The last row has a Msg_type value of status and Msg_test normally should be OK. If you do not get OK, you should try repairing the table with myisamchk --safe-recover. (REPAIR TABLE does not yet implement all the options of myisamchk.) With myisamchk --safe-recover, you can also use options that REPAIR TABLE does not support, such as --max-record-length.

If QUICK is given, REPAIR TABLE tries to repair only the index tree. This type of repair is like that done by myisamchk --recover --quick.

If you use EXTENDED, MySQL creates the index row by row instead of creating one index at a time with sorting. This type of repair is like that done by myisamchk --safe-recover.

There is also a USE_FRM mode available for REPAIR TABLE. Use this if the .MYI index file is missing or if its header is corrupted. In this mode, MySQL re-creates the .MYI file using information from the .frm file. This kind of repair cannot be done with myisamchk. Note: Use this mode only if you cannot use regular REPAIR modes. The .MYI header contains important table metadata (in particular, current AUTO_INCREMENT value and Delete link) that are lost in REPAIR ... USE_FRM. Don't use USE_FRM if the table is compressed because this information is also stored in the .MYI file.

REPAIR TABLE statements are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used. This is done so that REPAIR TABLE statements used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'

RESTORE TABLE restores the table or tables from a backup that was made with BACKUP TABLE. Existing tables are not overwritten; if you try to restore over an existing table, an error occurs. Just as for BACKUP TABLE, RESTORE TABLE currently works only for MyISAM tables. The directory should be specified as a full pathname.

The backup for each table consists of its .frm format file and .MYD data file. The restore operation restores those files, and then uses them to rebuild the .MYI index file. Restoring takes longer than backing up due to the need to rebuild the indexes. The more indexes the table has, the longer it takes.

RESTORE TABLE returns a result set with the following columns:

SHOW CHARACTER SET [LIKE 'pattern']

The SHOW CHARACTER SET statement shows all available character sets. It takes an optional LIKE clause that indicates which character set names to match. For example:

mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | cp1252 West European        | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+

The Maxlen column shows the maximum number of bytes required to store one character.

SHOW COLLATION [LIKE 'pattern']

The output from SHOW COLLATION includes all available character sets. It takes an optional LIKE clause whose pattern indicates which collation names to match. For example:

mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         |          |       0 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       0 |
| latin1_danish_ci  | latin1  | 15 |         |          |       0 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       0 |
| latin1_general_ci | latin1  | 48 |         |          |       0 |
| latin1_general_cs | latin1  | 49 |         |          |       0 |
| latin1_spanish_ci | latin1  | 94 |         |          |       0 |
+-------------------+---------+----+---------+----------+---------+

The Default column indicates whether a collation is the default for its character set. Compiled indicates whether the character set is compiled into the server. Sortlen is related to the amount of memory required to sort strings expressed in the character set.

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

SHOW COLUMNS displays information about the columns in a given table. It also works for views as of MySQL 5.0.1.

If the data types differ from what you expect them to be based on your CREATE TABLE statement, note that MySQL sometimes changes data types when you create or alter a table. The conditions for which this occurs are described in Section 13.1.5.1, “Silent Column Specification Changes”.

The FULL keyword causes the output to include the privileges you have as well as any per-column comments for each column.

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. In other words, these two statements are equivalent:

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW FIELDS is a synonym for SHOW COLUMNS. You can also list a table's columns with the mysqlshow db_name tbl_name command.

The DESCRIBE statement provides information similar to SHOW COLUMNS. See Section 13.3.1, “DESCRIBE Syntax”.

SHOW CREATE {DATABASE | SCHEMA} db_name

Shows the CREATE DATABASE statement that creates the given database. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE as of MySQL 5.0.2.

mysql> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

mysql> SHOW CREATE SCHEMA test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

SHOW CREATE DATABASE quotes table and column names according to the value of the SQL_QUOTE_SHOW_CREATE option. See Section 13.5.3, “SET Syntax”.

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

This statement is a MySQL extension. Similar to SHOW CREATE TABLE, it returns the exact string that can be used to re-create the named routine.

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
       Function: hello
       sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
                 RETURN CONCAT('Hello, ',s,'!')

SHOW CREATE TABLE tbl_name

Shows the CREATE TABLE statement that creates the given table. As of MySQL 5.0.1, this statement also works with views.

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM

SHOW CREATE TABLE quotes table and column names according to the value of the SQL_QUOTE_SHOW_CREATE option. See Section 13.5.3, “SET Syntax”.

SHOW CREATE VIEW view_name

This statement shows a CREATE VIEW statement that creates the given view.

mysql> SHOW CREATE VIEW v;
+------+----------------------------------------------------+
| View | Create View                                        |
+------+----------------------------------------------------+
| v    | CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` |
+------+----------------------------------------------------+

This statement was added in MySQL 5.0.1.

Prior to MySQL 5.0.11, the output columns from this statement were shown as Table and Create Table.

Use of SHOW CREATE VIEW requires the SHOW VIEW privilege and the SELECT privilege for the view in question.

You can also obtain information about view objects from INFORMATION_SCHEMA, which contains a VIEWS table. See Section 20.15, “The INFORMATION_SCHEMA VIEWS Table”.

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']

SHOW DATABASES lists the databases on the MySQL server host. SHOW SCHEMAS is a synonym for SHOW DATABASES as of MySQL 5.0.2.

You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege. You can also get this list using the mysqlshow command.

If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege.

SHOW ENGINE engine_name {LOGS | STATUS }

SHOW ENGINE displays log or status information about a storage engine. The following statements currently are supported:

SHOW ENGINE BDB LOGS
SHOW ENGINE INNODB STATUS

SHOW ENGINE BDB LOGS displays status information about existing BDB log files. It returns the following fields:

SHOW ENGINE INNODB STATUS displays extensive information about the state of the InnoDB storage engine.

The InnoDB Monitors provide additional information about InnoDB processing. See Section 14.2.11.1, “SHOW ENGINE INNODB STATUS and the InnoDB Monitors”.

Older (and now deprecated) synonyms for these statements are SHOW [BDB] LOGS and SHOW INNODB STATUS.

SHOW [STORAGE] ENGINES

SHOW ENGINES displays status information about the server's storage engines. This is particularly useful for checking whether a storage engine is supported, or to see what the default engine is. SHOW TABLE TYPES is a deprecated synonym.

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
 Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
 Engine: HEAP
Support: YES
Comment: Alias for MEMORY
*************************** 4. row ***************************
 Engine: MERGE
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 5. row ***************************
 Engine: MRG_MYISAM
Support: YES
Comment: Alias for MERGE
*************************** 6. row ***************************
 Engine: ISAM
Support: NO
Comment: Obsolete storage engine, now replaced by MyISAM
*************************** 7. row ***************************
 Engine: MRG_ISAM
Support: NO
Comment: Obsolete storage engine, now replaced by MERGE
*************************** 8. row ***************************
 Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 9. row ***************************
 Engine: INNOBASE
Support: YES
Comment: Alias for INNODB
*************************** 10. row ***************************
 Engine: BDB
Support: YES
Comment: Supports transactions and page-level locking
*************************** 11. row ***************************
 Engine: BERKELEYDB
Support: YES
Comment: Alias for BDB
*************************** 12. row ***************************
 Engine: NDBCLUSTER
Support: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 13. row ***************************
 Engine: NDB
Support: NO
Comment: Alias for NDBCLUSTER
*************************** 14. row ***************************
 Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 15. row ***************************
 Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 16. row ***************************
 Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 17. row ***************************
 Engine: FEDERATED
Support: YES
Comment: Federated MySQL storage engine
*************************** 18. row ***************************
 Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)

The Support value indicates whether the particular storage engine is supported, and which is the default engine. For example, if the server is started with the --default-table-type=InnoDB option, the Support value for the InnoDB row has the value DEFAULT. See Chapter 14, Storage Engines and Table Types.

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors.

The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.7, “SELECT Syntax”.

The SHOW COUNT(*) ERRORS statement displays the number of errors. You can also retrieve this number from the error_count variable:

SHOW COUNT(*) ERRORS;
SELECT @@error_count;

For more information, see Section 13.5.4.25, “SHOW WARNINGS Syntax”.

SHOW GRANTS FOR user

This statement lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. The account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

As of MySQL 5.0.24, if SHOW GRANTS FOR CURRENT_USER (or any of the equivalent syntaxes) is used in DEFINER context, such as within a stored procedure that is defined with SQL SECURITY DEFINER), the grants displayed are those of the definer and not the invoker.

SHOW GRANTS displays only the privileges granted explicitly to the named account. Other privileges might be available to the account, but they are not displayed. For example, if an anonymous account exists, the named account might be able to use its privileges, but SHOW GRANTS will not display them.

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW INDEX returns table index information. The format resembles that of the SQLStatistics call in ODBC.

SHOW INDEX returns the following fields:

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. These two statements are equivalent:

SHOW INDEX FROM mytable FROM mydb;
SHOW INDEX FROM mydb.mytable;

SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's indexes with the mysqlshow -k db_name tbl_name command.

SHOW INNODB STATUS

In MySQL 5.0, this is a deprecated synonym for SHOW ENGINE INNODB STATUS. See Section 13.5.4.9, “SHOW ENGINE Syntax”.

SHOW [BDB] LOGS

In MySQL 5.0, this is a deprecated synonym for SHOW ENGINE BDB LOGS. See Section 13.5.4.9, “SHOW ENGINE Syntax”.

SHOW OPEN TABLES [FROM db_name] [LIKE 'pattern']

SHOW OPEN TABLES lists the non-TEMPORARY tables that are currently open in the table cache. See Section 7.4.8, “How MySQL Opens and Closes Tables”.

SHOW OPEN TABLES returns the following fields:

The FROM and LIKE clauses may be used as of MySQL 5.0.12.

SHOW PRIVILEGES

SHOW PRIVILEGES shows the list of system privileges that the MySQL server supports. The exact list of privileges depends on the version of your server.

mysql> SHOW PRIVILEGES\G
*************************** 1. row ***************************
Privilege: Alter
Context: Tables
Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
Context: Functions,Procedures
Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
Context: Databases,Tables,Indexes
Comment: To create new databases and tables
*************************** 4. row ***************************
Privilege: Create routine
Context: Functions,Procedures
Comment: To use CREATE FUNCTION/PROCEDURE
*************************** 5. row ***************************
Privilege: Create temporary tables
Context: Databases
Comment: To use CREATE TEMPORARY TABLE
...

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

This statement is a MySQL extension. It returns characteristics of routines, such as the database, name, type, creator, and creation and modification dates. If no pattern is specified, the information for all stored procedures or all stored functions is listed, depending on which statement you use.

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
           Db: test
         Name: hello
         Type: FUNCTION
      Definer: testuser@localhost
     Modified: 2004-08-03 15:29:37
      Created: 2004-08-03 15:29:37
Security_type: DEFINER
      Comment:

You can also get information about stored routines from the ROUTINES table in INFORMATION_SCHEMA. See Section 20.14, “The INFORMATION_SCHEMA ROUTINES Table”.

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See Section 13.5.5.3, “KILL Syntax”. If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

This statement is very useful if you get the “too many connections” error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the SUPER privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users).

The output of SHOW PROCESSLIST may look like this:

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 3112
User: replikator
Host: artemis:2204
db: NULL
Command: Binlog Dump
Time: 2144
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 3113
User: replikator
Host: iconnect2:45781
db: NULL
Command: Binlog Dump
Time: 2086
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 5. row ***************************
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
5 rows in set (0.00 sec)

The columns have the following meaning:

Some State values commonly seen in the output from SHOW PROCESSLIST:

Most states correspond to very quick operations. If a thread stays in any of these states for many seconds, there might be a problem that needs to be investigated.

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']

SHOW STATUS provides server status information. This information also can be obtained using the mysqladmin extended-status command.

Partial output is shown here. The list of names and values may be different for your server. The meaning of each variable is given in Section 5.2.4, “Server Status Variables”.

mysql> SHOW STATUS;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 0          |
| Bytes_received           | 155372598  |
| Bytes_sent               | 1176560426 |
| Connections              | 30023      |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 8340       |
| Created_tmp_files        | 60         |
...
| Open_tables              | 1          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Opened_tables            | 44600      |
| Questions                | 2026873    |
...
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_created          | 30022      |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

With a LIKE clause, the statement displays only rows for those variables with names that match the pattern:

mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| Key_blocks_used    | 14955    |
| Key_read_requests  | 96854827 |
| Key_reads          | 162040   |
| Key_write_requests | 7589728  |
| Key_writes         | 3813196  |
+--------------------+----------+

The GLOBAL and SESSION options are new in MySQL 5.0.2. With the GLOBAL modifier, SHOW STATUS displays the status values for all connections to MySQL. With SESSION, it displays the status values for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

Some status variables have only a global value. For these, you get the same value for both GLOBAL and SESSION.

Note: Before MySQL 5.0.2, SHOW STATUS returned global status values. Because the default as of 5.0.2 is to return session values, this is incompatible with previous versions. To issue a SHOW STATUS statement that will retrieve global status values for all versions of MySQL, write it like this:

SHOW /*!50002 GLOBAL */ STATUS;

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

SHOW TABLE STATUS works likes SHOW TABLE, but provides a lot of information about each table. You can also get this list using the mysqlshow --status db_name command.

As of MySQL 5.0.1, this statement also displays information about views.

SHOW TABLE STATUS returns the following fields:

In the table comment, InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table.

For MEMORY tables, the Data_length, Max_data_length, and Index_length values approximate the actual amount of allocated memory. The allocation algorithm reserves memory in large amounts to reduce the number of allocation operations.

Beginning with MySQL 5.0.3, for NDB Cluster tables, the output of this statement shows appropriate values for the Avg_row_length and Data_length columns, with the exception that BLOB columns are not taken into account. In addition, the number of replicas is now shown in the Comment column (as number_of_replicas).

For views, all the fields displayed by SHOW TABLE STATUS are NULL except that Name indicates the view name and Comment says view.

SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command.

Before MySQL 5.0.1, the output from SHOW TABLES contains a single column of table names. Beginning with MySQL 5.0.1, this statement also lists any views in the database. As of MySQL 5.0.2, the FULL modifier is supported such that SHOW FULL TABLES displays a second output column. Values for the second column are BASE TABLE for a table and VIEW for a view.

Note: If you have no privileges for a table, the table does not show up in the output from SHOW TABLES or mysqlshow db_name.

SHOW TRIGGERS [FROM db_name] [LIKE expr]

SHOW TRIGGERS lists the triggers currently defined on the MySQL server. This statement requires the SUPER privilege. It was implemented in MySQL 5.0.10.

For the trigger ins_sum as defined in Section 18.3, “Using Triggers”, the output of this statement is as shown here:

mysql> SHOW TRIGGERS LIKE 'acc%'\G
*************************** 1. row ***************************
  Trigger: ins_sum
    Event: INSERT
    Table: account
Statement: SET @sum = @sum + NEW.amount
   Timing: BEFORE
  Created: NULL
 sql_mode:
  Definer: myname@localhost

Note: When using a LIKE clause with SHOW TRIGGERS, the expression to be matched (expr) is compared with the name of the table on which the trigger is declared, and not with the name of the trigger:

mysql> SHOW TRIGGERS LIKE 'ins%';
Empty set (0.01 sec)

A brief explanation of the columns in the output of this statement is shown here:

You must have the SUPER privilege to execute SHOW TRIGGERS.

See also Section 20.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

SHOW VARIABLES shows the values of MySQL system variables. This information also can be obtained using the mysqladmin variables command.

With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. With SESSION, it displays the values that are in effect for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

If the default system variable values are unsuitable, you can set them using command options when mysqld starts, and most can be changed at runtime with the SET statement. See Section 5.2.3, “Using System Variables”, and Section 13.5.3, “SET Syntax”.

Partial output is shown here. The list of names and values may be different for your server. Section 5.2.2, “Server System Variables”, describes the meaning of each variable, and Section 7.5.2, “Tuning Server Parameters”, provides information about tuning them.

mysql> SHOW VARIABLES;
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| auto_increment_increment        | 1                                   |
| auto_increment_offset           | 1                                   |
| automatic_sp_privileges         | ON                                  |
| back_log                        | 50                                  |
| basedir                         | /                                   |
| bdb_cache_size                  | 8388600                             |
| bdb_home                        | /var/lib/mysql/                     |
| bdb_log_buffer_size             | 32768                               |
...
| max_connections                 | 100                                 |
| max_connect_errors              | 10                                  |
| max_delayed_threads             | 20                                  |
| max_error_count                 | 64                                  |
| max_heap_table_size             | 16777216                            |
| max_join_size                   | 4294967295                          |
| max_relay_log_size              | 0                                   |
| max_sort_length                 | 1024                                |
...
| time_zone                       | SYSTEM                              |
| timed_mutexes                   | OFF                                 |
| tmp_table_size                  | 33554432                            |
| tmpdir                          |                                     |
| transaction_alloc_block_size    | 8192                                |
| transaction_prealloc_size       | 4096                                |
| tx_isolation                    | REPEATABLE-READ                     |
| updatable_views_with_limit      | YES                                 |
| version                         | 5.0.19-Max                          |
| version_comment                 | MySQL Community Edition - Max (GPL) |
| version_compile_machine         | i686                                |
| version_compile_os              | pc-linux-gnu                        |
| wait_timeout                    | 28800                               |
+---------------------------------+-------------------------------------+

With a LIKE clause, the statement displays only rows for those variables with names that match the pattern. To obtain the row for a specific variable, use a LIKE clause as shown:

SHOW VARIABLES LIKE 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';

To get a list of variables whose name match a pattern, use the ‘%’ wildcard character in a LIKE clause:

SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';

Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because ‘_’ is a wildcard that matches any single character, you should escape it as ‘\_’ to match it literally. In practice, this is rarely necessary.

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS

SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages, or nothing if the last statement that used a table generated no messages. A related statement, SHOW ERRORS, shows only the errors. See Section 13.5.4.11, “SHOW ERRORS Syntax”.

The list of messages is reset for each new statement that uses a table.

The SHOW COUNT(*) WARNINGS statement displays the total number of errors, warnings, and notes. You can also retrieve this number from the warning_count variable:

SHOW COUNT(*) WARNINGS;
SELECT @@warning_count;

The value of warning_count might be greater than the number of messages displayed by SHOW WARNINGS if the max_error_count system variable is set so low that not all messages are stored. An example shown later in this section demonstrates how this can happen.

The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.7, “SELECT Syntax”.

The MySQL server sends back the total number of errors, warnings, and notes resulting from the last statement. If you are using the C API, this value can be obtained by calling mysql_warning_count(). See Section 22.2.3.71, “mysql_warning_count()”.

Warnings are generated for statements such as LOAD DATA INFILE and DML statements such as INSERT, UPDATE, CREATE TABLE, and ALTER TABLE.

The following DROP TABLE statement results in a note:

mysql> DROP TABLE IF EXISTS no_such_table;
mysql> SHOW WARNINGS;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'no_such_table' |
+-------+------+-------------------------------+

Here is a simple example that shows a syntax warning for CREATE TABLE and conversion warnings for INSERT:

mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4)) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'TYPE=storage_engine' is deprecated, use
         'ENGINE=storage_engine' instead
1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES(10,'mysql'),(NULL,'test'),
    -> (300,'Open Source');
Query OK, 3 rows affected, 4 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 4

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'b' at row 1
*************************** 2. row ***************************
  Level: Warning
   Code: 1263
Message: Data truncated, NULL supplied to NOT NULL column 'a' at row 2
*************************** 3. row ***************************
  Level: Warning
   Code: 1264
Message: Data truncated, out of range for column 'a' at row 3
*************************** 4. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'b' at row 3
4 rows in set (0.00 sec)

The maximum number of error, warning, and note messages to store is controlled by the max_error_count system variable. By default, its value is 64. To change the number of messages you want stored, change the value of max_error_count. In the following example, the ALTER TABLE statement produces three warning messages, but only one is stored because max_error_count has been set to 1:

mysql> SHOW VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SET max_error_count=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t1 MODIFY b CHAR;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> SELECT @@warning_count;
+-----------------+
| @@warning_count |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1263 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

To disable warnings, set max_error_count to 0. In this case, warning_count still indicates how many warnings have occurred, but none of the messages are stored.

As of MySQL 5.0.3, you can set the SQL_NOTES session variable to 0 to cause Note-level warnings not to be recorded.

CACHE INDEX
  tbl_index_list [, tbl_index_list] ...
  IN key_cache_name

tbl_index_list:
  tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]

The CACHE INDEX statement assigns table indexes to a specific key cache. It is used only for MyISAM tables.

The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named hot_cache:

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

The syntax of CACHE INDEX enables you to specify that only particular indexes from a table should be assigned to the cache. The current implementation assigns all the table's indexes to the cache, so there is no reason to specify anything other than the table name.

The key cache referred to in a CACHE INDEX statement can be created by setting its size with a parameter setting statement or in the server parameter settings. For example:

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

Key cache parameters can be accessed as members of a structured system variable. See Section 5.2.3.1, “Structured System Variables”.

A key cache must exist before you can assign indexes to it:

mysql> CACHE INDEX t1 IN non_existent_cache;
ERROR 1284 (HY000): Unknown key cache 'non_existent_cache'

By default, table indexes are assigned to the main (default) key cache created at the server startup. When a key cache is destroyed, all indexes assigned to it become assigned to the default key cache again.

Index assignment affects the server globally: If one client assigns an index to a given cache, this cache is used for all queries involving the index, no matter which client issues the queries.

FLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [, flush_option] ...

The FLUSH statement clears or reloads various internal caches used by MySQL. To execute FLUSH, you must have the RELOAD privilege.

The RESET statement is similar to FLUSH. See Section 13.5.5.5, “RESET Syntax”.

flush_option can be any of the following:

FLUSH statements are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used. This is done so that FLUSH statements used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

Note: FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case because they would cause problems if replicated to a slave.

You can also access some of these statements with the mysqladmin utility, using the flush-hosts, flush-logs, flush-privileges, flush-status, or flush-tables commands.

Using FLUSH statements within stored functions or triggers is not supported in MySQL 5.0. However, you may use FLUSH in stored procedures, so long as these are not called from stored functions or triggers. See Section I.1, “Restrictions on Stored Routines and Triggers”.

See also Section 13.5.5.5, “RESET Syntax”, for information about how the RESET statement is used with replication.

KILL [CONNECTION | QUERY] thread_id

Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST statement and kill a thread with the KILL thread_id statement.

In MySQL 5.0.0, KILL allows the optional CONNECTION or QUERY modifier:

If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.

You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads.

Note: You cannot use KILL with the Embedded MySQL Server library, because the embedded server merely runs inside the threads of the host application. It does not create any connection threads of its own.

When you use KILL, a thread-specific kill flag is set for the thread. In most cases, it might take some time for the thread to die, because the kill flag is checked only at specific intervals:

LOAD INDEX INTO CACHE
  tbl_index_list [, tbl_index_list] ...

tbl_index_list:
  tbl_name
    [[INDEX|KEY] (index_name[, index_name] ...)]
    [IGNORE LEAVES]

The LOAD INDEX INTO CACHE statement preloads a table index into the key cache to which it has been assigned by an explicit CACHE INDEX statement, or into the default key cache otherwise. LOAD INDEX INTO CACHE is used only for MyISAM tables.

The IGNORE LEAVES modifier causes only blocks for the non-leaf nodes of the index to be preloaded.

The following statement preloads nodes (index blocks) of indexes for the tables t1 and t2:

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

This statement preloads all index blocks from t1. It preloads only blocks for the non-leaf nodes from t2.

The syntax of LOAD INDEX INTO CACHE enables you to specify that only particular indexes from a table should be preloaded. The current implementation preloads all the table's indexes into the cache, so there is no reason to specify anything other than the table name.

RESET reset_option [, reset_option] ...

The RESET statement is used to clear the state of various server operations. You must have the RELOAD privilege to execute RESET.

RESET acts as a stronger version of the FLUSH statement. See Section 13.5.5.2, “FLUSH Syntax”.

reset_option can be any of the following: