Unable to get a mysqldump when I use --flush-log option

[Subodh Konhor]

I am trying to use mysqldump to take regular backup of mysql database. We have migrated to centos 6.6 but still use mysql5.0 for our application.

I am trying to take a dump by issuing command

MYSQLDUMP --user=root --password='****' --host=localhost --verbose --master-data=2 --flush-logs --all-databases > $DUMPFILE

It runs for 8 hours but the statements on the dump file are

-- MySQL dump 10.11
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.0.92-50-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 */;
/*!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 2015-11-07 9:00:01


I have a total of 17 database but only get information_schema database dumped.

I can see that whenever I issue the dump command, a sleep is introduced for 8 hrs and when it finishes the dump command also exits.

Want to understand if the dump command has any issue or the options given while taking the dump sends it into sleep.

The above command works well and dump generated on Red Hat Enterprise Linux Server release 5.3

Suspecting some tables might be giving some issues therefore I tried only to dump only database test

[root@bugzilla-prod-2 dump]# time /usr/bin/mysqldump --user=root --password='****' --host=localhost --verbose --flush-logs --master-data=2 test > $(date +%Y%m%d%H%M%S).sql
-- Connecting to localhost...

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 3 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 7 | root | localhost | NULL | Sleep | 8 | | NULL |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)


mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
151112 2:31:31 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
----------
BACKGROUND THREAD
----------
fsync callers: 28 buffer pool, 0 other, 0 checkpoint, 7 log aio, 5 log sync, 0 archive
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9, signal count 9
Mutex spin waits 2, rounds 40, OS waits 2
RW-shared spins 7, OS waits 7; RW-excl spins 0, OS waits 0
Spin rounds per wait: 20.00 mutex, 20.00 RW-shared, 0.00 RW-excl
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (write thread)
I/O thread 11 state: waiting for i/o request (write thread)
I/O thread 12 state: waiting for i/o request (write thread)
I/O thread 13 state: waiting for i/o request (write thread)
I/O thread 14 state: waiting for i/o request (write thread)
I/O thread 15 state: waiting for i/o request (write thread)
I/O thread 16 state: waiting for i/o request (write thread)
I/O thread 17 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1374 OS file reads, 3801 OS file writes, 40 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
0 inserts, 0 merged recs, 0 merges
Hash table size 8850487, used cells 4197, node heap has 7 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 23 4248323875
Log flushed up to 23 4248323875
Last checkpoint at 23 4248323875
Max checkpoint age 1303413351
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
2655 log i/o's done, 0.00 log i/o's/second, 5 syncs, 10 checkpoints
log sync callers: 1326 buffer pool, background 17 sync and 20 async, 0 internal, checkpoint 10 sync and 0 async, 0 archive, commit 0 sync and 2638 async
log sync syncers: 1 buffer pool, background 3 sync and 0 async, 0 internal, checkpoint 1 sync and 0 async, 0 archive, commit 0 sync and 0 async
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 0; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 70922792 (70803896 + 118896)
Page hash 4426024
Dictionary cache 53171576 (53104856 + 66720)
File system 83520 (82672 + 848)
Lock system 10626072 (10625704 + 368)
Recovery system 0 (0 + 0)
Threads 407256 (406936 + 320)
innodb_io_pattern 0 (0 + 0)
Buffer pool size 262144
Buffer pool size, bytes 4294967296
Free buffers 260674
Database pages 1463
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1412, created 51, written 1326
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 3831, id 140363041879808, state: waiting for server activity
Number of rows inserted 1320, updated 0, deleted 1318, read 1993
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
------------
TRANSACTIONS
------------
Trx id counter 0 124061
Purge done for trx's n:o < 0 124060 undo n:o < 0 0
History list length 5
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3831, OS thread id 140368004228864
MySQL thread id 3, query id 5318 localhost root
SHOW ENGINE INNODB STATUS
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

I need to figure out why this happens.

Any help for my understanding would be appreciated.

Thanks,
Subodh