Bulk update Access Hosts when IP is changed

Wanabo

Wanabo

Verified User
Joined
Jan 19, 2013
Messages
339
Unfortunately my ip address changed. Now I have to manually edit Access Hosts for about 60 databases in DA.:(

I searched for a bulk solution and thought I had found one in this post: Remote mySQL server and local host

This UPDATE db SET Host='1.2.3.4' WHERE Host='localhost' looked promising.
I ran UPDATE db SET Host='new_ip' WHERE Host='old_ip' and indeed old IP's where replaced with the new IP.

But unfortunately I still could not connect with my new IP address to the databases. Restarted mysqld, no success. Rebooted my VPS, no success. So there must be something else too that need to be changed.

In DA the new ip adress is correctly shown under Access Host. When I remove the Access Host and add it again all is working fine.

Any help is appreciated.


Hint!!!! To make life easier please consider to support dyndns and simular services as Access Host. This way an IP change does not produce extra work.
 
Dynamic DNS provide you the ability to know the host and connect to.. the reverse DNS of your IP will never match the DynDNS Host, so, your request is about no-sense, cause your rDNS depend on your internet carrier.

Regarding the edit you made in the SQL for Access Host i wanna ask, did you check if manually change just one DB Allowed Host by phpMyAdmin does actually work?

Regards
 
You're probably right about dynamic dns, but when things will work with a database update I don't need dynamic dns.

I tried your suggestion to manually change the ip addres in phpMyAdmin. This also results in not being able to connect to the database.
 
I think so. But I think it has to do with the privileges. They are not set correctly when updating table db.
I mean "someuser" is not set correctly (privileges) but "someuser_sometable" is set correctly.
 
Ive tryed to edit an IP on phpMyAdmin and i did read the query it does, apparently, it remove the old privileges and create a new one, dont update it, so, maybe you should try to edit from phpMyAdmin, check the query, copy them and re-arrange them to your use.

Regards
 
Thanks for the help, but I think it's easier and safer to use DA to update Access Hosts.
 
You must log in or register to reply here.
Back
Top