CREATE USER user_specification [, user_specification] ...user_specification: user [ IDENTIFIED BY [PASSWORD] 'password'  | IDENTIFIED WITH auth_plugin [AS 'auth_string'] ]

The CREATE USER 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 row in the mysql.user table and assigns the account no privileges. An error occurs if the account already exists.

Each account name uses the format described in Section 6.2.3, "Specifying Account Names". For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

If you specify only the user name part of the account name, a host name part of '%' is used.

The user specification may indicate how the user should authenticate when connecting to the server:

The IDENTIFIED BY and IDENTIFIED WITH clauses are mutually exclusive, so at most one of them can be specified for a given user.

For additional information about setting passwords, see Section 6.3.5, "Assigning Account Passwords".

DROP USER user [, user] ...

The DROP USER statement removes one or more MySQL accounts and their privileges. It removes privilege rows for the account from all grant tables. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. Each account name uses the format described in Section 6.2.3, "Specifying Account Names". For example:

DROP USER 'jeffrey'@'localhost';

If you specify only the user name part of the account name, a host name part of '%' is used.

DROP USER does not automatically drop or invalidate databases or objects within them that the old user created. This includes stored programs or views for which the DEFINER attribute names the dropped user. Attempts to access such objects may produce an error if they execute in definer security context. (For information about security context, see Section 19.6, "Access Control for Stored Programs and Views".)

GRANT priv_type [(column_list)]  [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...]GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT OPTION]object_type: TABLE  | FUNCTION  | PROCEDUREpriv_level: *  | *.*  | db_name.*  | db_name.tbl_name  | tbl_name  | db_name.routine_nameuser_specification: user [ IDENTIFIED BY [PASSWORD] 'password'  | IDENTIFIED WITH auth_plugin [AS 'auth_string'] ]ssl_option: SSL  | X509  | CIPHER 'cipher'  | ISSUER 'issuer'  | SUBJECT 'subject'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 grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

However, if an account named in a GRANT statement does not already exist, GRANT may create it under the conditions described later in the discussion of the NO_AUTO_CREATE_USER SQL mode.

The REVOKE statement is related to GRANT and enables administrators to remove account privileges. See Section 13.7.1.5, "REVOKE Syntax".

When successfully executed from the mysql program, GRANT responds with Query OK, 0 rows affected. To determine what privileges result from the operation, use SHOW GRANTS. See Section 13.7.5.22, "SHOW GRANTS Syntax".

There are several aspects to the GRANT statement, described under the following topics in this section:

Privileges Supported by MySQL

The following table summarizes the permissible priv_type privilege types that can be specified for the GRANT and REVOKE statements. For additional information about these privileges, see Section 6.2.1, "Privileges Provided by MySQL".

The PROXY privilege was added in MySQL 5.5.7.

A trigger is associated with a table, so to create or drop a trigger, you must have the TRIGGER privilege for the table, not the trigger.

In GRANT statements, the ALL [PRIVILEGES] or PROXY privilege must be named by itself and cannot be specified along with other privileges. ALL [PRIVILEGES] stands for all privileges available for the level at which privileges are to be granted except for the GRANT OPTION and PROXY privileges.

USAGE can be specified to create a user that has no privileges, or to specify the REQUIRE or WITH clauses for an account without changing its existing privileges.

MySQL account information is stored in the tables of the mysql database. This database and the access control system are discussed extensively in Section 6.2, "The MySQL Access Privilege System", which you should consult for additional details.

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 nonzero 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 that variable.)

Privileges can be granted at several levels, depending on the syntax used for the ON clause. For REVOKE, the same ON syntax specifies which privileges to take away. The examples shown here include no IDENTIFIED BY 'password' clause for brevity, but you should include one if the account does not already exist, to avoid creating an insecure account that has no password.

Global Privileges

Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use ON *.* syntax:

GRANT ALL ON *.* TO 'someuser'@'somehost';GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

The CREATE TABLESPACE, CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative and can only be granted globally.

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

MySQL stores global privileges in the mysql.user table.

Database Privileges

Database privileges apply to all objects in a given database. To assign database-level privileges, use ON db_name.* syntax:

GRANT ALL ON mydb.* TO 'someuser'@'somehost';GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

If you use ON * syntax (rather than ON *.*) and you have selected a default database, privileges are assigned at the database level for the default database. An error occurs if there is no default database.

The CREATE, DROP, EVENT, GRANT OPTION, and LOCK TABLES privileges can be specified at the database level. Table or routine privileges also can be specified at the database level, in which case they apply to all tables or routines in the database.

MySQL stores database privileges in the mysql.db table.

Table Privileges

Table privileges apply to all columns in a given table. To assign table-level privileges, use ON db_name.tbl_name syntax:

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specify tbl_name rather than db_name.tbl_name, the statement applies to tbl_name in the default database. An error occurs if there is no default database.

The permissible priv_type values at the table level are ALTER, CREATE VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, SHOW VIEW, TRIGGER, and UPDATE.

MySQL stores table privileges in the mysql.tables_priv table.

Column Privileges

Column privileges apply to single columns in a given table. Each privilege to be granted at the column level must be followed by the column or columns, enclosed within parentheses.

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

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

MySQL stores column privileges in the mysql.columns_priv table.

Stored Routine Privileges

The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (procedures and functions). They can be granted at the global and database levels. Except for CREATE ROUTINE, these privileges can be granted at the routine level for individual routines.

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

The permissible priv_type values 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.

MySQL stores routine-level privileges in the mysql.procs_priv table.

Proxy User Privileges

The PROXY privilege enables one user to be a proxy for another. The proxy user impersonates or takes the identity of the proxied user.

GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';

When PROXY is granted, it must be the only privilege named in the GRANT statement, the REQUIRE clause cannot be given, and the only permitted WITH option is WITH GRANT OPTION.

Proxying requires that the proxy user authenticate through a plugin that returns the name of the proxied user to the server when the proxy user connects, and that the proxy user have the PROXY privilege for the proxied user. For details and examples, see Section 6.3.7, "Proxy Users".

MySQL stores proxy privileges in the mysql.proxies_priv table.

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. Granting ALL does not assign the PROXY privilege.

The object_type clause, if present, should be specified as TABLE, FUNCTION, or PROCEDURE when the following object is a table, a stored function, or a stored procedure.

The privileges for a database, table, column, or routine are formed additively as the logical OR of the privileges at each of the privilege levels. For example, if a user has a global SELECT privilege, the privilege cannot be denied by an absence of the privilege at the database, table, or column level. Details of the privilege-checking procedure are presented in Section 6.2.5, "Access Control, Stage 2: Request Verification".

MySQL enables you to grant privileges on databases or tables that do not exist. For tables, 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 databases or tables that are to be created at a later time.

Account Names and Passwords

The user value indicates the MySQL account to which the GRANT statement applies. 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, quotation marks 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'@'%.com'. Quote the user name and host name separately.

You can specify wildcards in the host name. For example, user_name@'%.example.com' applies to user_name for any host in the example.com domain, and user_name@'192.168.1.%' applies to user_name for any host in the 192.168.1 class C subnet.

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

MySQL does not support wildcards in user names. To refer to an anonymous user, specify an account with an empty user name with the GRANT statement:

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

In this case, any user who connects from the local host with the correct password for the anonymous user will be permitted access, with the privileges associated with the anonymous-user account.

For additional information about user name and host name values in account names, see Section 6.2.3, "Specifying Account Names".

To specify quoted values, quote database, table, column, and routine names as identifiers. Quote user names and host names as identifiers or as strings. Quote passwords as strings. For string-quoting and identifier-quoting guidelines, see Section 9.1.1, "String Literals", and Section 9.2, "Schema Object Names".

The "_" and "%" wildcards are permitted 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 determine whether the preceding warning applies to you, execute the following query, which lists any anonymous users:

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

To avoid the problem just described, delete the local anonymous user account using this statement:

DROP USER ''@'localhost';

GRANT supports host names up to 60 characters long. Database, table, column, and routine names can be up to 64 characters. User names can be up to 16 characters.

The user specification may indicate how the user should authenticate when connecting to the server, through inclusion of an IDENTIFIED BY or IDENTIFIED WITH clause. The syntax is the same as for the CREATE USER statement. See Section 13.7.1.1, "CREATE USER Syntax".

When the IDENTIFIED BY clause is present and you have global grant privileges, the password becomes the new password for the account, even if the account exists and already has a password. With no IDENTIFIED BY clause, the account password remains unchanged.

If the account named in a GRANT statement does not exist in the mysql.user table, GRANT creates it if the NO_AUTO_CREATE_USER SQL mode is not enabled. This is very insecure unless you specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.

If the account does not exist and NO_AUTO_CREATE_USER is enabled, GRANT fails and does not create the account unless you specify a nonempty password with IDENTIFIED BY or specify an IDENTIFIED WITH clause to name an authentication plugin.

