Issues with mysqldump after upgrade 5.5 -> 5.6

Question

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

Answer 1

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