View source for Mysql master-master replication hints


* tested mariadb 10.3 and mysql8
* this is working configuration for basic running
* next hints will be added if some problems found)

== Master servers settings ==

Server 1 configuration:

 [mysqld]
 ...
 server-id               = 1
 report_host             = master1
 log_bin                 = /var/lib/mysql/mariadb-bin.log
 log_bin_index           = /var/lib/mysql/mariadb-bin.index
 relay_log               = /var/lib/mysql/relay-bin
 relay_log_index         = /var/lib/mysql/relay-bin.index
 expire_logs_days        = 10
 
 auto_increment_increment = 2
 auto_increment_offset = 1
 slave-skip-errors=all

Server 2 configuration:

 [mysqld]
 ...
 server-id               = 2
 report_host             = master1
 log_bin                 = /var/lib/mysql/mariadb-bin.log
 log_bin_index           = /var/lib/mysql/mariadb-bin.index
 relay_log               = /var/lib/mysql/relay-bin
 relay_log_index         = /var/lib/mysql/relay-bin.index
 expire_logs_days        = 10
 
 auto_increment_increment = 2
 auto_increment_offset = 2
 slave-skip-errors=all

Server 1 create replication user + privileges:

 MariaDB [none]> create user 'master1'@'%' identified by 'test';
 MariaDB [none]> grant replication slave on *.* to 'master1'@'%';

Server 2 create replication user + privileges:

 MariaDB [none]> create user 'master2'@'%' identified by 'test';
 MariaDB [none]> grant replication slave on *.* to 'master2'@'%';

Server 1 get master status (will be used in the Server 2 slave setting):

 MariaDB [none]> show master status;
 +--------------------+----------+--------------+------------------+
 | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +--------------------+----------+--------------+------------------+
 | mariadb-bin.000004 | 52234945 |              |                  |
 +--------------------+----------+--------------+------------------+
 1 row in set (0.104 sec)

Server 2 get master status (will be used in the Server 1 slave setting):

 MariaDB [none]> show master status;
 +--------------------+----------+--------------+------------------+
 | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +--------------------+----------+--------------+------------------+
 | mariadb-bin.000005 | 12214311 |              |                  |
 +--------------------+----------+--------------+------------------+
 1 row in set (0.000 sec)

Server 1 set slave setting (use master status from server 2):

 MariaDB [none]> stop slave;
 MariaDB [none]> CHANGE MASTER TO MASTER_HOST='10.133.1.91', MASTER_USER='master2', MASTER_PASSWORD='test',MASTER_LOG_FILE='mariadb-bin.000005', MASTER_LOG_POS=12214311;
 MariaDB [none]> start slave;

Server 2 set slave setting (use master status from server 1):

 MariaDB [none]> stop slave;
 MariaDB [none]> CHANGE MASTER TO MASTER_HOST='10.133.1.90', MASTER_USER='master1', MASTER_PASSWORD='test',MASTER_LOG_FILE='mariadb-bin.000004', MASTER_LOG_POS=52234945;
 MariaDB [none]> start slave;

Slave status from Server 1 (Important: Slave_SQL_Running: Yes)

 MariaDB [none]> show slave status\G
 *************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.133.1.91
                   Master_User: master2
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000005
           Read_Master_Log_Pos: 12272564
                Relay_Log_File: relay-bin.000006
                 Relay_Log_Pos: 10099727
         Relay_Master_Log_File: mariadb-bin.000005
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 12272564
               Relay_Log_Space: 10100331
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 2
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 7325
 Slave_Non_Transactional_Groups: 3
    Slave_Transactional_Groups: 6447
 1 row in set (0.000 sec)

== Sensor settings ==
* option 'disable_partition_operations = yes' must be set on all sensors except one which will do all partition operations.