Connect to a MySQL/MariaDB database with PHP on a Cloud Server running Linux

Learn how to connect to a MySQL/MariaDB database using PHP on a Cloud Server running Linux. This tutorial will cover connecting to a database on the same server as the PHP application, as well as how to connect to a database on a different Cloud Server.

Requirements

  • A Cloud Server running Linux (any distribution).
  • LAMP stack (Apache, MySQL/MariaDB, and PHP) installed and running.
  • If you have a firewall, you will need to allow access to port 3306 on the MySQL/MariaDB server.
Cloud Backup powered by Acronis
Mitigate downtime with total workload protection
  • Automatic backup and easy recovery
  • Intuitive scheduling and management
  • AI-based threat protection

Create an example database

For the purpose of this tutorial, we will use a test database. If you wish to create one for testing, log in to the MySQL/MariaDB client:

mysql -u root -p

Create the test database:

CREATE DATABASE phptest;

Switch to the test database:

USE phptest;

Create a basic table:

CREATE TABLE employees (id char(1), name varchar(40));

Add two employees to the table:

INSERT INTO employees VALUES (1, "Alice");
INSERT INTO employees VALUES (2, "Bob");

Use SELECT * FROM employees; to verify that the records were created correctly:

MySQL [phptest] SELECT * FROM employees;
+------+-------+
| id   | name  |
+------+-------+
| 1    | Alice |
| 2    | Bob   |
+------+-------+
2 rows in set (0.00 sec)

Create a MySQL/MariaDB user

You will need to create a MySQL/MariaDB user in order to generate login credentials for the PHP script. On the database server, log in to the MySQL/MariaDB client:

mysql -u root -p

The following command will create a user and grant the user permissions for the database your script will access:

GRANT ALL ON [database name].* TO [username]@[location] IDENTIFIED BY '[password]';

Replace:

  • [database name] with the name of your database.
  • [username] with the username you want to create for your database.
  • [location] with the location of the PHP script. If the PHP script and the MySQL database are on the same server, use localhost. Otherwise, use the IP address of the server where the PHP script is located.
  • [password] with a strong password for this user.

For example, to create a user phpuser with password Ig86N3tUa9, located on the same server as the MySQL database, with access to the database phptest, the command is:

GRANT ALL ON phptest.* TO phpuser@localhost IDENTIFIED BY 'Ig86N3tUa9';

To create a second user phpuser2 which will connect from a server with IP address 192.168.0.1 the command is:

HiDrive Cloud Storage
Store and share your data on the go
  • Store, share and edit data easily
  • ISO-certified European data centres
  • Highly secure and GDPR compliant

Create an example PHP script

For the purpose of this tutorial, we will use a test PHP script to connect to the test database. If you wish to create one for testing, create a file /var/www/html/phpconnect.php and open it for editing with the command:

sudo nano /var/www/html/phpconnect.php

Put the following content into this file:

<html>
<head>
<title>Test PHP Connection Script</title>
</head>
<body>
<h3>Welcome to the PHP Connect Test</h3>
<?php
$dbname = 'phptest';
$dbuser = 'username';
$dbpass = 'password';
$dbhost = 'location';
$connect = mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to connect to '$dbhost'");
mysql_select_db($dbname) or die("Could not open the database '$dbname'");
$result = mysql_query("SELECT id, name FROM employees");
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf("ID: %s  Name: %s <br>", $row[0], $row[1]);
}
?>
</body>
</html>

Update the username, password, and host name as needed for testing. For example, to test with the script on the same server as the database, the connection information will be:

$dbuser = 'phpuser';
$dbpass = 'Ig86N3tUa9';
$dbhost = 'localhost';

To test with a script located on a different server from the database (IP address 192.168.0.2), the connection information will be:

$dbuser = 'phpuser2';
$dbpass = 'Rq53yur62I';
$dbhost = '192.168.0.2';

Troubleshoot a remote MySQL/MariaDB connection

If your script fails to connect from a different server, consult the following steps to help troubleshoot the error. More details may also be logged on the server's /var/log/httpd/error_log.

User information

If you have problems connecting to a MySQL/MariaDB database on a different server, first verify the following:

  • Are you using the correct MySQL/MariaDB username and password?
  • Does this username have the correct location set?

MySQL only allows users to log in from the location specified when the account is created. For example, if you create a user with the following statement:

GRANT ALL ON phptest.* TO phpuser@localhost IDENTIFIED BY 'Ig86N3tUa9';

This user will ONLY be able to connect to MySQL/MariaDB from the same server where the database is located (localhost). This user will not be able to connect to MySQL/MariaDB from a different server.

Command line connection test

If this information is correct, you can test the connection from the command line. On the server where the script is located, connect to the remote MySQL/MariaDB database with the command:

mysql -u [username] -h [host server IP address] -p

For example, to connect to a database on 192.168.0.2 with username phpuser2 the command is:

mysql -u phpuser2 -h 192.168.0.2 -p

If the connection works, you will be logged in to the MySQL/MariaDB client on the remote server.

Firewall

Verify that all relevant firewall rules have been updated to allow TCP/UDP traffic on port 3306 for the database server.

Note that all Cloud Servers are affected by the default Firewall Policy which is controlled from the Cloud Panel.

Was this article helpful?