Step by step guide to set up Multi source replication in MariaDB

Expectations:
1. You should be knowing how to set up replication in MySQL
2. You have basic knowledge of MySQL components such as binary logs, relay logs, mysqldump

Multisource Replication

Multi-source Replication means that one server has many masters from which it replicates. This can be one or more schema from each master instances. Introduced in MariaDB 10.0

My Environment 

I have multiple servers in my production setup. There are different flavors of MySQL (Percona, MariaDB, MySQL Enterprise edition) running in servers.  Each servers has its own Slaves and the slaves are read only. Applications are connecting to both master and slaves.
Here I am taking two server sets for this example : Core, Messaging

My Requirement

I need to setup a MySQL instance where All schema from Core (cdb1, cdb2 , cdb3)  and 1 schema from Messaging ( mdb1 out of mdb1, mdb2 and mdb3)  has to be replicated for Operational reporting.

Implemantation

As we know the requirement and understood the environment we can now start implementing the multi-source replication.

1. Download and install MariaDB. 
https://mariadb.com/kb/en/the-mariadb-library/yum/

2. Backup and restore the required schema from each server

mysqldump can be used to take the backup.Do remember to give the option --master-data=2 --single-transaction While taking the dump.

NOTE:  --master-data will impose a global read lock for a short period of time. Read the document carefully.
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data

mysqldump -uroot -p --triggers --routines --events --master-data=2 --single-transaction --databases cdb1 cdb2 cdb3 > coredbs.sql

Alternative option avoid interruption in the master server :
Take backup from a slave which is not used by application. Setup replication from slave to the reporting server we configure now. (log-slave-updates should be set to True for this). Later you can change replication master to the actual master server by stopping the slave for a small duration.
ie, Stop the slave; get the binary log position of the slave; stop the slave in reporting server; change master with the new binary log position and mater as the actual master.

3. Create the replication user for the new slave in the Master database instances with proper privileges. 

CREATE USER 'repl_user'@'repserver.myfirm.com' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'repserver.myfirm.com';


4. Get the binary log position from the dump file.
Binary log position will be there in first section of the dump file OR search for "CHANGE MASTER"  in the dump file

 grep -i "CHANGE MASTER" coredbs.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=2292;

5. Set replication client for different databases with appropriate connection name.

CHANGE MASTER 'core_slave' TO MASTER_HOST="repserver.myfirm.com", MASTER_USER="repl_user", MASTER_PASSWORD="password", MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=2292

Where core_slave is the connection name which is used to identify the connection. It can be any string of your choice.
Do same (step 4 and 5)  for the messaging server as well.

6. Set schemas to be replicated. 

In our case, we are replicating all databases from 'core' and only one out of three databases from 'messaging'. So we will set to replicate only one database that is mdb1 to be replicated from messaging_slave where messaging_slave is the connection name for the messaging slave.

Edit /etc/my.cnf file to add the following.

messaging_slave.replicate_do_db=mdb1

If you just want to ignore only one schema from one server, then we can use the below command.
messaging_slave.replicate_ignore_db=mdb1

Restart the mysql instance to make the changes effect.

service mysql restart

7. Start the slaves.

MariaDB [(none)]> start all slaves;

MariaDB [(none)]> show all slaves status \G

You are done. You can see the output with all slaves status

MariaDB [(none)]> show all slaves status \G
*************************** 1. row ***************************
              Connection_name: core_slave
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: coredb.myfirm.com
                  Master_User: repl_userl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 2292
               Relay_Log_File: mysql_relay-core_slave.000015
                Relay_Log_Pos: 526
        Relay_Master_Log_File: mysql-bin.000015
             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: 2292
              Relay_Log_Space: 526
              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: 101
               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
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 1256
    Slave_received_heartbeats: 1
       Slave_heartbeat_period: 30.000
               Gtid_Slave_Pos:
*************************** 2. row ***************************
              Connection_name: messaging
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: messaging_db.myfirm.com
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.014108
          Read_Master_Log_Pos: 9973820
               Relay_Log_File: mysql_relay-shared.005953
                Relay_Log_Pos: 8624388
        Relay_Master_Log_File: mysql-bin.014108
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mdb1
          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: 9973820
              Relay_Log_Space: 40135315
              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: 102
               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
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 1175801050
    Slave_received_heartbeats: 59
       Slave_heartbeat_period: 30.000
               Gtid_Slave_Pos:
2 rows in set (0.00 sec)

MariaDB [(none)]>


To understand more commands on how to manage the multi source replication
http://alidba.blogspot.com/2017/08/Maria-commands.html

To setup monitoring in zabbix for multisource replication
http://alidba.blogspot.com/2017/08/multisource-replication-zabbix.html