Home / server / Questions / 601662
Accepted
Milos Cuculovic
Asked: 2014-06-04 23:43:52 +0800 CST

MySQL - Does the change in MySQL master db password affect on slave db?

  • 772

I want to change the MySQL DB password on the master db. My question is: do we have to care for the slave DB when changing the master database password?

mysql

3 Answers

  1. Best Answer

    From the docs

    User privileges are replicated only if the mysql database is replicated. That is, the GRANT, REVOKE, SET PASSWORD, CREATE USER, and DROP USER statements take effect on the slave only if the replication setup includes the mysql database.

    If you are replicating all databases, but do not want statements that affect user privileges to be replicated, set up the slave not to replicate the mysql database, using the --replicate-wild-ignore-table=mysql.% option. The slave recognizes that privilege-related SQL statements have no effect, and thus it does not execute those statements.

    See Section 16.4.1.16, “Replication of the mysql System Database”, for more information.

    • 2

  2. it really DEPENDS. :)

    according to the doc

    MySQL 5.1.14 and later. Data modification statements made to tables in the mysql database are replicated according to the value of binlog_format; if this value is MIXED, these statement are replicated using the row-based format. However, statements that would normally update this information indirectly—such GRANT, REVOKE, and statements manipulating triggers, stored routines, and views—are replicated to slaves using statement-based replication.

    MySQL 5.1.13 and earlier. User privileges are replicated only if the mysql database is replicated. That is, the GRANT, REVOKE, SET PASSWORD, CREATE USER, and DROP USER statements take effect on the slave only if the replication setup includes the mysql database.

    for our instance, mysql 5.5 will automatically CREATE / DROP USER xxx@'xxx' on the slave even we are not doing replication of the mysql system database.

    • 2

  3. it depends whether or not you are slaving the "mysql" database, the internal mysql db where all user permissions are kept. if the replication user has been given access to "*.*", then all the tables in "mysql.*" should replicate over, including "mysql.user", where passwords are stored.

    when changes to this tables are made, a "flush privileges" should be executed, so it may be necessary to run this on the slaves? i always specify specific db's for replication, so i have not tested this.

    • 1