Thesqldump: flush-privileges option

backupMySQLmysqldumppermissions

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?

Best Answer

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."

Related Solutions

Mysql – How to log verbose output from thesqldump

Your 1st mysqldump makes table structures and INSERTs and puts it in dump.sql.

Your 2nd dump is a remote dump that is piped straight into mysql in localhost.

If you are trying to catch any output based on errors, try this:

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename 2> output.log | mysql --user=username --password=p@ssw0rd -h localhost localdatabase

Using 2> will catch any error-based output (aka stderr). The mysqldump should still pipe normal console output (aka stdout) to the other mysql session and load the data as intended.

EXAMPLE : I have a small database called sample on my PC.

I ran this:

C:\LWDBA>mysqldump -u... -p... --verbose sample 2>sample.txt > sample.sql

C:\LWDBA>type sample.txt
-- Connecting to localhost...
-- Retrieving table structure for table users...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from localhost...

C:\LWDBA>type sample.sql
-- MySQL dump 10.13  Distrib 5.5.12, for Win64 (x86)
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version       5.5.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `users_tbl_points` int(11) NOT NULL,
  `users_tbl_rank` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `users_tbl_points` (`users_tbl_points`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,785523,9),(2,443080,20),(3,858830,7),(4,964909,3),(5,248056,24),
(6,345553,21),(7,983596,2),(8,881325,6),(9,455836,19),(10,635204,16),(11,808514,8),
(12,136960,28),(13,259255,22),(14,885399,5),(15,649229,15),(16,589948,18),(17,2055,30),
(18,240429,25),(19,195981,26),(20,258620,23),(21,705158,12),(22,749931,11),(23,634182,17),
(24,921117,4),(25,703038,13),(26,751842,10),(27,650093,14),(28,994943,1),(29,24437,29),
(30,137355,27);
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-03-02 15:49:54

C:\LWDBA>

Give it a Try !!!

Thesqldump freezing on a specific table

Conjecture #1 : Single Transaction

The dump for the sys_data database has to create far more MVCC information to load and dump from ibdata1. Please Click Here to See the InnoDB Infrastructure Map and notice the section in ibdata1 that has 1023 undo logs.

When you were dumping an entire database, mysqldump will export the tables in alphabetical order. There can times that undo logs are populated with row data in the event of a crash and recovery as needed. Perhaps those logs are being loaded and unloaded from previous dumps. The undo space populated by the dump of previous tables (tables before tb_trade_376) may need some housecleaning performed while the dump of tb_trade_376 is in progress. This would be particularly true for a very busy server where INSERTs, UPDATEs, and DELETEs are being done to the previous tables.

Conjecture #2 : InnoDB Buffer Pool

If you are not writing anything to the database at all, another place to look would be the InnoDB Buffer Pool. Think about it: the data pages of every table being dumped would have to be loaded in the Buffer Pool simply because you are doing a SELECT. The SQL_NO_CACHE prevents the query results from entering the query cache, but does not prevent data movement in and out of the Buffer Pool. The data pages accessed from the previous tables have to be invalidated and overwritten in the Buffer Pool for each and every table prior to tb_trade_376.

Conjecture #3 : innodb_file_per_table

If innodb_file_per_table is disabled, then everything and its grandmother is in ibdata1. What would it contain? ibdata1 would have the following:

Data Pages for Every InnoDB Table
Index Pages for Every InnoDB Table
Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

InnoDB Architecture

This means that the Undo Logs are competing for space with the data and index pages of every table. This would cause ibdata1 to grow rapidly. If you have innodb_file_per_table disabled, you need to cleanup the InnoDB Infrastructure to keep InnoDB tables out of ibdata1.

Conjecture #4 : MyISAM Tables

How can MyISAM tables cause a problem? If any of the tables in the mysqldump are MyISAM and still receiving INSERTs, UPDATEs, and DELETEs during the database dump, it could possibly disable the checkpoint mechanism of the --single-transaction option midstream. This is plausible because MyISAM is a non-transactional storage engine. If any of the previous tables are MyISAM and are still receiving writes, all bets are off for all the tables in the dump to have the same point-in-time. Each table being dumped after a MyISAM table in encountered is basically on it own in a transactional sense.

SUMMARY

You may have one or more of these issues going on. Please compensate with one of the following:

Arrange to dump of each table into separate files
Setup MySQL Replication
- You have freedom to tune MySQL on the Slave
- You can stop the Slave and dump from the Slave. This will make point-in-time simple since a stopped slave means no more writes. YOu can start the slave once all dumps are done.
Deal with MyISAM
- Convert all MyISAM tables to InnoDB. Prior to MySQL 5.6, a MyISAM table cannot be converted to InnoDB if there are FULLTEXT indexes.
- If you cannot convert a MyISAM table, please stop writing to it during the dump
Do the ibdata1 cleanup
Use a larger buffer pool