Mysql adding user for remote access

Question

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 1

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.

Answer 2

Follow instructions (steps 1 to 3 aren't needed in Windows):

1. Find mysql config to edit:

/etc/mysql/my.cnf (Mysql 5.5)

/etc/mysql/conf.d/mysql.cnf (Mysql 5.6+)

2. Find bind-address=127.0.0.1 in config file change bind-address=0.0.0.0 (you can set bind address to one of your interface IPs or like me use 0.0.0.0)

3. Restart mysql service run on console: service mysql restart

4. Create a user with a safe password for remote connection. To do this run following command in mysql (if you are linux user to reach mysql console run mysql and if you set password for root run mysql -p):

GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%' IDENTIFIED BY 'safe_password' WITH GRANT OPTION;

Now you should have a user with name of user and password of safe_password with capability of remote connect.

Answer 3

for what DB is the user? look at this example

mysql> create database databasename;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on databasename.* to cmsuser@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

so to return to you question the "%" operator means all computers in your network.

like aspesa shows I'm also sure that you have to create or update a user. look for all your mysql users:

SELECT user,password,host FROM user;

as soon as you got your user set up you should be able to connect like this:

mysql -h localhost -u cmsuser -p

hope it helps

Answer 4

I had used an existing user that had password using mysql_navtive_password

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

Rather than

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED BY 'password';

thus was not able to connect. Deleting a old one and creating a new without mysql_native_password did the trick