We have had to restore a few tables from row based binary logs. It's kind of a pain in the ass to be honest. In short I started out by reloading the dump from the night before. I then took the binary logs and ran something like this.. mysqlbinlog -v -base64-output=DECODE-ROWS mysql-bin.005157 > /home/recovery/db_dumb.sql. this outputs the binary log as sql querys we were then able to run these files in order to recreate the database. I know you can also use mysqlbinlog to select and specific start and stop time if that's of any help to you. I think you can also select multiple bin files with that command to make one huge sql file.
Not sure if this is the "correct" way of doing it but i know it works. Brent Foor On Wed, Nov 30, 2011 at 9:38 AM, Travis Paul <[email protected]> wrote: > Greetings FWLUG, > I was curious if anyone had any experience cloning a MySQL database > from Binary Logs? I've always backed up mysql using mysqldump so I've > never had to deal with binary logs much aside from replication. > > Anyways, I've got some binary logs from a tape backup and I need to > use them to create a copy of the database so I can do some manual > auditing and compare database integrity from a backup several weeks > ago with the current database. > > I put my bin logs in a tmp directory, concatenated them into the > 'mysqlrestore' file, then tried to load them into a fresh DB on in a > VM. > > # mysqlbinlog mysqlrestore | mysql -u root -p > > I've tried backups from various points and they all give me different > errors. Is what I'm trying to accomplish possible? > > Some errors from various backup points: > mysqlbinlog: unknown variable 'ssl-ca=/etc/mysql/ca-cert.pem' > ERROR: Error in Log_event::read_log_event(): 'Event too small', > data_len: 1, event_type: -46 > > For backups I've always loaded a file from a mysql dump like: > # mysql -u root -p < from_mysqldump.sql > > So before I go digging into these errors I just want to make sure I'm > doing something that is actually possible. > > Thanks! > > _______________________________________________ > Fwlug mailing list > [email protected] > http://mail.fortwaynelug.org/mailman/listinfo/fwlug_fortwaynelug.org > > This is a public list and all posts are archived publicly. Please keep > this in mind before posting. >
_______________________________________________ Fwlug mailing list [email protected] http://mail.fortwaynelug.org/mailman/listinfo/fwlug_fortwaynelug.org This is a public list and all posts are archived publicly. Please keep this in mind before posting.
