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!