You said that you had a dump to start with, if only I had a dump from a previous date I wouldn't need to use the binary logs at all, and I think that is the root of my problem. It seems like the bin logs are meant to get a database to an exact point in time, by first using a dump then using the bin logs to zero in on specific sql commands.
This restore has been a HUGE pain in the butt, and we have been gnawing at it since last Wednesday. I think I am going to implement a basic mysqldump cronjob moving forward. Thanks for your help Brent! On Wed, Nov 30, 2011 at 10:06 AM, Brent Foor <[email protected]> wrote: > 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. > _______________________________________________ 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.
