1

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? – Cristian Porta Feb 24 '14 at 7:53
  • Yes. It's a new system all around. – ethrbunny Feb 24 '14 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. – Michael - sqlbot Feb 24 '14 at 12:17
  • I suggest start from disable --quick option. – a_vlad Nov 21 '16 at 6:24
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

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.

  • 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 '16 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. – RolandoMySQLDBA Nov 21 '16 at 6:26
  • we are talking about different - his dump not finished, nothing about binary logs – a_vlad Nov 21 '16 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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