Mysql: Re-assign host access permission to MySQL user

If you have several thousand MySQL users with access from a specific host.
The problem is that now if you are going to have two machines (more in the future) which will need to use the same account to access each of their databases ….

For reference, the solution is:


1
2
3
UPDATE mysql.user SET host = '10.0.0.%' WHERE host = 'AAA' AND user != 'root';
UPDATE mysql.db   SET host = '10.0.0.%' WHERE host = 'AAA' AND user != 'root';
FLUSH PRIVILEGES;

OR


1
2
3
4
5
6
7
UPDATE mysql.user SET Host = '%' WHERE Host = '192.168.0.%' AND User = 'binaryop_binary';
UPDATE mysql.db   SET Host = '%' WHERE Host = '192.168.0.1' AND User = 'binaryop_binary';

UPDATE mysql.user SET Host = '%' WHERE Host = '192.168.0.%' AND User = 'binaryop_forums';
UPDATE mysql.db   SET Host = '%' WHERE Host = '192.168.0.1' AND User = 'binaryop_forums';

FLUSH PRIVILEGES;

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Scroll to top