Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to get some clarification about the --flush-privileges option for mysqldump.

Here's the description of the option from the MySQL docs:

--flush-privileges    
Send a FLUSH PRIVILEGES statement to the server after dumping the mysql database.
This option should be used any time the dump contains the mysql database and any other
database that depends on the data in the mysql database for proper restoration. 

When it says that it sends the flush statement after dumping the database, I read that to mean that the data, schema, etc. is dumped into the backup file and then the flush statement is sent to the database that was just dumped (after it was dumped).

I was wondering what dumping the data, etc. did that required the privileges to be flushed, so I started searching for an explanation to be sure when and why to use it.

As I read various answers, it occurred to me that it would make sense if the flush statement was being included in the dump file. Then after the contents of the file was loaded into a database, the flush privileges statement was run to update the settings after the new info was imported.

So, how does it work?
A) Flush the source database after dumping the data to a file? If so, why is this necessary?
B) Flush the destination database after importing the contents of the dump file?
C) Something other than the possibilities I've described?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

The documentation is misleading. I read it exactly the same way you do, which isn't what the utility does.

Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema...

--
-- Flush Grant Tables
--

/*! FLUSH PRIVILEGES */;

...which of course causes the server where the dump is being restored to re-read the potentially-changed grant tables.

And, that's all it does.

Confirmation of this can be found in the source code for mysqldump "10.13" (the version bundled with MySQL Server 5.5.30):

if (flush_privileges && using_mysql_db == 0)
{
  fprintf(md_result_file,"\n--\n-- Flush Grant Tables \n--\n");
  fprintf(md_result_file,"\n/*! FLUSH PRIVILEGES */;\n");
}

The using_mysql_db == 0 comparison was confusing at first, but it turns out to be the return value from a string comparison function where "0" means "identical."

share|improve this answer
    
This is the very answer needed for this question. +1 !!! –  RolandoMySQLDBA Mar 4 '13 at 0:51
    
Great Answer, thanks for providing documentation and additional details. –  codewaggle Mar 4 '13 at 13:39

In light of Michael-sqlbot's answer, try to keep one more thing in perspective.

The only time using --flush-privileges is good for a mysqldump is when

  • you dump --all-databases (which includes the mysql schema)
  • you dump the mysql schema only.

What's even more superficial about the --flush-privileges option for mysqldump is that you can just append the command to the output yourself like this:

mysqldump --all-databases --routines --triggers > MySQLData.sql
echo "FLUSH PRIVILEGES;" >> MySQLData.sql

This assumes you cared to remember this. That's why the --flush-privileges option was added to mysqldump as a reminder.

share|improve this answer
    
Good note about it being needed only if the mysql schema is dumped. It was hinted at by the using_mysql_db == 0 check that @Michael-sqlbot explained. –  codewaggle Mar 4 '13 at 13:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.