Other Account Characteristics

The WITH clause is used for several purposes:

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 combine 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 nonadministrative 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!

For additional information about security risks associated with particular privileges, see Section 6.2.1, "Privileges Provided by MySQL".

Several WITH clause options specify limits on use of server resources by an account:

To specify resource limits for an existing user without affecting existing privileges, use GRANT USAGE at the global level (ON *.*) and name the limits to be changed. For example:

GRANT USAGE ON *.* TO ...  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

Limits not specified retain their current values.

For more information on restricting access to server resources, see Section 6.3.4, "Setting Account Resource Limits".

MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the user name 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 6.3.8, "Using SSL for 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 protected]'  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/ O=MySQL Finland AB/CN=Tonu Samuel/[email protected]'  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

The order of the options does not matter, but no option can be specified twice. The AND keyword is optional between REQUIRE options.

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.

MySQL and Standard SQL Versions of GRANT

The biggest differences between the MySQL and standard SQL 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 name uses the format described in Section 6.2.3, "Specifying Account Names". For example:

RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';

If you specify only the user name part of the account name, a host name part of '%' is used.

RENAME USER causes the privileges held by the old user to be those held by the new user. However, RENAME USER does not automatically drop or invalidate databases or objects within them that the old user created. This includes stored programs or views for which the DEFINER attribute names the old user. Attempts to access such objects may produce an error if they execute in definer security context. (For information about security context, see Section 19.6, "Access Control for Stored Programs and Views".)

The privilege changes take effect as indicated in Section 6.2.6, "When Privilege Changes Take Effect".

REVOKE priv_type [(column_list)]  [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...REVOKE PROXY ON user FROM user [, user] ...

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts. Each account name uses the format described in Section 6.2.3, "Specifying Account Names". For example:

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

If you specify only the user name part of the account name, a host name part of '%' is used.

For details on the levels at which privileges exist, the permissible priv_type and priv_level values, and the syntax for specifying users and passwords, see Section 13.7.1.3, "GRANT Syntax"

To use the first REVOKE syntax, you must have the GRANT OPTION privilege, and you must have the privileges that you are revoking.

To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine 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.

REVOKE removes privileges, but does not drop mysql.user table entries. To remove a user account entirely, use DROP USER (see Section 13.7.1.2, "DROP USER Syntax") or DELETE.

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 nonzero 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.)

When successfully executed from the mysql program, REVOKE responds with Query OK, 0 rows affected. To determine what privileges result from the operation, use SHOW GRANTS. See Section 13.7.5.22, "SHOW GRANTS Syntax".

SET PASSWORD [FOR user] = { PASSWORD('cleartext password')  | OLD_PASSWORD('cleartext password')  | 'encrypted password' }

The SET PASSWORD statement assigns a password to an existing MySQL user account. When the read_only system variable is enabled, the SUPER privilege is required to use SET PASSWORD, in addition to whatever other privileges might be required.

If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the cleartext (unencrypted) password should be given as the argument to the function, which hashes the password and returns the encrypted password string. If the password is specified without using either function, it should be the already encrypted password value as a literal string. In all cases, the encrypted password string must be in the format required by the authentication method used for the account.

With no FOR user clause, this statement sets the password for the current user. (To see which account the server authenticated you as, invoke the CURRENT_USER() function.) Any client who successfully connects to the server using a nonanonymous account can change the password for that account.

With a FOR user clause, this statement sets the password for the named user. You must have the UPDATE privilege for the mysql database to do this. The user account name uses the format described in Section 6.2.3, "Specifying Account Names". The user value should be given as 'user_name'@'host_name', where 'user_name' and 'host_name' are exactly as listed in the User and Host columns of the mysql.user table row. (If you specify only a user name, a host name of '%' is used.) For example, to set the password for an account with User and Host column values of 'bob' and '%.example.org', write the statement like this:

SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');

That is equivalent to the following statements:

UPDATE mysql.user SET Password=PASSWORD('cleartext password')  WHERE User='bob' AND Host='%.example.org';FLUSH PRIVILEGES;

Another way to set the password is to use GRANT:

GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';

The old_passwords system variable value determines the hashing method used by PASSWORD(). If you specify the password using that function and SET PASSWORD rejects the password as not being in the correct format, it may be necessary to set old_passwords to change the hashing method. For descriptions of the permitted values, see Section 5.1.4, "Server System Variables".

For more information about setting passwords, see Section 6.3.5, "Assigning Account Passwords"

If you are using MySQL Replication, be aware that, currently, a password used by a replication slave as part of a CHANGE MASTER TO statement is effectively limited to 32 characters in length; the password can be longer, but any excess characters are truncated. This is not due to any limit imposed by the MySQL Server generally, but rather is an issue specific to MySQL Replication. (For more information, see Bug #43439.)

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] 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 InnoDB and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

For more information on how the analysis works within InnoDB, see Section 14.3.15, "Limits 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. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.

This statement requires SELECT and INSERT privileges for the table.

ANALYZE TABLE is supported for partitioned tables, and you can use ALTER TABLE ... ANALYZE PARTITION to analyze one or more partitions; for more information, see Section 13.1.7, "ALTER TABLE Syntax", and Section 18.3.3, "Maintenance of Partitions".

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.7.5.23, "SHOW INDEX Syntax".

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

By default, the server writes ANALYZE TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, use the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

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 InnoDB, MyISAM, ARCHIVE, and CSV tables. For MyISAM tables, the key statistics are updated as well.

To check a table, you must have some privilege for it.

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

CHECK TABLE is supported for partitioned tables, and you can use ALTER TABLE ... CHECK PARTITION to check one or more partitions; for more information, see Section 13.1.7, "ALTER TABLE Syntax", and Section 18.3.3, "Maintenance of Partitions".

Output

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 7.6, "MyISAM 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.

Checking Version Compatibility

The FOR UPGRADE option checks whether the named tables are compatible with the current version of MySQL. 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:

Checking Data Consistency

The other check options that can be given are shown in the following table. These options are passed to the storage engine, which may use them or not.

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;

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.

Use of CHECK TABLE ... EXTENDED might influence the execution plan generated by the query optimizer.

Some problems reported by CHECK TABLE cannot be corrected automatically:

InnoDB Tables

The following notes apply to InnoDB tables:

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

CHECKSUM TABLE reports a table checksum. This statement requires the SELECT privilege for the table.

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.17, "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 nonexistent table, CHECKSUM TABLE returns NULL and generates a warning.

