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;
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.
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
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.
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