// Tutorial //

How To Connect to MySQL Managed Database via phpMyAdmin

How To Connect to MySQL Managed Database via phpMyAdmin

The author selected the Diversity in Tech Fund to receive a donation as part of the Write for DOnations program.

Introduction

The phpMyAdmin tool allows users to interact with MySQL database management through a web interface that can handle the administration tasks. This tool provides a web interface to create, store, and edit entire databases created with MySQL. You can use the script interface to run PHP scripts to connect to MySQL databases for customized operations rather than using the command line interface.

In this tutorial, you will install phpMyAdmin and use it to connect to a MySQL Managed Database cluster on your Ubuntu 20.04 server.

Prerequisites

To complete this guide, you will need:

Step 1 — Installing phpMyAdmin and Configuring Apache

In this step, you will install the phpMyAdmin package and the web server necessary to access your MySQL Managed Database.

First, log in to your Ubuntu server and update it with the latest packages:

  1. sudo apt update

Then install phpMyAdmin from the default Ubuntu repositories:

  1. sudo apt -y install phpmyadmin

During the installation process, you will be prompted to choose a web server: Apache or Lighttpd. Select apache2 for this tutorial.

Web server selection when configuring phpMyAdmin

Next, you will be prompted to select Yes or No to use dbconfig-common for setting up the database in the same Ubuntu server.

 selection when configuring phpMyAdmin

Choose No if you are installing phpMyAdmin to connect to MySQL Managed Database and you do not want to install MySQL in the localhost Ubuntu server.

Once the phpMyAdmin package has finished installing, you will update the apache.conf file. Use your favorite text editor to edit the linked file that has been placed in your Apache configuration directory. This example uses nano:

  1. sudo nano /etc/apache2/apache2.conf

Add the following line to the bottom of the file:

/etc/apache2/apache2.conf
...
# phpMyAdmin Configuration
Include /etc/phpmyadmin/apache.conf

This line will set up phpMyAdmin under the Apache web server. Save and close the file.

You have now updated your server by installing phpMyAdmin and configuring Apache. Next, you will configure phpMyAdmin with the MySQL Managed Database credentials.

Step 2 — Configuring phpMyAdmin with MySQL Managed Database

In this step, you will configure phpMyAdmin with your MySQL Managed Database connection details.

Navigate to your MySQL Managed Database panel and find the Connection Details section. Click the Download CA certificate link to download the ca-certificate.crt file from your database page overview tab:

How to download CA-certificate

In a new terminal session from your local machine, copy the certificate file to /etc/phpmyadmin/ on the Ubuntu server:

  1. scp Downloads/ca-certificate.crt root@your-server-ip:/etc/phpmyadmin

Back in the terminal session for your server, open the phpMyAdmin configuration file named config.inc.php:

  1. sudo nano /etc/phpmyadmin/config.inc.php

At the bottom of the config.inc.php file, add the following lines with the Managed Database configuration details:

/etc/phpmyadmin/config.inc.php
...
$i++;
$cfg['Servers'][$i]['host'] = 'your_database_cluster_hostname.b.db.ondigitalocean.com';
$cfg['Servers'][$i]['port'] = '25060';
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['ssl_ca'] = '/etc/phpmyadmin/ca-certificate.crt';

The initial $i++; is a post-increment operator that returns $i, then increments $i by one. This line helps you manage multiple MySQL Managed Databases with phpMyAdmin.

Because phpMyAdmin supports the administration of multiple MySQL servers, the $cfg['Servers'] array has been added, which can contain the login information for different servers.

$cfg['Servers'][$i]['host'] refers to the MySQL Managed Database hostname, and $cfg['Servers'][$i]['port'] is the port number that it calls to (in this case, port 25060).

$cfg['Servers'][$i]['ssl'], which evaluates to true in this circumstance, communicates whether to enable SSL for the connection between phpMyAdmin and the MySQL Managed Database to secure the connection. With that, you also update the $cfg['Servers'][$i]['ssl_ca'] line with the path to the CA file that will connect to the MySQL Managed Database when using SSL.

If you want to add another MySQL Managed Database, you can add lines from $i++; to $cfg['Servers'][$i]['ssl_ca'] in config.inc.php with the appropriate hostname and CA-certificate file.

Save and close the configuration file.

Then restart apache2 with this command:

  1. sudo service apache2 restart

Now you can access the web interface by visiting your server’s public IP address or your domain name followed by phpmyadmin:

http://your_domain_or_ip/phpmyadmin

You will have to supply your MySQL Managed Database username and password to log in to the MySQL Database.

phpMyAdmin login page

Once you have logged in to the database, you will see the following administration screen:

phpMyAdmin MySQL Managed Database page

You can add the IP address of the server where you have installed phpMyAdmin as a trusted source. This modification will limit access to the database to this specific IP address.

Conclusion

You have now configured phpMyAdmin to connect to a MySQL Managed Database cluster on your Ubuntu 20.04 server. Using this interface, you can create databases, users, and tables and perform the usual operations like deleting and modifying structures and data.

You can review other database tutorials, such as securing your phpMyAdmin instance in Step 3 of How To Install and Secure phpMyAdmin on Ubuntu 20.04.

If you’ve enjoyed this tutorial and our broader community, consider checking out our DigitalOcean products which can also help you achieve your development goals.

Learn more here


About the authors
Default avatar
Cloud Support Engineer

Default avatar
Technical Editor

Still looking for an answer?

Was this helpful?
1 Comments

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Wonderful article, thanks muchly!