MySQL Replication – How to Ignore Tables

MySQLreplication

When setting up master/slave replication in Mysql, do you then need to ignore any tables in /etc/mysql/my.cnf or should replicate_wild_ignore_table be leaved out under normal circumstances?

This is what I'm doing…

my.cnf

replicate_wild_ignore_table = mysql.%,information_schema.%,phpmyadmin.%

Best Answer

Please change the directive to one of the following:

replicate_wild_ignore_table=phpmyadmin.%

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.%

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.

Related Solutions

Mysql – Replication Broken, List Out of Sync Tables

With regard to the first point

check (and alert) replication is broken?

just check the following

MYSQL_CONN="-uroot -ppassword"
SLAVESTATUS_OUTPUT=/tmp/ShowSlaveStatus.txt
mysql ${MYSQL_CONN} -ANe"SHOW SLAVE STATUS\G" > ${SLAVESTATUS_OUTPUT}
IO_RUNNING=`grep "Slave_IO_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`
SQL_RUNNING=`grep "Slave_SQL_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`

Just check for both IO_RUNNING and SQL_RUNNING being Yes.

If either value is No, check the error message.

You could check for other things such as Seconds_Behind_Master ( Does `Seconds_Behind_Master` show exact Slave Lag from Master? )

With regard to the second point

get a list of tables that are out of synch across various databases?

The best tool in the world for this is pt-table-checksum. Please read the documentation on it because the output will be all the tables in the specified databases. You will have to parse the text output to compare the checksum values between Master and Slave.

I wrote a part post about using this tool:

In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working (This one has shell scripting I made to do the parsing for you)

Mysql – Configured MySQL replication but its not working

What concerns me here is the use of both replicate-do-db on the Slave and binlog-do-db on the Master at the same time.

There is no need to use both. I would remove binlog-do-db from the Master. I would also look over any queries with fully qualified table names. Why ?

If you replicate any SQL use fully qualified table names (dbname.tblname) and you use replicate-do-db, MySQL Replication gets confused if you execute USE dbname.

Percona explained this in conjunction with binlog-do-db and replicate-do-db back in May 2009.

Percona also suggested reading the replication filtering rules thoroughly.