Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.%
share|improve this question

migrated from stackoverflow.com Nov 12 '12 at 14:01

This question came from our site for professional and enthusiast programmers.

1  
Instead of this why do not you try to add to my.cnf replicate-do-db? –  ravnur Nov 10 '12 at 9:53
    
because mysqlperformanceblog.com/2009/05/14/… –  clarkk Nov 10 '12 at 10:30

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

I think it all depends on why you are replicating. If you want a strict 1:1 copy of your db server then you don't need any ignore_table entries. If you just want to make some subset of your DBs/tables available then you will need to add entries to filter them in/out.

But as you pointed out in the included link - this creates a risk of inadvertently breaking replication. Since every binlog statement gets copied and run on the replication slave if it encounters a reference that hasn't been specifically dealt with and the referred table isn't available it will error out. Fixing this is usually trivial but it does require attention.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.