Emitting FLUSH PRIVILEGES Statement in mysqldump

The mysqldump command in MySQL is used to create logical backups of databases, producing SQL statements that can recreate the databases’ structure and content. One of the options available is --flush-privileges, which instructs mysqldump to include a FLUSH PRIVILEGES statement in the dump file. This statement reloads the grant tables and applies the new privileges, ensuring that any changes made to user privileges during the backup are immediately applied.

Let’s explore some examples of using mysqldump with the --flush-privileges option:

Example 1: Dump a single database with flushing privileges.

mysqldump --flush-privileges -u username -p database_name > database_dump.sql

This command dumps the database_name into database_dump.sql and includes a FLUSH PRIVILEGES statement at the end of the dump file to apply any privilege changes.

Example 2: Dump all databases with flushing privileges.

mysqldump --flush-privileges -u username -p --all-databases > all_databases_dump.sql

Here, mysqldump dumps all databases into all_databases_dump.sql and includes a FLUSH PRIVILEGES statement at the end of the dump file.

Example 3: Dump a specific table with flushing privileges.

mysqldump --flush-privileges -u username -p database_name table_name > table_dump.sql

This command dumps only the specified table_name from database_name into table_dump.sql, with a FLUSH PRIVILEGES statement included.

Example 4: Dump a database and compress output with flushing privileges.

mysqldump --flush-privileges -u username -p --opt database_name | gzip > database_dump.sql.gz

This command dumps database_name and compresses the output into database_dump.sql.gz, ensuring the FLUSH PRIVILEGES statement is also included.

Verification: To verify that the FLUSH PRIVILEGES statement is included in the dump file, you can open the generated SQL dump file (.sql or .sql.gz) using a text editor or command-line viewer. Search for the FLUSH PRIVILEGES statement towards the end of the file. Its presence confirms that the command executed correctly and the privileges have been refreshed.

Also check similar articles.

Flushing Logs Before Dumping with mysqldump
Escaping Fields in mysqldump Output
Optionally Enclosing Fields in mysqldump Output
Enclosing Fields in mysqldump Output
Setting Field Terminator in mysqldump Output