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 from IONOS
Make costly downtime a thing of the past and back up your business the easy way!
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 with IONOS!
Based in Europe, HiDrive secures your data in the cloud so you can easily access it from any device!
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.
Related articles
"Error establishing a database connection" MySQL/MariaDB error
Learn how to fix "Error establishing a database connection” for MySQL/MariaDB on a Linux Server. With this guide you can easily fix the error for MySQL and MariaDB.
Use PHP to Retrieve Information From a MySQL/MariaDB Database
PHP is designed to easily integrate into a website. One of the most common uses for PHP is to take content from a database and output it on an HTML page. This tutorial will cover how to connect to a MySQL/MariaDB database, pull out information from a simple table, and display it in a simple HTML table.
Fix a MySQL/MariaDB “Too many connections” error
Learn how to solve the MySQL/MariaDB “Too many connections” error, which occurs when all available MySQL/MariaDB connections are in use. This error may occur in a script which connects to MySQL/MariaDB, or on a webpage which is generated (in whole or in part) from elements provided by a MySQL/MariaDB database. Read on for more.
kentohShutterstock MariaDB vs. MySQL
MySQL is the most used database management system in the world. This open source software has been available for productive use since 1995 and is generally considered a standard solution for users of relational databases. This could change in the near future. MariaDB, a community-developed fork of MySQL is catching up on its predecessor and is consequently becoming an increasingly attractive…
RDVectorShutterstock How to import a MySQL/MariaDB Database
Learn basic MySQL/MariaDB disaster recovery and preparedness: How to import databases from an exported MySQL dump file.
