I am trying the following command to extract SQL statements from binlog for using it in another database (Amazon RDS)

mysqlbinlog /var/lib/mysql/binlog.000027 --start-position=54375264 --base64-output=NEVER > /home/ec2-user/output2.sql

But I got the following error:

ERROR: --base64-output=never specified, but binlog contains a Table_map event which must be printed in base64.

I read in the documentation that mysqlbinlog exits with an error if a row event is found that must be displayed using BINLOG. Is there any work around to extract SQL statements from binlog?

Update 1 : I run the command again with option --verbose but I did not get any return in the command line

[root@server ec2-user]# mysqlbinlog /var/lib/mysql/binlog.000027 --start-position=54375264 --base64-output=DECODE-ROWS --verbose  > /home/ec2-user/output4.sql
[root@server ec2-user]# 
[root@server ec2-user]#

When I browse output4.sql, I can see some valide SQL + some commented SQL like this:

#130906 14:54:55 server id 1  end_log_pos 269447893     Query   thread_id=1221718733    exec_time=0 error_code=0
SET TIMESTAMP=1378479295/*!*/;
# at 269447893
# at 269447950
#130906 14:54:55 server id 1  end_log_pos 269447950     Table_map: `dbname`.`tablename` mapped to number 102
#130906 14:54:55 server id 1  end_log_pos 269448102     Update_rows: table id 102 flags: STMT_END_F
### UPDATE `dbname`.`tablename`
###   @1='edited by me'
###   @2=0
###   @3=1373178370
### SET
###   @1='edited by me'
###   @2=0
###   @3=1378479295
# at 269448102
#130906 14:54:55 server id 1  end_log_pos 269448162     Query   thread_id=1221718733    exec_time=0 error_code=0

Update 2 : I am following the tutorial here https://engineering.gosquared.com/migrating-mysql-to-amazon-rds

I want to update Amazon RDS from the output of binlog. Currently Amazon RDS do not allow writing from binlog. I am trying to extract the SQL statments from binlog so I can update Amazon RDS. Is there any way to update Amazon RDS from binlog?

2 Answers 2

--base64-output=DECODE-ROWS --verbose

This is the format you need to be using if you are reviewing the binlog manually... it extracts the maximum amount of useful data from the log... but it will help if you understand how binary logging works in MySQL, first.

RDS (at least for MySQL 5.5 and 5.6) uses binlog_format = MIXED. This means the server may choose, on a query-by-query basis, to log either the actual SQL statement that was executed ("statement" log), or the actual rows changed by the query ("row" log) but not both.

The "commented SQL" that you are seeing is, as explained in the documentation, a human-readable pseudo-sql reconstruction of the actual data that was changed on the master by your query, for events logged using the ROW format. The body of the query that made the change is not preserved, because it is not needed for replication -- slave servers can replicate the exact same changes to their tables as occurred on the master, based on these before ("where") and after ("set") row images.

You can't "get" the original SQL from these events because it's not being saved to the binary log when events are written like this. In the example you posted, it's telling you that the value of the 3rd column changed.

###   @3=1373178370 # old value

###   @3=1378479295 # new value

This "pseudo-SQL" is commented out because it isn't actual valid SQL that a server could execute. It's eyeball-compatible-only. (For a server to replay these row events, it needs to see the BINLOG blocks, which are a base64-encoded version of the information that mysqlbinlog is decoding for you, here).

The column names are also not preserved in the binary log for row-based events, because since they should be the same on master and slave, that information is not needed.

(Edit: you mentioned RDS but re-reading your question, it seems that you may be trying to apply these logs to RDS rather than having retrieved them from RDS; retrieving logs from RDS is only possible with RDS/MySQL 5.6. The conclusion, then, is that the server that originated these logs is either logging in MIXED or ROW mode.)

Update: I see what you are doing now. To summarize the link you posted, you're trying to (1) take a point-in-time snapshot of an existing server with mysqldump, (2) restore that snapshot to an RDS instance, then (3) use mysqlbinlog to play the transactions forward from the binlog to sync the RDS server to your existing production server, and finally (4) migrate your application to RDS.

Bottom line: You are overthinking the nature of the problem and are concerned without cause. MySQL Server knows how to interpret the output of mysqlbinlog without either the --base64-output or --verbose flags and apply the changes it sees, whether or not the original query SQL is logged because when the query isn't logged, the actual row changes are logged.

When you run mysqlbinlog you see what looks almost like noise. Example:

#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

This is a base-64-encoded version of very tightly-packed binary "row images" containing the actual change that needs to be made to the database. A MySQL server knows how to decode and apply this change. Conversely, when you decode-rows and verbose, you're asking mysqlbinlog to decode the binary log for human eyeballs.

Leave your server's binlog_format setting as it is. The binary logs should work without a problem.

But now, forget everything you were planning because literally in the last 24 hours, Amazon has announced a much better way: they have opened up the ability to temporarily connect an RDS instance, as a slave, to a non-RDS master, to allow the RDS instance to be brought into real-time automatically prior to being promoted to master.

This is exactly the solution to what you are trying to accomplish and is much easier and less error prone.

To import data from a MySQL database that is not in Amazon RDS, you can configure replication from that database to an instance of MySQL running in Amazon RDS. The MySQL database you are migrating from can be running either on-premise in your data center, or on an Amazon EC2 instance. The MySQL instance in Amazon RDS must be running either version 5.5.33 or 5.6.13. Using replication to migrate the data reduces downtime.


No kidding.

As a DBA, I've been very reluctant to use RDS in my own production networks, preferring to retain full control over all aspects of the server... and by "reluctant," I mean "we didn't." I finally relented when Amazon made it possible in RDS for MySQL 5.6 to use the RDS instance as master to a non-RDS slave ... and, now, using an RDS master as slave to a legacy server to simplify migration seems like another positive sign for the viability of RDS.

  • I updated the question to make it more clear. What if I change the binlog format to binlog_format = STATEMENT ?
    – usef_ksa
    Commented Sep 6, 2013 at 18:11

The only possible work around is mentioned in the MySQL Documentation

You can tell mysqlbinlog to suppress the BINLOG statements for row events by using the --base64-output=DECODE-ROWS option. This is similar to --base64-output=NEVER but does not exit with an error if a row event is found. The combination of --base64-output=DECODE-ROWS and --verbose provides a convenient way to see row events only as SQL statements:

Give it a Try !!!

  • I updated the question to include --verbose. Does --base64-output=DECODE-ROWS ignore the events if it is unable to get SQL from it? This means losing some data!
    – usef_ksa
    Commented Sep 6, 2013 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.