access database remotely

Question

I have an ubuntu server with mysql installed, at one point I saw the php code running on the server could access mysql but I can't access mysql remotely, for another server or sequal pro.

$ mysql -u root -p
mysql> GRANT ALL on *.* TO 'thomas'@'%';
mysql> exit
$ mysql -u thomas -p
mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for thomas@localhost                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'thomas'@'localhost' IDENTIFIED BY PASSWORD '[ENCRYPTEDPASSWORD]' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
$ mysql -u thomas -p -h [SERVERIP]
ERROR 1045 (28000): Access denied for user 'thomas'@'[SERVERNAME]' (using password: YES)

Update:

I wen't in and dropped all the users that where made (some didn't have passwords) I was going by the mysql.user table and using the drop user command.

CREATE USER 'thomas'@'localhost' IDENTIFIED BY '[PASSWORD]';
GRANT ALL PRIVILEGES ON *.* TO 'thomas'@'localhost' IDENTIFIED BY '[PASSWORD]';
GRANT ALL PRIVILEGES ON *.* TO 'thomas'@'[SERVERIP]' IDENTIFIED BY '[PASSWORD]';
GRANT ALL PRIVILEGES ON *.* TO 'thomas'@'%' IDENTIFIED BY '[PASSWORD]';

the table now looks like this:

+--------------+------------------+
| Host         | User             | 
+--------------+------------------+
| localhost    | root             | 
| localhost    | phpmyadmin       | 
| 127.0.0.1    | root             | 
| ::1          | root             | 
| localhost    | debian-sys-maint | 
| localhost    | thomas           | 
| [SERVERIP]   | thomas           | 
| %            | thomas           | 
+--------------+------------------+

check out this answer: http://stackoverflow.com/a/11225588/1007273 — hovanessyan, Jun 27 '12 at 17:56
Most probably mysqld is not listening on the correct Ethernet interface... — hovanessyan, Jun 27 '12 at 17:57
@hovanessyan The listening **must** be OK, else hew ouldn't get `Access denied` but `Can't connect` — Eugen Rieck, Jun 27 '12 at 17:59
Keep in mind, the password for `thomas@localhost` is not necessarily the same as for `thomas@%`... — Wrikken, Jun 27 '12 at 18:02

Eugen Rieck · Accepted Answer · 2012-06-27T18:34:30.527

2

Well, you have

GRANT ALL PRIVILEGES ON *.* TO 'thomas'@'localhost'

but you are not on localhost. You might want to try

GRANT ALL PRIVILEGES ON *.* TO 'thomas'@'%'

Ofcourse you have to add IDENTIFIED BY 'password' if you so desire

edited Jun 27 '12 at 18:34

answered Jun 27 '12 at 17:58

Eugen Rieck

64,175
10
70
92

I was missing `PRIVILEGES`, but it still doesn't work. No matter what I do, what user I do it under `show grants;` does not come back with anything other than `localhost`. – ThomasReggi Jun 27 '12 at 18:05
I can see that the `mysql.user` is changed but nothing in `show grants;`. – ThomasReggi Jun 27 '12 at 18:29
AFAIK `SHOW GRANTS` will show only the **applicable** grants - so if you are logged in from localhost, you won't see the global privileges. – Eugen Rieck Jun 27 '12 at 18:30
Thanks @Eugen_Rieck makes sense. Any reason why I still can't connect? – ThomasReggi Jun 27 '12 at 18:32
Did you forget to give the `'thomas'@'%'` a password? I updated my answer – Eugen Rieck Jun 27 '12 at 18:35
ok.. i got it working when im ssh'd into the server and I run `mysql -u thomas -p -h [SERVERIP]`. This is progress right? Sequal pro a mysql client still won't connect. – ThomasReggi Jun 27 '12 at 18:48
php won't connect either... =[ – ThomasReggi Jun 27 '12 at 19:48

access database remotely

1 Answers1