mysql – global foreign_key_checks = 0 on the RBR slave

CREATE SCHEMA counts;
CREATE TABLE `user` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR (50) NULL DEFAULT NULL,
`address` VARCHAR (50) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE SCHEMA data;
CREATE TABLE `project` (
`id` INT (11) NOT NULL,
`account_user_id` INT (11) NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_user` (` user_id`),
CONSTRAINT `fk_user` FOREIGN KEY (` account_user_id`) REFERENCES
`accounts`.`user` (` id`)
) ENGINE = InnoDB;

I have line-based replication slaves (RBRs), which should not contain the "accounts" schema. I'm using replicate_wild_ignore_table & # 39; accounts & # 39; & # 39;% & # 39; So that only the schema & # 39; data & # 39; exist.

The only problem is that the replication stops when we insert in data.project with: "Error_code: 1452; HA_ERR_NO_REFERENCED_ROW;" since the accounts.user.id does not exist on the slave.

Defining "set global set foreign_key_checks = 0" permanently on slaves seems like a good solution in this case. My thought is:

  • Key constraints are applied to the master, where writes and updates occur. The rejected declarations do not appear in the binlog / log are not replicated to the slave.
  • Values ​​for each inserted / updated record are explicitly defined by line-based replication. If an FK causes an update, it will be captured in another binlog statement, which will be replicated to the slaves.
  • Therefore, I do not have to worry that the slave checks the FK constraints.

Is there a fault in my thinking? Has anyone tried that? I would appreciate any idea.

Other options I've considered:

  1. set slave-skip-errors = 1452

  2. Master and slave – removes the foreign key constraint

    • Not ideal; we want to ensure the integrity of the data on the master.
  3. Slave – removes the foreign key constraint

    • Also not ideal; There are several slaves to maintain, and others may be needed. There are several cross-schema FKs that can add more in the future. I fear that it will become difficult to manage.