MySQL in a Nutshell [43]
SLAVE
This option is not logged and has been deprecated. Use the RESET SLAVE statement instead.
STATUS
Resets the session values and counters for key caches to 0. The current thread’s session status variables are set to those of the global variables. The max_used_conections variable is set to the number of sessions open at the time.
TABLE[ table, ...]
Followed by one or more table names, this option forces the given tables to be closed. This will terminate any active queries on the given tables. Specified without any tables, the option has the same effect as TABLES.
TABLES
Causes all tables to be closed, all queries to be terminated, and the query cache to be flushed. This is the same as TABLE with no table name.
TABLES WITH READ LOCK
Closes all tables and locks them with a global read lock. This will allow users to view the data, but not to update it or insert records. The lock will remain in place until the UNLOCK TABLES statement is executed. This option is not logged.
USER_RESOURCES
Resets all user resource values that are calculated on an hourly basis. These are the values for the columns max_questions, max_updates, and max_connections in the user table of the mysql database. Use this FLUSH option when users have been blocked because they exceed hourly limits. If these columns are missing, see Chapter 16 for the explanation of mysql_fix_privilege_tables.
Name
GRANT
Synopsis
GRANT privilege[,...] [(column[,...])][, ...]
ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}
TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]
[REQUIRE NONE |
[{SSL|X509}] [CIPHER 'cipher' [AND]]
[ISSUER 'issue' [AND]]
[SUBJECT 'subject']]
[WITH [GRANT OPTION |
MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count |
MAX_USER_CONNECTIONS count] ...]
This statement may be used to create new MySQL users, but its primary use is for granting user privileges. Privileges can be global (apply to all databases on the server), database-specific, table-specific, or column-specific. Users can now also be limited by functions and procedures. Additionally, users can be limited by number of connections or by a maximum of resources per hour.
The privileges to grant to a user are listed immediately after the GRANT keyword in a comma-separated list. To restrict a user to specific columns in a table, list those columns in a comma-separated list within parentheses. This is then followed by the ON clause in which the privileges granted may be limited to a database, table, function, or procedure. To limit the privileges to a function, use the FUNCTION keyword; to limit them to a procedure, use the PROCEDURE keyword.
For tables, the keyword TABLE is optional and the default. You can then specify the database to which the privileges relate in quotes, followed by a period (.) and the name of the table, function, or procedure in quotes. You may also use the asterisk wildcard (*) to specify all databases or all tables, functions, or procedures offered by the database.
In the TO clause, give the username (in quotes) and the IP address or host (also in quotes) for which the user account privileges are permitted, separated by an at sign (@). To provide the password for the user account, add the IDENTIFIED BY clause, followed by the user’s password in plain text and enclosed in quotes. To provide the password in encrypted hash form, add the keyword PASSWORD just before the password given. You can use the WITH clause to grant the GRANT OPTION privilege to a user so that that user may execute this statement. The GRANT statement with the IDENTIFIED BY clause can be used to change a password for an existing user.
For an explanation of how to restrict user accounts based on types of connections, see the next section of this statement (GRANT: Type of connection restrictions”). For information on how to restrict user