Comments on Setting up Master-Master Replication with MySQL on Debian 8 (Jessie)
This tutorial describes a replicated MySQL setup (Mater/Master replication) with 2 nodes where data can be read and written to both nodes at the same time. MySQL takes care to replicate the data to the other node and ensures that primary auto increment keys don't collide.
10 Comment(s)
Comments
In the sample mysql configurations, I noticed that auto-increment-offset has the same value of '1', shouldn't that be different values for each node?
Also probably you need unlock the tables and enable read-write again after the slaves have been setup.
Excellent article. Any suggestions on how an app server would fail over to the 2ND DB would be appreciated.
As far as i remember mysql will use the mysql db 'mysql' to store user information.
so when you use
# Do not replicate the following databasesbinlog-ignore-db = mysqlreplicate-ignore-db = mysqlWill this replicate users as well or only the databases?
Great tutorial. Compact, understandable - just perfect topic. Thanks a lot :)
Do you have any advice about re-configuring an existing single master deployment into a master-master deployment? Specifically, with about 500Gb of data in the tables already what are the appropriate settings for the auto-increment-* configuration. I'm assuming 2 for the incrment is still right but should the offsets still be 1 and 2 or something that reflects the various ID fields already in use like 1000001 and 1000002 say?
Great tuttorial i followed many in an attempt to get this working and this was the first to work successfully (thus far).
nice tutorial..:D this work for me.. thanks a lot..:D
The start of the replication on server2 seems to be not correct. On both servers replication needs to be started. So for server2 also a MASTER_LOG_FILE and a MASTER_LOG_POS must be specified. Like the procedure is described here, the replication is not symmetrical. Since we do master-master replication I would expect the setup and the start procedures equal on both servers.
Hello.
This is a very good documented "tutorial", but it didn't work for me.
My databases don't sync and I did everything there was.
Can you please answer me on this comment.
Best regards.Nick P.
I know this is an outdated tutorial but i applied this on Debian 11 with MariaDB 10.5.15. It all worked flawless until the last lines.On server01 I have "Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event; the first event 'mysql-bin.000001' at 107, the last event read from 'mysql-bin.000001' at 107, the last byte read from 'mysql-bin.000001' at 126.'" and any update on server02 is not replicate into server01.