Reassign host authority to MySQL user

Question

I have several thousand MySQL users configured to access a specific host. The problem is that now I will have two machines (in the future) that will need to use the same account to access each of their databases.

I would like a quick and easy (as automated as possible) way to start and modify the host part of each user account in accordance with the internal network pattern. For example:

'bugsy' @ 'internalfoo' has access to the database 'bugsy'.

I want to now allow access to errors from anywhere on the internal network

'bugsy' @ '10 .0.0.% 'has access to the bugsy database.

Answer 1

For reference, the solution is:

UPDATE mysql.user SET host = '10.0.0.%' WHERE host = 'internalfoo' AND user != 'root'; UPDATE mysql.db SET host = '10.0.0.%' WHERE host = 'internalfoo' AND user != 'root'; FLUSH PRIVILEGES; 

Answer 2

The accepted answer only renamed the user, but the privileges remained.

I would recommend using:

 RENAME USER 'foo'@'1.2.3.4' TO 'foo'@'1.2.3.5'; 

According to MySQL documentation :

USER RECIPIENT leads to the fact that the previous user privileges belong to those that belong to the new user.

Answer 3

More general answer

 UPDATE mysql.user SET host = {newhost} WHERE user = {youruser} 

Answer 4

I didn’t have to do this, so take it with salt and a lot of help from “test, test, test”.

What happens if (in a safe test environment) you directly modify the Host column in the mysql.user and possibly mysql.db ? (For example, with the update statement.) I do not think MySQL uses the user host as part of the password encoding (the PASSWORD function does not assume that it does), but you will have to try to be sure. You may need to issue the FLUSH PRIVILEGES command (or stop and restart the server).

For some storage systems (for example, MyISAM), you may also need to check / change the .frm file for any types created by the user. The .frm file stores the qualifier, including the qualifier node. (I had to do this when moving databases between hosts, where there was an incorrect configuration, due to which the wrong host was recorded ...)