MySQL in a Nutshell [42]
Name
DROP USER
Synopsis
DROP USER 'user'@'host'
Use this statement to delete a user account for the MySQL server. As of version 5.0.2 of MySQL, this statement will delete the user account and its privileges from all grant tables. The username is given within quotes, followed by the at sign (@) and the host IP address or hostname within quotes. This statement requires a CREATE USER privilege or DELETE privilege for the mysql database, which contains user account information and privileges. Dropping a user account does not affect current sessions for the user account. It will take effect when any sessions opened by the user terminate. Use the KILL statement (explained in Chapter 7) to terminate an open client session for a user that has been dropped.
Some users may have more than one user account (i.e., user and host combinations). You should check the server’s mysql.user table to be sure:
SELECT User,Host
FROM mysql.user
WHERE User LIKE 'paola';
+-------+--------------+
| User | Host |
+-------+--------------+
| paola | localhost |
| paola | caporale.com |
+-------+--------------+
DROP USER 'paola'@'localhost',
'paola'@'caporale.com';
Prior to version 5.0.2 of MySQL, the DROP USER statement won’t delete a user that has any privileges set to 'Y'. To eliminate the user account’s privileges, issue the REVOKE statement before using DROP USER:
REVOKE ALL ON *.* FROM 'paola'@'localhost';
DROP USER 'paola'@'localhost';
The ALL option is used to ensure revocation of all privileges. The *.* covers all tables in all databases. Prior to version 4.1.1 of MySQL, you would have to issue the following instead of a DROP USER statement:
DELETE FROM mysql.user
WHERE User='paola' AND Host='localhost';
FLUSH PRIVILEGES;
Notice that the FLUSH PRIVILEGES statement is necessary for the preceding DELETE statement to take effect immediately. It’s not necessary after the DROP USER statement, though.
Name
FLUSH
Synopsis
FLUSH [LOCAL|NO_WRITE_TO_BINLOG] option[, ...]
Options:
DES_KEY_FILE, HOSTS, LOGS, MASTER, PRIVILEGES, QUERY_CACHE,
STATUS, TABLE, TABLES, TABLES WITHOUT READ LOCK, USER_RESOURCES
Use this statement to clear and reload temporary caches in MySQL. It requires RELOAD privileges. To prevent this statement from writing to the binary log file, the NO_WRITE_TO_BINLOG flag or its LOCAL alias may be given. A particular cache to flush may be given as an option. Multiple options (see Table 4-1) may be given in a comma-separated list.
As of version 5.1 of MySQL, FLUSH cannot be used in stored functions and triggers, but can be used in stored procedures. As an alternative to the FLUSH statement, you can use the mysqladmin command (see Chapter 16).
Table 4-1. Options for FLUSH statement
Option
Explanation
DES_KEY_FILE
Reloads the DES encryption file, which is given with the --des-key-file option at startup or in the options file.
HOSTS
Clears the hosts cache, which is used to minimize host/IP address lookups. The hosts cache may need to be flushed if a host has been blocked from accessing the server.
LOGS
Used to close all of the log files and reopen them. If the server has binary logging enabled, it will change the binary log file to the next in numeric sequence. If the error log was enabled, it will rename the error log to the same name, but with the ending -old, and start a new error log. This option is not logged.
MASTER
This option is not logged and has been deprecated. Use the RESET MASTER statement instead.
PRIVILEGES
Reloads the grant tables for user privileges. This is necessary if the user table in the mysql database has been modified manually, without the GRANT statement.
QUERY CACHE
Instructs the server