Issues with mysqldump after upgrade 5.5 -> 5.6 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&gt; $fn.err | gzip &gt; $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
The problem here is just a misunderstanding
Using --flush-logs with mysqldump simply closes and opens all files handles against the following:

Error Log
Engine Logs (Redo Logs Flushed to disk)
Relay Log (If Replication is setup)
General Log (if Enabled)
Slow Log (if Enabled)
Binary Logs (if Enabled)
See MySQL Documentation under Bulletpoint #3 ([log_type] LOGS)

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
DELETE ALL BINARY LOGS
If the DB Server is not used as MySQL Replication Master, then run
BINARY LOGGING IS ENABLED
mysql&gt; RESET MASTER;

That will erase all binary logs and start with the first one (like mysql-bin.000001)
BINARY LOGGING IS NOT ENABLED
Simply go the the OS and run the delete command (rm for Linux,del for Windows)
PURGE BINARY LOGS
If you want to keep binary logs from the last 48 hours, you run
mysql&gt; PURGE BINARY LOGS BEFORE NOW() - INTERVAL 48 HOUR;

mysqld will erase all binary logs older than 48 hours.
AUTOMATIC PURGING
Simply set the following in my.cnf (or my.ini)
[mysqld]
expire_logs_days=2

Then, either restart mysqld or run the following as root
mysql&gt; 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.