Mariadb replication - Ignore users changes



  • I posted this question on ServerFault but was told to ask it here.

    I need to set up multi-source replication (3 masters and 1 slave). I'd like to avoid replicating users' changes on the slave because it breaks replication.

    I've followed this : https://serverfault.com/questions/865487/mysql-replication-failing-on-mysql-user-changes but it didn't seem to work.

    I've tried different configurations but none works.

    • Ignore table mysql on the slave :
    replicate_ignore_db=mysql
    
    • Ignore table mysql for each connection on the slave:
    master1.replicate_ignore_db=mysql
    master2.replicate_ignore_db=mysql
    master3.replicate_ignore_db=mysql
    
    • Ignore the mysql table in the master's binlog :
    binlog-ignore-db=mysql
    

    I'm working with Mariadb 10.6 on Debian 9. The master's binlog format is ROW.

    I'm still getting errors in the replication regarding users changes. How can I make the replication ignore the users' changes ?



  • As user changing SQL like ALTER USER etc is replicated as a statement, to prevent the replication of those, use SET SQL_LOG_BIN=0 in the session where the SQL is executed. Sorry, replication filtering won't help.

    ref: https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#sql_log_bin .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2