Robert Citek
2013-02-13 14:02:53 UTC
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: ***@googlegroups.com
To subscribe: cwelug-***@googlegroups.com
To unsubscribe: cwelug-***@googlegroups.com
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 cwelug+***@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
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: ***@googlegroups.com
To subscribe: cwelug-***@googlegroups.com
To unsubscribe: cwelug-***@googlegroups.com
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 cwelug+***@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.