MySQL Forums
Forum List  »  Replication

mysql 5.7 replication does not work
Posted by: xr fang
Date: July 22, 2021 08:35PM

I need to setup a simple replication for mysql 5.7, here is the my.cnf file:

master: 5.7.18-log running on centos 7.5

server_id = 1
log-bin = mysql-bin
binlog_format = MIXED
binlog_do_db = nnr
binlog_do_db = devmon

slave: 5.7.35-log running on debian 10.10

server_id = 2
log-bin = mysql-bin
binlog_format = MIXED
replicate_do_db = nnr
replicate_do_db = devmon

I followed the procedure given by digitalocean: https://www.digitalocean.com/community/tutorials/how-to-set-up-replication-in-mysql

The problem is, after setup, all seems ok, but there are some error given by SHOW SLAVE STATUS:

Slave_IO_State: Waiting for master to send event
Master_Host: 10.25.33.122
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 90295
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 1829
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: nnr,devmon
Last_Errno: 1142
Last_Error: Error 'TRIGGER command denied to user 'root'@'%' for table 'HB_leaplinker'' on query. Default database: 'nnr'. Query: ... ...
... ...
Last_SQL_Errno: 1142
Last_SQL_Error: Error 'TRIGGER command denied to user 'root'@'%' for table 'HB_leaplinker'' on query. Default database: 'nnr'. Query: ... ...
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: dfb6e681-f733-11ea-be51-525400de8261
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 210723 10:03:45
... ...

My questions are:

1. It seems that there are some errors in replication, how can I figure out where is the problem, can I remove specific statements from binlog so that it can continue?

2. Is there any problem in the my.cnf settings, specifically, what are the default value for binlog_do_db and replicate_do_db? Do I have to set these, or if I omit it just replicate ALL databases?

Thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql 5.7 replication does not work
401
July 22, 2021 08:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.