In MySQL 5.5, CHECKSUM TABLE returns 0 for partitioned tables unless you include the EXTENDED option. This issue is resolved in MySQL 5.6. (Bug #11933226, Bug #60681)

The checksum value depends on the table row format. If the row format changes, the checksum also changes. For example, the storage format for VARCHAR changed between MySQL 4.1 and 5.0, so if a 4.1 table is upgraded to MySQL 5.0, the checksum value may change.

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

Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.

Use OPTIMIZE TABLE in these cases, depending on the type of table:

This statement requires SELECT and INSERT privileges for the table.

OPTIMIZE TABLE is also supported for partitioned tables. For information about using this statement with partitioned tables and table partitions, see Section 18.3.3, "Maintenance of Partitions".

OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables.

OPTIMIZE TABLE is also supported for dynamic columns of in-memory NDBCLUSTER tables. It does not work for Disk Data tables. The performance of OPTIMIZE on Cluster tables can be tuned by adjusting the value of the ndb_optimization_delay system variable, which controls the number of milliseconds to wait between processing batches of rows by OPTIMIZE TABLE. See Section 17.1.6.11, "Previous MySQL Cluster Issues Resolved in MySQL 5.1, MySQL Cluster NDB 6.x, and MySQL Cluster NDB 7.x", for more information.

OPTIMIZE TABLE is not supported for tables using other storage engines except as noted previously.

For MySQL Cluster tables, OPTIMIZE TABLE can be interrupted by (for example) killing the SQL thread performing the OPTIMIZE operation.

InnoDB Details

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. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

mysql> OPTIMIZE TABLE foo;+----------+----------+----------+-------------------------------------------------+| Table | Op   | Msg_type | Msg_text |+----------+----------+----------+-------------------------------------------------+| test.foo | optimize | note | Table does not support optimize, doing recreate ||  |  |  | + analyze instead   || test.foo | optimize | status   | OK  |+----------+----------+----------+-------------------------------------------------+

InnoDB stores data using a page-allocation method and does not suffer from fragmentation in the same way that legacy storage engines (such as MyISAM) will. When considering whether or not to run optimize, consider the workload of transactions that your server will process:

MyISAM Details

For MyISAM tables, OPTIMIZE TABLE works as follows:

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

Result Set

OPTIMIZE TABLE returns a result set with the following columns.

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

By default, the server writes OPTIMIZE TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, use the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

OPTIMIZE TABLE does not sort R-tree indexes, such as spatial indexes on POINT columns. (Bug #23578)

As of MySQL 5.5.6, OPTIMIZE TABLE table catches and throws any errors that occur while copying table statistics from the old file to the newly created file. For example. if the user ID of the owner of the .frm, .MYD, or .MYI file is different from the user ID of the mysqld process, OPTIMIZE TABLE generates a "cannot change ownership of the file" error unless mysqld is started by the root user.

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] 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, ARCHIVE, and CSV tables. See Section 14.5, "The MyISAM Storage Engine", and Section 14.8, "The ARCHIVE Storage Engine", and Section 14.7, "The CSV Storage Engine"

This statement requires SELECT and INSERT privileges for the table.

REPAIR TABLE is supported for partitioned tables. However, the USE_FRM option cannot be used with this statement on a partitioned table.

You can use ALTER TABLE ... REPAIR PARTITION to repair one or more partitions; for more information, see Section 13.1.7, "ALTER TABLE Syntax", and Section 18.3.3, "Maintenance of Partitions".

Normally, you should never have to run REPAIR TABLE. However, if disaster strikes, this statement 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 C.5.4.2, "What to Do If MySQL Keeps Crashing", and Section 14.5.4, "MyISAM Table Problems".

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 for a MyISAM table, you should try repairing it with myisamchk --safe-recover. (REPAIR TABLE does not 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 you use the QUICK option, REPAIR TABLE tries to repair only the index file, and not the data file. This type of repair is like that done by myisamchk --recover --quick.

If you use the EXTENDED option, 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.

The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file. This kind of repair cannot be done with myisamchk.

If USE_FRM is not used, REPAIR TABLE checks the table to see whether an upgrade is required. If so, it performs the upgrade, following the same rules as CHECK TABLE ... FOR UPGRADE. See Section 13.7.2.2, "CHECK TABLE Syntax", for more information. REPAIR TABLE without USE_FRM upgrades the .frm file to the current version.

By default, the server writes REPAIR TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, use the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

You may be able to increase REPAIR TABLE performance by setting certain system variables. See Section 8.6.3, "Speed of REPAIR TABLE Statements".

As of MySQL 5.5.6, REPAIR TABLE table catches and throws any errors that occur while copying table statistics from the old corrupted file to the newly created file. For example. if the user ID of the owner of the .frm, .MYD, or .MYI file is different from the user ID of the mysqld process, REPAIR TABLE generates a "cannot change ownership of the file" error unless mysqld is started by the root user.

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} SONAME shared_library_name

A user-defined function (UDF) is a way to extend MySQL with a new function that works like a native (built-in) MySQL function such as ABS() or CONCAT().

function_name is the name that should be used in SQL statements to invoke the function. The RETURNS clause indicates the type of the function's return value. DECIMAL is a legal value after RETURNS, but currently DECIMAL functions return string values and should be written like STRING functions.

shared_library_name is the basename of the shared object file that contains the code that implements the function. The file must be located in the plugin directory. This directory is given by the value of the plugin_dir system variable. For more information, see Section 23.3.2.5, "Compiling and Installing User-Defined Functions".

To create a function, you must have the INSERT privilege for the mysql database. This is necessary because CREATE FUNCTION adds a row to the mysql.func system table that records the function's name, type, and shared library name. If you do not have this table, you should run the mysql_upgrade command to create it. See Section 4.4.7, "mysql_upgrade - Check and Upgrade MySQL Tables".

An active function is one that has been loaded with CREATE FUNCTION and not removed with DROP FUNCTION. All active functions are reloaded each time the server starts, unless you start mysqld with the --skip-grant-tables option. In this case, UDF initialization is skipped and UDFs are unavailable.

For instructions on writing user-defined functions, see Section 23.3.2, "Adding a New User-Defined Function". For the UDF mechanism to work, functions must be written in C or C++ (or another language that can use C calling conventions), your operating system must support dynamic loading and you must have compiled mysqld dynamically (not statically).

An AGGREGATE function works exactly like a native MySQL aggregate (summary) function such as SUM or COUNT(). For AGGREGATE to work, your mysql.func table must contain a type column. If your mysql.func table does not have this column, you should run the mysql_upgrade program to create it (see Section 4.4.7, "mysql_upgrade - Check and Upgrade MySQL Tables").

DROP FUNCTION function_name

This statement drops the user-defined function (UDF) named function_name.

To drop a function, you must have the DELETE privilege for the mysql database. This is because DROP FUNCTION removes a row from the mysql.func system table that records the function's name, type, and shared library name.

DROP FUNCTION is also used to drop stored functions (see Section 13.1.26, "DROP PROCEDURE and DROP FUNCTION Syntax").

INSTALL PLUGIN plugin_name SONAME 'shared_library_name'

This statement installs a server plugin. It requires the INSERT privilege for the mysql.plugin table.

plugin_name is the name of the plugin as defined in the plugin descriptor structure contained in the library file (see Section 23.2.4.2, "Plugin Data Structures"). Plugin names are not case sensitive. For maximal compatibility, plugin names should be limited to ASCII letters, digits, and underscore because they are used in C source files, shell command lines, M4 and Bourne shell scripts, and SQL environments.

shared_library_name is the name of the shared library that contains the plugin code. The name includes the file name extension (for example, libmyplugin.so, libmyplugin.dll, or libmyplugin.dylib).

The shared library must be located in the plugin directory (the directory named by the plugin_dir system variable). The library must be in the plugin directory itself, not in a subdirectory. By default, plugin_dir is the plugin directory under the directory named by the pkglibdir configuration variable, but it can be changed by setting the value of plugin_dir at server startup. For example, set its value in a my.cnf file:

[mysqld]plugin_dir=/path/to/plugin/directory

If the value of plugin_dir is a relative path name, it is taken to be relative to the MySQL base directory (the value of the basedir system variable).

INSTALL PLUGIN loads and initializes the plugin code to make the plugin available for use. A plugin is initialized by executing its initialization function, which handles any setup that the plugin must perform before it can be used. When the server shuts down, it executes the deinitialization function for each plugin that is loaded so that the plugin has a change to perform any final cleanup.

INSTALL PLUGIN also registers the plugin by adding a line that indicates the plugin name and library file name to the mysql.plugin table. At server startup, the server loads and initializes any plugin that is listed in the mysql.plugin table. This means that a plugin is installed with INSTALL PLUGIN only once, not every time the server starts. Plugin loading at startup does not occur if the server is started with the --skip-grant-tables option.

A plugin library can contain multiple plugins. For each of them to be installed, use a separate INSTALL PLUGIN statement. Each statement names a different plugin, but all of them specify the same library name.

INSTALL PLUGIN causes the server to read option (my.cnf) files just as during server startup. This enables the plugin to pick up any relevant options from those files. It is possible to add plugin options to an option file even before loading a plugin (if the loose prefix is used). It is also possible to uninstall a plugin, edit my.cnf, and install the plugin again. Restarting the plugin this way enables it to the new option values without a server restart.

For options that control individual plugin loading at server startup, see Section 5.1.8.1, "Installing and Uninstalling Plugins". If you need to load plugins for a single server startup when the --skip-grant-tables option is given (which tells the server not to read system tables), use the --plugin-load option. See Section 5.1.3, "Server Command Options".

To remove a plugin, use the UNINSTALL PLUGIN statement.

For additional information about plugin loading, see Section 5.1.8.1, "Installing and Uninstalling Plugins".

To see what plugins are installed, use the SHOW PLUGINS statement or query the INFORMATION_SCHEMA.PLUGINS table.

If you recompile a plugin library and need to reinstall it, you can use either of the following methods:

UNINSTALL PLUGIN plugin_name

This statement removes an installed server plugin. It requires the DELETE privilege for the mysql.plugin table.

plugin_name must be the name of some plugin that is listed in the mysql.plugin table. The server executes the plugin's deinitialization function and removes the row for the plugin from the mysql.plugin table, so that subsequent server restarts will not load and initialize the plugin. UNINSTALL PLUGIN does not remove the plugin's shared library file.

You cannot uninstall a plugin if any table that uses it is open.

Plugin removal has implications for the use of associated tables. For example, if a full-text parser plugin is associated with a FULLTEXT index on the table, uninstalling the plugin makes the table unusable. Any attempt to access the table results in an error. The table cannot even be opened, so you cannot drop an index for which the plugin is used. This means that uninstalling a plugin is something to do with care unless you do not care about the table contents. If you are uninstalling a plugin with no intention of reinstalling it later and you care about the table contents, you should dump the table with mysqldump and remove the WITH PARSER clause from the dumped CREATE TABLE statement so that you can reload the table later. If you do not care about the table, DROP TABLE can be used even if any plugins associated with the table are missing.

For additional information about plugin loading, see Section 5.1.8.1, "Installing and Uninstalling Plugins".

SHOW AUTHORS

The SHOW AUTHORS statement displays information about the people who work on MySQL. For each author, it displays Name, Location, and Comment values.

This statement is deprecated as of MySQL 5.5.29 and is removed in MySQL 5.6.

SHOW BINARY LOGSSHOW MASTER LOGS

Lists the binary log files on the server. This statement is used as part of the procedure described in Section 13.4.1.1, "PURGE BINARY LOGS Syntax", that shows how to determine which logs can be purged.

mysql> SHOW BINARY LOGS;+---------------+-----------+| Log_name  | File_size |+---------------+-----------+| binlog.000015 | 724935 || binlog.000016 | 733481 |+---------------+-----------+

SHOW MASTER LOGS is equivalent to SHOW BINARY LOGS.

In MySQL 5.5.24 and earlier, the SUPER privilege was required to use this statement. Starting with MySQL 5.5.25, a user with the REPLICATION CLIENT privilege may also execute this statement.

SHOW BINLOG EVENTS   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed.

The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.9, "SELECT Syntax".

SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr]

