Ofer Inbar a écrit : > I'm trying to set up MySQL backup & restore using mysqldump and binary > logs, so we can run mysqldump infrequently and use binary logs for the > daily backups (because mysqldump makes the production database unusable > for too long a time to run every night). > > - I can make full dumps using mysqldump > - Binary logging works > - I can restore full backups from mysqldump > - Restoring binary logs gives me "Duplicate entry" errors > > > Redhat Enterprise Linux 4, MySQL 5.0.24 installed from the Redhat RPMs. > > (since our production server runs 5.0.24, I'm playing with that version > on my test setup, so that I can be sure whatever procedure I come up > with will definitely work on the production server.) > > I can repeat the problem with this procedure on the test db: > - Import a full mysqldump file from the prodution db > - flush logs > - run a full mysqldump with --flush-logs --master-data=2 > - do a bunch of stuff that writes data > - drop the database, and recreate it, flushing the log > - make a copy of the binary log made since the mysqldump > - import the mysqldump I made locally > => now I have all data correctly restored up to that point > > Now, I attempt to restore the binary log and this happens: > > mysqlbinlog [binlogfile] | mysql -u root -p[db root password] [db name] > ERROR 1062 (23000) at line 21: Duplicate entry '16021330' for key 1 > > > I've googled around for similar errors, and found several conflicting > reports about bugs related to inserting 0's into auto_increment > columns. The insert statement at line 21 in the example above is > indeed inserting into a table that has an auto_increment columnm, but > it is not inserting a value into that column at all, nor are any > earlier statements in the binary log. I've checked the date in the > database at the time of the restore and there are no rows in the table > where the value of that column is 0 or NULL. > > I believe what I'm trying to do is a pretty standard way to set up > backup and restore for a production mysql database, so it should work. > Any ideas? > > -- Cos (Ofer Inbar) -- [EMAIL PROTECTED] > It's been said that if a sysadmin does his job perfectly, he's the > fellow that people wonder what he does and why the company needs him, > until he goes on vacation. -- comp.unix.admin FAQ > Hi,
I think your problem is in the way your trying to playback the binlog. I guess this is happening when you try to play the first of your binlog right ? Using mysqlbinlog in the way you put it will replay all the log but you are probably somewhere in the middle of that log (Or maybe that binlog is already inside your backup) Since you dumped with --master-data=2 write the statement of the position in the binlog you were in as a comment inside your dump. So 2 solutions, you looks at that line, which will tell you which binlog was in use and which position and then you use: For the first binlog: mysqlbinlog binlog_to_start --start-position=pos_id | mysql And simply for all other mysqlbing other_binlog | mysql Or a different way that someone recommended is to setup a local replication on itself. Which i'll leave as an exercice :) Hope that helps! -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
