Solved

# Mysql 5.5 flush logs

Posted on 2011-10-11
503 Views
Im am using mysql 5.5 and using the following command to backup the db, and flush logs mysqldump --opt --user --password=******  --flush-logs --delete-master-logs dbname > E:\mysqldump\db.mysql

The command works but we the the following error message: 'Purge Binary Logs' TO 'Mysqllog;  .000061'';

Error message is attached.

mysql-bkp-bin-log-issue.png
0
Question by:klajdi

LVL 24

Expert Comment

It looks like a bug in mysqldump as it should form the filename as just 'mysqllog.000061', not 'mysqllog'.000061'.

Suggest you just remove the --delete-master-logs option and just do a PURGE BINARY LOGS. See http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html
0

Author Comment

Will the Purge BInary Logs do the same thing as --delete-master-logs option?

And how to put all in the same command, to replace it with PURGE BINARY LOGS ?

Thanks,
0

LVL 24

Expert Comment

Yes but you can't do it in one command.  PURGE BINARY LOGS is to be done via the mysql client.  I actually dont recommend that you do this - I prefer to keep the binary logs up created after the previous full backup in case I need to do a point in time restore.  Better just set them to expire after certain number days.  See http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_expire_logs_days
0

Author Comment

johanntagle:

mysqldump --opt --user --password=******  --flush-logs --delete-master-logs dbname > E:\mysqldump\dbname.mysql

How to run only this command in mysql: --flush-logs --delete-master-logs

is it possible?

0

Author Comment

Just that command without backing up the database.
0

Author Comment

If I log-in to mysql as root and i run both those commands they work successfully.

( mysql -u root -p).

mysql> FLUSH LOGS;
mysql> RESET MASTER;

How do I get those commands to run in a batch file? Or a script to run automatically?

For example like this command run automatically saved .bat: mysqldump --opt --user --password=******  --flush-logs --delete-master-logs dbname > E:\mysqldump\dbname.mysql
0

LVL 24

Expert Comment

Put those two commands in a separate .sql file then put the following in your batch file:
0

Author Comment

If I log-in to mysql as root and i run both those commands they work successfully.

( mysql -u root -p).

mysql> FLUSH LOGS;
mysql> RESET MASTER;

How do I get those commands to run in a batch file? Or a script to run automatically?

For example like this command run automatically saved .bat: mysqldump --opt --user --password=******  --flush-logs --delete-master-logs dbname > E:\mysqldump\dbname.mysql
0

LVL 24

Expert Comment

The above is exactly my answer to that.
0

Author Comment

What do you mean by: Put those two commands in a separate .sql file ?
0

Author Comment

0

LVL 24

Accepted Solution

Put these 2 lines into a new .sql file e.g. E:\path\to\filename.sql:

FLUSH LOGS;
RESET MASTER;

Then put this to a .bat file:

0

Author Comment

johan Thanks a lot for all your help. I really appreciate it.

One last question: Will the above command do the same thing as this command: --flush-logs --delete-master-logs
0

LVL 24

Expert Comment

Yes so you can delete that from your mysqldump.  Suggest also that you run those commands before you perform mysqldump, so that the logs for things that happen just before you create your backup are kept.
0

Author Comment

John my last set of questions:

1) backup the database no bin log deletion, no master deletion ( I have this command)
2) backup bin log (new step)(Is there a command to only backup the bin log?
3) delete bin log and master log (I have this command)
4) delete bin log from previews full backup (Is there such a command for this function?
0

LVL 24

Expert Comment

To backup bin logs, you basically issue a flush logs command then manually copy the actual binary log files, except for te most recent one, to whatever backup media you choose.

There's no command available for deleting bin logs of previous backup.  MySQL also doesn't know which bin logs are for what backup.
0

Author Comment

Thanks John

Can this scrip be revised to be used for backing up mysql binary logs automatically without having to copy anything manually?

Also can you remove unwanted commands from the below script?
My location for
log_file=E:\mysql1\binlogs.000001
binlog_dir=E:\mysql1\binlogs
backup_dir=E:\msql\backup

#
# This script backup binary log files
#

backup_user=binlog
backup_port=3306
backup_host=localhost
log_file=/var/log/binlog_backup.log
binlog_dir=/san/mysql-us/mysqllogs
backup_dir=/home/dagnus/mysql_binlog_backup/

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
export PATH

Log()
{
echo "date : $*" >>$log_file
}

mysql_options()
{
common_opts="--user=$backup_user --password=$backup_password"
if [ "$backup_host" != "localhost" ]; then common_opts="$common_opts --host=$backup_host --port=$backup_port"
fi
}

mysql_command()
{
mysql $common_opts --batch --skip-column-names$1 -e "$2" } Log "[INIT] Starting MySQL binlog backup" Log "Flushing MySQL binary logs (FLUSH LOGS)" mysql_command mysql "flush logs" master_binlog=mysql_command mysql "show master status" 2>/dev/null | cut -f1 Log "Current binary log is:$master_binlog"

copy_status=0

for b in mysql_command mysql "show master logs" | cut -f1
do
if [ -z $first_log ]; then first_log=$b
fi
if [ $b !=$master_binlog ]; then
Log "Copying binary log ${b} to${backup_dir}"
rsync -a $binlog_dir/$b $backup_dir >& /dev/null if [$? -ne 0 ]; then
copy_status=1
break
fi
else
break
fi
done

if [ \$copy_status -eq 1 ]; then
Log "[ERR] Failed to copy binary logs cleanly...aborting"
exit 1
fi
0

LVL 24

Expert Comment

Think you already need to post this as a new question so that other experts can help as I'm not very good at Windows batch scripting.
0

## Featured Post

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
As companies replace their old PBX phone systems with Unified IP Communications, many are finding out that legacy applications such as fax do not work well with VoIP. Fortunately, Cloud Faxing provides a cost-effective alternative that works over an…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…