How to Allow Host to Connect to MySQL Server

Hello Dev, if you’re trying to connect to MySQL server from a remote host, but keep receiving connection errors, you’re not alone. This is a common issue that many developers face. However, don’t worry, in this article we’ll explore different methods to allow a host to connect to a MySQL server.

Method 1: Grant Remote Access to MySQL Server

By default, MySQL server only allows access from the localhost. In order to allow access from a remote host, you need to grant the host access to the server. Here’s how:

Step 1: Log in to MySQL Server

First, log in to your MySQL server by opening your terminal and typing:

mysql -u root -p

Enter your password when prompted.

Step 2: Create a New User Account

Next, create a new user account that will be used to connect to the server from the remote host. Here’s an example:

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

In this example, we’re creating a new user called ‘new_user’ and giving them access from any host (‘%’). Change ‘password’ to a strong password of your choosing.

Step 3: Grant Permissions to the New User

Now, grant permissions to the new user. Here’s an example:

GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'%';

This grants the new user all privileges to all databases on the server from any host. If you want to grant more specific permissions, replace ‘*’ with the name of the database you want to grant access to.

Step 4: Flush the Privileges

After granting permissions, flush the privileges by typing:

FLUSH PRIVILEGES;

This ensures that the server reloads the grant tables and applies the changes.

Step 5: Exit the MySQL Server

Finally, exit the MySQL server by typing:

exit;

Now, the remote host should be able to connect to the MySQL server using the new user account you just created.

Method 2: Edit MySQL Server Configuration File

If you don’t want to create a new user account, you can also edit the MySQL server configuration file to allow remote access. Here’s how:

Step 1: Locate the Configuration File

First, locate the MySQL server configuration file. The file is usually located in the ‘/etc/mysql/mysql.conf.d/’ directory. You can find the exact location by typing:

sudo find / -name my.cnf

This will search for the configuration file and output its location.

Step 2: Edit the Configuration File

Next, open the configuration file in a text editor by typing:

sudo nano /path/to/configuration/file

Find the ‘bind-address’ line in the file and replace ‘localhost’ with ‘0.0.0.0’. This will allow the server to bind to all network interfaces, including the public IP address of the server.

Step 3: Save and Exit the File

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

Step 4: Restart the MySQL Server

Finally, restart the MySQL server by typing:

sudo systemctl restart mysql.service

Now, the remote host should be able to connect to the MySQL server using the server’s public IP address.

FAQs

We hope this article helped you allow a host to connect to a MySQL server. If you have any further questions or run into issues, feel free to leave a comment below.