How to connect to a MySQL database: A Step-by-Step Instruction

Connecting to a remote MySQL database allows you to work with its contents similarly to local databases. You can edit and read information over the Internet from any computer, regardless of your location.

In this article, we will consider how to connect to MySQL in several ways. For this purpose, we will use Hostman cloud databases. Launching a database will take a couple of minutes.

Connection Methods

There are several options to connect; which one to choose is up to you:

MySQL command line. Included with the MySQL server.
GUIs like phpMyAdmin, Sequel Pro, MySQL Workbench, etc.

In all of the above methods, you can connect via a secure channel thanks to an SSL certificate.

Host IP address

If you use a cloud database, you can find the required IP address in your control panel in the "Databases" section.

If the database is hosted on a server (i.e. it is not a cloud database), the required IP is the IP address of your server.

Number of database connections available

The allocated amount of RAM limits the number of users or applications working simultaneously with the database. You can change your Hostman database plan to scale it according to your requirements.

Connecting from different operating systems

Let's consider how to connect to the MySQL database from different operating systems. The commands will look like this:

Windows

mysql --host=<host> \
      --port=3306 \
      --user=<user_name> \
      --password \
      --database=<database_name> \
      --ssl-mode=disabled

Linux

mysql --host=<host> \
      --port=3306 \
      --user=<user_name> \
      --password \
      --database=<database_name> \
      --ssl-mode=disabled

Connecting with program code

Python

import mysql.connector
cnx = mysql.connector.connect(user='<user_name>', password='<password>',
                              host='<host>',
                              database='<database_name>')
cnx.close()

PHP

<?php 

 $dbh = new PDO('mysql:host=<host>; port=3306; dbname=<database_name>', '<user_name>', '<password>');

Node.JS

const mysql = require("mysql2");
const connection = mysql.createConnection({
     port: "3306",
     user: "<user_name>",
     host: "<host>",
     database: "<database_name>",
     password: "<password>"
});

Connecting via phpMyAdmin

Let's see how to connect to MySQL Server through the phpMyAdmin GUI. You only need to enter a few new lines in the configuration file located at /etc/phpmyadmin/config.inc.php:

     $cfg['Servers'][$i]['auth_type'] = 'cookie';
     $cfg['Servers'][$i]['host'] = '<host>';
     $cfg['Servers'][$i]['connect_type'] = 'tcp';
     $cfg['Servers'][$i]['port'] = '3306';
     $cfg['Servers'][$i]['compress'] = false;
     $cfg['Servers'][$i]['extension'] = 'mysqli';
     $cfg['Servers'][$i]['AllowNoPassword'] = false;

The Hostman platform has ready-made instances of phpMyAdmin and Adminer. All you have to do when using them is to enter connection details. Note that they work only with white IP addresses.

Conclusion

You can work with a remote database with or without cryptographic data protection. It depends on your task. Connection is possible both manually from Linux and Windows operating systems and automatically if the application has the appropriate code. The question of how to connect to a local MySQL server has the same solution. The only difference is the address that you need to specify in the commands.

How to Connect to a MySQL Database