← Back to team overview

maria-discuss team mailing list archive

Re: Galera cluster as slave: Duplicate entry errors

 

hi,all

I also came into the almost same situation, my migration like below:

1. use xtrabackup back a mysql5.5.x database
2. set up the first node of PXC5.6 with the backup
3. start the master-slave replication between mysql5.5 and the first node of pxc5.6
4. add new node to the PXC5.6

when i process the step 4, I found the replication is stopped caused by the auto-increment issue.
And i found the binlog-format of mysql5.5.x is statement-based, So i setup a middle node with row
based binlog as the master of PXC, and it works. the operation steps like below:
1. use xtrabackup back a mysql5.5.x database
2. setup a middle node mysql5.6.x with the backup and binlog-format=row
3. start the master-slave replication between mysql5.5 and mysql5.6.x
4. set up the first node of PXC5.6 with the backup
5. start the master-slave replication between mysql5.6.x and the first node of pxc5.6
6. add new node to the PXC5.6

I do not deep into find the reason, but i know there must be something different for pxc and normal slave
at the method of handling auto-increment field,So with row-based binlog, there is no need to handle the
auto-increment field values, and the behaviour of them will be the same.


On Sep 6, 2014, at 8:00, Daniel Black <daniel.black@xxxxxxxxxxxxx> wrote:

> 
> 
> ----- Original Message -----
>> 
>> Hmm, maybe an idea by myself on the cause:
>> 
>> the initial sync to galera was done as followed:
>> 
>> - dump on the (locked) master
>> - inserted the dumps on a galera node
>> - started replication (change master to...) with the saved state of the
>> master
>> 
>> I've read somewhere that galera handles the auto_increments with a
>> offset for each node (by design) - so you cannot rely on it.
> 
> This should only matter if you are writing directly to the galera nodes.
> 
> It can be disabled however.
> http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep-auto-increment-control
> 
>> That is no
>> problem for the application at all. But as i use ROW based replication
>> the autoincrements (created on the master) could exist on the galera
>> side already, right?
> 
> Did you compare the contents of the row on master and galera?
> 
>> what would be a way to avoid it? i think i need another migration
>> scenario...
> 
> It looks quite sane to me.
> 
> -- 
> -- 
> Daniel Black, Engineer @ Open Query (http://openquery.com.au)
> Remote expertise & maintenance for MySQL/MariaDB server environments.
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp


References