I always seem to get in a tangle with installing and setting up mySql.
The essential problem seems to be that mySql wants to refer to the local machine as 'localhost', and 127.0.0.1, and 'chris'. Thus when I try and add a user it seems that the user gets added with privileges for one (or some) but not all of 'localhost', and 127.0.0.1, and 'chris' and then doesn't work because I need to acces the database as one of the other host names.
Adding a user as 'user'@* seems to be even worse and doesn't work at all.
I also seem to be having trouble changing the password for the mySql super user, I tried doing it via phpmyadmin and that seems to have done nothing, I can't quite fathom the syntax for doing it in mySql from the command line in the mysql front-end.
I *do* know SQL fairly well and usually once I have the password/host chaos sorted out mysql works well for me but this part of it really stinks IMHO when you're working on localhost (or is it 127.0.0.1 or is it chris?). It's fine when working remotely, there's only one possible name for the place where the database is.
Chris G wrote:
I always seem to get in a tangle with installing and setting up mySql.
The essential problem seems to be that mySql wants to refer to the local machine as 'localhost', and 127.0.0.1, and 'chris'. Thus when I try and add a user it seems that the user gets added with privileges for one (or some) but not all of 'localhost', and 127.0.0.1, and 'chris' and then doesn't work because I need to acces the database as one of the other host names.
Adding a user as 'user'@* seems to be even worse and doesn't work at all.
MySQL host (and strings in queries) wildcards are of the form "%", so you would need to add something like:
"Grant all on database.* to 'user'@'%' identified by 'password'"; "Flush privileges" (depending upon version, may not be neccesary now)
You also need to be aware that in the table mysql.user, there are a set of top-level access rules - there will generally be several inserted by default for 'users'@'localhost'. This can cause some fun as if you grant new privileges to user@'%' and then connect locally, this rule can appear *after* the default, meaning it is never reached: so either delete the row with the localhost host, or overrule with an additional grant for 'user'@'localhost' (to which 127.0.0.1 should be an alias, and possibly "chris" depending upon how that's resolved). You can see what's already there with a "select * from mysql.user;"
On Mon, Nov 24, 2008 at 12:24:00PM +0000, simon ransome wrote:
Chris G wrote:
I always seem to get in a tangle with installing and setting up mySql.
The essential problem seems to be that mySql wants to refer to the local machine as 'localhost', and 127.0.0.1, and 'chris'. Thus when I try and add a user it seems that the user gets added with privileges for one (or some) but not all of 'localhost', and 127.0.0.1, and 'chris' and then doesn't work because I need to acces the database as one of the other host names.
Adding a user as 'user'@* seems to be even worse and doesn't work at all.
MySQL host (and strings in queries) wildcards are of the form "%", so you would need to add something like:
"Grant all on database.* to 'user'@'%' identified by 'password'"; "Flush privileges" (depending upon version, may not be neccesary now)
You also need to be aware that in the table mysql.user, there are a set of top-level access rules - there will generally be several inserted by default for 'users'@'localhost'.
Yes, I seem to remember this being a problem. In fact I seem to remember that using '%' as the hostname doesn't work at all in some cases.
This can cause some fun as if you grantnew privileges to user@'%' and then connect locally, this rule can appear *after* the default, meaning it is never reached: so either delete the row with the localhost host, or overrule with an additional grant for 'user'@'localhost' (to which 127.0.0.1 should be an alias, and possibly "chris" depending upon how that's resolved). You can see what's already there with a "select * from mysql.user;"
OK, thanks, I'll try '%' as host and delete all other entries for the user and see if I get what I want.
As I understand it, confirmed here[1], MySQL interprets "localhost" and "127.0.0.1" differently; the former uses Unix sockets if available where the latter uses TCP/IP.
That ought not affect the privileges, as far as I know, but it might have some bearing that I can't think of?
Also, what does /etc/hosts have for 127.0.0.1? Some distros used to have localhost.localdomain rather than localhost, and changing them to localhost has been known to assist[2]
[1] http://books.google.co.uk/books?id=BL0NNoFPuAQC&pg=PA533&lpg=PA533&a...
[2] http://books.google.co.uk/books?id=s_87mv-Eo4AC&pg=PA282&lpg=PA282&a...
On Mon, Nov 24, 2008 at 03:32:56PM +0000, Mark Rogers wrote:
As I understand it, confirmed here[1], MySQL interprets "localhost" and "127.0.0.1" differently; the former uses Unix sockets if available where the latter uses TCP/IP.
That ought not affect the privileges, as far as I know, but it might have some bearing that I can't think of?
Yes, it's confusing to say the least though. I don't really understand where the three entries in the mysql users table even come from, I didn't put them there explicitly but I always seem to end up with three entries for the mySql root/superuser for 127.0.0.1, for localhost and for chris (which is the name of the machine).
Also, what does /etc/hosts have for 127.0.0.1? Some distros used to have localhost.localdomain rather than localhost, and changing them to localhost has been known to assist[2]
Ubuntu 8.10 seems to set it to:-
127.0.0.1 localhost
-
Chris G -
Mark Rogers -
simon ransome