A few months ago I updated a MySQL setup from 5.5 to 5.6. Since then I've been having problems with a script that I use to dump the various databases so that I can back them up.

The script is a short piece of perl that gets a list of all databases and then calls mysqldump for each as follows:

mysqldump -udb_account -pdb_pw -hserver.com --single-transaction --flush-logs 
   --routines --triggers --quick $fn 2> $fn.err | gzip > $fn.mysql.gz

Issue: many of these databases have hundreds of tables (ever increasing). For these larger dbs the mysqldump command often exits after only a single table. If I run the command from a terminal session it executes correctly. (typically it runs as a cron job 1x/wk)

The .err file contains no messages. Neither does the server.err file in the MySQL root dir.

Note: this script had been running fine on MySQL 5.5 for several years. This problem started happening when I upgraded to 5.6.

Also: the --flush-logs portion isn't working. The mysql_bin folder has never been emptied since this system was brought online.

One variable that I haven't controlled for yet: when run as a CRON job the script forks 3 processes at a time. When I test the command in a term session I'm only doing one at a time.

System in question:

  • CentOS 6.4
  • x64
  • 64Gb RAM
  • Are you shure that your mysqldump bin use the 5.6 version instead of 5.5? Feb 24, 2014 at 7:53
  • Yes. It's a new system all around.
    – ethrbunny
    Feb 24, 2014 at 12:08
  • Add --verbose to the mysqldump options and see what you get in the error logs. This option doesn't change the dump files, but it writes some progress information to STDERR. You should, at a minimum, see what it is "trying" to do, written to the error logs. Feb 24, 2014 at 12:17
  • I suggest start from disable --quick option.
    – a_vlad
    Nov 21, 2016 at 6:24

1 Answer 1


The problem here is just a misunderstanding

Using --flush-logs with mysqldump simply closes and opens all files handles against the following:

In the case of binary logs, the current binary log is closed and a new binary log is open. In essence, it is the only log file that is numerically rotated For example, suppose you run SHOW MASTER STATUS; and you see the binary log file names mysql-bin.000027. Running FLUSH LOGS; or FLUSH BINARY LOGS; would close mysql-bin.000027 (which stops recording transactions to that file) and open mysql-bin.000028 (which now records incoming transactions).

However, rotation does not imply automatic deletion.

There are two ways to clean up the binary logs


If the DB Server is not used as MySQL Replication Master, then run



That will erase all binary logs and start with the first one (like mysql-bin.000001)


Simply go the the OS and run the delete command (rm for Linux,del for Windows)


If you want to keep binary logs from the last 48 hours, you run


mysqld will erase all binary logs older than 48 hours.


Simply set the following in my.cnf (or my.ini)


Then, either restart mysqld or run the following as root

mysql> SET GLOBAL expire_logs_days = 2;

That way, every manual or automatic flush of binary logs will delete logs older than 2 days.

Once expire_logs_days is set, mysqldump will cleanup old logs every time it does --flush-logs.

  • I think biggest problem of author - "For these larger dbs the mysqldump command often exits after only a single table. If I run the command from a terminal session it executes correctly. (typically it runs as a cron job 1x/wk)". His Job fail without errors from cron script.
    – a_vlad
    Nov 21, 2016 at 6:23
  • @a_vlad If no error spits out, something tells me someone must have erased some binary logs manually, which can damage mysqld's ability to perform log rotations. See my post dba.stackexchange.com/questions/151129/… and the links to my other posts showing why. Nov 21, 2016 at 6:26
  • we are talking about different - his dump not finished, nothing about binary logs
    – a_vlad
    Nov 21, 2016 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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