No Problem sorry to hear of your issues. the only way you would be able to recreate the whole database with nothing but bin logs is if you had every binary log from the point you started adding data up to today. I would suggest getting a dump of the database asap. Are you using myisam or innodb? if you are using myisam you could simply stop the server for a few minutes and copy all the files out of the database and drop them into another one. if that's the case let me know and I can elaborate on how to do that.
On Wed, Nov 30, 2011 at 10:32 AM, Travis Paul <[email protected]> wrote: > 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. >
_______________________________________________ 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.