The SHOW CHARACTER SET statement shows all available character sets. The LIKE clause, if present, indicates which character set names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements". 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' | WHERE expr]

This statement lists collations supported by the server. By default, the output from SHOW COLLATION includes all available collations. The LIKE clause, if present, indicates which collation names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements". 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 Collation and Charset columns indicate the names of the collation and the character set with which it is associated. Id is the collation ID. Default indicates whether the 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.

To see the default collation for each character set, use the following statement. Default is a reserved word, so to use it as an identifier, it must be quoted as such:

mysql> SHOW COLLATION WHERE `Default` = 'Yes';+---------------------+----------+----+---------+----------+---------+| Collation   | Charset  | Id | Default | Compiled | Sortlen |+---------------------+----------+----+---------+----------+---------+| big5_chinese_ci | big5 |  1 | Yes | Yes  |   1 || dec8_swedish_ci | dec8 |  3 | Yes | Yes  |   1 || cp850_general_ci | cp850 |  4 | Yes | Yes  |   1 || hp8_english_ci  | hp8  |  6 | Yes | Yes  |   1 || koi8r_general_ci | koi8r |  7 | Yes | Yes  |   1 || latin1_swedish_ci   | latin1   |  8 | Yes | Yes  |   1 |...

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

SHOW COLUMNS displays information about the columns in a given table. It also works for views. The LIKE clause, if present, indicates which column names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

SHOW COLUMNS displays information only for those columns for which you have some privilege.

mysql> SHOW COLUMNS FROM City;+------------+----------+------+-----+---------+----------------+| Field  | Type | Null | Key | Default | Extra  |+------------+----------+------+-----+---------+----------------+| Id | int(11)  | NO   | PRI | NULL | auto_increment || Name   | char(35) | NO   | | | || Country | char(3)  | NO   | UNI | | || District   | char(20) | YES  | MUL | | || Population | int(11)  | NO   | | 0   | |+------------+----------+------+-----+---------+----------------+5 rows in set (0.00 sec)

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

The FULL keyword causes the output to include the column collation and comments, as well as the privileges you have 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 COLUMNS displays the following values for each table column:

Field indicates the column name.

Type indicates the column data type.

Collation indicates the collation for nonbinary string columns, or NULL for other columns. This value is displayed only if you use the FULL keyword.

The Null field contains YES if NULL values can be stored in the column, NO if not.

The Key field indicates whether the column is indexed:

If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order PRI, UNI, MUL.

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

The Default field indicates the default value that is assigned to the column. This is NULL if the column has an explicit default of NULL, or if the column definition has no DEFAULT clause.

The Extra field contains any additional information that is available about a given column. The value is nonempty in these cases: auto_increment for columns that have the AUTO_INCREMENT attribute; on update CURRENT_TIMESTAMP for TIMESTAMP columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.

Privileges indicates the privileges you have for the column. This value is displayed only if you use the FULL keyword.

Comment indicates any comment the column has. This value is displayed only if you use the FULL keyword.

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.8.1, "DESCRIBE Syntax".

The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements also provide information about tables. See Section 13.7.5, "SHOW Syntax".

SHOW CONTRIBUTORS

The SHOW CONTRIBUTORS statement displays information about the people who contribute to MySQL source or to causes that we support. For each contributor, it displays Name, Location, and Comment values.

This statement is deprecated as of MySQL 5.5.29 and is removed in MySQL 5.6.

SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

Shows the CREATE DATABASE statement that creates the given database. If the SHOW statement includes an IF NOT EXISTS clause, the output too includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.

mysql> SHOW CREATE DATABASE test\G*************************** 1. row ***************************   Database: testCreate Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */mysql> SHOW CREATE SCHEMA test\G*************************** 1. row ***************************   Database: testCreate 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 5.1.4, "Server System Variables".

SHOW CREATE EVENT event_name

This statement displays the CREATE EVENT statement needed to re-create a given event. It requires the EVENT privilege for the database from which the event is to be shown. For example (using the same event e_daily defined and then altered in Section 13.7.5.19, "SHOW EVENTS Syntax"):

mysql> SHOW CREATE EVENT test.e_daily\G*************************** 1. row ***************************   Event: e_daily sql_mode:   time_zone: SYSTEM Create Event: CREATE EVENT `e_daily` ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Saves total number of sessions then clears the table each day' DO BEGIN  INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions;  DELETE FROM site_activity.sessions; ENDcharacter_set_client: latin1collation_connection: latin1_swedish_ci  Database Collation: latin1_swedish_ci

character_set_client is the session value of the character_set_client system variable when the event was created. collation_connection is the session value of the collation_connection system variable when the event was created. Database Collation is the collation of the database with which the event is associated.

Note that the output reflects the current status of the event (ENABLE) rather than the status with which it was created.

SHOW CREATE FUNCTION func_name

This statement is similar to SHOW CREATE PROCEDURE but for stored functions. See Section 13.7.5.11, "SHOW CREATE PROCEDURE Syntax".

SHOW CREATE PROCEDURE proc_name

This statement is a MySQL extension. It returns the exact string that can be used to re-create the named stored procedure. A similar statement, SHOW CREATE FUNCTION, displays information about stored functions (see Section 13.7.5.10, "SHOW CREATE FUNCTION Syntax").

Both statements require that you be the owner of the routine or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL.

mysql> SHOW CREATE PROCEDURE test.simpleproc\G*************************** 1. row ***************************   Procedure: simpleproc sql_mode: Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT)  BEGIN  SELECT COUNT(*) INTO param1 FROM t;  ENDcharacter_set_client: latin1collation_connection: latin1_swedish_ci  Database Collation: latin1_swedish_cimysql> SHOW CREATE FUNCTION test.hello\G*************************** 1. row *************************** Function: hello sql_mode: Create Function: CREATE FUNCTION `hello`(s CHAR(20))  RETURNS CHAR(50)  RETURN CONCAT('Hello, ',s,'!')character_set_client: latin1collation_connection: latin1_swedish_ci  Database Collation: latin1_swedish_ci

character_set_client is the session value of the character_set_client system variable when the routine was created. collation_connection is the session value of the collation_connection system variable when the routine was created. Database Collation is the collation of the database with which the routine is associated.

SHOW CREATE TABLE tbl_name

Shows the CREATE TABLE statement that creates the given table. To use this statement, you must have some privilege for the table. This statement also works with views.

mysql> SHOW CREATE TABLE t\G*************************** 1. row ***************************   Table: tCreate 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 5.1.4, "Server System Variables".

SHOW CREATE TRIGGER trigger_name

This statement shows a CREATE TRIGGER statement that creates the given trigger.

mysql> SHOW CREATE TRIGGER ins_sum\G*************************** 1. row ***************************   Trigger: ins_sum  sql_mode:SQL Original Statement: CREATE DEFINER=`bob`@`localhost` TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount  character_set_client: latin1  collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci

You can also obtain information about trigger objects from INFORMATION_SCHEMA, which contains a TRIGGERS table. See Section 20.25, "The INFORMATION_SCHEMA TRIGGERS Table".

SHOW CREATE VIEW view_name

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

mysql> SHOW CREATE VIEW v\G*************************** 1. row *************************** View: v Create View: CREATE ALGORITHM=UNDEFINED  DEFINER=`bob`@`localhost`  SQL SECURITY DEFINER VIEW  `v` AS select 1 AS `a`,2 AS `b`character_set_client: latin1collation_connection: latin1_swedish_ci

character_set_client is the session value of the character_set_client system variable when the view was created. collation_connection is the session value of the collation_connection system variable when the view was created.

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.27, "The INFORMATION_SCHEMA VIEWS Table".

