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, which are the most likely?

Regards,
- Robert

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql