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.

I have a prod database, say appdb, and I only care about the data in that db. Is it safe to declare replicate-ignore-db for each of mysql,information_schema,performance_schema,test, as some tutorials show? I define my own analytics users on the slave and manage permissions there. I don't want extra users or tuning artefacts from master, in fact I may tune the slave separately... However, I cannot recite the perils of replicate-do-db and replicate-ignore-db in my sleep, as some answers here advise, so am wondering what else should I care about in this case!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

There's no need to avoid replicating performance_schema or information_schema -- they are not real databases with real tables, they are generated by server internals to interact via SQL, but you'll notice from their table definitions that they aren't normal. They can't be altered, modified, dropped, created, updated, inserted into, or deleted from... and they don't cause replication events. The performance_schema does not have to be configured the same on master and slave.

The mysql database, you need to allow to replicate, because the users on the master need to be valid users on the slave -- otherwise, you will encounter potential problems with definitions and execution of views, triggers, stored procedures, stored functions, and events, all of which have "definer" attributes that need the defining users to exist on the slave in many cases.

However, you are free to create additional users on the slave as long as you don't, later, generate events on the master that would conflict with those users.

A slave needs to be run in global read_only mode, which prevents users with write permissions granted to them from actually modifying data on the slave, unless they have the SUPER privilege, which bypasses this restriction (which is also why I, as a DBA, have two accounts... I connect to slaves with my account that lacks SUPER unless I have a specific reason to do otherwise).

There is no "tuning" that I can think of that's stored in the system tables in the mysql schema.

All of these things in mind, I'd suggest that the best/easiest/safest approach is to let replication do it's thing and cross any problematic bridges when you come to them... which is typically far less likely when you replicate everything than if you try to be selective.

share|improve this answer
    
Basically I had a sessions database on the master which went out of sync and generated replication errors. I excluded it and things were peachy again. While googling I've found the exlusions above, ignoring the pre-existing standard daatbases, so thought might as well ignore them. So you're saying only mysql has to be replicated for sure? –  Alexy Feb 3 at 3:28

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.