If the user that the MySQL server does not have permissions to read and write to the files, then the server will not recognize the table. Usually the server runs under the 'mysql' user.
I would first stop your mysql instance before trying to change the ownership.
Once that is done, then (from a non-root user) do sudo chown mysql bsf_session.*
in the directory with the files.
You could also try to change the group (sudo chgrp mysql bsf_session.*
), however having the files as the same owner as the server is the best option.
If you do not have sudo privileges or root access, then you will need to contact whoever does to have them change the ownership.
--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
...
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
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.
— http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html
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.
Best Answer
If you see [tablename].MYD and [tablename].MYI files it's MYISAM. If you see [tablename].ibd or only the .frm file then it's InnoDB. Based on that the process:
MyISAM
You can simply copy the .MYI, .MYD and .frm files to any existing MySQL instance's database directory.
For example:
You have your database in
/var/lib/mysql/sitename
then you find the table which will be/var/lib/mysql/sitename/[dbname]/[tablename].*
(.MYD, .MYI and .frm). You can copy only these three files to an arbitrary database on a newly installed MySQL like/var/lib/mysql/sitename/[restored_db]/
.InnoDB
It's possible to import tablespace since 5.6 but if you can it's much simpler to copy the whole directoy. It's a common procedure to clone new replication slaves.
In your case you copy the whole
/var/lib/mysql/sitename
directory onto the new server and start MySQL over it. Make sure the innodb_log_file_size and innodb_log_files_in_group matches the size and number of what you have now. These are the files calledib_logfile0
andib_logfile1
.When you have the server running you can use mysqldump or any other method you prefer.