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;

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

*

Scroll to top