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

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

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

DROP USER user [, user] ...

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

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

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

DROP USER user;

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

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

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

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

object_type =
    TABLE
  | FUNCTION
  | PROCEDURE

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

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

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

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

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

Privileges can be granted at several levels:

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

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

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

The REFERENCES privilege currently is unused.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The privileges for a column can be calculated as follows:

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

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

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

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

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

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

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

GRANT ... IDENTIFIED BY 'mypass';

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

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

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

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

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

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

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

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

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

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

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

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

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

See Section 5.9.4, “Limiting Account Resources”.

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

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

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

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

The AND keyword is optional between REQUIRE options.

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

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

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

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

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

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

The RENAME USER statement was added in MySQL 5.0.2.

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

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

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

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

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

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

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

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

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

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

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

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

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

That is equivalent to the following statements:

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

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

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

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

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

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

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

This statement requires SELECT and INSERT privileges for the table.

ANALYZE TABLE returns a result set with the following columns:

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

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

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

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

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

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

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

BACKUP TABLE returns a result set with the following columns:

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

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

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

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

CHECK TABLE returns a result set with the following columns:

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

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

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

Currently, FOR UPGRADE discovers these incompatibilities:

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

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

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

CHECK TABLE test_table FAST QUICK;

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

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

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

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

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

Some problems reported by CHECK TABLE cannot be corrected automatically:

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

CHECKSUM TABLE reports a table checksum.

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

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

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

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

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

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

This statement requires SELECT and INSERT privileges for the table.

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

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

For MyISAM tables, OPTIMIZE TABLE works as follows:

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

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

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

OPTIMIZE TABLE returns a result set with the following columns:

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

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

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

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

This statement requires SELECT and INSERT privileges for the table.

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

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

REPAIR TABLE returns a result set with the following columns:

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

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

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

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

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

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

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

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

RESTORE TABLE returns a result set with the following columns:

SHOW CHARACTER SET [LIKE 'pattern']

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

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

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

SHOW COLLATION [LIKE 'pattern']

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

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

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

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

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

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

The FULL keyword causes the outpu