How to allow remote connection to mysql

 

This article explains how to set up a user on your MySQL server in order to connect to a MySQL database remotely.

Many websites and applications start off with their web server and database backend hosted on the same machine. With time, though, a setup like this can become cumbersome and difficult to scale. A common solution is to separate these functions by setting up a remote database, allowing the server and database to grow at their own pace on their own machines.

In order to perform these steps, you must have local server access to log in as the root MySQL user.

Create the remote connection

To create a remote connection:

1. On your database server, as a user with root privileges, open your MySQL configuration file.

To locate it, enter the following command:

 mysql --help
The location displays similar to the following:
 
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

2. Navigate to the line that begins with the bind-address directive.

If it exists, change the value as follows.

If it doesn’t exist, add it anywhere except the [mysqld] section.

bind-address            = 0.0.0.0

3. Save your changes to the configuration file and exit the text editor.

4. Restart the MySQL service:

# systemctl restart mysql

Grant access

Perform the following steps to grant access to a user from a remote host:

1. Log in to your MySQL server locally as the root user by using the following command:

 # mysql -u root -p

You are prompted for your MySQL root password.

2. Use a GRANT command in the following format to enable access for the remote user. 

# GRANT ALL ON <database_name>.* TO <database_username>@<remote_server_ip_address> IDENTIFIED BY '<database_user_password>';

Verify database access

To test the connection remotely, access the MySQL server from another Linux server.

# mysql -u <database_username> -h <remote_server_ip_address> -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.6.26 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> _