0

I have just set up an Ubuntu web server (I'm new to Linux) and after many issues, I have finally got round to setting my website up. However, I cannot connect to the local database using either the IP address or 'localhost'. PHPMyAdmin works absolutely fine, but I can't connect to mysql even when I use the same access details as PHPMyAdmin.

I am connecting with the following PHP code (which worked fine on my laptop and on my other server):

mysqli_connect($server, $username, $password, $database);

I have created a brand new account and granted full permissions to it, but with no luck.

If I use the IP address to connect, I get the following error:

Not connected : Can't connect to MySQL server on '(ip address)' (111)

If I use 'localhost', I get the following error:

Not connected : Access denied for user 'newuser'@'localhost' (using password: YES)

I am 100% sure the password is correct and, seeing as PHPMyAdmin works fine, I am sure the MySQL service is running.

Any help would be appreciated!

EDIT : SELECT * FROM mysql.User;

localhost root *049124FA19B22B4F6343D5C6FB5E31F710CA3AF7 Y Y Y Y Y Y Y Y Y Y Y Y‌​ Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0  
ns3367936 root *049124FA19B22B4F6343D5C6FB5E31F710CA3AF7 Y Y Y Y Y Y Y Y Y Y Y Y‌​ Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0
127.0.0.1 root *049124FA19B22B4F6343D5C6FB5E31F710CA3AF7 Y Y Y Y Y Y Y Y Y Y Y Y‌​ Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 
::1 root *049124FA19B22B4F6343D5C6FB5E31F710CA3AF7 Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y‌​ Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0
localhost N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 ‌​0 NULL 
ns3367936.ip-37-187-88.eu N N N N N N N N N N N N N N N N N N N N N N N N N N ‌​N N N 0 0 0 0 NULL 
localhost debian-sys-maint *7866534019D21D9BA648FD769CFAAD256D2F7438 Y Y Y Y Y Y‌​ Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y N 0 0 0 0 NULL 
localhost phpmyadmin *049124FA19B22B4F6343D5C6FB5E31F710CA3AF7 N N N N N N N N N‌​ N N N N N N N N N N N N N N N N N N N N 0 0 0 0 NULL     
localhost newuser *049124FA19B22B4F6343D5C6FB5E31F710CA3AF7 Y Y Y Y Y Y Y Y Y Y ‌​N Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 0 0 0 NULL
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
thebronsonite
  • 129
  • 2
  • 12
  • Please run `SELECT * FROM mysql.User;` in phpmyadmin and paste the output. – dotancohen Feb 17 '14 at 16:17
  • 1
    PHPMyAdmin uses local host. Do you have user accounts flagged for access (commonly via IP unless using SSH/SSL)? – Marc Feb 17 '14 at 16:18
  • I managed to fix this by stopping the mysql service "sudo service mysql stop" and starting it again like this "sudo mysqld_safe --skip_grant-tables". Is it OK to run mysql in safe mode, or is there a permanent fix? datamafia, no, I don't think I do. – thebronsonite Feb 17 '14 at 16:19
  • Possible duplicate http://stackoverflow.com/questions/1420839/cant-connect-to-mysql-server-error-111 – CBusBus Feb 17 '14 at 16:24
  • @SOliver Hi, the problem isn't really the difference between using the ip address and localhost, but rather that it won't connect at all (unless I'm missing something!). – thebronsonite Feb 17 '14 at 16:31
  • Are you sure you are connecting to the same MySQL server that phpMyAdmin is connecting to. Whats in your `$server, $username, $database` – RiggsFolly Feb 17 '14 at 16:37
  • @RiggsFolly I'm pretty sure, as it works when I run mysql in safe mode. $server = localhost, $username = newuser (or phpmyadmin), $database = test (I can access the test database through phpmyadmin). – thebronsonite Feb 17 '14 at 16:39
  • You should not consider that a solution. `ITS VERY INSECURE` as it allows anybody access with no password – RiggsFolly Feb 17 '14 at 16:44
  • @RiggsFolly OK, thank you. I'm taking it out of safe mode now. – thebronsonite Feb 17 '14 at 16:48
  • Are you running mysql and the apache server all on the same machine, or is the mysql server on another machine? – RiggsFolly Feb 17 '14 at 16:56
  • @RiggsFolly It's all on the same machine. – thebronsonite Feb 17 '14 at 17:05
  • Is this any help http://stackoverflow.com/questions/19219338/mysql-error-111-cant-connect-to-server – RiggsFolly Feb 17 '14 at 17:10
  • @RiggsFolly Not really. I've commented out the bind-address line (there was no skip-networking line), but I still get the same error. – thebronsonite Feb 17 '14 at 17:25
  • @RiggsFolly Sorry, that did make a slight difference. I now get the same error when using the ip address, rather than localhost (access denied). – thebronsonite Feb 17 '14 at 17:26

1 Answers1

0

You get the error while trying to login from 127.0.0.1 because you have not created an account for 'newuser' that is allowed access MySQL from an ip address of 127.0.0.1.

See your 'root' accounts for example.

There are 4 accounts for 'root' i.e. root@localhost, root@127.0.0.1, root@::1 and root@ns3367936.

A MySQL account requires the Username and a host. You only have 'newuser@localhost` and you need newuser@127.0.0.1 as well, and maybe root@::1 also if you have IPV6 running

Of course you coudl also edit the existing 'newuser' account and wilcard the host so that that user can login from any host.

Host in this case I always find is a misleading name, in fact it means the location that the user is trying to login from. So if you wanted to allow the 'newuser' account to login from a remote location like home, you could also add a newuser@11.22.33.44 account, assuming your home IP address was 11.22.33.44

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149