Posted  by 

Cannot Access Mysql Remotely Ubuntu

The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user. You can test this as follows by switching to system root, and then type. On the other hand, if you're still unable to access the database remotely, then something else may be causing the issue. In either case, you may find it helpful to follow our guide on How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04 to set up a more secure remote database configuration. Can’t Connect to MySQL Server Remotely on Ubuntu Last updated on May 2nd, 2020 MySQL by default will not allow incoming remote connections for security reasons. In this article we will edit the MySQL config file mysqld.cnf to allow remote connections. If your Ubuntu server has firewall enabled, then you will want to open connection to the database server Simply run the commands below to open the firewall to client from from the IP address to the port only For example, to open Ubuntu Firewall to allow IP address 192.168.1.2 to connect on port 3306. I just installed Ubuntu 16.04 LTS along with the packages php, mariadb and nginx. I ran mysqlsecureinstallation and changed the root password. Now when I try to login to mysql using the root account while logged in Ubuntu as normal user account I get access denied. When I login using sudo mysql, mysql doesn't even ask me password.

Cannot Access Mysql Remotely UbuntuMay 2, 2016 / MySQL
2 Comments on How to grant MySQL remote access: in Linux and Windows

Firstly, by default MySQL is installed only listening to localhost. It’s closed so that it won’t listen to your public IPs externally. This tutorial works both for Ubuntu, Debian and CentOS. To grant mysql remote access, we need to first of all open the MySQL to listen to external connections, and then, enable the user for remote networks. It’s very easy and simple to allow remote connections in MySQL

Step 1 – Allow remote connections to the MySQL server

Before grant mysql remote access, it’s necessary to open the server port 3306.

First, we will update our system and install Nmap. This utility shows the currently opened ports in the server. In this article, we will show how to allow remote connections both in centos, ubuntu and windows.

Linux Ubuntu/Debian:

In CentOS:

Now we will run nmap to make sure mysql isn’t listening in our public IP. Make sure to replace [server-ip] with your public server IP.

The output should be something like:

As we can see, there is no server daemon opened on port 3306, and we need to configure MySQL to listen in our public IP.

Let’s edit our my.cnf file

Now you must find this line:

And replace with:

Also, make sure this line is commented (with a # in the beginning):

This will tell MySQL to listen in both localhost (127.0.0.1) and in the public IP of the server.

Cannot access mysql remotely ubuntu command

After finishing, type CTRL+X to exit, and Y to save.

Now you must restart the MySQL server. This restart process may be different according to your Linux distro.

Debian 7 and older

Ubuntu 14 and newer

CentOS 6.x and older:

Cannot Access Mysql Remotely Ubuntu

CentOS 7.x and Debian 8:

Let’s run nmap again to confirm if the port 3306 is opened in the public IP:

We can see 3306/tcp open mysql, which means that the MySQL port is opened and waiting for connections.

To test, we will use HeidiQSL, which is a freeware Windows MySQL client, and we will try to connect to our server:

We will get an error message because we haven’t granted the privileges to the user to access remotely yet.

Step 2 – Grant remote access to MySQL users

To solve this issue, we must grant the remote access privileges to our myqsl user. So you must open connect to mysql locally:

Now we will grant the privileges using the command “GRANT ALL PRIVILEGES”. Please note that even if the username is already configured for the localhost access, we will have to set a new password for it to connect remotely. You must replace [username] and [new-password] in this command.

This command will ensure all the IP addresses can connect to the server using this username and password. We will explain how to limit and restrict access to certain IPs or networks.

Now you should be able to connect to the remote MySQL server using the configured username and password.

Grant remote access to specific IPs or networks

To restrict the IP or network, you should replace the % (wildcard) with the IP or block.

Examples:

This will only allow connections from the IP 177.85.98.15

Acura radio unlock codes. This will allow connections from the IP range 177.85.98.1 to 177.85.98.254

Now, this example grant mysql remote access to any hostname ending in *.myprovider.com

To allow access from any network, without restriction, you should keep only the % (wildcard). This will grant mysql remote access to any IP or network.

Grant mysql remote access to specific MySQL databases and tables

To restrict the access to a specific database or table, you should replace the *.* with: [database].[table]

This allows access only to the mydb database, with any table:

This command grants access only to the table clients, inside the mydb database:

If you need to grant access specifically to more than one table, then you should run the command more than once:

Grant option

You can also allow such user to be able to delegate and grant privileges to other users, by adding WITH GRANT OPTION to the end of the command.

It would look like:

If you need to recover the mysql user password, you can also read this article.

Ubuntu Mysql Client

Conclusion

How To Setup Mysql On Ubuntu

It’s simple to allow MySQL remote connections to a database. But be warned that you need to specify the desired hostname or IPs that have access to it. Also, it’s important to allow such connections in port 3306 in your firewall.