Berikut beberapa perintah Mysql Flush yang biasa digunakan. Sebelum menggunakan perintah berikut, kita sudah harus masuk ke dalam mysql.
Flush Privileges
Ketika sudah melakukan perizinan ke user dan database tertentu (grant).
> flush privileges;
Flush Tables
Digunakan ketika akan menutup semua tabel yang sedang dibuka dan membersihkan cache untuk meng-efisienkan memory.
> flush tables;
Flush Hosts
Perintah ini menggunakan tabel host cache, jika jumlah koneksi maksimum telah tercapai, mysql tidak dapat membuat koneksi baru pada host tertentu. Perintah ini akan me-reset tabel tersebut dan mengizinkan koneksi ke host tertentu.
> flush hosts;
Flush Logs
Perintah ini menutup dan membuka kembali file log, jika file log cukup besar dan memakan waktu lama untuk membukanya, menjalankan perintah ini akan membuat file log kosong baru.
Actually, we need to perform flush-privileges operation to tell the server to reload the grant tables. This can be done by issuing FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command. FLUSH PRIVILEGES is really needed if we modify the grant tables directly using such as INSERT, UPDATE or DELETE, the changes have no effect on privileges checking until we either restart the server or tell it to reload the tables. But, Privileges assigned through GRANT choice don't want FLUSH PRIVILEGES to take effect - MySQL server cannotice these changes and reload the grant tables instantly.This statement triggers TiDB to reload the in-memory copy of privileges from the privilege tables. You should execute FLUSH PRIVILEGES after making manual edits to tables such as mysql.user. Executing this statement is not required after using privilege statements such as GRANT or REVOKE. Executing this statement requires the RELOAD privilege.
Synopsis
FlushStmtFLUSHNoWriteToBinLogAliasOptFlushOptionNoWriteToBinLogAliasOptNO_WRITE_TO_BINLOGLOCALFlushOptionPRIVILEGESSTATUSTIDBPLUGINSPluginNameListHOSTSLogTypeOptLOGSTableOrTablesTableNameListOptWithReadLockOpt
FlushStmt ::= 'FLUSH' NoWriteToBinLogAliasOpt FlushOption NoWriteToBinLogAliasOpt ::= ( 'NO_WRITE_TO_BINLOG' | 'LOCAL' )? FlushOption ::= 'PRIVILEGES' | 'STATUS' | 'TIDB' 'PLUGINS' PluginNameList | 'HOSTS' | LogTypeOpt 'LOGS' | TableOrTables TableNameListOpt WithReadLockOpt
Examples
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)MySQL compatibility
This statement is understood to be fully compatible with MySQL. Any compatibility differences should be reported via an issue on GitHub.
The syntax of the flush privileges command is given below. After executing the following command, the grant table will be reloaded in the memory.
FLUSH PRIVILEGES;
Uses of MySQL Flush Privileges:
You have to create a new MySQL user to know the uses of the FLUSH PRIVILEGES command.
Connect with the MySQL server:
Open the terminal and connect with the MySQL server by executing the following command.
$ sudo mysql -u root
Create a new user:
Run the following command to create a new user with the username, ‘fahmida’ and the password ‘secret.’
CREATE USER 'fahmida'@'localhost' IDENTIFIED BY 'secret';
When a new user is created in MySQL, the default permissions are set for the new users. The user table of the MySQL database contains the permission-related data of all users. Run the following command to check the permission for the newly created user, ‘fahmida.’
SELECT * FROM mysql.user WHERE user='fahmida' \G;
The above command will generate a long list of information. Some portion of the output has shown in the following image. The output shows that no permissions are granted for the user, ‘fahmida’ by default.
Update the user’s permission:
The specific permission for any user can be granted by using the UPDATE query or by using the GRANT command. If the permission is changed by using the UPDATE query, then the FLUSH PRIVILEGE command is required to make the change effective. If the permission is changed by using the GRANT command, then the FLUSH PRIVILEGE command is not required to use to make the change effective.
Run the following UPDATE query to set the SELECT permission for the user, ‘fahmida’ for all tables of all databases.
UPDATE mysql.user set Select_priv='Y' WHERE user='fahmida';
To make the update operation effective, you have to run the FLUSH PRIVILEGE command.
FLUSH PRIVILEGES;
Rerun the following command after setting the SELECT permission for the user, ‘fahmida.’
SELECT * FROM mysql.user WHERE user='fahmida' \G;
Output:
The following output shows that SELECT privilege has been granted to the user.
Run the following GRANT command to set the INSERT and DELETE permissions for the user, ‘fahmida.’ You don’t need to use the FLUSH PRIVILEGES command for this command.
GRANT INSERT, DELETE ON *.* TO fahmida@'localhost';
Run the following SELECT command again after setting the INSERT and DELETE permissions for the user, ‘fahmida.’
SELECT * FROM mysql.user WHERE user='fahmida' \G;
Output:
The following output shows that INSERT and DELETE privileges have been granted to the user.
Change the user’s password:
Many commands exist in MySQL to change the user’s password. SET, ALTER, and UPDATE statements. The FLUSH PRIVILEGE command will be required to use if the password is changed by using the UPDATE query, and the FLUSH PRIVILEGE command will not be required if the password is changed by SET or ALTER commands. The uses of ALTER and UPDATE statements have been shown here.
Run the following command to check the value of the authentication_string value before changing the password.
SELECT authentication_string FROM mysql.user WHERE user='fahmida';
Output:
The following output shows the authentication_string value for the password, ‘secret.’
Run the following ALTER command to change the password for the user, ‘fahmida.’
ALTER USER 'fahmida'@'localhost' IDENTIFIED BY 'secret2';
You don’t need to use the FLUSH PRIVILEGES command to activate the password changed by the ALTER statement. Run the following command to check the authentication_string value after changing the password.
SELECT authentication_string FROM mysql.user WHERE user='fahmida';
Output:
The following output shows the new authentication_string value after changing the password.
Run the following UPDATE statement to set the NULL password by using authentication_string for the user, ‘fahmida.’
UPDATE mysql.user SET authentication_string = NULL WHERE user='fahmida';
The FLUSH PRIVILEGES command is required to use here to make the change effective for the UPDATE statement.
FLUSH PRIVILEGES;
Run the following command to check the authentication_string value after changing the password.
SELECT authentication_string FROM mysql.user WHERE user='fahmida';
Output:
The following output shows the new authentication_string value after changing the password.
Conclusion:
The uses of the FLUSH PRIVILEGES command after granting privileges and changing the password for the user have been shown in this tutorial by creating a new user account. Using the FLUSH Privilege command will be cleared after reading this tutorial.