You can easily connect to MySQL Linux or MariaDB Linux with a Cloud Server, allowing you to run applications flexibly. With the right access credentials and configuration, successfully connecting to MySQL Linux is quick and straightforward.

Managed Databases
Time-saving database services
  • Enterprise-grade architecture managed by experts
  • Flexible solutions tailored to your requirements
  • Leading security in ISO-certified data centers

Requirements

  • A cloud server running Linux (any distribution).
  • LAMP stack (Apache, MySQL/MariaDB, and PHP) installed and active.
  • If you are using a firewall, you must allow access to port 3306 on the MySQL/MariaDB server.

How to connect to MySQL Linux

Databases are often connected to Linux cloud servers to run applications flexibly and at scale. This setup allows multiple services or web applications to access the same data without requiring separate local installations. A cloud server also simplifies database management, backups, and security updates. In this tutorial, we’ll show you how to set up a MySQL or MariaDB database on a Linux server and connect it using a PHP script.

Step 1: Creating a sample database

For the purposes of this tutorial, we will use a test database. To create one, first log in:

mysql -u root -p

Create the test database with the following command:

CREATE DATABASE phptest;

Now switch to the test database:

USE phptest;

Create a simple example table here. In our example, we’ll choose a table that stores employees:

CREATE TABLE employees (id INT, name VARCHAR(40));

Now add two employees to the table:

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

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

SELECT * FROM employees;

The output should look like this:

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

Step 2: Creating a MySQL/MariaDB user

To enable authenticated access while protecting the database from unauthorized connections, you now need to create a MySQL/MariaDB user. To do this, log in to the database server again using the MySQL/MariaDB client:

mysql -u root -p

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

CREATE USER [Username]@[Location] IDENTIFIED BY [Password];
GRANT ALL PRIVILEGES ON [DatabaseName].* TO [Username]@[Location];
FLUSH PRIVILEGES;

Replace the following placeholders in the command above:

  • [DatabaseName] with the name of your database.
  • [Username] with the name of the user 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 secure password for your user.

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

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

To create a second user named phpuser2 who connects from a server with the IP address 192.168.0.1, use the following command:

CREATE USER 'phpuser2'@'192.168.0.1' IDENTIFIED BY 'Rq53yur62I';
GRANT ALL PRIVILEGES ON phptest.* TO 'phpuser2'@'192.168.0.1';
FLUSH PRIVILEGES;
Compute Engine
The ideal IaaS for your workload
  • Cost-effective vCPUs and powerful dedicated cores
  • Flexibility with no minimum contract
  • 24/7 expert support included

Step 3: Creating a PHP script

To verify that your PHP application can connect to the database on your Linux server, you can create a small test script. This script connects to the test database, retrieves data from a table, and displays it in the browser. This allows you to confirm that the user, password, and host are configured correctly and that the database is accessible.

First, create the test script:

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

Next, insert the following content into the file:

<html>
<head>
<title>Test PHP Connection Script</title>
</head>
<body>
<h3>Welcome to the PHP Connect Test</h3>
<?php
$dbname = [DatabaseName];
$dbuser = [Username];
$dbpass = [Password];
$dbhost = [Location];
// Create connection
$connect = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
if (!$connect) {
die("Connection failed: " . mysqli_connect_error());
}
// Fetch data
$result = mysqli_query($connect, "SELECT id, name FROM employees");
while ($row = mysqli_fetch_assoc($result)) {
echo "ID: {$row['id']}  Name: {$row['name']}<br>";
}
mysqli_close($connect);
?>
</body>
</html>
html

Replace Username, Password, Location, and Hostname with your corresponding data.
For example, if you want to test a local connection where the script is on the same server as the database, use:

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

To verify the remote connection with the script, when it is located on a different server than the database, assign the variables the following values:

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

How to troubleshoot a remote MySQL/MariaDB connection

If your PHP application or another script cannot connect to MySQL Linux or connect to MariaDB Linux from another server, various issues may be causing this. The following solutions might help fix the problem:

Solution 1: Check user and permissions

If you have issues when trying to connect to MySQL Linux or connect to MariaDB Linux on another server, first check the following:

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

By default, MySQL/MariaDB allows a user to log in only from the host specified when created. For instance, if your user was created like this:

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

then the login will only work on the same server where the database is running (localhost). A script on a different server must have a user set up for the remote IP address or % (all hosts).

Solution 2: Command line connection test

Before checking the PHP script, you should test the connection directly via the MySQL CLI. On the server where the script runs, use:

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

For example, to connect to a database at 192.168.0.2 using the username phpuser2, the command is:

mysql -u phpuser2 -h 192.168.0.2 -p

If the connection works, this command will log you into the MySQL/MariaDB client on the remote server.

Solution 3: Firewall

Make sure all relevant firewall rules are updated to allow TCP/UDP traffic on port 3306 for the database server. Keep in mind that all cloud servers are subject to the default firewall policy managed through the Cloud Panel.

Go to Main Menu