Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [mysqlbinlog]

mysqlbinlog : Utility for Processing Binary Log and Relay Logs

mysqlbinlog : The command line utility to display the SQL statements that have been recorded. The purpose of this utility is to extract SQL commands for use in "playing back" SQL for point-in-time data restoration. You can also compare and analyze the order SQL in recorded on a Master versus how the relay logs process the same SQL on a Slave.

The context for each SQL statement recorded are the following:

  • Date/Time Stamps
  • Current Database in Use
  • Server ID the SQL Command
  • Position of the SQL Statement Relative to Next Command

When dumping, you can specify

  • Starting and/or Ending DateTime
  • Starting and/or Ending Position
  • Database to Dump From
  • tail -f of Binary Logs

For more information, please see MySQL Documentation on mysqlbinlog

76 questions
15
votes
5 answers

Why do the MySQL bin log files still exist after a purge or flush?

I've used PURGE BINARY LOGS as well as FLUSH LOGS, but the mysql directory still contains these files: mysql-bin.000025 mysql-bin.000024 mysql-bin.000023 mysql-bin.000022 mysql-bin.000021 mysql-bin.000020 mysql-bin.000019 mysql-bin.index Is there a…
lamp_scaler
  • 253
  • 1
  • 2
  • 4
10
votes
3 answers

Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

For some reason, when I try to open up my tables that are stored in .frm and .ibd files (whether on MySQL or phpmyadmin) it gives me a syntax error, or it says it does not exist. I've read restore table from .frm and .ibd file? that had similar…
carment
  • 421
  • 1
  • 3
  • 8
8
votes
4 answers

Show sql statements from mysql binlog (GTID)

I have mysql binlogs for replication through GTID. I'm trying to show executed update/insert statements like this: mysqlbinlog --base64-output=DECODE-ROWS mysql-bin.000024 But all I see is something like this, no traces of update or insert…
Glasnhost
  • 378
  • 1
  • 5
  • 14
6
votes
1 answer

How do I get the MySQL binary log coordinates from the binary files?

I'm attempting to set up replication for a large (120 GB) MySQL database and I messed up. I shut down the master, copied the data files, and restarted the master. Now I've set up the slave & started it up, and I realize that I neglected to get the…
Kryten
  • 275
  • 1
  • 4
  • 9
5
votes
1 answer

Is it possible to gain the precise position of a live mysql database when performing full backup

I have a production database that I usually run fullbackups on once every week. I have bin logs turned on for incremental backups. The production server is on the west coast while business hours are on the east coast. When I perform the fullback up…
user2108258
  • 153
  • 1
  • 4
4
votes
1 answer

Is it possible to convert mysql binlog from statement format to row format?

The mysql server online is of version 4.1, which doesn't support row-based binary log. Nevertheless, I need the row-based binlog. Can I use the binlog generated by old mysql and import it into another mysql of higher version that supports row-base…
amazingjxq
  • 141
  • 4
4
votes
1 answer

why my MySQL binary log file size is so small?

I have a lot of small binary log files , each of them just only 126 Bytes -rw-rw----. 1 mysql mysql 126 Jan 6 16:13 mysql-bin.000001 -rw-rw----. 1 mysql mysql 126 Jan 6 16:16 mysql-bin.000002 -rw-rw----. 1 mysql mysql 150 Jan 7 10:34…
LawrenceLi
  • 321
  • 3
  • 15
3
votes
1 answer

Is there any drawback of MIXED replication mode?

So, recent mysql server allows three types of replication mode: STATEMENT, ROW, MIXED. They all have pros/cons, and I pick the mode according to site characteristics. When I get a replication error I sometimes need to examine the binlog files using…
Manny Calavera
  • 463
  • 2
  • 10
3
votes
2 answers

MySQL bin log missing data?

I'm trying to make heads and tails of my binary logs and am coming up short. I have many entries similar to the following from mysqlbinlog but I'm missing log data that I know should be there. # at 1050548 #130617 18:40:03 server id 1 end_log_pos…
Greg
  • 31
  • 1
  • 4
3
votes
2 answers

MySQL binlog missing queries

In direct reference to the accepted answer in this question MySQL doesn't log to the binary log file when you use direct database.table syntax in your update queries (eg. UPDATE Foo.Bar SET ...). Is the above true? It doesn't say this explicitly…
Adam Copley
  • 294
  • 1
  • 2
  • 12
3
votes
1 answer

How can you close or flush a binlog file on a remote server

I am replicating my binlog files to a secondary server using the mysqlbinlog --stop-never --read-from-remote-server flags. If the primary database server is compromised I want to replay the binlogs on the remote server, but I'm having trouble with…
bwizzy
  • 153
  • 7
3
votes
1 answer

MySQL binary log reporting execution time as UINT_MAX

Looking through the MySQL Binlogs, I see some data that just does not make sense. There are queries that say they have been running for 4294967295 seconds (which also happens to be UINT_MAX on this system). The queries that are reported with these…
Questionmark
  • 133
  • 4
2
votes
1 answer

Hi disk I/O and crash when turning on sync_binlog in MySQL

I have a MySQL master server (MariaDB 10.0.17) that have a very odd behavior. It has one slave fetching data from it. When I set global sync_binlog = 1, the server I/O goes very high and after some hours the server crashes. In the graph bellow, at…
Daniel Cukier
  • 165
  • 1
  • 9
2
votes
2 answers

Get missed transactions from slave

I'm trying to write a disaster recovery plan, I have one way replication with 1 master(A) and 3 slaves(B,C,D) A /|\ / | \ B C D Let's say my master crash, and I loose all data on that server, I still have 3 slaves, and I'll promote…
altmannmarcelo
  • 1,111
  • 8
  • 26
2
votes
1 answer

what happens If I remove mysql-bin.index file?

Because of the file storage full, we are removing mysql bin log files. Accidentally, we removed mysql-bin.index file. What happens if I remove mysql-bin.index file? These below are my DBMS info. Thank you. MariaDB 10.4 Single Instance engine: InnoDB…
Bohyun
  • 35
  • 3
1
2 3 4 5 6