Mysql Access Denied For User Password When Prompted

However, in some situations, it is necessary to access the MySQL server from remote location.

For example, when you want to connect to the remote MySQL server from your local system, or when using a multi-server deployment where the application is running on a different machine from the database server.

One option would be to access the MySQL server through SSH Tunnel and another is to configure the MySQL server to accept remote connections.

In this guide, we will go through the steps necessary to allow remote connections to a MySQL server.

Configuring MySQL Server The first step is to set the MySQL server to listen on a specific IP address or all IP addresses on the machine.

If the MySQL server and clients can communicate with each other over a private network, then the best option is to set the MySQL server to listen only on the private IP.

Otherwise, if you want to connect to the server over a public network set the MySQL server to listen on all IP addresses on the machine.

To do so, you need to edit the MySQL configuration file and add or change the value of the bind-address option.

If the address is 0.0.0.0, the MySQL server accepts connections on all host IPv4 interfaces.

If you have IPv6 configured on your system, then instead of 0.0.0.0, use::.

The location of the MySQL configuration file differs depending on the distribution.

In Ubuntu and Debian the file is located at etcmysqlmysql.conf.dmysqld.cnf, while in Red Hat based distributions such as CentOS, the file is located at etcmy.cnf.

Open the file with your text editor: sudo nano etcmysqlmysql.conf.dmysqld.cnf Search for a line that begins with bind-address and set its value to the IP address on which a MySQL server should listen.

By default, the value is set to 127.0.0.1 (listens only in localhost).

In this example, well set the MySQL server to listen on all IPv4 interfaces by changing the value to 0.0.0.0 mysqld.cnf bind-address 0.0.0.0.

Once done, restart the MySQL service for changes to take effect.

Only root or users with sudo privileges can restart services.

To restart the MySQL service on Debian or Ubuntu, type: sudo systemctl restart mysql On RedHat based distributions like CentOS to restart the service run: sudo systemctl restart mysqld Granting Access to a User from a Remote Machine The next step is to allow access to the database to the remote user.

Mysql Access Denied For User Password When Prompted

Log in to the MySQL server as the root user by typing: sudo mysql If you are using the old, native MySQL authentication plugin to log in as root run the command below and enter the password when prompted: mysql -uroot -p From inside the MySQL shell, use the GRANT statement to grant access for the remote user.

TO usernameipaddress IDENTIFIED BY userpassword; Where: databasename is the name of the database that the user will connect to.

For example, to grant access to a database dbname to a user named foo with password mypasswd from a client machine with IP 10.8.0.5, you would run: GRANT ALL ON dbname.

TO foo10.8.0.5 IDENTIFIED BY mypasswd; Configuring Firewall The last step is to configure your firewall to allow traffic on port 3306 (MySQL default port) from the remote machines.

Iptables If you are using iptables as your firewall, the command below will allow access from any IP address on the Internet to the MySQL port.

To allow access from any IP address on the Internet (very insecure) run: sudo ufw allow 3306tcp Allow access from a specific IP address: sudo ufw allow from 10.8.0.5 to any port 3306 FirewallD FirewallD is the default firewall management tool in CentOS.

To allow access from any IP address on the Internet (very insecure) type: sudo firewall-cmd --permanent --zonepublic --add-port3306tcp sudo firewall-cmd --reload To allow access from a specific IP address on a specific port, you can either create a new FirewallD zone or use a rich rule.

If everything is setup up correctly, you will be able to login to the remote MySQL server.

If you get an error like below, then either the port 3306 is not open, or the MySQL server is not listening on the IP address.

Mysql Access Denied For User Mac