mysqlmysql-workbenchdatabase-replicationmirroringdatabase-mirroring

MySQL Replication VS Mirroring and how to do both?


MySQL Replication VS Mirroring and how to do both, does anyone knows some information on this topic?


Solution

  • MySQL Replication: https://www.youtube.com/watch?v=APAmsHAYUiI

    So replication it is one way relationship: Master to Slave.

    Mysql:

    1. Stop Slave: stop slave;
    2. Tell the Slave that it is a Slave and where to locate the Master:

    Mysql:

    CHANGE MASTER TO 
    MASTER_HOST = '192.168.1.111', 
    MASTER_USER = 'master_replication_user', 
    MASTER_PASSWORD =  'master_password', 
    MASTER_LOG_FILE = 'DESKTOP-MASTER-PC-bin.000978', 
    MASTER_LOG_POS = 678; 
    
    1. Start Slave: start slave;
    2. Check Slave Status: show slave status;


    MySQL Mirroring: https://www.ryadel.com/en/mysql-master-master-replication-setup-in-5-easy-steps/

    The main point here is: There are two Masters (so they like a slave to each other, but they will never admit it). You just tell Master A that it is slave of the Master B. And then you tell Master B that he is the slave of Master A. It is reciprocal relationship.

    1. Stop Master A: stop slave;
    2. Stop Master B: stop slave;

    3.Tell the Master A where to locate the Master B

    CHANGE MASTER TO 
    MASTER_HOST = '192.168.1.111', 
    MASTER_USER = 'master_b_replication_user', 
    MASTER_PASSWORD =  'master_b_password', 
    MASTER_LOG_FILE = 'DESKTOP-MASTER-B-PC-bin.000756', 
    MASTER_LOG_POS = 888; 
    

    4.Tell the Second Master where to locate the First Master

    CHANGE MASTER TO 
    MASTER_HOST = '192.168.1.112', 
    MASTER_USER = 'master_a_replication_user', 
    MASTER_PASSWORD =  'master_a_password', 
    MASTER_LOG_FILE = 'DESKTOP-MASTER-A-PC-bin.000001', 
    MASTER_LOG_POS = 777; 
    

    Mysql:

    5.Master A: start slave;

    6.Master B: start slave;

    7.Check Master A Status: show slave status;

    8.Check Master B Status: show slave status;