Mysql adding user for remote access

mysql remote-access
user2333586 picture user2333586 · Apr 29, 2013 · Viewed 338.4k times · Source

I created user user@'%' with password 'password. But I can not connect with:

mysql_connect('localhost:3306', 'user', 'password');

When I created user user@'localhost', I was able to connect. Why? Doesn't '%' mean from ANY host?

Answer

apesa picture apesa · Apr 29, 2013

In order to connect remotely you have to have MySQL bind port 3306 to your machine's IP address in my.cnf. Then you have to have created the user in both localhost and '%' wildcard and grant permissions on all DB's as such . See below:

my.cnf (my.ini on windows)

#Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx

then

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

Then

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
flush privileges;

Depending on your OS you may have to open port 3306 to allow remote connections.

Related questions

ERROR 2003 (HY000): Can't connect to MySQL server (111)

This question is related to the following questions: Can't connect to MySQL server error 111 Trying to connect to remote MySQL host (error 2003) I am configuring a new MySQL (5.1) server on my local machine. I need to provide remote access to …

Read More
MySQL remote connection fails with "unknown authentication method"

I am trying to remotely connect to MySQL server online from my local machine, but I am getting the following error: Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password] in C:\xampp\htdocs\…

Read More
Run MySQL query on remote machine through ssh in command line

I am trying to run MySQL query on remote machine with this command: ssh [email protected] "mysql -uroot -proot -e \"use test";"" I am not able to use that database. Please suggest a working command.

Read More