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
