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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
