MySQL in a Nutshell [30]
Name
RESET
Synopsis
RESET [QUERY CACHE|MASTER|SLAVE]
Use this statement to reset certain server settings and log files. The RELOAD privilege is required to use this statement. The QUERY CACHE option clears the cache containing SQL query results.
Use the MASTER option to reset a master used for replication. This statement must be executed from the master itself. It will start a new binary log file, as well as delete the binary log file names from the index file and delete the contents of the binary log index file. The SLAVE option is used to reset a slave used for replication and must be executed from the slave itself. It will start a new relay log file and delete any existing ones, as well as delete its notation of its position in the master’s binary log. See Chapter 8 on replication for more information on these two options.
Name
REVOKE
Synopsis
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user[, ...]
REVOKE privilege[,...] [(column[, ...])]
ON {[{database|*}.{table|*}] | *}
FROM 'user'@'host'[, ...]
Use this statement to revoke some or all privileges that were granted to a user with the GRANT statement. The first syntax is used to revoke all privileges from a user. Multiple users may be given in a comma-separated list. A list of users and their privileges are stored in the mysql database, in the user table in particular:
REVOKE ALL PRIVILEGES
ON *.*
FROM 'paola'@localhost';
To revoke only some privileges, use the second syntax structure, giving the specific privileges to be removed in a comma-separated list after the keyword REVOKE. For a list of privileges and their descriptions, see Table 4-2 under the description of the GRANT statement earlier in this chapter.
To revoke privileges for specific columns, list the columns within parentheses in a comma-separated list. Privileges that are granted based on columns are stored in the columns_priv table of the mysql database. Privileges may be revoked on a specific table for a specific database. To revoke privileges on all tables of a database, specify an asterisk as a wildcard for the table name. You can do the same for the database name to apply the statement to all databases. Table-specific privileges are stored in the tables_priv table, and database-specific privileges are stored in the db table.
Name
SET PASSWORD
Synopsis
SET PASSWORD [FOR 'user'@'host'] = PASSWORD('password')
Use this statement to change the password for a user account. The username and host must be given. The change of password will apply only to the given combination of username and host. It won’t apply to other hosts for the same user in the grant tables.
To get a list of user accounts on your server, enter the following SQL statement:
SELECT User, Host FROM mysql.user;
If the FOR clause is not given with the SET PASSWORD statement, the current user account is assumed. The PASSWORD( ) function will encrypt the password given.
This statement does not need to be followed by a FLUSH PRIVILEGES statement. It will automatically update the privileges cache for the new password. If you updated your server from a version before 4.1 to a new version, you may have problems changing a user account’s password and cause the user account’s password to become invalid. You may need to run the mysql_fix_privilege_tables utility to change the Password column in the user table in the mysql database. See Chapter 16 for more information on this utility.
Here is an example of changing a user account’s password:
SET PASSWORD FOR 'kenneth'@'localhost' = PASSWORD('his_password');
Name
SHOW GRANTS
Synopsis
SHOW GRANTS [FOR 'user'[@'host']]
This SQL statement displays the GRANT statement for a given user. If the FOR clause is not given, the current user account is assumed. If the username is given without reference to a particular host, the wildcard % is assumed. Otherwise, the username should be followed by the host as shown here:
SHOW GRANTS FOR 'russell'@'localhost'\G
*************************** 1. row ***************************
Grants for russell@localhost:
GRANT