Description:
Hi Team,
We are using Multi Master - Single Slave replication setup for Backup. For this operation we setup for below configurations:
DB Details :
10 Masters - M1,M2,....M10
3 Slaves - S1, S2, S3
Replication Scenarios:
* S1 Slave is acting as a slave of all Master DB's M1, M2,.....M10. This replication setup done through channel replication setup.
* In S1 also we created some DB (D1, D2, D3) to consolidate all Master DB data into Single DB to run some scripts.
* For S2, S3 is the Slave of S1. For this scenario S1 act as Master.
For S1 my.conf:
server-id =
master-info-repository=TABLE
relay-log-info-repository=TABLE
log-slave-updates=true
binlog-do-db=D1 (consolidate data through script)
binlog-do-db=D2(consolidate data through script)
binlog-do-db=D3(consolidate data through script)
log-bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 1
max_binlog_size = 100M
replicate-do-db =db_name_1(M1) (channel m1)
replicate-do-db = db_name_2(M2) (channel m2)
replicate-do-db = db_name_3(M3) (channel m3)
replicate-do-db = db_name_4(M4) (channel m4)
replicate-do-db = db_name_5(M5) (channel m5)
replicate-do-db = db_name_6(M6) (channel m6)
replicate-do-db = db_name_7(M7) (channel m7)
replicate-do-db = db_name_8(M8) (channel m8)
replicate-do-db = db_name_9(M9) (channel m9)
replicate-do-db = db_name_10(M10) (channel m10)
In Slave S2,S3 my.conf:
server-id =
master-info-repository=TABLE
relay-log-info-repository=TABLE
replicate-do-db =db_name_1(M1) (channel m1)
replicate-do-db = db_name_2(M2) (channel m2)
replicate-do-db = db_name_3(M3) (channel m3)
replicate-do-db =D1 (from Slave S1) (channel s1)
replicate-do-db =D2(from Slave S1) (channel s1)
replicate-do-db =D3(from Slave S1) (channel s1)
Binlog Format is ROW.
Question:
* In S2, S3 for channel name s1 replication struck at a particular position. When i checked the position we will get the below error.
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 2576897841, event_type: 49
ERROR: Could not read entry at offset 65482: Error in log format or read error.
how can fix this issue?
* In S1 we will enable all the DB's binlog-do-db. It's cause replication channels for m1,m2,m3,m4....m10. The second behind master is always keep increasing. How can we avoid such a Performance issue? and how will improve the Performance?
* In S1 is any way to Enable bin log options with different locations with each db and How can we replicate that bin logs s1 from s2, s3 slaves?
How to repeat:
For S1 my.conf:
server-id =
master-info-repository=TABLE
relay-log-info-repository=TABLE
log-slave-updates=true
binlog-do-db=D1 (consolidate data through script)
binlog-do-db=D2(consolidate data through script)
binlog-do-db=D3(consolidate data through script)
log-bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 1
max_binlog_size = 100M
replicate-do-db =db_name_1(M1) (channel m1)
replicate-do-db = db_name_2(M2) (channel m2)
replicate-do-db = db_name_3(M3) (channel m3)
replicate-do-db = db_name_4(M4) (channel m4)
replicate-do-db = db_name_5(M5) (channel m5)
replicate-do-db = db_name_6(M6) (channel m6)
replicate-do-db = db_name_7(M7) (channel m7)
replicate-do-db = db_name_8(M8) (channel m8)
replicate-do-db = db_name_9(M9) (channel m9)
replicate-do-db = db_name_10(M10) (channel m10)
In Slave S2,S3 my.conf:
server-id =
master-info-repository=TABLE
relay-log-info-repository=TABLE
replicate-do-db =db_name_1(M1) (channel m1)
replicate-do-db = db_name_2(M2) (channel m2)
replicate-do-db = db_name_3(M3) (channel m3)
replicate-do-db =D1 (from Slave S1) (channel s1)
replicate-do-db =D2(from Slave S1) (channel s1)
replicate-do-db =D3(from Slave S1) (channel s1)
Binlog Format is ROW.
Description: Hi Team, We are using Multi Master - Single Slave replication setup for Backup. For this operation we setup for below configurations: DB Details : 10 Masters - M1,M2,....M10 3 Slaves - S1, S2, S3 Replication Scenarios: * S1 Slave is acting as a slave of all Master DB's M1, M2,.....M10. This replication setup done through channel replication setup. * In S1 also we created some DB (D1, D2, D3) to consolidate all Master DB data into Single DB to run some scripts. * For S2, S3 is the Slave of S1. For this scenario S1 act as Master. For S1 my.conf: server-id = master-info-repository=TABLE relay-log-info-repository=TABLE log-slave-updates=true binlog-do-db=D1 (consolidate data through script) binlog-do-db=D2(consolidate data through script) binlog-do-db=D3(consolidate data through script) log-bin = /var/log/mysql/mysql-bin.log expire_logs_days = 1 max_binlog_size = 100M replicate-do-db =db_name_1(M1) (channel m1) replicate-do-db = db_name_2(M2) (channel m2) replicate-do-db = db_name_3(M3) (channel m3) replicate-do-db = db_name_4(M4) (channel m4) replicate-do-db = db_name_5(M5) (channel m5) replicate-do-db = db_name_6(M6) (channel m6) replicate-do-db = db_name_7(M7) (channel m7) replicate-do-db = db_name_8(M8) (channel m8) replicate-do-db = db_name_9(M9) (channel m9) replicate-do-db = db_name_10(M10) (channel m10) In Slave S2,S3 my.conf: server-id = master-info-repository=TABLE relay-log-info-repository=TABLE replicate-do-db =db_name_1(M1) (channel m1) replicate-do-db = db_name_2(M2) (channel m2) replicate-do-db = db_name_3(M3) (channel m3) replicate-do-db =D1 (from Slave S1) (channel s1) replicate-do-db =D2(from Slave S1) (channel s1) replicate-do-db =D3(from Slave S1) (channel s1) Binlog Format is ROW. Question: * In S2, S3 for channel name s1 replication struck at a particular position. When i checked the position we will get the below error. ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 2576897841, event_type: 49 ERROR: Could not read entry at offset 65482: Error in log format or read error. how can fix this issue? * In S1 we will enable all the DB's binlog-do-db. It's cause replication channels for m1,m2,m3,m4....m10. The second behind master is always keep increasing. How can we avoid such a Performance issue? and how will improve the Performance? * In S1 is any way to Enable bin log options with different locations with each db and How can we replicate that bin logs s1 from s2, s3 slaves? How to repeat: For S1 my.conf: server-id = master-info-repository=TABLE relay-log-info-repository=TABLE log-slave-updates=true binlog-do-db=D1 (consolidate data through script) binlog-do-db=D2(consolidate data through script) binlog-do-db=D3(consolidate data through script) log-bin = /var/log/mysql/mysql-bin.log expire_logs_days = 1 max_binlog_size = 100M replicate-do-db =db_name_1(M1) (channel m1) replicate-do-db = db_name_2(M2) (channel m2) replicate-do-db = db_name_3(M3) (channel m3) replicate-do-db = db_name_4(M4) (channel m4) replicate-do-db = db_name_5(M5) (channel m5) replicate-do-db = db_name_6(M6) (channel m6) replicate-do-db = db_name_7(M7) (channel m7) replicate-do-db = db_name_8(M8) (channel m8) replicate-do-db = db_name_9(M9) (channel m9) replicate-do-db = db_name_10(M10) (channel m10) In Slave S2,S3 my.conf: server-id = master-info-repository=TABLE relay-log-info-repository=TABLE replicate-do-db =db_name_1(M1) (channel m1) replicate-do-db = db_name_2(M2) (channel m2) replicate-do-db = db_name_3(M3) (channel m3) replicate-do-db =D1 (from Slave S1) (channel s1) replicate-do-db =D2(from Slave S1) (channel s1) replicate-do-db =D3(from Slave S1) (channel s1) Binlog Format is ROW.