Linux MySQL Connect: the tutorial

You can easily connect your MySQL or MariaDB database to a Linux cloud server to run your applications more flexibly. With the right identifiers and a suitable configuration, the MySQL connection under Linux is simple and quick.

Linux MySQL Connect: what are the prerequisites?

• A Cloud server running under Linux (any compatible distribution).
• An installed and operational LAMP stack (Apache, MySQL/MariaDB and PHP).
• If a firewall is enabled, ensure that access to port 3306 is open for the MySQL/MariaDB server.

Linux MySQL Connect: step-by-step guide

Databases are often linked to Cloud Linux servers to provide flexible and scalable management. This allows multiple web services or applications to access the same data without requiring separate local installations.

A cloud server also facilitates database maintenance, backups and security updates.

In this tutorial, we show you how to configure a MySQL or MariaDB database on a Linux server and connect it to a PHP script.

Step 1: Create a sample database

For this tutorial, we will use a test database. To create it, start by logging in:

Create the test database with the following command:

Now move on to the test database:

Now create a simple example table. In this example, we will create a table intended to record employees:

CREATE TABLE employes (ID INT, Nom VARCHAR(40));

Now add two employees to the table:

INSERT INTO employes VALUES (1, 'Alice');
INSERT INTO employes VALUES (2, 'Bob');

To check if the records were created correctly, you can use the following SQL query:

The output should look like this:

+------+-------+
| ID   | Nom  |
+------+-------+
| 1    | Alice |
| 2    | Bob   |
+------+-------+
2 rows in set (0,002 sec)

Step 2: Create a MySQL/MariaDB user

To allow secure access to the MySQL/MariaDB database and prevent unauthorized access, create a new dedicated user.

Then reconnect to the database server using the MySQL or MariaDB client:

The following command creates a user and grants them permissions for the database we created in the first step:

CREATE USER [NomUtilisateur]@[Emplacement] IDENTIFIED BY [MotDePasse];
GRANT ALL PRIVILEGES ON [NomBaseDeDonnees].* TO [NomUtilisateur]@[Emplacement];
FLUSH PRIVILEGES;

Replace the following in the command above:

• [NomBaseDeDonnees] : the name of your database.
• [NomUtilisateur] : the name of the user you want to create.
• [Emplacement] : the location of the PHP script. If the PHP script and the MySQL database are on the same server, use localhost. Otherwise, provide the IP address of the server hosting the PHP script.
• [MotDePasse] : a secure password for your user.

For example, to create a named user phpuser with password Ig86N3tUa9located on the same server as the MySQL database and having access to the database phptestrun the following command:

CREATE USER 'phpuser'@'localhost' IDENTIFIED BY 'Ig86N3tUa9';
GRANT ALL PRIVILEGES ON phptest.* TO 'phpuser'@'localhost';
FLUSH PRIVILEGES;

To create a second named user phpuser2which connects from a server with the IP address 192.168.0.1the command is as follows:

CREATE USER 'phpuser2'@'192.168.0.1' IDENTIFIED BY 'Rq53yur62I';
GRANT ALL PRIVILEGES ON phptest.* TO 'phpuser2'@'192.168.0.1';
FLUSH PRIVILEGES;

Step 3: Create a PHP Script

To test the connection between your PHP application on the Linux server and the database, we will create a small test script. This script connects to the test database, reads data from a table and displays it in the browser. This verifies that the user, password, and host are configured correctly and that the database is accessible.

Start by creating the test script:

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

Next, insert the following content into the file:

Welcome to the PHP Connect Test
";
}
mysqli_close($connect);
?>

Replace NomUtilisateur, MotDePasse, Emplacement And NomBaseDeDonnees by the corresponding data. For example, if you want to test a local connection, that is, when the script and the database are on the same server, use the following configuration:

$dbname="phptest";
$dbuser="phpuser";
$dbpass="Ig86N3tUa9";
$dbhost="localhost";

To test a remote connection, when the script is on a server other than the database, assign the following values ​​to the variables:

$dbname="phptest";
$dbuser="phpuser2";
$dbpass="Rq53yur62I";
$dbhost="192.168.0.2";

Troubleshooting Remote MySQL/MariaDB Connection

If your PHP application or other script cannot connect to the MySQL/MariaDB database from a remote server, there are several possible causes. The following solutions can help you identify and correct the problem.

Solution 1: Check the user and their permissions

If you fail to connect to a MySQL/MariaDB database hosted on another server, first check the following:

• Are the MySQL/MariaDB username and password correct?
• Does the user have the correct access location configured?

By default, MySQL and MariaDB only allow connections from the host defined during user creation. For example, if the user was created this way:

CREATE USER 'phpuser'@'localhost' IDENTIFIED BY 'Ig86N3tUa9';
GRANT ALL PRIVILEGES ON phptest.* TO 'phpuser'@'localhost';
FLUSH PRIVILEGES;

in this case the connection will only work on the same server where the database is running (localhost). A script hosted on another server therefore requires a user configured with the remote IP address or wildcard % (to allow all hosts).

Solution 2: Test the command line connection

Before troubleshooting the PHP script, first test the connection directly from the MySQL command line. On the server where the script is running, use the following command:

mysql -u [NomUtilisateur] -h [Emplacement] -p

To establish a connection to a database on 192.168.0.2 with username phpuser2the command is as follows:

mysql -u phpuser2 -h 192.168.0.2 -p

If the connection is successful, this command will open a session in the MySQL/MariaDB client of the remote server.

Solution 3: Firewall

Verify that the firewall rules allow TCP/UDP traffic on database server port 3306. Keep in mind that all Cloud servers apply a firewall policy managed via the Cloud Panel by default.