Mysql – Should I exclude thesql database from multi master replication

MySQLreplication

Running mysql in a two master replication setup. There are many databases and they are all replicating correctly including the mysql database itself.

Is it safe to replicate mysql or should I exclude it via the my.cnf ?

Best Answer

Since the mysql schema is usually manipulated with GRANT and REVOKE commands, there is no need to let the mysql schema replicate. Why ?

Someone could hack privileges in by means of INSERT commands into mysql.user.

Such hacks are possible because I wrote about it before

Oct 28, 2011 : MySQL: trying to create root user
Sep 12, 2011 : How do you gain access to embedded MySQL database via localhost command line?
Mar 06, 2011 : Installing default MySQL Tables in Windows (mysql_install_db)

If these hacks were done on a Master, a Slave might intercept them. The new grants on the Slave would not be active until someone restarted mysqld on the Slave or ran FLUSH PRIVILEGES; on the Slave.

So, to answer your question: exclude it via the my.cnf

Related Solutions

MySQL Master/Slave Replication – Structure Not Replicating

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

The replicate-* options are restrictive by their presence. The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."

In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing. It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.

MySQL Master-Master Replication – Handling Duplicate Key Issues

If you want to safely skip duplicate key errors, please add this to my.cnf on both Masters

[mysqld]
slave-skip-errors=1062

and restart mysql

Please never run

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1000;

as this may skip real data the Slave should have.

UPDATE 2014-06-16 20:51 EDT

Please look at the query you are replicating

UPDATE dle_post SET comm_num=comm_num-1 where id='353'

If comm_num is 0, you cannot decrement 0 if it is BIGINT UNSIGNED. The minimum BIGINT UNSIGNED value is 0. So, subtracting 1 from a 0 is not allowed. Storing a negative number is not allowed, either. That explains the error.

UPDATE 2014-06-16 21:51 EDT

You could have repair all those zero comm_nums by setting them to 1 trillion

UPDATE dle_post SET comm_num = 1000000000000 where comm_num = 0;

This is just my suggestion.You could set it to whatever value you see fit.

You should be really looking closer to find these situations.