MySQL lets you use different sql_mode settings to tell the server the type of SQL syntax to support. For example, you might use the ANSI SQL mode to ensure MySQL correctly interprets the standard SQL concatenation operator, the double bar (||), in your queries. If you then create a view that concatenates items, you might worry that changing the sql_mode setting to a value different from ANSI could cause the view to become invalid. But this is not the case. No matter how you write out a view definition, MySQL always stores it the same way, in a canonical form. Here is an example that shows how the server changes a double bar concatenation operator to a CONCAT() function:

mysql> SET sql_mode = 'ANSI';Query OK, 0 rows affected (0.00 sec)mysql> CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;Query OK, 0 rows affected (0.01 sec)mysql> SHOW CREATE VIEW test.v\G*************************** 1. row *************************** View: v Create View: CREATE VIEW "v" AS select concat('a','b') AS "col1"...1 row in set (0.00 sec)

The advantage of storing a view definition in canonical form is that changes made later to the value of sql_mode will not affect the results from the view. However an additional consequence is that comments prior to SELECT are stripped from the definition by the server.

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

SHOW DATABASES lists the databases on the MySQL server host. SHOW SCHEMAS is a synonym for SHOW DATABASES. The LIKE clause, if present, indicates which database names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

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.

MySQL implements databases as directories in the data directory, so this statement simply lists directories in that location. However, the output may include names of directories that do not correspond to actual databases.

SHOW ENGINE engine_name {STATUS | MUTEX}

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

SHOW ENGINE INNODB STATUSSHOW ENGINE INNODB MUTEXSHOW ENGINE {NDB | NDBCLUSTER} STATUSSHOW ENGINE PERFORMANCE_SCHEMA STATUS

SHOW ENGINE INNODB STATUS displays extensive information from the standard InnoDB Monitor about the state of the InnoDB storage engine. For information about the standard monitor and other InnoDB Monitors that provide information about InnoDB processing, see Section 14.3.14.2, "SHOW ENGINE INNODB STATUS and the InnoDB Monitors".

SHOW ENGINE INNODB MUTEX displays InnoDB mutex statistics. The statement displays the following fields:

Information from this statement can be used to diagnose system problems. For example, large values of spin_waits and spin_rounds may indicate scalability problems.

Use SHOW ENGINE PERFORMANCE_SCHEMA STATUS to inspect the internal operation of the Performance Schema code:

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G...*************************** 3. row ***************************  Type: performance_schema  Name: events_waits_history.row_sizeStatus: 76*************************** 4. row ***************************  Type: performance_schema  Name: events_waits_history.row_countStatus: 10000*************************** 5. row ***************************  Type: performance_schema  Name: events_waits_history.memoryStatus: 760000...*************************** 57. row ***************************  Type: performance_schema  Name: performance_schema.memoryStatus: 26459600...

This statement is intended to help the DBA understand the effects that different Performance Schema options have on memory requirements.

Name values consist of two parts, which name an internal buffer and a buffer attribute, respectively. Interpret buffer names as follows:

Buffer attributes have these meanings:

In some cases, there is a direct relationship between a Performance Schema configuration parameter and a SHOW ENGINE value. For example, events_waits_history_long.row_count corresponds to performance_schema_events_waits_history_long_size. In other cases, the relationship is more complex. For example, events_waits_history.row_count corresponds to performance_schema_events_waits_history_size (the number of rows per thread) multiplied by performance_schema_max_thread_instances ( the number of threads).

If the server has the NDBCLUSTER storage engine enabled, SHOW ENGINE NDB STATUS displays cluster status information such as the number of connected data nodes, the cluster connectstring, and cluster binlog epochs, as well as counts of various Cluster API objects created by the MySQL Server when connected to the cluster. Sample output from this statement is shown here:

mysql> SHOW ENGINE NDB STATUS;+------------+-----------------------+---------------------------------------+| Type   | Name  | Status |+------------+-----------------------+---------------------------------------+| ndbcluster | connection | cluster_node_id=7, || |   | connected_host=192.168.0.103, || |   | connected_port=1186,  || |   | number_of_data_nodes=4,   || |   | number_of_ready_data_nodes=3, || |   | connect_count=0   || ndbcluster | NdbTransaction | created=6, free=0, sizeof=212 || ndbcluster | NdbOperation  | created=8, free=8, sizeof=660 || ndbcluster | NdbIndexScanOperation | created=1, free=1, sizeof=744 || ndbcluster | NdbIndexOperation | created=0, free=0, sizeof=664 || ndbcluster | NdbRecAttr | created=1285, free=1285, sizeof=60 || ndbcluster | NdbApiSignal  | created=16, free=16, sizeof=136   || ndbcluster | NdbLabel  | created=0, free=0, sizeof=196 || ndbcluster | NdbBranch | created=0, free=0, sizeof=24  || ndbcluster | NdbSubroutine | created=0, free=0, sizeof=68  || ndbcluster | NdbCall   | created=0, free=0, sizeof=16  || ndbcluster | NdbBlob   | created=1, free=1, sizeof=264 || ndbcluster | NdbReceiver   | created=4, free=0, sizeof=68  || ndbcluster | binlog | latest_epoch=155467,  || |   | latest_trans_epoch=148126, || |   | latest_received_binlog_epoch=0,   || |   | latest_handled_binlog_epoch=0, || |   | latest_applied_binlog_epoch=0 |+------------+-----------------------+---------------------------------------+

The rows with connection and binlog in the Name column were added to the output of this statement in MySQL 5.1. The Status column in each of these rows provides information about the MySQL server's connection to the cluster and about the cluster binary log's status, respectively. The Status information is in the form of comma-delimited set of name/value pairs.

The connection row's Status column contains the name/value pairs described in the following table.

The binlog row's Status column contains information relating to MySQL Cluster Replication. The name/value pairs it contains are described in the following table.

See Section 17.6, "MySQL Cluster Replication", for more information.

The remaining rows from the output of SHOW ENGINE NDB STATUS which are most likely to prove useful in monitoring the cluster are listed here by Name:

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.

mysql> SHOW ENGINES\G*************************** 1. row ***************************  Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO  XA: NO  Savepoints: NO*************************** 2. row ***************************  Engine: MyISAM Support: YES Comment: MyISAM storage engineTransactions: NO  XA: NO  Savepoints: NO*************************** 3. row ***************************  Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES  XA: YES  Savepoints: YES*************************** 4. row ***************************  Engine: EXAMPLE Support: YES Comment: Example storage engineTransactions: NO  XA: NO  Savepoints: NO*************************** 5. row ***************************  Engine: ARCHIVE Support: YES Comment: Archive storage engineTransactions: NO  XA: NO  Savepoints: NO*************************** 6. row ***************************  Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO  XA: NO  Savepoints: NO*************************** 7. row ***************************  Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write »  to it disappears)Transactions: NO  XA: NO  Savepoints: NO*************************** 8. row ***************************  Engine: FEDERATED Support: YES Comment: Federated MySQL storage engineTransactions: NO  XA: NO  Savepoints: NO*************************** 9. row ***************************  Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO  XA: NO  Savepoints: NO

The output from SHOW ENGINES may vary according to the MySQL version used and other factors. The values shown in the Support column indicate the server's level of support for the storage engine, as shown in the following table.

A value of NO means that the server was compiled without support for the engine, so it cannot be enabled at runtime.

A value of DISABLED occurs either because the server was started with an option that disables the engine, or because not all options required to enable it were given. In the latter case, the error log file should contain a reason indicating why the option is disabled. See Section 5.2.2, "The Error Log".

You might also see DISABLED for a storage engine if the server was compiled to support it, but was started with a --skip-engine_name option. For the NDBCLUSTER storage engine, DISABLED means the server was compiled with support for MySQL Cluster, but was not started with the --ndbcluster option.

All MySQL servers support MyISAM tables, because MyISAM is the default storage engine. It is not possible to disable MyISAM.

The Transactions, XA, and Savepoints columns indicate whether the storage engine supports transactions, XA transactions, and savepoints, respectively.

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

This statement is similar to SHOW WARNINGS, except that it displays information only for errors, rather than for errors, warnings, and notes.

The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.9, "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;

SHOW ERRORS and error_count apply only to errors, not warnings or notes. In other respects, they are similar to SHOW WARNINGS and warning_count. In particular, SHOW ERRORS cannot display information for more than max_error_count messages, and error_count can exceed the value of max_error_count if the number of errors exceeds max_error_count.

For more information, see Section 13.7.5.41, "SHOW WARNINGS Syntax".

SHOW EVENTS [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr]

This statement displays information about Event Manager events. It requires the EVENT privilege for the database from which the events are to be shown.

In its simplest form, SHOW EVENTS lists all of the events in the current schema:

