Greetings all, I have a client that has a database with several large innodb tables, the sum total measuring about 100 GB with an application that is continuously changing or inserting records. In an attempt to set up replication (master-slave), I have turned on bin-logging and dumped the data using the following command:
$ mysqldump --quick --single-transaction --all-databases --master-data=2 After importing the data into the slave and starting the slave threads, a 'show slave status' is reporting lots of 'duplicate entry' errors. I am trying to imagine how duplicate keys are possible using a specific example. Let's say I have a database with a single table called 'Example' containing 5 records with keys 1, 2, 3, 4, and 5. I dump the entire database, import it into a slave, and enable replication. Then replication stops with 'duplicate entry', which is because an insert statement was in the relay logs: 'insert into example (id) values (3)'. How can I explain the 'duplicate entry' error? 1) That record was deleted and then inserted on the master, but only the Insert was logged. For example, this was run on the master: SET sql_log_bin=0; delete from example where id=3; SET sql_log_bin=1; insert into example (id) values (3); 2) During the dump, entry 3 was inserted into the table AND recorded in the bin-logs after the start of the dump. That is, at the start of the dump only records 1, 2, 4, and 5 existed. During the dump entry 3 was inserted. So, the dump contained records 1, 2, 3, 4, and 5. I assume the option --single-transaction would handle this scenario, but maybe there is an assumption I overlooked. 3) Record 3 was added to the slave and the master. That is, the dump had only records 1, 2, 4, and 5. Record 3 was inserted on the slave after the import but before replication started. Record 3 was also inserted on the master, which was recorded in the bin-logs. Replication was then started on the slave. Any other possibilities? Do other scenarios become likely if there are two or more tables? Of those, how would you rank their likelihood? Regards, - Robert -- -- Central West End Linux Users Group (via Google Groups) Main page: http://www.cwelug.org To post: [email protected] To subscribe: [email protected] To unsubscribe: [email protected] More options: http://groups.google.com/group/cwelug --- You received this message because you are subscribed to the Google Groups "Central West End Linux Users Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
