DROP USER user
The DROP USER statement deletes a MySQL account that doesn't have any privileges. It serves to remove the account record from the user table. The account is named using the same format as for GRANT or REVOKE; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table record for the account.
To remove a MySQL user account, you should use the following procedure, performing the steps in the order shown:
Use SHOW GRANTS to determine what privileges the account has. See SHOW GRANTS.
Use REVOKE to revoke the privileges displayed by SHOW GRANTS. This removes records for the account from all the grant tables except the user table, and revokes any global privileges listed in the user table. See GRANT.
Delete the account by using DROP USER to remove the user table record.
The DROP USER statement was added in MySQL 4.1.1. Before 4.1.1, you should first revoke the account privileges as just described. Then delete the user table record and flush the grant tables like this:
mysql> DELETE FROM mysql.user
-> WHERE User='user_name' and Host='host_name';
mysql> FLUSH PRIVILEGES;
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
The GRANT and REVOKE statements allow system administrators to create MySQL user accounts and to grant rights to and revoke them from accounts. GRANT and REVOKE are implemented in MySQL 3.22.11 or later. For earlier MySQL versions, these statements do nothing.
MySQL account information is stored in the tables of the mysql database. This database and the access control system are discussed extensively in Chapter 5, Database Administration, which you should consult for additional details.
Privileges can be granted at four levels:
Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.
Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke only database privileges.
Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke only table privileges.
Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE, you must specify the same columns that were granted.
To make it easy to revoke all privileges, MySQL 4.1.2 has added the following syntax, which drops all database-, table-, and column-level privileges for the named users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
Before MySQL 4.1.2, all privileges cannot be dropped at once. Two statements are necessary:
REVOKE ALL PRIVILEGES ON *.* FROM user [, user] ... REVOKE GRANT OPTION ON *.* FROM user [, user] ...
For the GRANT and REVOKE statements, priv_type can be specified as any of the following:
| Privilege | Meaning |
| ALL [PRIVILEGES] | Sets all simple privileges except GRANT OPTION |
| ALTER | Allows use of ALTER TABLE |
| CREATE | Allows use of CREATE TABLE |
| CREATE TEMPORARY TABLES | Allows use of CREATE TEMPORARY TABLE |
| CREATE VIEW | Allows use of CREATE VIEW |
| DELETE | Allows use of DELETE |
| DROP | Allows use of DROP TABLE |
| EXECUTE | Allows the user to run stored procedures (MySQL 5.0) |
| FILE | Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE |
| INDEX | Allows use of CREATE INDEX and DROP INDEX |
| INSERT | Allows use of INSERT |
| LOCK TABLES | Allows use of LOCK TABLES on tables for which you have the SELECT privilege |
| PROCESS | Allows use of SHOW FULL PROCESSLIST |
| REFERENCES | Not yet implemented |
| RELOAD | Allows use of FLUSH |
| REPLICATION CLIENT | Allows the user to ask where the slave or master servers are |
| REPLICATION SLAVE | Needed for replication slaves (to read binary log events from the master) |
| SELECT | Allows use of SELECT |
| SHOW DATABASES | SHOW DATABASES shows all databases |
| SHOW VIEW | Allows use of SHOW CREATE VIEW |
| SHUTDOWN | Allows use of mysqladmin shutdown |
| SUPER | Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached |
| UPDATE | Allows use of UPDATE |
| USAGE | Synonym for “no privileges” |
| GRANT OPTION | Allows privileges to be granted |
USAGE can be used when you want to create a user that has no privileges.
Use SHOW GRANTS to determine what privileges the account has. See SHOW GRANTS.
The CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ..., SHOW DATABASES and SUPER privileges are new in MySQL 4.0.2. To use them after upgrading to 4.0.2 or later, you must upgrade your grant tables. Similarly, the CREATE VIEW and SHOW VIEW privileges are new in MySQL 5.0.1. To use them after upgrading to 5.0.1 or later, you must upgrade your grant tables. See the section called “Upgrading the Grant Tables”.
In older MySQL versions that do not have the SUPER privilege, the PROCESS privilege can be used instead.
You can assign global privileges by using ON *.* syntax or database privileges by using ON db_name.* syntax. If you specify ON * and you have a current database, the privileges will be granted in that database. (Warning: If you specify ON * and you don't have a current database, the privileges granted will be global!)
The EXECUTION, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative privileges that can only be granted globally (using ON *.* syntax).
Other privileges can be granted globally or at more specific levels.
The only priv_type values you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER.
The only priv_type values you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT, and UPDATE.
For the global, database, and table levels, GRANT ALL assigns only the privileges that exist at the level you are granting. For example, if you use GRANT ALL ON db_name.*, that is a database-level statement, so none of the global-only privileges such as FILE will be granted.
For column-level privileges (that is, when you specify column_list), you must explicitly name the privileges to be granted. You cannot use ALL as a privilege specifier.
MySQL allows you to create database-level privileges even if the database doesn't exist, to make it easy to prepare for database use. However, MySQL currently does not allow you to create table-level privileges if the table doesn't exist.
MySQL does not automatically revoke any privileges even if you drop a table or drop a database.
Note: the ‘_’ and ‘%’ wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a ‘_’ character as part of a database name, you should specify it as ‘\_’ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ....
In order to accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user value in the form user_name@host_name. If you want to specify a user_name string containing special characters (such as ‘-’), or a host_name string containing special characters or wildcard characters (such as ‘%’), you can quote the username or hostname (for example, 'test-user'@'test-hostname'). Quote the username and hostname separately.
You can specify wildcards in the hostname. For example, user_name@'%.loc.gov' applies to user_name for any host in the loc.gov domain, and user_name@'144.155.166.%' applies to user_name for any host in the 144.155.166 class C subnet.
The simple form user_name is a synonym for user_name@'%'.
MySQL doesn't support wildcards in usernames. Anonymous users are defined by inserting entries with User='' into the mysql.user table or creating a user with an empty name with the GRANT statement:
mysql> GRANT ALL ON test.* TO ''@'localhost' ...
When specifying quoted values, quote database, table, or column names as identifiers, using backticks (‘`’). Quote hostnames, usernames, or passwords as strings, using apostrophes (‘'’).
Warning: If you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user_name@localhost. Otherwise, the anonymous-user account for the local host in the mysql.user table will be used when named users try to log in to the MySQL server from the local machine! (This anonymous-user account is created during MySQL installation.)
You can determine whether this applies to you by executing the following query:
mysql> SELECT Host, User FROM mysql.user WHERE User='';
If you want to delete the local anonymous-user account to avoid the problem just described, use these statements:
mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User=''; mysql> FLUSH PRIVILEGES;
For the moment, GRANT only supports host, table, database, and column names up to 60 characters long. A username can be up to 16 characters.
The privileges for a table or column are formed additively from the logical OR of the privileges at each of the four privilege levels. For example, if the mysql.user table specifies that a user has a global SELECT privilege, the privilege cannot be denied by an entry at the database, table, or column level.
The privileges for a column can be calculated as follows:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges
In most cases, you grant rights to a user at only one of the privilege levels, so life isn't normally this complicated. The details of the privilege-checking procedure are presented in the section called “The MySQL Access Privilege System”.
If you grant privileges for a username/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE statement. In other words, GRANT may create user table entries, but REVOKE will not remove them; you must do that explicitly using DROP USER or DELETE.
In MySQL 3.22.12 or later, if a new user is created or if you have global grant privileges, the user's password is set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, it is replaced by the new one.
Warning: If you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is insecure.
Passwords can also be set with the SET PASSWORD statement. See SET PASSWORD.
In the IDENTIFIED BY clause, the password should be given as the literal password value. It is unnecessary to use the PASSWORD() function as it is for the SET PASSWORD statement. For example:
GRANT ... IDENTIFIED BY 'mypass';
If you don't want to send the password in clear text and you know the hashed value that PASSWORD() would return for the password, you can specify the hashed value preceded by the keyword PASSWORD:
GRANT ... IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
In a C program, you can get the hashed value by using the make_scrambled_password() C API function.
If you grant privileges for a database, an entry in the mysql.db table is created if needed. If all privileges for the database are removed with REVOKE, this entry is deleted.
If a user has no privileges for a table, the table name is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement). If a user has no privileges for a database, the database name is not displayed by SHOW DATABASES unless the user has the SHOW DATABASES privilege.
The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the GRANT OPTION privilege, because two users with different privileges may be able to join privileges!
You cannot grant another user a privilege you don't have yourself; the GRANT OPTION privilege allows you to give away only those privileges you possess.
Be aware that when you grant a user the GRANT OPTION privilege at a particular privilege level, any privileges the user already possesses (or is given in the future!) at that level are also grantable by that user. 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, the user can give away not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can give away INSERT, SELECT, and UPDATE.
You should not grant ALTER privileges to a normal user. If you do that, the user can try to subvert the privilege system by renaming tables!
The MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, and MAX_CONNECTIONS_PER_HOUR count options are new in MySQL 4.0.2. They limit the number of queries, updates, and logins a user can perform during one hour. If count is 0 (the default), this means there is no limitation for that user. See the section called “Limiting Account Resources”. Note: To specify any of these options for an existing user without affecting existing privileges, use GRANT USAGE ON *.* ... WITH MAX_....
MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the username and password. To specify SSL-related options for a MySQL account, use the REQUIRE clause of the GRANT statement. (For background on the use of SSL with MySQL, see the section called “Using Secure Connections”.)
There are different possibilities for limiting connection types for an account:
If an account has no SSL or X509 requirements, unencrypted connections are allowed if the username and password are valid. However, encrypted connections also can be used at the client's option, if the client has the proper certificate and key files.
The REQUIRE SSL option tells the server to allow only SSL-encrypted connections for the account. Note that this option can be omitted if there are any access-control records that allow non-SSL connections.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
-> IDENTIFIED BY 'goodsecret' REQUIRE SSL;
REQUIRE X509 means that the client must have a valid certificate but that the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
-> IDENTIFIED BY 'goodsecret' REQUIRE X509;
REQUIRE ISSUER 'issuer' places the restriction on connection attempts that the client must present a valid X509 certificate issued by CA 'issuer'. If the client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unneccessary.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
-> IDENTIFIED BY 'goodsecret'
-> REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/
O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';
Note that the ISSUER value should be entered as a single string.
REQUIRE SUBJECT 'subject' places the restriction on connection attempts that the client must present a valid X509 certificate with subject 'subject' in it. If the client presents a certificate that is valid but has a different subject, the server rejects the connection.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
-> IDENTIFIED BY 'goodsecret'
-> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
O=MySQL demo client certificate/
CN=Tonu Samuel/Email=tonu@example.com';
Note that the SUBJECT value should be entered as a single string.
REQUIRE CIPHER 'cipher' is needed to ensure that strong enough ciphers and key lengths will be used. SSL itself can be weak if old algorithms with short encryption keys are used. Using this option, you can ask for some exact cipher method to allow a connection.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
-> IDENTIFIED BY 'goodsecret'
-> REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause like this:
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
-> IDENTIFIED BY 'goodsecret'
-> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
O=MySQL demo client certificate/
CN=Tonu Samuel/Email=tonu@example.com'
-> AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
-> AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
Note that the SUBJECT and ISSUER values each should be entered as a single string.
Starting from MySQL 4.0.4, the AND keyword is optional between REQUIRE options.
The order of the options does not matter, but no option can be specified twice.
When mysqld starts, all privileges are read into memory. Database, table, and column privileges take effect at once, and user-level privileges take effect the next time the user connects. Modifications to the grant tables that you perform using GRANT or REVOKE are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, and so on), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables. See the section called “When Privilege Changes Take Effect”.
Note that if you are using table or column privileges for even one user, the server examines table and column privileges for all users and this slows down MySQL a bit. Similarly, if you limit the number of queries, updates, or connections for any users, the server must monitor these values.
The biggest differences between the standard SQL and MySQL versions of GRANT are:
In MySQL, privileges are associated with a username/hostname combination and not with only a username.
Standard SQL doesn't have global or database-level privileges, nor does it support all the privilege types that MySQL supports.
MySQL doesn't support the standard SQL TRIGGER or UNDER privileges.
Standard SQL privileges are structured in a hierarchical manner. If you remove a user, all privileges the user has been granted are revoked. In MySQL, the granted privileges are not automatically revoked; you must revoke them yourself.
With standard SQL, when you drop a table, all privileges for the table are revoked. With standard SQL, when you revoke a privilege, all privileges that were granted based on the privilege are also revoked. In MySQL, privileges can be dropped only with explicit REVOKE statements or by manipulating the MySQL grant tables.
In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don't have the INSERT privilege will be set to their default values. Standard SQL requires you to have the INSERT privilege on all columns.
SET PASSWORD = PASSWORD('some password')
SET PASSWORD FOR user = PASSWORD('some password')
The SET PASSWORD statement assigns a password to an existing MySQL user account.
The first syntax sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.
The second syntax sets the password for a specific account on the current server host. Only clients with access to the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:
mysql> SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
That is equivalent to the following statements:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpass')
-> WHERE User='bob' AND Host='%.loc.gov';
mysql> FLUSH PRIVILEGES;
Note: If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use this statement without reading the section called “Password Hashing in MySQL 4.1” first. The password format changed in MySQL 4.1, and under certain circumstances it is possible that if you change your password, you might not be able to connect to the server afterward.
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
This statement analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock. This works on MyISAM and BDB tables and (as of MySQL 4.0.13) InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk -a.
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. The statement returns a table with the following columns:
| Column | Value |
| Table | The table name |
| Op | Always analyze |
| Msg_type | One of status, error, info, or warning |
| Msg_text | The message |
You can check the stored key distribution with the SHOW INDEX statement. See SHOW INDEX.
If the table hasn't changed since the last ANALYZE TABLE statement, the table will not be analyzed again.
Before MySQL 4.1.1, ANALYZE TABLE statements are not written to the binary log. As of MySQL 4.1.1, they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used.
BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'
Note: This statement is deprecated. We are working on a better replacement for it that will provide online backup capabilities. In the meantime, the mysqlhotcopy script can be used instead.
BACKUP TABLE copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. The statement works only for MyISAM tables. It copies the .frm definition and .MYD data files. The .MYI index file can be rebuilt from those two files. The directory should be specified as a full pathname. Before using this statement, please see the section called “Database Backups”.
During the backup, a read lock is held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot (preventing any of them from being changed during the backup operation), you must first issue a LOCK TABLES statement to obtain a read lock for every table in the group.
The statement returns a table with the following columns:
| Column | Value |
| Table | The table name |
| Op | Always backup |
| Msg_type | One of status, error, info, or warning |
| Msg_text | The message |
BACKUP TABLE is available in MySQL 3.23.25 and later.
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
CHECK TABLE works only on MyISAM and InnoDB tables. On MyISAM tables, This is the same thing as running myisamchk --medium-check tbl_name on the table.
If you don't specify any option, MEDIUM is used. Checks the table or tables for errors. For MyISAM tables, the key statistics are updated. The statement returns a table with the following columns:
| Column | Value |
| Table | The table name |
| Op | Always check |
| Msg_type | One of status, error, info, or warning |
| Msg_text | The message |
Note that the statement might produce many rows of information for each checked table. The last row will have 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 the section called “Table Maintenance and Crash Recovery”. Table is already up to date means that the storage engine for the table indicated that there was no need to check the table.
The different check options are as follows:
| Type | Meaning |
| QUICK | Don't scan the rows to check for incorrect links. |
| FAST | Only check tables that haven't been closed properly. |
| CHANGED | Only check tables that have been changed since the last check or haven't been closed properly. |
| MEDIUM | Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. |
| EXTENDED | Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but will take a long time! |
These options apply only to checking MyISAM tables and are ignored for InnoDB tables.
If none of the options QUICK, MEDIUM, or EXTENDED are specified, the default check type for dynamic-format MyISAM tables is MEDIUM. 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, which does a quick check on the table to see whether it was closed properly:
CHECK TABLE test_table FAST QUICK;
Note: In some cases, CHECK TABLE will change the table! This happens if the table is marked as “corrupted” or “not closed properly” but CHECK TABLE doesn't find any problems in the table. In this case, CHECK TABLE marks the table as okay.
If a table is corrupted, it's 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 doesn't find an error in the data file. (In most cases, MySQL should find, under normal usage, any error in the data file. If this happens, the table is marked as “corrupted” and cannot be used until it's 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 your table from time to time. In most cases, FAST is to be preferred over CHANGED. (The only case when it isn't preferred is when you suspect that you have found a bug in the MyISAM code.)
EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. (This is very unlikely if a normal check has succeeded!)
Some problems reported by CHECK TABLE can't be corrected automatically:
Found row where the auto_increment column has the value 0.
This means that you have a row in the table where the AUTO_INCREMENT index column contains the value 0. (It's possible to create a row where the AUTO_INCREMENT column is 0 by explicitly setting the column to 0 with an UPDATE statement.)
This isn't an error in itself, but could cause trouble if you decide to dump the table and restore it or do an ALTER TABLE on the table. In this case, the AUTO_INCREMENT column will change value according to the rules of AUTO_INCREMENT columns, which could cause problems such as a duplicate-key error.
To get rid of the warning, just execute an UPDATE statement to set the column to some other value than 0.
CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]
Reports a table checksum.
If QUICK is specified, 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, currently supported only for MyISAM tables. See CREATE TABLE.
In EXTENDED mode the whole table is read row by row and the checksum is calculated. This can be very slow for large tables.
By default, if neither QUICK nor EXTENDED is specified, MySQL returns a live checksum if the table storage engine supports it and scans the table otherwise.
This statement is implemented in MySQL 4.1.1.
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it's not likely that you need to do this more than once a week or month and only on certain tables.
For the moment, OPTIMIZE TABLE works only on MyISAM, BDB and InnoDB tables. For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. It was also the case for InnoDB tables before MySQL 4.1.3; starting from this version it is mapped to ALTER TABLE. See ANALYZE TABLE.
You can get OPTIMIZE TABLE to work on other table types by starting mysqld with the --skip-new or --safe-mode option; in this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.
OPTIMIZE TABLE works as follows:
If the table has deleted or split rows, repair the table.
If the index pages are not sorted, sort them.
If the statistics are not up to date (and the repair couldn't be done by sorting the index), update them.
Note that MySQL locks the table during the time OPTIMIZE TABLE is running.
Before MySQL 4.1.1, OPTIMIZE TABLE statements are not written to the binary log. As of MySQL 4.1.1, they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used.
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk --recover tbl_name. REPAIR TABLE works only on MyISAM tables. Normally you should never have to run this statement. However, if disaster strikes, REPAIR TABLE is very likely to get back all your data from a MyISAM table. If your tables become corrupted often, you should try to find the reason for it, to eliminate the need to use REPAIR TABLE. See the section called “What to Do If MySQL Keeps Crashing”. See MyISAM table problems.
The statement returns a table with the following columns:
| Column | Value |
| Table | The table name |
| Op | Always repair |
| Msg_type | One of status, error, info, or warning |
| Msg_text | The message |
The REPAIR TABLE statement might produce many rows of information for each repaired table. The last row will have a Msg_type value of status and Msg_test normally should be OK. If you don't get OK, you should try repairing the table with myisamchk --safe-recover, because REPAIR TABLE does not yet implement all the options of myisamchk. We plan to make it more flexible in the future.
If QUICK is given, REPAIR TABLE tries to repair only the index tree. This type of repair is like that done by myisamchk --recover --quick.
If you use EXTENDED, MySQL creates the index row by row instead of creating one index at a time with sorting. (Before MySQL 4.1, this might be better than sorting on fixed-length keys if you have long CHAR keys that compress very well.) This type of repair is like that done by myisamchk --safe-recover.
As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR TABLE. Use it if the .MYI index file is missing or if its header is corrupted. In this mode, MySQL will re-create the .MYI file using information from the .frm file. This kind of repair cannot be done with myisamchk. Note: Use this mode only if you cannot use regular REPAIR modes. .MYI header contains important table metadata (in particular, current AUTO_INCREMENT value and Delete link) that will be lost in REPAIR ... USE_FRM. Don't use USE_FRM if the table is compressed, as this information is also stored in .MYI file.
Before MySQL 4.1.1, REPAIR TABLE statements are not written to the binary log. As of MySQL 4.1.1, they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used.
Warning: If the server dies during a REPAIR TABLE operation, it's essential after restarting it that you immediately execute another REPAIR TABLE statement for the table before performing any other operations on it. (It's always good to start by making a backup.) In the worst case, you might have a new clean index file without information about the data file, and then the next operation you perform could overwrite the data file. This is an unlikely, but possible scenario.
RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'
Restores the table or tables from a backup that was made with BACKUP TABLE. Existing tables will not be overwritten; if you try to restore over an existing table, you will get an error. Just as BACKUP TABLE, RESTORE TABLE currently works only for MyISAM tables. The directory should be specified as a full pathname.
The backup for each table consists of its .frm format file and .MYD data file. The restore operation restores those files, then uses them to rebuild the .MYI index file. Restoring takes longer than backing up due to the need to rebuild the indexes. The more indexes the table has, the longer it will take. The statement returns a table with the following columns:
| Column | Value |
| Table | The table name |
| Op | Always restore |
| Msg_type | One of status, error, info, or warning |
| Msg_text | The message |
SET allows you to set variables and options.
SHOW has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following:
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern'] SHOW CREATE DATABASE db_name SHOW CREATE TABLE tbl_name SHOW DATABASES [LIKE 'pattern'] SHOW [STORAGE] ENGINES SHOW ERRORS [LIMIT [offset,] row_count] SHOW GRANTS FOR user SHOW INDEX FROM tbl_name [FROM db_name] SHOW INNODB STATUS SHOW [BDB] LOGS SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW STATUS [LIKE 'pattern'] SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern'] SHOW WARNINGS [LIMIT [offset,] row_count]
If the syntax for a given SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL ‘%’ and ‘_’ wildcard characters. The pattern is useful for restricting statement output to matching values. Note that there are other forms of these statements described elsewhere:
The SET PASSWORD statement for assigning account passwords is described in See SET PASSWORD.
The SHOW statement has forms that provide information about replication master and slave servers:
SHOW BINLOG EVENTS SHOW MASTER LOGS SHOW MASTER STATUS SHOW SLAVE HOSTS SHOW SLAVE STATUS
These forms of SHOW are described in the section called “Replication Statements”.
SET variable_assignment [, variable_assignment] ...
variable_assignment:
user_var_name = expr
| [GLOBAL | SESSION] system_var_name = expr
| @@[global. | session.]system_var_name = expr
SET sets different types of variables that affect the operation of the server or your client. It can be used to assign values to user variables or system variables. In MySQL 4.0.3, we added the GLOBAL and SESSION options and allowed most important system variables to be changed dynamically at runtime. The system variables that you can set at runtime are described in the section called “Dynamic System Variables”.
In older versions of MySQL, SET OPTION is used instead of SET, but this is now deprecated; just leave out the word OPTION.
The following example show the different syntaxes you can use to set variables.
A user variable is written as @var_name and can be set as follows:
SET @var_name = expr;
Further information about user variables is given in the section called “User Variables”.
System variables can be referred to in SET statements as var_name. The name optionally can be preceded by GLOBAL or @@global. to indicate explicitly that the variable is a global variable, or by SESSION, @@session., or @@ to indicate that it is a session variable. LOCAL and @@local. are synonyms for SESSION and @@session.. If no modifier is present, SET sets the session variable.
The @@var_name syntax for system variables is supported to make MySQL syntax compatible with some other database systems.
If you set several system variables in the same statement, the last used GLOBAL or SESSION option is used for variables that have no mode specified.
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
If you set a system variable using SESSION (the default), the value remains in effect until the current session ends or until you set the variable to a different value. If you set a system variable using GLOBAL, which requires the SUPER privilege, the value is remembered and used for new connections until the server restarts. If you want to make a variable setting permanent, you should put it in an option file. See the section called “Using Option Files”.
To prevent incorrect usage, MySQL produces an error if you use SET GLOBAL with a variable that can only be used with SET SESSION or if you do not specify GLOBAL when setting a global variable.
If you want to set a SESSION variable to the GLOBAL value or a GLOBAL value to the compiled-in MySQL default value, you can set it to DEFAULT. For example, the following two statements are identical in setting the session value of max_join_size to the global value:
SET max_join_size=DEFAULT; SET @@session.max_join_size=@@global.max_join_size;
You can get a list of most system variables with SHOW VARIABLES. See SHOW VARIABLES. To get a specific variable name or list of names that match a pattern, use a LIKE clause:
SHOW VARIABLES LIKE 'max_join_size'; SHOW GLOBAL VARIABLES LIKE 'max_join_size';
You can also get the value for a specific value by using the @@[global.|local.]var_name syntax with SELECT:
SELECT @@max_join_size, @@global.max_join_size;
When you retrieve a variable with SELECT @@var_name (that is, you do not specify global., session., or local.), MySQL returns the SESSION value if it exists and the GLOBAL value otherwise.
The following list describes variables that have non-standard syntax or that are not described in the list of system variables that is found in the section called “Server System Variables”. Although these variables are not displayed by SHOW VARIABLES, you can obtain their values with SELECT (with the exception of CHARACTER SET and SET NAMES). For example:
mysql> SELECT @@AUTOCOMMIT; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+
Set the autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you have to use COMMIT to accept a transaction or ROLLBACK to cancel it. If you change AUTOCOMMIT mode from 0 to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See COMMIT.
If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error The table tbl_name is full will not occur for SELECT operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). As of MySQL 4.0, you should normally never need to set this variable, because MySQL automatically converts in-memory tables to disk-based tables as necessary. This variable previously was named SQL_BIG_TABLES.
This maps all strings from and to the client with the given mapping. Before MySQL 4.1, the only allowable value for charset_name is cp1251_koi8, but you can add new mappings by editing the sql/convert.cc file in the MySQL source distribution. As of MySQL 4.1.1, SET CHARACTER SET sets three session system variables: character_set_client and character_set_results are set to the given character set, and character_set_connection to the value of character_set_database.
The default mapping can be restored by using a value of DEFAULT.
Note that the syntax for SET CHARACTER SET differs from that for setting most other options.
If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, they are ignored. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different than that required by their parent/child relationships. This variable was added in MySQL 3.23.52. See InnoDB foreign key constraints.
The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other databases. As of MySQL 3.23.25, you can read its value with SELECT @@IDENTITY. As of MySQL 4.0.3, you can also set its value with SET IDENTITY.
Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log.
Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update theh value returned by the mysql_insert_id() C API function.
SET NAMES sets the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set.
The default mapping can be restored by using a value of DEFAULT.
Note that the syntax for SET NAMES differs from that for setting most other options. This statement is available as of MySQL 4.1.0.
If set to 1 (the default), you can find the last inserted row for a table that contains an AUTO_INCREMENT column by using the following construct:
WHERE auto_increment_column IS NULL
This behavior is used by some ODBC programs, such as Access. SQL_AUTO_IS_NULL was added in MySQL 3.23.52.
If set to 0, MySQL aborts SELECT statements that probably will take a very long time (that is, statements for which the optimizer estimates that the number of examined rows will exceed the value of max_join_size). This is useful when an inadvisable WHERE statement has been issued. The default value for a new connection is 1, which allows all SELECT statements.
If you set the max_join_size system variable to a value other than DEFAULT, SQL_BIG_SELECTS will be set to 0.
SQL_BUFFER_RESULT forces results from SELECT statements to be put into temporary tables. This helps MySQL free the table locks early and can be beneficial in cases where it takes a long time to send results to the client. This variable was added in MySQL 3.23.13.
If set to 0, no logging is done to the binary log for the client. The client must have the SUPER privilege to set this option. This variable was added in MySQL 3.23.16.
If set to 1, no logging is done to the general query log for this client. The client must have the SUPER privilege to set this option.
If set to 0, no logging is done to the update log for the client. The client must have the SUPER privilege to set this option. This variable was added in MySQL 3.22.5. Starting from MySQL 5.0.0, it is deprecated and is mapped to SQL_LOG_BIN (see the section called “Changes in release 5.0.0 (22 Dec 2003: Alpha)”).
If set to 1, SHOW CREATE TABLE quotes table and column names. If set to 0, quoting is disabled. This option is enabled by default so that replication will work for tables with table and column names that require quoting. This variable was added in MySQL 3.23.26. SHOW CREATE TABLE.
If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. This variable was added in MySQL 3.22.32.
The maximum number of records to return from SELECT statements. The default value for a new connection is “unlimited.” If you have changed the limit, the default value can be restored by using a SQL_SELECT_LIMIT value of DEFAULT.
If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of SQL_SELECT_LIMIT.
This variable controls whether single-row INSERT statements produce an information string if warnings occur. The default is 0. Set the value to 1 to produce an information string. This variable was added in MySQL 3.22.11.
Set the time for this client. This is used to get the original timestamp if you use the binary log to restore rows. timestamp_value should be a Unix epoch timestamp, not a MySQL timestamp.
If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB tables are performed. If set to 0, no uniqueness checks are done. This variable was added in MySQL 3.23.52. See InnoDB foreign key constraints.
SHOW CHARACTER SET [LIKE 'pattern']
The SHOW CHARACTER SET statement shows all available character sets. It takes an optional LIKE clause that indicates which character set names to match. For example:
mysql> SHOW CHARACTER SET LIKE 'latin%'; +---------+-----------------------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+-----------------------------+-------------------+--------+ | latin1 | ISO 8859-1 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 used to store one character.
SHOW CHARACTER SET is available as of MySQL 4.1.0.
SHOW COLLATION [LIKE 'pattern']
The output from SHOW COLLATION includes all available character sets. It takes an optional LIKE clause that indicates which collation names to match. For example:
mysql> SHOW COLLATION LIKE 'latin1%'; +-------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+---------+----+---------+----------+---------+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 0 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +-------------------+---------+----+---------+----------+---------+
The Default column indicates whether a collation is the default for its character set. Compiled indicates whether the character set is compiled into the server. Sortlen is related to the amount of memory required to sort strings expressed in the character set.
SHOW COLLATION is available as of MySQL 4.1.0.
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW COLUMNS lists the columns in a given table. If the column types differ from what you expect them to be based on your CREATE TABLE statement, note that MySQL sometimes changes column types when you create or alter a table. The conditions for which this occurs are described in the section called “Silent Column Specification Changes”.
The FULL keyword can be used from MySQL 3.23.32 on. It causes the output to include the privileges you have for each column. As of MySQL 4.1, FULL also causes any per-column comments to be displayed.
You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. These two statements are equivalent:
mysql> SHOW COLUMNS FROM mytable FROM mydb; mysql> SHOW COLUMNS FROM mydb.mytable;
SHOW FIELDS is a synonym for SHOW COLUMNS. You can also list a table's columns with the mysqlshow db_name tbl_name command.
The DESCRIBE statement provides information similar to SHOW COLUMNS. See DESCRIBE.
SHOW CREATE {DATABASE | SCHEMA} db_name
Shows a CREATE DATABASE statement that will create the given database. It was added in MySQL 4.1. SHOW CREATE SCHEMA can be used as of MySQL 5.0.2.
mysql> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
Database: test
Create Database: CREATE DATABASE `test`
/*!40100 DEFAULT CHARACTER SET latin1 */
SHOW CREATE TABLE tbl_name
Shows a CREATE TABLE statement that will create the given table. It was added in MySQL 3.23.20.
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id INT(11) default NULL auto_increment,
s char(60) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM
SHOW CREATE TABLE quotes table and column names according to the value of the SQL_QUOTE_SHOW_CREATE option. SET SQL_QUOTE_SHOW_CREATE.
SHOW CREATE VIEW view_name
This statement shows a CREATE VIEW statement that will create the given view.
mysql> SHOW CREATE VIEW v; +-------+----------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------+ | v | CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` | +-------+----------------------------------------------------+
This statement was added in MySQL 5.0.1.
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']
SHOW DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command. As of MySQL 4.0.2, you will see only those databases for which you have some kind of privilege, if you don't have the global SHOW DATABASES privilege.
If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege.
SHOW SCHEMAS can be used as of MySQL 5.0.2
SHOW [STORAGE] ENGINES
SHOW ENGINES shows you status information about the storage engines. This is particularly useful for checking whether a storage engine is supported, or to see what the default engine is. This statement is implemented in MySQL 4.1.2. SHOW TABLE TYPES is a deprecated synonym.
mysql> SHOW ENGINES\G *************************** 1. row *************************** Engine: MyISAM Support: DEFAULT Comment: Default engine as of MySQL 3.23 with great performance *************************** 2. row *************************** Engine: HEAP Support: YES Comment: Alias for MEMORY *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables *************************** 4. row *************************** Engine: MERGE Support: YES Comment: Collection of identical MyISAM tables *************************** 5. row *************************** Engine: MRG_MYISAM Support: YES Comment: Alias for MERGE *************************** 6. row *************************** Engine: ISAM Support: NO Comment: Obsolete storage engine, now replaced by MyISAM *************************** 7. row *************************** Engine: MRG_ISAM Support: NO Comment: Obsolete storage engine, now replaced by MERGE *************************** 8. row *************************** Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keys *************************** 9. row *************************** Engine: INNOBASE Support: YES Comment: Alias for INNODB *************************** 10. row *************************** Engine: BDB Support: YES Comment: Supports transactions and page-level locking *************************** 11. row *************************** Engine: BERKELEYDB Support: YES Comment: Alias for BDB *************************** 12. row *************************** Engine: NDBCLUSTER Support: YES Comment: Clustered, fault-tolerant, memory-based tables *************************** 13. row *************************** Engine: NDB Support: YES Comment: Alias for NDBCLUSTER *************************** 14. row *************************** Engine: EXAMPLE Support: YES Comment: Example storage engine *************************** 15. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine
A Support value indicates whether the particular storage engine is supported, and which is the default engine. For example, if the server is started with the --default-table-type=InnoDB option, then the Support value for the InnoDB row will have the value DEFAULT.
SHOW ERRORS [LIMIT [offset,] row_count] SHOW COUNT(*) ERRORS
This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors. SHOW ERRORS is available as of MySQL 4.1.0.
The LIMIT clause has the same syntax as for the SELECT statement. See SELECT.
The SHOW COUNT(*) ERRORS statement displays the number of errors. You can also retrieve this number from the error_count variable:
SHOW COUNT(*) ERRORS; SELECT @@error_count;
For more information, see SHOW WARNINGS.
SHOW GRANTS FOR user
This statement lists the GRANT statements that must be issued to duplicate the privileges for a MySQL user account.
mysql> SHOW GRANTS FOR 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+
As of MySQL 4.1.2, to list privileges for the current session, you can use any of the following statements:
SHOW GRANTS; SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS FOR CURRENT_USER();
Before MySQL 4.1.2, you can find out what user the session was authenticated as by selecting the value of the CURRENT_USER() function (new in MySQL 4.0.6). Then use that value in the SHOW GRANTS statement. See CURRENT_USER().
SHOW GRANTS is available as of MySQL 3.23.4.
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INDEX returns table index information in a format that resembles the SQLStatistics call in ODBC.
SHOW INDEX returns the following fields:
The name of the table.
0 if the index can't contain duplicates, 1 if it can.
The name of the index.
The column sequence number in the index, starting with 1.
The column name.
How the column is sorted in the index. In MySQL, this can have values ‘A’ (Ascending) or NULL (Not sorted).
The number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so it's not necessarily accurate for small tables. The higher the cardinality, the greater the chance that MySQL will use the index when doing joins.
The number of indexed characters if the column is only partly indexed. NULL if the entire column is indexed.
Indicates how the key is packed. NULL if it is not.
Contains YES if the column may contain NULL, '' if not.
The index method used (BTREE, FULLTEXT, HASH, RTREE).
Various remarks. Before MySQL 4.0.2 when the Index_type column was added, Comment indicates whether an index is FULLTEXT.
The Packed and Comment columns were added in MySQL 3.23.0. The Null and Index_type columns were added in MySQL 4.0.2.
You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. These two statements are equivalent:
mysql> SHOW INDEX FROM mytable FROM mydb; mysql> SHOW INDEX FROM mydb.mytable;
SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's indexes with the mysqlshow -k db_name tbl_name command.
SHOW INNODB STATUS
This statement shows extensive information about the state of the InnoDB storage engine.
SHOW [BDB] LOGS
SHOW LOGS displays status information about existing log files. It was implemented in MySQL 3.23.29. Currently, it displays only information about Berkeley DB log files, so an alias for it (available as of MySQL 4.1.1) is SHOW BDB LOGS.
SHOW LOGS returns the following fields:
The full path to the log file.
The log file type (BDB for Berkeley DB log files).
The status of the log file (FREE if the file can be removed, or IN USE if the file is needed by the transaction subsystem)
SHOW PRIVILEGES
SHOW PRIVILEGES shows the list of system privileges that the underlying MySQL server supports. This statement is implemented as of MySQL 4.1.0.
mysql> SHOW PRIVILEGES\G *************************** 1. row *************************** Privilege: Select Context: Tables Comment: To retrieve rows from table *************************** 2. row *************************** Privilege: Insert Context: Tables Comment: To insert data into tables *************************** 3. row *************************** Privilege: Update Context: Tables Comment: To update existing rows *************************** 4. row *************************** Privilege: Delete Context: Tables Comment: To delete existing rows *************************** 5. row *************************** Privilege: Index Context: Tables Comment: To create or drop indexes *************************** 6. row *************************** Privilege: Alter Context: Tables Comment: To alter the table *************************** 7. row *************************** Privilege: Create Context: Databases,Tables,Indexes Comment: To create new databases and tables *************************** 8. row *************************** Privilege: Drop Context: Databases,Tables Comment: To drop databases and tables *************************** 9. row *************************** Privilege: Grant Context: Databases,Tables Comment: To give to other users those privileges you possess *************************** 10. row *************************** Privilege: References Context: Databases,Tables Comment: To have references on tables *************************** 11. row *************************** Privilege: Reload Context: Server Admin Comment: To reload or refresh tables, logs and privileges *************************** 12. row *************************** Privilege: Shutdown Context: Server Admin Comment: To shutdown the server *************************** 13. row *************************** Privilege: Process Context: Server Admin Comment: To view the plain text of currently executing queries *************************** 14. row *************************** Privilege: File Context: File access on server Comment: To read and write files on the server
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist statement. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See KILL. If you don't use the FULL keyword, only the first 100 characters of each query are shown.
Starting from MySQL 4.0.12, the statement reports the hostname for TCP/IP connections in host_name:client_port format to make it easier to determine which client is doing what.
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).
Some states commonly seen in the output from SHOW PROCESSLIST:
The thread is performing (automatic) checking of the table.
Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, then you should verify that you don't have a full disk and that the disk is not in very heavy use.
Slave connecting to master.
The temporary result set was larger than tmp_table_size and the thread is now changing the temporary table from in-memory to disk-based format to save memory.
The thread is creating a temporary table to hold a part of the result for the query.
The server is executing the first part of a multiple-table delete and deleting only from the first table.
The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.
The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
Someone has sent a kill to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.
The query is locked by another query.
The thread is processing rows for a SELECT statement and also is sending data to the client.
The thread is doing a sort to satisfy a GROUP BY.
The thread is doing a sort to satisfy a ORDER BY.
The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished.
The query was using SELECT DISTINCT in such a way that MySQL couldn't optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.
The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is now trying to reopen it.
The repair code is using sorting to create indexes.
The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.
The thread is waiting for the client to send a new statement to it.
The thread is waiting to get an external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the --skip-external-locking option.
The INSERT DELAYED handler is trying to get a lock for the table to insert rows.
The thread is searching for rows to update and updating them.
The thread is waiting on a GET_LOCK().
The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to be able to reopen the table, it must wait until all other threads have closed the table in question.
This notification happens if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
The INSERT DELAYED handler has processed all pending inserts and is waiting for new ones.
Most states correspond to very quick operations. If a thread stays in any of these states for many seconds, there might be a problem that needs to be investigated.
There are some other states that are not mentioned in the preceding list, but many of them are useful only for finding bugs in the server.
SHOW STATUS [LIKE 'pattern']
SHOW STATUS provides server status information. This information also can be obtained using the mysqladmin extended-status command.
Partial output is shown here. The list of variables and their values may be different for your server. The meaning of each variable is given in See the section called “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 those variables 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 | +--------------------+----------+
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW TABLE STATUS (new in MySQL 3.23) works likes SHOW TABLE, but provides a lot of information about each table. You can also get this list using the mysqlshow --status db_name command.
SHOW TABLE STATUS returns the following fields:
The name of the table.
The storage engine for the table. Before MySQL 4.1.2, this value is labeled as Type. See Chapter 15, MySQL Storage Engines and Table Types.
The version number of the table's .frm file.
The row storage format (Fixed, Dynamic, or Compressed).
The number of rows. Some storage engines, such as MyISAM and ISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation.
The average row length.
The length of the data file.
The maximum length of the data file. For fixed-row formats, this is the maximum number of rows in the table. For dynamic-row formats, this is the total number of data bytes that can be stored in the table, given the data pointer size used.
The length of the index file.
The number of allocated but unused bytes.
The next AUTO_INCREMENT value.
When the table was created.
When the data file was last updated.
When the table was last checked.
The table's character set and collation. (New in 4.1.1)
The live checksum value (if any). (New in 4.1.1)
Extra options used with CREATE TABLE.
The comment used when creating the table (or some information why MySQL couldn't access the table information).
In the table comment, InnoDB tables will report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the freespace is for just that table.
For MEMORY (HEAP) 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.
SHOW [FULL|OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command.
Before MySQL 5.0.1, the output from SHOW TABLES contains a single column of table names. Beginning with MySQL 5.0.1, also lists the views in the database. As of MySQL 5.0.2, the FULL modifier is supported such that SHOW FULL TABLES displays a second output column. Values in the second column are BASE TABLE for a table and VIEW for a view.
Note: If you have no privileges for a table, the table will not show up in the output from SHOW TABLES or mysqlshow db_name.
SHOW OPEN TABLES lists the tables that are currently open in the table cache. See the section called “How MySQL Opens and Closes Tables”. The Comment field in the output tells how many times the table is cached and in_use. OPEN can be used from MySQL 3.23.33 on.
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW VARIABLES shows the values of some MySQL system variables. This information also can be obtained using the mysqladmin variables command.
The GLOBAL and SESSION options are new in MySQL 4.0.3. With GLOBAL, you will get the values that will be used for new connections to MySQL. With SESSION, you will get the values that are in effect for the current connection. If you use neither option, the default SESSION. LOCAL is a synonym for SESSION.
If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts or at runtime with the SET statement. See the section called “mysqld Command-Line Options” and SET.
Partial output is shown here. The list of variables and their values may be different for your server. The meaning of each variable is given in See the section called “Server System Variables”. Information about tuning them is provided in the section called “Tuning Server Parameters”.
mysql> SHOW VARIABLES; +---------------------------------+------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------| | back_log | 50 | | basedir | /usr/local/mysql | | bdb_cache_size | 8388572 | | bdb_log_buffer_size | 32768 | | bdb_home | /usr/local/mysql | ... | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_relay_log_size | 0 | | max_sort_length | 1024 | ... | timezone | EEST | | tmp_table_size | 33554432 | | tmpdir | /tmp/:/mnt/hd2/tmp/ | | version | 4.0.4-beta | | wait_timeout | 28800 | +---------------------------------+------------------------------+
With a LIKE clause, the statement displays only those variables that match the pattern:
mysql> SHOW VARIABLES LIKE 'have%'; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | have_bdb | YES | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink | DISABLED | | have_openssl | YES | | have_query_cache | YES | +--------------------+----------+
SHOW WARNINGS [LIMIT [offset,] row_count] SHOW COUNT(*) WARNINGS
SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages, or nothing if the last statement that used a table generated no messages. This statement is implemented as of MySQL 4.1.0. A related statement, SHOW ERRORS, shows only the errors. See SHOW ERRORS.
The list of messages is reset for each new statement that uses a table.
The SHOW COUNT(*) WARNINGS statement displays the total number of errors, warnings, and notes. You can also retrieve this number from the warning_count variable:
SHOW COUNT(*) WARNINGS; SELECT @@warning_count;
The value of warning_count might be greater than the number of messages displayed by SHOW WARNINGS if the max_error_count system variable is set low enough that not all messages are stored. An example shown later in this section demonstrates how this can happen.
The LIMIT clause has the same syntax as for the SELECT statement. See SELECT.
The MySQL server sends back the total number of errors, warnings, and notes resulting from the last statement. If you are using the C API, this value can be obtained by calling mysql_warning_count(). See mysql_warning_count().
Note that the framework for warnings was added in MySQL 4.1.0, at which point many statements did not generate warnings. In 4.1.1, the situation is much improved, with warnings generated for statements such as LOAD DATA INFILE and DML statements such as INSERT, UPDATE, CREATE TABLE, and ALTER TABLE.
The following DROP TABLE statement results in a note:
mysql> DROP TABLE IF EXISTS no_such_table; mysql> SHOW WARNINGS; +-------+------+-------------------------------+ | Level | Code | Message | +-------+------+-------------------------------+ | Note | 1051 | Unknown table 'no_such_table' | +-------+------+-------------------------------+
Here is a simple example that shows a syntax warning for CREATE TABLE and conversion warnings for INSERT:
mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4)) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: 'TYPE=storage_engine' is deprecated, use
'ENGINE=storage_engine' instead
1 row in set (0.00 sec)
mysql> INSERT INTO t1 VALUES(10,'mysql'),(NULL,'test'),
-> (300,'open source');
Query OK, 3 rows affected, 4 warnings (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 4
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1265
Message: Data truncated for column 'b' at row 1
*************************** 2. row ***************************
Level: Warning
Code: 1263
Message: Data truncated, NULL supplied to NOT NULL column 'a' at row 2
*************************** 3. row ***************************
Level: Warning
Code: 1264
Message: Data truncated, out of range for column 'a' at row 3
*************************** 4. row ***************************
Level: Warning
Code: 1265
Message: Data truncated for column 'b' at row 3
4 rows in set (0.00 sec)
The maximum number of error, warning, and note messages to store is controlled by the max_error_count system variable. By default, its value is 64. To change the number of messages you want stored, change the value of max_error_count. In the following example, the ALTER TABLE statement produces three warning messages, but only one is stored because max_error_count has been set to 1:
mysql> SHOW VARIABLES LIKE 'max_error_count'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_error_count | 64 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> SET max_error_count=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t1 MODIFY b CHAR; Query OK, 3 rows affected, 3 warnings (0.00 sec) Records: 3 Duplicates: 0 Warnings: 3 mysql> SELECT @@warning_count; +-----------------+ | @@warning_count | +-----------------+ | 3 | +-----------------+ 1 row in set (0.01 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1263 | Data truncated for column 'b' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec)
To disable warnings, set max_error_count to 0. In this case, warning_count still indicates how many warnings have occurred, but none of the messages are stored.
CACHE INDEX tbl_index_list [, tbl_index_list] ... IN key_cache_name tbl_index_list: tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]
The CACHE INDEX statement assigns table indexes to a specific key cache. It is used only for MyISAM tables.
The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named hot_cache:
mysql> CACHE INDEX t1, t2, t3 IN hot_cache; +---------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+--------------------+----------+----------+ | test.t1 | assign_to_keycache | status | OK | | test.t2 | assign_to_keycache | status | OK | | test.t3 | assign_to_keycache | status | OK | +---------+--------------------+----------+----------+
The syntax of CACHE INDEX allows you to specify that only particular indexes from a table should be assigned to the cache. However, 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 the section called “Structured System Variables”.
A key cache must exist before you can assign indexes to it:
mysql> CACHE INDEX t1 in non_existent_cache; ERROR 1283 (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 what client issues the queries.
CACHE INDEX was added in MySQL 4.1.1.
FLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [, flush_option] ...
You should use the FLUSH statement if you want to clear some of the internal caches MySQL uses. To execute FLUSH, you must have the RELOAD privilege. flush_option can be any of the following:
Empties the host cache tables. You should flush the host tables if some of your hosts change IP number or if you get the error message Host ... is blocked. When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host tables allows the host to attempt to connect again. See the section called “Host 'host_name' is blocked”. You can start mysqld with --max_connect_errors=999999999 to avoid this error message.
Reloads the DES keys from the file that was specified with the --des-key-file option at server startup time.
Closes and reopens all log files. If you have specified an update log file or a binary log file without an extension, the extension number of the log file will be incremented by one relative to the previous file. If you have used an extension in the file name, MySQL will close and reopen the update log or binary log file. See the section called “The Update Log”. On Unix, this is the same thing as sending a SIGHUP signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld will ignore SIGHUP and SIGQUIT).
Reloads the privileges from the grant tables in the mysql database.
Defragment the query cache to better utilize its memory. This statement does not remove any queries from the cache, unlike RESET QUERY CACHE.
Resets most status variables to zero. This is something you should use only when debugging a query. See the section called “How to Report Bugs or Problems”.
When no tables are named, closes all open tables and forces all tables in use to be closed. This also flushes the query cache. With one or more table names, flushes only the given tables. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.
Closes all open tables and locks all tables for all databases with a read lock until you execute UNLOCK TABLES. This is very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.
Resets all user resources to zero. This enables clients that have reached their hourly connection, query, or update limits to resume activity. See GRANT.
Before MySQL 4.1.1, FLUSH statements are not written to the binary log. As of MySQL 4.1.1, they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case because they would cause problems if replicated to a slave.
You can also access some of these statements with the mysqladmin utility, using the flush-hosts, flush-logs, flush-privileges, flush-status, or flush-tables commands.
Take also a look at the RESET statement used with replication. See RESET.
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.
As of MySQL 5.0.0, KILL allows the optional CONNECTION or QUERY modifiers:
KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given thread_id.
KILL QUERY terminates the statement that the connection currently is executing, but leaves the connection intact.
If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.
You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads.
Note: You currently cannot use KILL with the Embedded MySQL Server library, because the embedded server merely runs inside the threads of the host application, it does not create connection threads of its own. When you do a 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:
In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.
During ALTER TABLE, the kill flag is checked before each block of rows are read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted.
During UPDATE or DELETE, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. Note that if you are not using transactions, the changes will not be rolled back!
GET_LOCK() will abort and return NULL.
An INSERT DELAYED thread will quickly flush (insert) all rows it has in memory and terminate.
If the thread is in the table lock handler (state: Locked), the table lock will be quickly aborted.
If the thread is waiting for free disk space in a write call, the write is aborted with a "disk full" error message.
Some threads might refuse to be killed. For example, REPAIR TABLE, CHECK TABLE, and OPTIMIZE TABLE cannot be killed before MySQL 4.1 and run to completion. This now is changed: REPAIR TABLE and OPTIMIZE TABLE can be killed as of MySQL 4.1.0, as can CHECK TABLE as of MySQL 4.1.3. However, killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that IS corrupted and will be unusable (reads and writes to it will fail) until you optimize or repair it again.
LOAD INDEX INTO CACHE
tbl_index_list [, tbl_index_list] ...
tbl_index_list:
tbl_name
[[INDEX|KEY] (index_name[, index_name] ...)]
[IGNORE LEAVES]
The LOAD INDEX INTO CACHE statement preloads a table index into the key cache to which it has been assigned by an explicit CACHE INDEX statement, or into the default key cache otherwise. LOAD INDEX INTO CACHE is used only for MyISAM tables.
The IGNORE LEAVES modifier causes only blocks for the non-leaf nodes of the index to be preloaded. The following statement preloads nodes (index blocks) of indexes of the tables t1 and t2:
mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES; +---------+--------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+--------------+----------+----------+ | test.t1 | preload_keys | status | OK | | test.t2 | preload_keys | status | OK | +---------+--------------+----------+----------+
This statement preloads all index blocks from t1. It preloads only blocks for the non-leaf nodes from t2.
The syntax of LOAD INDEX INTO CACHE allows you to specify that only particular indexes from a table should be preloaded. However, 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.
LOAD INDEX INTO CACHE was added in MySQL 4.1.1.
RESET reset_option [, reset_option] ...
The RESET statement is used to clear the state of various server operations. It also acts as a stronger version of the FLUSH statement. See FLUSH. To execute RESET, you must have the RELOAD privilege.
reset_option can be any of the following:
Deletes all binary logs listed in the index file, resets the binary log index file to be empty, and creates a new binary log file. Previously named FLUSH MASTER. See the section called “SQL Statements for Controlling Master Servers”.
Removes all query results from the query cache.
Makes the slave forget its replication position in the master binary logs. Previously named FLUSH SLAVE. See the section called “SQL Statements for Controlling Slave Servers”.