Home / server / Questions / 75422
Accepted
imaginative
Asked: 2009-10-17 17:55:55 +0800 CST

mysql best practices with replication of the mysql db (grants storage)

  • 772

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?

mysql

3 Answers

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

    • 2
  2. Best Answer

    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.

    • 2

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

    • 1