Please change the directive to one of the following:
replicate_wild_ignore_table=phpmyadmin.%
or
replicate_ignore_db=phpmyadmin.%
mysql database
No need to do mysql. Why? Doing GRANT
and REVOKE
commands will bypass replicate_wild_ignore_table=mysql.%
because the SQL does not explicitly mention mysql schema tables.
This will get by replicate_wild_ignore_table=mysql.%
:
GRANT ALL PRIVILEGES ON *.* to rolando@locahost;
This will get caught by replicate_wild_ignore_table=mysql.%
:
INSERT INTO mysql.user VALUES (...);
If you want to keep replicate_wild_ignore_table=mysql.%
, I suggest the following:
SET sql_log_bin = 0; INSERT INTO mysql.user VALUES (...);
This will prevent the SQL from being recorded in the master's binary logs. Consequently, all SQL executed in the DB Session after SET sql_log_bin = 0;
will not replicate.
information_schema database
As far as the information_schema database, mysqld uses it to monitor database metadata. Each is unique to the MySQL instance. They never replicate intrinsically because you have the option to keep different table on Master and Slave. If the information_schema
was replicated, then creating replication schemes such as
would be impossible for Slaves to handle.
SUMMARY
Doing
replicate_wild_ignore_table=phpmyadmin.%
or
replicate_ignore_db=phpmyadmin.%
should be all you need. Notwithstanding, make sure any SQL that prepends phpmyadmin to all it table names may still slide by if the default database is not phpmyadmin
.
https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html describes the supported variations in table definitions.
For one, it appears that this is supported with statement-based replication:
When using statement-based replication, a simple rule of thumb to follow is, “If the statement run on the master would also execute successfully on the slave, it should also replicate successfully”.
With row-based replication, only certain type conversions are supported, which are affected by the slave_type_conversions
variable. The key quotes according to my reading are:
[Conversions are supported between] any of the string types CHAR, VARCHAR, and TEXT, including conversions between different widths.
However:
[Row-based] Replication between columns using different character sets is not supported.
Best Answer
I have read it here: https://www.percona.com/blog/2012/02/02/stop-delete-ignore-on-tables-with-foreign-keys-can-break-replication/
The details of how DELETE IGNORE can be unsafe for replication is shown in that blog.
The solution to work around the issues to do either one of:
Don't use DELETE IGNORE
Use Row-Based Replication by setting binlog-format=ROW