Again no problem, feel free to email me if you need anymore help, I don't know THAT much about mysql but i have managed it for our company about 5 years.
On Wed, Nov 30, 2011 at 10:52 AM, Travis Paul <[email protected]> wrote: > "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." > > That's exactly what I was thinking, I'm glad you said that! I would > assume there is some log rotation going on that wouldn't even make > that possible with my tape backup. > > Some tables in this database are InnoDB and some are MyISAM, I've got > some cronjob scripts somewhere in github to get a dump from each (I > know one requires locks and one doesn't but I can't recall which > offhand) so I guess I've got my work cut out for me. > > Thanks again > > On Wed, Nov 30, 2011 at 10:41 AM, Brent Foor <[email protected]> wrote: > > 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. > > > > _______________________________________________ > 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.
