MySQL administration is one of those things I have to do pretty infrequently, so when I do there’s always something that trips me up and wastes a bit of time.
This week it was granting permissions on a database.
To grant permissions on a database to a user you use something like…
GRANT ALL ON db1.* TO 'user'@'hostname';
Hostname can also be ‘%’, which is a wildcard for any host.
My gotcha was that ‘%’ does not include ‘localhost’, so the locally hosted application couldn’t access the database that I could connect to remotely.
It’s not immediately obvious in the documentation that this is the case. It says that ‘%’ ” applies to user_name for any host in the 192.168.1 class C subnet”, which of course in hind-sight does not include ‘localhost’. While the local machine is in that class-C in most networks, the connection will come via the loopback address, not through an external network, so it’s origin host will not be in the ‘%’ set.
It can seem slightly counter intuitive but it makes sense when you understand a little more about what’s happening.
