0

I have a replication enviroment and is working fine, but today my disk going full.

I check and see that the mysql-bin.xxx files are growing very very fast (I know they contain all changes in the database(s), so naturally they will grow, but today it is not normal the way that they are growing... that is, very fast and continuously!).

Then I use mysqlbinlog to see the content of de last mysql-bin.xxx file but is to dificult to see any error.

My question

Is there some way to perceive, check, anything... where or why my mysql-bin files are growing?

  • Do you have any big update to your DB? For example update x set y=1 where id < 1000000 – HamoonDBA Jun 25 '17 at 3:40
  • No i do not have ... that's the problem – Arnaldo Raxach Jun 26 '17 at 13:00
0

Maybe your application updated very large records. In this situation MySQL bin log growing fast.

But if you want to print row events from MySQL Bin Log, you Can use --verbose switch.

For example: I want to dump print row events from binlog.005594 to see statement.

mysqlbinlog --base64-output=DECODE-ROWS --verbose binlog.005594 > statement_dump

The output is like this:

UPDATE `users`.`posts`
WHERE
@1=4266310158
@2=9618518585
@3=54576
@4=NULL
@5=NULL
@6=NULL
@7=''
@8=2017-06-22 12:28:27
@9=1
@10=1827
@11=5933727040501033372
### SET
@1=4266310158
@2=9618518585
@3=54576
@4=NULL
@5=NULL
@6=NULL
@7=''
@8=2017-06-22 12:28:27
 @9=1
@10=1828
@11=5933727040501033372

@n are order of columns


Another tool for do this:

https://github.com/danfengcao/binlog2sql

I didn't test it

  • Thank you very much for your quick response, very grateful, I'll prove it – Arnaldo Raxach Jun 26 '17 at 13:03
  • @ArnaldoRaxach you're welcome. If my post helped you please score my post. Thank you – HamoonDBA Jul 1 '17 at 12:51
0

Are you using replication? If not, do you have the binlogs turned on for backups?

Do you have expire_logs_days set? To what?

Give us some clues of what write queries are going on.

Are you UPDATEing all the rows in large tables? If so, this is a sign of a mis-designed schema.

Are you doing massive DELETEs? Let's discuss alternatives.

  • Yes, I am using replication and my expire_logs_days is in 2, obviously they should be making big modifications, apparently in the applications that consume the databases of the server, the behavior has calmed, that is to say it has not happened more, it must have been some Script of the side of the applications that towards mofificaciones, I suppose, when I have more time I dedicate myself to review more carefully. Thank you very much for your answers – Arnaldo Raxach Jul 7 '17 at 17:21

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.