Php cannot access mysql server on ubuntu server

Question

I have setup the ubuntu server and my php code is establishing a database connection with mysql running on the same server. My credentials for the database are correct, crossed checked it thrice but i am unable to figure out what i a missing. So what i have done so far.

Make sure the mysql server is running.

1. mysql -u root -h 127.0.0.1 -p // I can login into mysql server on ubuntu server.
2. Run this command 'netstat -tulpen' and get this entry tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 107 20280 -

Error on apache when php is trying to access mysql:

PHP Warning: mysqli::__construct(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: YES)

Lastly my database credentials look like this:

// here I have tried 127.0.0.1:3306, localhost as well but it does not seem to work
$dbhost2 = 'localhost:3306'; 
$dbuser2 = 'root';
$dbpass2 = 'somepassword';
$dbname2 = 'my_db_name';

Updated: This is how i am connecting my php code is using the above variables to connect to the database

$DBCONN2 = @mysqli_connect($dbhost2, $dbuser2, $dbpass2, $dbname2) or die('Failed');

Updated 2: There is also another connection being used like this

$DBCONNi = new mysqli($dbhost2, $dbuser2, $dbpass2, $dbname2) or die('Failed');

Answer 1

Try with PDO https://github.com/fxstar/PhpJsCss/blob/master/PDOapi/_PDO_Class.php

    // Mysql connect    
function Conn(){
    try{
        // data from config file globals variables
        global $mysqlhost, $mysqluser, $mysqlpass, $mysqlport, $mysqldb;
        // pdo
        $conn = new PDO('mysql:host='.$mysqlhost.';port='.$mysqlport.';dbname='.$mysqldb.';charset=utf8', $mysqluser, $mysqlpass);
        // don't cache query
        $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        // show warning text
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        // throw error exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // don't colose connecion on script end
        $conn->setAttribute(PDO::ATTR_PERSISTENT, false);
        // set utf for connection utf8_general_ci or utf8_unicode_ci 
        $conn->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'");
        // Buffered query
        // $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true);

        // PDO SSL
        // $conn = new PDO('mysql:host='.$mysqlhost.';port='.$mysqlport.';dbname='.$mysqldb.';charset=utf8', $mysqluser, $mysqlpass,array( PDO::MYSQL_ATTR_SSL_KEY    =>'/path/to/client-key.pem', PDO::MYSQL_ATTR_SSL_CERT=>'/path/to/client-cert.pem', PDO::MYSQL_ATTR_SSL_CA    =>'/path/to/ca-cert.pem'));
        // Or
        // $conn->setAttribute(PDO::MYSQL_ATTR_SSL_KEY => '/path/to/client-key.pem');
        // $conn->setAttribute(PDO::MYSQL_ATTR_SSL_CERT => '/path/to/client-cert.pem');
        // $conn->setAttribute(PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca-cert.pem');           
        return $conn;
    }catch(Exception $e){
        echo "Mysql connection error!!!";
        return 0;
    }
    // $rows = $res->fetchAll(PDO::FETCH_ASSOC);
    // $cnt = $res->rowCount();
    // $id = $this->db->lastInsertId();
    // buffered query
    // $stmt = $db->prepare('select * from foo', array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true)
}

Answer 2

mysql -u root -h 127.0.0.1 -p

...

$dbhost2 = 'localhost:3306';

In MySQL, 'localhost' is not a network address. 'localhost' is interpreted as meaning 'use the defined (AF_UNIX) filesystem socket, not a network socket'. In addition, the authentication for 'localhost' connections is handled separately from network connections.

The path to the filesystem socket is defined in 'my.cnf'. The location of my.cnf is defined when the relevant servers and clients are compiled, but is usually '/etc'

While I've not tried explicitly with mysqlnd, I would expect its behaviour to be consistent with clients using libmysql.

Change your PHP code to use '127.0.0.1' (specifying the default port number 3306 should be redundant)

Answer 3

Try without password or try to add password to mysql root user. ANd see here Warning: mysqli_connect(): (HY000/1045): Access denied for user 'username'@'localhost' (using password: YES)