Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


MySQL Master-Master replication issue
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

MySQL Master-Master replication issue

punkstar69punkstar69 Member
edited July 2018 in Help

I've completed a MySQL (mariadb) mirror-mirror replication and so far everything seems fine.

If i stop the mysql service on one machine and do changes to the other's machine sql db, once I start the service to the first one, changes auto sync.

But once I shutdown the complete machine and trying to power it up in order to merge the changes that I did while it was powered off, replication is not starting.

I do receive the following error on slow slave status:

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

I am sure that I can fix this by setting new binlog positions and re-starting slave, however, i would like to have a more automated way so they do sync in all cases.

Whats your experiences/suggestions guys?
Thank in advance.

Comments

  • HBAndreiHBAndrei Member, Top Host, Host Rep

    punkstar69 said: MySQL (mariadb) mirror-mirror replication

    I think you mean master-master replication.

    What's the my.cnf look like for each servers? (post just the replication bit, not the entire thing).

  • punkstar69punkstar69 Member
    edited July 2018

    @HBAndrei said:

    punkstar69 said: MySQL (mariadb) mirror-mirror replication

    I think you mean master-master replication.

    What's the my.cnf look like for each servers? (post just the replication bit, not the entire thing).

    Yeap, Typo. Fixed. Thanks!

    Its the default my.cnf along with these lines: https://pastebin.com/QzzKnRbC

    Just noticed sync_binlog - is this what will override syncing ?

  • HBAndreiHBAndrei Member, Top Host, Host Rep

    Where's the my.cnf from master2? Why are the relay_log lines commented out?

  • punkstar69punkstar69 Member
    edited July 2018

    Same config applied to master2, except the server-id which is set to '2'.

    Relay_log is by default commented out and replication works pretty fine without it.
    Are they needed?

  • gbshousegbshouse Member, Host Rep

    Huh RTFM especially in context of config file parameters

  • @gbshouse said:
    Huh RTFM especially in context of config file parameters

    RTFM for having relay_log enabled whereas my replication works fine without it? Okay

  • In case someone's facing the same:

    You have to define the following in my.cnf:

    binlog_do_db = myDB
    slave_exec_mode = IDEMPOTENT

    In order to replicate 'myDB' database.
    I though that not defining this would replicate all of my MySQL dbs, but it seems that its not making the trick.

    After restarting machines, everything synced back.

    Thanked by 2irm Ndha
Sign In or Register to comment.