A MySQL database server is a relational database management system (RDBMS) that uses SQL (Structured Query Language) for managing and manipulating data.

Now, a remote connection to a MySQL database server is necessary for various scenarios like;

• Distributed Applications: If your application is deployed on multiple servers or locations, and the database server is located separately from the application servers, you would need to connect to the MySQL database remotely. This is common in distributed or cloud-based architectures.

• Remote Development: Developers may need to connect to a MySQL database server remotely when working on a project from a different location. This allows them to access and manage the database from their development environment.

• Third-Party Access: In some cases, third-party applications or services may need to connect to your MySQL database remotely to retrieve or update data. This can be relevant for integration with external systems or services.

• Database Administration: Database administrators (DBAs) often need to connect to MySQL servers remotely to perform maintenance tasks, monitor performance, optimize queries, and manage security settings. Remote access provides flexibility for administrators to manage databases from different locations.

• Backup and Restore: Remote connections are essential when performing database backup and restore operations. Backup utilities and tools often need to connect to the MySQL server from a different machine to create or restore backups.

• Testing and QA: During the testing and quality assurance phase of application development, testing environments may be set up on different servers. Connecting to the MySQL database remotely allows testers to access and manipulate data as needed.

• Business Intelligence (BI) and Reporting Tools: BI tools and reporting applications may need to connect to the MySQL database remotely to generate reports, analyze data, and visualize trends. Remote access enables these tools to fetch data from the central database server.

• Client Applications: Desktop or mobile applications that interact with a MySQL database may be deployed on devices that are not on the same network as the database server. Remote connections enable these client applications to communicate with the database server over the internet.

In this article, we’ll walk through the process of setting up a remote connection to a MySQL server. Establishing remote access is essential for scenarios such as distributed applications, remote development, and third-party access. We’ll cover the necessary steps along with command examples.

MySQL Server Configuration

Before allowing remote connections, ensure that your MySQL server is configured to accept external connections. Edit the MySQL server configuration file, typically named my.cnf or my.ini, depending on your operating system.

sudo nano /etc/mysql/my.cnf

If the above command doesn’t work, you can try this one;

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change the bind-address

The bind-address is a configuration option that specifies the network interface or IP address on which the MySQL server should listen for incoming connections.

The bind-address configuration option is found in the mysqld.cnf configuration file;

So, you have to locate the bind-address parameter and set it to the server’s IP address or 0.0.0.0 to allow connections from any IP address;

bind-address = 0.0.0.0

Save the changes and restart the MySQL server.

# Restart MySQL service
sudo service mysql restart

MySQL User and Privileges Setup

Create a MySQL user account that can connect from a remote host. Replace username and password with your preferred values.

Login into the MYSQL server as the root user

Create a different user that can remotely access the MYSQL server, you need to first log in as root using the following command;

mysql -u root -p

This command specifies the MySQL user (-u root for the root user) and prompts for the password (-p). Press Enter after entering the command.

After pressing Enter, you’ll be prompted to enter the root password for MySQL. Type the password and press Enter. Note that when entering the password, you won’t see characters on the screen for security reasons.

Enter password:

After entering the correct password, you should be logged in as the root user, and you will see the MySQL command prompt.

Verify the Connection

Once logged in, you can verify your connection by checking the MySQL server version or running other SQL queries

-- Check MySQL server version
SELECT VERSION();

This query retrieves the MySQL server version and confirms that you have successfully connected as the root user.

Create a new User

Use the following SQL command to create a new user. Replace new_user with the desired username, and replace password with a strong password for the new user:

CREATE USER 'new_user'@'%' IDENTIFIED BY 'password';

The @'%' part defines the host from which the user is allowed to connect. In this case, % is a wildcard that means the user is allowed to connect from any host. This allows the user to connect remotely from any IP address

Grant necessary privileges

Grant the necessary privileges to the user. The following command grants all privileges on all databases:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

Granting privileges to a MySQL user involves giving the user specific rights and permissions to perform various operations on databases, tables, or other database objects. These privileges control what actions the user can execute, such as querying data, modifying database structures, or managing user accounts. The GRANT statement is used to assign these privileges in MySQL.

Flush privileges for changes to take effect

For changes to take effect, you need to flush privileges using the following command;

FLUSH PRIVILEGES;

In MySQL, the FLUSH PRIVILEGES statement is used to reload the privileges from the grant tables in the MySQL database. When you grant or revoke privileges to a MySQL user using the GRANT or REVOKE statements, the changes take effect immediately for the current session, but they might not take effect for other running sessions until the privileges are flushed.

Exit the MYSQL Shell

To exit the MYSQL terminal, you can use the following command;

EXIT;

Firewall Configuration

Adjust the firewall settings to permit incoming connections on the MySQL port (default is 3306). Use the appropriate command for your firewall management tool, such as ufw or iptables.

sudo ufw allow 3306

Test Remote Connection

From a remote machine, attempt to connect to the MySQL server using the MySQL command-line client. Replace your_server_ip, username, and password with your server’s IP address and the credentials you created.

mysql -u username -p -h your_server_ip

Enter the password when prompted. If successful, you should now have a remote connection to your MySQL server.

Security Considerations

It’s crucial to prioritize security when enabling remote connections. Consider using SSH tunneling, implementing SSL/TLS encryption, and regularly updating passwords to enhance the security of your MySQL server.

Conclusion

Setting up a remote connection to a MySQL server involves configuring server settings, creating user accounts, and adjusting firewall rules. By following these steps and incorporating security best practices, you can ensure a secure and efficient remote connection to your MySQL database for various use cases.