Mysqldump –flush-logs results in small backup files

backupmysql-5.5transaction-log

New to MySQL and just learning how to setup a daily full backup (binary logs enabled). My goal is to run a daily full backup of the databases/schemas and reset the binary log.

I'm currently using an edited script that creates individual backup files for each schema which is great.

Each run through the loop executes this against a database and all looks great except the binary log does not reset.:

%mysqldumpexe% --user=%dbuser% --password=%dbpass% --routines --master-data=2 --databases %%F --log-error=%errorLogPath% > "E:\MySQLBackup\Database\%%F.%backuptime%.sql"

When I add the –flush-logs parameter to the line and run it, it:
1) Creates separate binary logs for each database (currently I have only one)
2) Creates full backup files that are 1-2kb with no data?!

What am I doing wrong here? I want the full backup to be a complete dump of the database at the time and also reset the binary log so it contains the changes just after the full backup.

Thanks in advance

Best Answer

This is related to a bug with new installs of mysql on debian wheezy whereby the error.log file is owned by root.root, as per this: http://tipstricks.itmatrix.eu/?p=1352

I fixed it with chown mysql.adm /var/log/mysql/error.log

Related Solutions

Sql-server – What ‘resets’ the transaction log

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)

Best Answer

Related Solutions

Sql-server – What ‘resets’ the transaction log

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Related Question