Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

is there a best practice to ensure that privileges do not get replicated to mysql slave servers? the problem is, there a maintenance users in the mysql table that have different passwords on each box. things like startup/shutdown scripts use these tables. when you replicate from a master, it changes the password and breaks things.

what are best practices to ensure that the actual mysql db (that contains grants, users, hosts) does not break because of replication?

do you typically replicate privileges over to the slaves as well?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

Don't use replicate-do-db and replicate-ignore-db, because those options are dangerous. look at this article.

Just use --replicate-wild-ignore-table=mysql.% option.

I would reccomend you to set --replicate-wild-ignore-table=mysql.user - this skip only password and global grants replication, while table and column level grants are still replicated.

You can also use SET SQL_LOG_BIN=0 to disable logging statements for current session, eg. when you create new user or change a password.

share|improve this answer

The FAQ explains:

Start the server with the --replicate-wild-ignore-table=mysql.% option.

You can also use this as a my.cnf option.

Typically we do replicate GRANTs. because we often want slaves to behave as identical read-only copies of the master. This way our applications can switch seamlessly between DB servers for RO operations. You just have to account for this when developing other utilities and pay close attention to what your GRANTs are permitting, especially with regards to hostnames.

share|improve this answer

On the slave you have more options to filter the data. There are replicate-do-db and replicate-ignore-db options which and there are also replicate-do-table, replicate-ignore-table, replicate-wild-do-table and replicate-wild-ignore-table options which can be used to filter out the content you don't want to replicate. I hope it helps.

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.