• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

Mysql 5.5 flush logs

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.

Please help.
mysql-bkp-bin-log-issue.png
0
klajdi
Asked:
klajdi
  • 10
  • 8
1 Solution
 
johanntagleCommented:
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
 
klajdiAuthor Commented:
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
 
johanntagleCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
klajdiAuthor Commented:
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?

Please help!
0
 
klajdiAuthor Commented:
Just that command without backing up the database.
0
 
klajdiAuthor Commented:
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
 
johanntagleCommented:
Put those two commands in a separate .sql file then put the following in your batch file:
mysql -u username --password=****** < E:\path\to\filename.sql
0
 
klajdiAuthor Commented:
What about this question:

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
 
johanntagleCommented:
The above is exactly my answer to that.
0
 
klajdiAuthor Commented:
What do you mean by: Put those two commands in a separate .sql file ?
0
 
klajdiAuthor Commented:
I'm not sure how do it, can you please help?
0
 
johanntagleCommented:
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:

mysql -u username --password=****** < E:\path\to\filename.sql
0
 
klajdiAuthor Commented:
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
 
johanntagleCommented:
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
 
klajdiAuthor Commented:
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
 
johanntagleCommented:
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
 
klajdiAuthor Commented:
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_password=password
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
 
johanntagleCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now