Connect to remote mysql db from php

#1

This follow up may be a bit late but since I am having a similar problem let me continue this thread with a description of the problem I am facing getting the same error. I am attempting to connect from a web server on FEDORA 33 to mysql running on UBUNTU. I have a Java app that connects just fine from the web server box so credentials are known and I am using the same. Questions I have relate to the pre-requisites that need to be in place on each side for the standard PHP-connect via mysqli to work. For the Java app I needed the driver .jar as part of the build path. What has to be in place for PHP as the error now is only telling me “Connect Error (2002) Permission denied”? This is what the .php file has:

<?php
$mysqli = new mysqli('remote IP', 'user', 'pwd', 'db name');
/*
 * This is the "official" OO way to do it,
 * BUT $connect_error was broken until PHP 5.2.9 and 5.3.0.
 */
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
echo 'Success... ' . $mysqli->host_info . "\n";

$mysqli->close();
?>

Any help would be appreciated, I am looking into SELinux issue on rhw mysql server box but not sure if that is going in the right direction.
Thanks much.

Failed Connection error using cpanel in mysql
#2

This looks relevant. Was you java app running through a different web server?

#3

Actually the Java app is just running as an executable .jar file on the same box the web-server is on, in other words not a web app, no browser involved. Just going against the remote instance of the UBUNTU based mysql instance.

#4

Are you running SELinux?

#5

On the mysql server \s returns saying SSL: not in use. I have mysql-client installed on the client box and am able to connect to the remote mysql server. In other words I created the remote user for the mysql server and remote login via mysql-client also worked. I have only tried the various samples that check a valid connection via PHP, but so far nothing has given me a response indicating it worked. I am holding off with ssl configuration/activation since it should initially work between the two PCs here in the lab. Now that I have mysql-client installed on the client box is there anything in there that needs to be referenced by PHP in order for it to have access to the proper protocol? I am asking because I have primary Java background and am drawing comparisons to .jar resources, sorry.

#6

Nothing I know of; I think the problem is actually due to your web server rather than PHP. These two are separate; your web server accepts requests then runs a PHP script to resolve them. This means the PHP script - and therefore, the MySQL connection - are being run as the server user, which may not have the permissions you expect.

If you run your PHP script from the command line, does it work?

#7

I tried running the script from the command line and sure enough it provided confirmation regarding the ssl suspition, here is what was returned: [klaus@fedora html]$ php sqlConn.php
PHP Warning: mysqli::__construct(): (HY000/3159): Connections using insecure transport are prohibited while --require_secure_transport=ON. in /var/www/html/db_connection.php on line 8.
here is line 8 from the db_connection.php file: $mysqli = new mysqli($dbhost, $dbuser, $dbpass,$db) or die(“Connect failed: %s\n”. $conn -> error);
The question now is, is there anything in this line to include something like
“–require_secure_transport=OFF” ? This then appears to be a default configuration on the mysql server. I will try to find out if it can be changed or if I have to change configuration on the client side to comply with the secure transport ON requirement… Thank you skawid !

#8

Question: For remote connections to mysql server version 8.0.22 on Ubuntu is it possible to connect without ssl? All I have found so far is directives to prepare PHP for use with certs and provide those references in the connection script. It would be nice to know if that is the only way now. Thanks.

#9

I do not see to get the format right to include code here, I have uploaded the php script that runs fine from the command line. It returns the proper results from the database. however running from the web server is not working at this time. I will however try to run from the web-server/browser after logging in with the credentials required by the mysql server and report back.

#10

Not sure how to upload the sample script here, so if it looks crappy I apologize:

Remote DB connection... <?php /*$conn = OpenCon();*/ $dbhost = "10.0.0.13"; $dbuser = ""; $dbpass = ""; $db = ""; $Conn = mysqli_connect($dbhost, $dbuser, $dbpass, $db) or die("Connect failed: %s\n". $Conn -> error); /* * This is the "official" OO way to do it, * BUT $connect_error was broken until PHP 5.2.9 and 5.3.0. */ if(!$Conn) { echo "Error: Unable to connect to MySQL." . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } echo "Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL; echo "Host information: " . mysqli_get_host_info($Conn) . PHP_EOL;

$query = “select * from Members”;
if ($result = $Conn->query($query)) {
while ($row = $result->fetch_row()) {
printf("%s (%s,%s,%s)\n", $row[0], $row[1], $row[2], $row[3]);
}
mysqli_close($Conn);
}
?>

This runs fine from the command line. having the file located at “/var/www/html/sqlConn.php” and directing the browser to this location (localhost/sqlConn.php) only shows up as: “Connect failed: %s” I also created a user matching the here used credentials and logged in with them, however no difference. I wonder what is missing, any ideas?

#11

Op, do not post your database credentials for the whole world to see. I fixed it for you.

Sponsor our Newsletter | Privacy Policy | Terms of Service