mysql> SELECT CURRENT_USER(), SCHEMA();+----------------+----------+| CURRENT_USER() | SCHEMA() |+----------------+----------+| jon@ghidora | myschema |+----------------+----------+1 row in set (0.00 sec)mysql> SHOW EVENTS\G*************************** 1. row ***************************  Db: myschema Name: e_daily Definer: jon@ghidora   Time zone: SYSTEM Type: RECURRING  Execute at: NULL  Interval value: 10  Interval field: SECOND  Starts: 2006-02-09 10:41:23 Ends: NULL  Status: ENABLED  Originator: 0character_set_client: latin1collation_connection: latin1_swedish_ci  Database Collation: latin1_swedish_ci

To see events for a specific schema, use the FROM clause. For example, to see events for the test schema, use the following statement:

SHOW EVENTS FROM test;

The LIKE clause, if present, indicates which event names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

SHOW EVENTS output has the following columns:

For more information about SLAVE_DISABLED and the Originator column, see Section 16.4.1.10, "Replication of Invoked Features".

The event action statement is not shown in the output of SHOW EVENTS. Use SHOW CREATE EVENT or the INFORMATION_SCHEMA.EVENTS table.

Times displayed by SHOW EVENTS are given in the event time zone, as discussed in Section 19.4.4, "Event Metadata".

The columns in the output of SHOW EVENTS are similar to, but not identical to the columns in the INFORMATION_SCHEMA.EVENTS table. See Section 20.7, "The INFORMATION_SCHEMA EVENTS Table".

SHOW FUNCTION CODE func_name

This statement is similar to SHOW PROCEDURE CODE but for stored functions. See Section 13.7.5.28, "SHOW PROCEDURE CODE Syntax".

SHOW FUNCTION STATUS [LIKE 'pattern' | WHERE expr]

This statement is similar to SHOW PROCEDURE STATUS but for stored functions. See Section 13.7.5.29, "SHOW PROCEDURE STATUS 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'. If you specify only the user name part of the account name, a host name part of '%' is used. For additional information about specifying account names, see Section 13.7.1.3, "GRANT Syntax".

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();

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 GRANTS requires the SELECT privilege for the mysql database, except to see the privileges for the current user.

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]

SHOW INDEX returns table index information. The format resembles that of the SQLStatistics call in ODBC. This statement requires some privilege for any column in the table.

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;

The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

You can also list a table's indexes with the mysqlshow -k db_name tbl_name command.

SHOW MASTER STATUS

This statement provides status information about the binary log files of the master. It requires either the SUPER or REPLICATION CLIENT privilege.

Example:

mysql> SHOW MASTER STATUS;+---------------+----------+--------------+------------------+| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |+---------------+----------+--------------+------------------+| mysql-bin.003 | 73   | test | manual,mysql |+---------------+----------+--------------+------------------+

SHOW OPEN TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

SHOW OPEN TABLES lists the non-TEMPORARY tables that are currently open in the table cache. See Section 8.4.3.1, "How MySQL Opens and Closes Tables". The FROM clause, if present, restricts the tables shown to those present in the db_name database. The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

SHOW OPEN TABLES returns the following columns:

If you have no privileges for a table, it does not show up in the output from SHOW OPEN TABLES.

SHOW PLUGINS

SHOW PLUGINS displays information about server plugins. Plugin information is also available in the INFORMATION_SCHEMA.PLUGINS table. See Section 20.13, "The INFORMATION_SCHEMA PLUGINS Table".

Example of SHOW PLUGINS output:

mysql> SHOW PLUGINS\G*************************** 1. row ***************************   Name: binlog Status: ACTIVE   Type: STORAGE ENGINELibrary: NULLLicense: GPL*************************** 2. row ***************************   Name: CSV Status: ACTIVE   Type: STORAGE ENGINELibrary: NULLLicense: GPL*************************** 3. row ***************************   Name: MEMORY Status: ACTIVE   Type: STORAGE ENGINELibrary: NULLLicense: GPL*************************** 4. row ***************************   Name: MyISAM Status: ACTIVE   Type: STORAGE ENGINELibrary: NULLLicense: GPL...

SHOW PLUGINS returns the following columns:

For plugins installed with INSTALL PLUGIN, the Name and Library values are also registered in the mysql.plugin table.

For information about plugin data structures that form the basis of the information displayed by SHOW PLUGINS, see Section 23.2, "The MySQL Plugin API".

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: Databases  Comment: To use CREATE FUNCTION/PROCEDURE*************************** 5. row ***************************Privilege: Create temporary tables  Context: Databases  Comment: To use CREATE TEMPORARY TABLE...

Privileges belonging to a specific user are displayed by the SHOW GRANTS statement. See Section 13.7.5.22, "SHOW GRANTS Syntax", for more information.

SHOW PROCEDURE CODE proc_name

This statement is a MySQL extension that is available only for servers that have been built with debugging support. It displays a representation of the internal implementation of the named stored procedure. A similar statement, SHOW FUNCTION CODE, displays information about stored functions (see Section 13.7.5.20, "SHOW FUNCTION CODE Syntax").

Both statements require that you be the owner of the routine or have SELECT access to the mysql.proc table.

If the named routine is available, each statement produces a result set. Each row in the result set corresponds to one "instruction" in the routine. The first column is Pos, which is an ordinal number beginning with 0. The second column is Instruction, which contains an SQL statement (usually changed from the original source), or a directive which has meaning only to the stored-routine handler.

mysql> DELIMITER //mysql> CREATE PROCEDURE p1 () -> BEGIN ->   DECLARE fanta INT DEFAULT 55; ->   DROP TABLE t2; ->   LOOP -> INSERT INTO t3 VALUES (fanta); -> END LOOP; ->   END//Query OK, 0 rows affected (0.00 sec)mysql> SHOW PROCEDURE CODE p1//+-----+----------------------------------------+| Pos | Instruction |+-----+----------------------------------------+|   0 | set fanta@0 55 ||   1 | stmt 9 "DROP TABLE t2" ||   2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" ||   3 | jump 2 |+-----+----------------------------------------+4 rows in set (0.00 sec)

In this example, the nonexecutable BEGIN and END statements have disappeared, and for the DECLARE variable_name statement, only the executable part appears (the part where the default is assigned). For each statement that is taken from source, there is a code word stmt followed by a type (9 means DROP, 5 means INSERT, and so on). The final row contains an instruction jump 2, meaning GOTO instruction #2.

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr]

This statement is a MySQL extension. It returns characteristics of a stored procedure, such as the database, name, type, creator, creation and modification dates, and character set information. A similar statement, SHOW FUNCTION STATUS, displays information about stored functions (see Section 13.7.5.21, "SHOW FUNCTION STATUS Syntax").

The LIKE clause, if present, indicates which procedure or function names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

mysql> SHOW PROCEDURE STATUS LIKE 'sp1'\G*************************** 1. row ***************************  Db: test Name: sp1 Type: PROCEDURE Definer: testuser@localhost Modified: 2004-08-03 15:29:37 Created: 2004-08-03 15:29:37   Security_type: DEFINER Comment:character_set_client: latin1collation_connection: latin1_swedish_ci  Database Collation: latin1_swedish_ci

character_set_client is the session value of the character_set_client system variable when the routine was created. collation_connection is the session value of the collation_connection system variable when the routine was created. Database Collation is the collation of the database with which the routine is associated.

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

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows you which threads are running. You can also get this information from the INFORMATION_SCHEMA PROCESSLIST table or 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). 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).

Threads can be killed with the KILL statement. See Section 13.7.6.4, "KILL Syntax".

Here is an example of SHOW PROCESSLIST output:

mysql> SHOW FULL PROCESSLIST\G*************************** 1. row ***************************Id: 1User: system userHost:db: NULLCommand: ConnectTime: 1030455State: Waiting for master to send eventInfo: NULL*************************** 2. row ***************************Id: 2User: system userHost:db: NULLCommand: ConnectTime: 1004State: Has read all relay log; waiting for the slave   I/O thread to update itInfo: NULL*************************** 3. row ***************************Id: 3112User: replikatorHost: artemis:2204db: NULLCommand: Binlog DumpTime: 2144State: Has sent all binlog to slave; waiting for binlog to be updatedInfo: NULL*************************** 4. row ***************************Id: 3113User: replikatorHost: iconnect2:45781db: NULLCommand: Binlog DumpTime: 2086State: Has sent all binlog to slave; waiting for binlog to be updatedInfo: NULL*************************** 5. row ***************************Id: 3123User: stefanHost: localhostdb: apollonCommand: QueryTime: 0State: NULLInfo: SHOW FULL PROCESSLIST5 rows in set (0.00 sec)

The columns produced by SHOW PROCESSLIST have the following meanings:

SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]type: ALL  | BLOCK IO  | CONTEXT SWITCHES  | CPU  | IPC  | MEMORY  | PAGE FAULTS  | SOURCE  | SWAPS

The SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage for statements executed during the course of the current session.

Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON:

mysql> SET profiling = 1;

SHOW PROFILES displays a list of the most recent statements sent to the server. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling.

All statements are profiled except SHOW PROFILE and SHOW PROFILES, so you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list.

SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to row_count rows. If LIMIT is given, OFFSET offset may be added to begin the output offset rows into the full set of rows.

By default, SHOW PROFILE displays Status and Duration columns. The Status values are like the State values displayed by SHOW PROCESSLIST, although there might be some minor differences in interpretion for the two statements for some status values (see Section 8.12.5, "Examining Thread Information").

Optional type values may be specified to display specific additional types of information:

Profiling is enabled per session. When a session ends, its profiling information is lost.

mysql> SELECT @@profiling;+-------------+| @@profiling |+-------------+|   0 |+-------------+1 row in set (0.00 sec)mysql> SET profiling = 1;Query OK, 0 rows affected (0.00 sec)mysql> DROP TABLE IF EXISTS t1;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE TABLE T1 (id INT);Query OK, 0 rows affected (0.01 sec)mysql> SHOW PROFILES;+----------+----------+--------------------------+| Query_ID | Duration | Query |+----------+----------+--------------------------+| 0 | 0.000088 | SET PROFILING = 1 || 1 | 0.000136 | DROP TABLE IF EXISTS t1  || 2 | 0.011947 | CREATE TABLE t1 (id INT) |+----------+----------+--------------------------+3 rows in set (0.00 sec)mysql> SHOW PROFILE;+----------------------+----------+| Status   | Duration |+----------------------+----------+| checking permissions | 0.000040 || creating table   | 0.000056 || After create | 0.011363 || query end | 0.000375 || freeing items | 0.000089 || logging slow query   | 0.000019 || cleaning up  | 0.000005 |+----------------------+----------+7 rows in set (0.00 sec)mysql> SHOW PROFILE FOR QUERY 1;+--------------------+----------+| Status | Duration |+--------------------+----------+| query end  | 0.000107 || freeing items  | 0.000008 || logging slow query | 0.000015 || cleaning up | 0.000006 |+--------------------+----------+4 rows in set (0.00 sec)mysql> SHOW PROFILE CPU FOR QUERY 2;+----------------------+----------+----------+------------+| Status   | Duration | CPU_user | CPU_system |+----------------------+----------+----------+------------+| checking permissions | 0.000040 | 0.000038 |   0.000002 || creating table   | 0.000056 | 0.000028 |   0.000028 || After create | 0.011363 | 0.000217 |   0.001571 || query end | 0.000375 | 0.000013 |   0.000028 || freeing items | 0.000089 | 0.000010 |   0.000014 || logging slow query   | 0.000019 | 0.000009 |   0.000010 || cleaning up  | 0.000005 | 0.000003 |   0.000002 |+----------------------+----------+----------+------------+7 rows in set (0.00 sec)

You can also get profiling information from the PROFILING table in INFORMATION_SCHEMA. See Section 20.15, "The INFORMATION_SCHEMA PROFILING Table". For example, the following queries produce the same result:

SHOW PROFILE FOR QUERY 2;SELECT STATE, FORMAT(DURATION, 6) AS DURATIONFROM INFORMATION_SCHEMA.PROFILINGWHERE QUERY_ID = 2 ORDER BY SEQ;

SHOW PROFILES

The SHOW PROFILES statement, together with SHOW PROFILE, displays profiling information that indicates resource usage for statements executed during the course of the current session. For more information, see Section 13.7.5.31, "SHOW PROFILE Syntax".

SHOW RELAYLOG EVENTS   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the relay log of a replication slave. If you do not specify 'log_name', the first relay log is displayed. This statement has no effect on the master.

The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.9, "SELECT Syntax".

SHOW SLAVE HOSTS

Displays a list of replication slaves currently registered with the master. (Before MySQL 5.5.3, only slaves started with the --report-host=host_name option are visible in this list.)

The list is displayed on any server (not just the master server). The output looks like this:

mysql> SHOW SLAVE HOSTS;+------------+-----------+------+-----------+| Server_id  | Host  | Port | Master_id |+------------+-----------+------+-----------+|  192168010 | iconnect2 | 3306 | 192168011 || 1921680101 | athena | 3306 | 192168011 |+------------+-----------+------+-----------+

Some MySQL versions report another variable, Rpl_recovery_rank. This variable was never used, and was removed in MySQL 5.5.3. (Bug #13963)

SHOW SLAVE STATUS

This statement provides status information on essential parameters of the slave threads. It requires either the SUPER or REPLICATION CLIENT privilege.

If you issue this statement using the mysql client, you can use a \G statement terminator rather than a semicolon to obtain a more readable vertical layout:

mysql> SHOW SLAVE STATUS\G*************************** 1. row ***************************   Slave_IO_State: Waiting for master to send event  Master_Host: localhost  Master_User: root  Master_Port: 3306 Connect_Retry: 3  Master_Log_File: gbichot-bin.005  Read_Master_Log_Pos: 79   Relay_Log_File: gbichot-relay-bin.005 Relay_Log_Pos: 548 Relay_Master_Log_File: gbichot-bin.005 Slave_IO_Running: Yes Slave_SQL_Running: Yes  Replicate_Do_DB:  Replicate_Ignore_DB:   Replicate_Do_Table:   Replicate_Ignore_Table:  Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:   Last_Errno: 0   Last_Error: Skip_Counter: 0  Exec_Master_Log_Pos: 79  Relay_Log_Space: 552  Until_Condition: None   Until_Log_File: Until_Log_Pos: 0   Master_SSL_Allowed: No   Master_SSL_CA_File:   Master_SSL_CA_Path:  Master_SSL_Cert: Master_SSL_Cipher:   Master_SSL_Key: Seconds_Behind_Master: 8Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error:   Last_SQL_Errno: 0   Last_SQL_Error:  Replicate_Ignore_Server_Ids: 0 Master_Server_Id: 1

The following list describes the fields returned by SHOW SLAVE STATUS. For additional information about interpreting their meanings, see Section 8.12.5.6, "Replication Slave I/O Thread States".

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

SHOW STATUS provides server status information. This information also can be obtained using the mysqladmin extended-status command. The LIKE clause, if present, indicates which variable names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements". This statement does not require any privilege. It requires only the ability to connect to the server.

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.1.6, "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  |+--------------------+----------+

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. The scope for each status variable is listed at Section 5.1.6, "Server Status Variables".

Each invocation of the SHOW STATUS statement uses an internal temporary table and increments the global Created_tmp_tables value.

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

SHOW TABLE STATUS works likes SHOW TABLES, but provides a lot of information about each non-TEMPORARY table. You can also get this list using the mysqlshow --status db_name command. The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

This statement also displays information about views.

SHOW TABLE STATUS returns the following fields:

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.

For NDBCLUSTER 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

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 | IN} db_name] [LIKE 'pattern' | WHERE expr]

SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command. The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

This statement also lists any views in the database. 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.

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

SHOW TRIGGERS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

SHOW TRIGGERS lists the triggers currently defined for tables in a database (the default database unless a FROM clause is given). This statement returns results only for databases and tables for which you have the TRIGGER privilege. The LIKE clause, if present, indicates which table names to match and causes the statement to display triggers for those tables. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements".

For the trigger ins_sum as defined in Section 19.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@localhostcharacter_set_client: latin1collation_connection: latin1_swedish_ci  Database Collation: latin1_swedish_ci

character_set_client is the session value of the character_set_client system variable when the trigger was created. collation_connection is the session value of the collation_connection system variable when the trigger was created. Database Collation is the collation of the database with which the trigger is associated.

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:

See also Section 20.25, "The INFORMATION_SCHEMA TRIGGERS Table".

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

SHOW VARIABLES shows the values of MySQL system variables. This information also can be obtained using the mysqladmin variables command. The LIKE clause, if present, indicates which variable names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 20.31, "Extensions to SHOW Statements". This statement does not require any privilege. It requires only the ability to connect to the server.

With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. As of MySQL 5.5.3, if a variable has no global value, no value is displayed. Before 5.5.3, the session value is displayed. With SESSION, SHOW VARIABLES 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.

SHOW VARIABLES is subject to a version-dependent display-width limit. For variables with very long values that are not completely displayed, use SELECT as a workaround. For example:

SELECT @@GLOBAL.innodb_data_file_path;

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.1.5, "Using System Variables", and Section 13.7.4, "SET Syntax".

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

