User Changes not Replicating¶
Length: 518 words; Published: April 1, 2014; Updated: September 12, 2019; Category: Schema & SQL; Type: Troubleshooting
When a DBA make changes to data in the mysql database—that’s the database that contains user names and privileges—if done in a straightforward way (e.g., with an UPDATE
statement), they’re not replicated to the other nodes in the cluster. This can cause problems for users, as well as frustrate the DBA.
Scenario
Suppose you made some changes to database users, but when you check the other nodes, you find they have not replicated to the cluster. For instance, suppose you want to change the host address from which a user, bob may access the cluster. You log into a node and use the UPDATE
statement to change the Host
column in the mysql.user
table for Bob.
UPDATE mysql.user
SET Host = '12.0.12.34'
WHERE User = 'bob'
AND Host = '12.0.56.78';
FLUSH PRIVILEGES;
When that’s finished, you ask Bob to log in from the new host. He tries to connect to the cluster, but through a different node than the one on which you entered the update. He can’t log in, even thought his local IP address is 12.0.56.78
. He gets this error message:
Enter password:
ERROR 1045 (28000): Access denied
for user 'bob'@'12.0.56.78'
(using password: YES)
You then tell him to log into the cluster through the same node through which you made the update. He is then successful. The problem is that the change to the user
table didn’t replicate to the other nodes.
Galera replicates only InnoDB tables. Therefore, any tables you create should not use other storage engines. However, the system tables contained in the mysql
database use the MyISAM storage engine. This includes the user
table.
Since these tables are not replicated, you shouldn’t change them directly by using SQL statements like INSERT
, UPDATE
, or DELETE
. Instead, you have to use SQL statements like CREATE USER
, RENAME USER
, DROP USER
, and GRANT
. If you have difficulty remembering or are unsure which you may not use, just remember that if you have to execute FLUSH PRIVILEGES
for the change to take effect, you’re changing the data directly and it won’t be replicated.
Solution
While direct modifications to the system tables do not replicate, you may use DDL statements replicate at the statement level. Changes made to the system tables in this manner will be made to the entire cluster.
Therefore, to make changes to the mysql.user
table, use statements like CREATE USER
, RENAME USER
DROP USER
. And use the GRANT
statement to set user privileges. So instead of using the UPDATE
statement in the previous example, you should have used the RENAME USER
statement like so:
RENAME USER 'bob'@'127.0.12.34'
TO 'bob'@'127.0.56.78';
This change the host address for the user in a way that will replicate through the cluster. The user may now access the database from the given IP address through any node in the cluster.
If you have a need to change something directly using a statement such as UPDATE
, to circumvent the usual methods, you must execute the statement on each node, including FLUSH PRIVILEGES
.
Related Documents