UPWORK/ODESK guru: Tips and Tricks for clients

skype: upwork.link , email: info@upwork.link

Mysql: Re-assign host access permission to MySQL user

without comments

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;

Written by spectre

December 21st, 2014 at 3:03 pm

Posted in Mysql

Leave a Reply

*