mysql> SHOW VARIABLES;+-----------------------------------------+---------------------------+| Variable_name   | Value |+-----------------------------------------+---------------------------+| auto_increment_increment | 1 || auto_increment_offset   | 1 || autocommit  | ON || automatic_sp_privileges | ON || back_log | 50 || basedir | /home/jon/bin/mysql-5.5   || big_tables  | OFF   || binlog_cache_size   | 32768 || binlog_direct_non_transactional_updates | OFF   || binlog_format   | STATEMENT || binlog_stmt_cache_size  | 32768 || bulk_insert_buffer_size | 8388608   |...| max_allowed_packet  | 1048576   || max_binlog_cache_size   | 18446744073709547520  || max_binlog_size | 1073741824 || max_binlog_stmt_cache_size  | 18446744073709547520  || max_connect_errors  | 10 || max_connections | 151   || max_delayed_threads | 20 || max_error_count | 64 || max_heap_table_size | 16777216  || max_insert_delayed_threads  | 20 || max_join_size   | 18446744073709551615  |...| thread_handling | one-thread-per-connection || thread_stack | 262144 || time_format | %H:%i:%s  || time_zone   | SYSTEM || timed_mutexes   | OFF   || timestamp   | 1316689732 || tmp_table_size  | 16777216  || tmpdir  | /tmp  || transaction_alloc_block_size | 8192  || transaction_prealloc_size   | 4096  || tx_isolation | REPEATABLE-READ   || unique_checks   | ON || updatable_views_with_limit  | YES   || version | 5.5.17-log || version_comment | Source distribution   || version_compile_machine | x86_64 || version_compile_os  | Linux || wait_timeout | 28800 || warning_count   | 0 |+-----------------------------------------+---------------------------+

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 information about the conditions (errors, warnings, and notes) that resulted from the last statement in the current session that generated messages. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

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

SHOW WARNINGS is also used following EXPLAIN EXTENDED, to display the extra information generated by EXPLAIN when the EXTENDED keyword is used. See Section 8.8.3, "EXPLAIN EXTENDED Output Format".

The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.9, "SELECT Syntax".

A related statement, SHOW ERRORS, shows only the error conditions (it excludes warnings and notes). See Section 13.7.5.18, "SHOW ERRORS Syntax".

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

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

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: 1287Message: 'TYPE=storage_engine' is deprecated, use 'ENGINE=storage_engine' instead1 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: 4mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Warning   Code: 1265Message: Data truncated for column 'b' at row 1*************************** 2. row ***************************  Level: Warning   Code: 1263Message: Data truncated, NULL supplied to NOT NULL column 'a' at row 2*************************** 3. row ***************************  Level: Warning   Code: 1264Message: Data truncated, out of range for column 'a' at row 3*************************** 4. row ***************************  Level: Warning   Code: 1265Message: Data truncated for column 'b' at row 34 rows in set (0.00 sec)

The max_error_count system variable controls the maximum number of error, warning, and note messages for which the server stores information, and thus the number of messages that SHOW WARNINGS displays. By default, max_error_count is 64. To change the number of messages the server can store, change the value of max_error_count.

The value of warning_count is not limited by max_error_count if the number of messages generated exceeds max_error_count.

In the following example, the ALTER TABLE statement produces three warning messages (as shown by the value of warning_count), 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: 3mysql> 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.

The following DROP TABLE statement results in a note:

mysql> DROP TABLE IF EXISTS test.no_such_table;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> SHOW WARNINGS;+-------+------+-------------------------------+| Level | Code | Message   |+-------+------+-------------------------------+| Note  | 1051 | Unknown table 'no_such_table' |+-------+------+-------------------------------+

If the sql_notes system variable is set to 0, notes do not increment warning_count and the server does not record them.

The MySQL server sends back a count indicating the total number of errors, warnings, and notes resulting from the last statement. From the C API, this value can be obtained by calling mysql_warning_count(). See Section 22.8.3.72, "mysql_warning_count()".

BINLOG 'str'

BINLOG is an internal-use statement. It is generated by the mysqlbinlog program as the printable representation of certain events in binary log files. (See Section 4.6.7, "mysqlbinlog - Utility for Processing Binary Log Files".) The 'str' value is a base 64-encoded string the that server decodes to determine the data change indicated by the corresponding event. This statement requires the SUPER privilege.

CACHE INDEX  tbl_index_list [, tbl_index_list] ...  [PARTITION (partition_list | ALL)]  IN key_cache_nametbl_index_list:  tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]partition_list:  partition_name[, partition_name][, ...]

The CACHE INDEX statement assigns table indexes to a specific key cache. It is used only for MyISAM tables. After the indexes have been assigned, they can be preloaded into the cache if desired with LOAD INDEX INTO CACHE.

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.1.5.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.

In MySQL 5.5, this statement is also supported for partitioned MyISAM tables. You can assign one or more indexes for one, several, or all partitions to a given key cache. For example, you can do the following:

CREATE TABLE pt (c1 INT, c2 VARCHAR(50), INDEX i(c1)) PARTITION BY HASH(c1) PARTITIONS 4;SET GLOBAL kc_fast.key_buffer_size = 128 * 1024;SET GLOBAL kc_slow.key_buffer_size = 128 * 1024;CACHE INDEX pt PARTITION (p0) IN kc_fast;CACHE INDEX pt PARTITION (p1, p3) IN kc_slow;

The previous set of statements performs the following actions:

If you wish instead to assign the indexes for all partitions in table pt to a single key cache named kc_all, you can use either one of the following 2 statements:

CACHE INDEX pt PARTITION (ALL) IN kc_all;CACHE INDEX pt IN kc_all;

The two statements just shown are equivalent, and issuing either one of them has exactly the same effect. In other words, if you wish to assign indexes for all partitions of a partitioned table to the same key cache, then the PARTITION (ALL) clause is optional.

When assigning indexes for multiple partitions to a key cache, the partitions do not have to be contiguous, and you are not required to list their names in any particular order. Indexes for any partitions that are not explicitly assigned to a key cache automatically use the server's default key cache.

As of MySQL 5.5, index preloading is also supported for partitioned MyISAM tables. For more information, see Section 13.7.6.5, "LOAD INDEX INTO CACHE Syntax".

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

The FLUSH statement has several variant forms that clear or reload various internal caches, flush tables, or acquire locks. To execute FLUSH, you must have the RELOAD privilege. Specific flush options might require additional privileges, as described later.

By default, the server writes FLUSH statements to the binary log so that they replicate to replication slaves. To suppress logging, use the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

The FLUSH statement causes an implicit commit. See Section 13.3.3, "Statements That Cause an Implicit Commit".

The RESET statement is similar to FLUSH. See Section 13.7.6.6, "RESET Syntax", for information about using the RESET statement with replication.

flush_option can be any of the following items.

The mysqladmin utility provides a command-line interface to some flush operations, using commands such as flush-hosts, flush-logs, flush-privileges, flush-status, and flush-tables. See Section 4.5.2, "mysqladmin - Client for Administering a MySQL Server".

FLUSH TABLES Syntax

FLUSH TABLES has several forms, described following. As of MySQL 5.5.3, if any variant of the TABLES option is used in a FLUSH statement, it must be the only option used. FLUSH TABLE is a synonym for FLUSH TABLES, except that TABLE does not work with the WITH READ LOCK variants prior to MySQL 5.5.3.

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.

KILL permits an 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.

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 [PARTITION (partition_list | ALL)] [[INDEX|KEY] (index_name[, index_name] ...)] [IGNORE LEAVES]partition_list: partition_name[, partition_name][, ...]

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. In MySQL 5.5, it is also supported for partitioned MyISAM tables; in addition, indexes on partitioned tables can be preloaded for one, several, or all partitions.

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

IGNORE LEAVES is also supported for partitioned MyISAM tables.

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 nonleaf 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.

In MySQL 5.5, it is possible to preload indexes on specific partitions of partitioned MyISAM tables. For example, of the following 2 statements, the first preloads indexes for partition p0 of a partitioned table pt, while the second preloads the indexes for partitions p1 and p3 of the same table:

LOAD INDEX INTO CACHE pt PARTITION (p0);LOAD INDEX INTO CACHE pt PARTITION (p1, p3);

To preload the indexes for all partitions in table pt, you can use either one of the following 2 statements:

LOAD INDEX INTO CACHE pt PARTITION (ALL);LOAD INDEX INTO CACHE pt;

The two statements just shown are equivalent, and issuing either one of them has exactly the same effect. In other words, if you wish to preload indexes for all partitions of a partitioned table, then the PARTITION (ALL) clause is optional.

When preloading indexes for multiple partitions, the partitions do not have to be contiguous, and you are not required to list their names in any particular order.

LOAD INDEX INTO CACHE ... IGNORE LEAVES fails unless all indexes in a table have the same block size. You can determine index block sizes for a table by using myisamchk -dv and checking the Blocksize column.

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.7.6.3, "FLUSH Syntax".

The RESET statement causes an implicit commit. See Section 13.3.3, "Statements That Cause an Implicit Commit".

reset_option can be any of the following: