Mysql – thesql best practices with replication of the thesql db (grants storage)

MySQLmysql-replicationmysql5

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?

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.

Related Solutions

MySQL Replication – How Fast is MySQL Replication Performance?

MySQL replication happens as close to real-time as possible, as limited by disk and network I/O. The slaves open a socket to the master, which is kept open. When a transaction occurs on the master, it gets recorded in the binlog, and is simply replayed on the slave(s). If the socket between master and slave is interrupted, the binlog is replayed for the slave upon the next successful connection.

Multi-master replication does the same thing, but in both directions.

Some basic calculations will assist you in making a better determination of your bandwidth needs.

Average transaction size * number of slaves * updates/minute = bandwidth needed

Hope this helps.

Mysql – Restart thesql replication after sql_error

You can use the following commands (on mysql prompt):

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;

The value 1 represents the number of statements to skip. You can do it repeatedly until the replication is fixed. You can see this page.

Best Answer

Related Solutions

MySQL Replication – How Fast is MySQL Replication Performance?

Mysql – Restart thesql replication after sql_error

Related Topic