master-master replication and auto increment offset

master-master replication and auto increment offset

by ronny lippold -
Number of replies: 5

hi guys,

i'm new in moodle administration for the sysadmin site.


so we try to upgrade to the newest version, we want to try more redundancy.

starting with the database.


master master replication for it self, should not be an problem, we only want one active master for writing.

but i'm not sure about the offsets for the auto_increment.

in master master, you need to setup an offset to not run into duplicate key.

so here is an example config:

master1

auto_increment_increment = 2

auto_increment_offset = 1


master2

auto_increment_increment = 2

auto_increment_offset = 2


can this be a problem for moodle application?


i hope, i explained everything ... i not ask back to me.


thanks for help and kind regards,

ronny

Average of ratings: -
In reply to ronny lippold

Re: master-master replication and auto increment offset

by ronny lippold -

hi ... maybe more explanation


that means:

if you have an auto increment field, like user is you will get on insert on master1:

1,3,5,7

and if you switch to master2:

2,4,6,8

some applications have problems, when the increment field is not a following number.

like select 3 rows from the table.


hope, someone can help me,

thanks,

ronny

Average of ratings: -
In reply to ronny lippold

Re: master-master replication and auto increment offset

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Ronny,
you don't need to bother about how auto increment fields are managed in the database server: when Moodle will create new entities, they will be properly referenced without the need to know base offset and increment step.

HTH,
Matteo

Average of ratings: -
In reply to Matteo Scaramuccia

Re: master-master replication and auto increment offset

by ronny lippold -

hi matteo,


thanks for answering ... this will help me to build up the database relication.

i ve seen so many ugly things in web applications :D


ronny

Average of ratings: -
In reply to ronny lippold

Re: master-master replication and auto increment offset

by Ken Task -
Picture of Particularly helpful Moodlers

Tinkered with master-slave before but didn't run any site that I thought would justify the time spent.  Having said that, did a little digging (revisiting) and discovered (am not a certified DB admin):

Master-master setups might be old technology now.  There is now 'Group Replication' which might address the issue you've raised.

Master-master

https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

note what it says on that page ... link to new method ...

https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04

https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

above is version 8 of MySQL

https://dev.mysql.com/doc/refman/5.7/en/group-replication.html

Think others would be interested in how you get along with this, so please share back your discoveries! ;)

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: master-master replication and auto increment offset

by ronny lippold -

hi ken and thank you.


i  think, we will stay on with maria db and master-master replication is a quite simple construct.

i will start with this and give you updates, how it will work.


next part will be the sync script to transfer the files to the other "node".


ronny

Average of ratings: -