[mysql] Mysql adding user for remote access

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?

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 gmeier -p

hope it helps

Follow instructions (steps 1 to 3 don'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 restart mysql

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.

An alternative way is to use MySql Workbench. Go to Administration -> Users and privileges -> and change 'localhost' with '%' in 'Limit to Host Matching' (From host) attribute for users you wont to give remote access Or create new user ( Add account button ) with '%' on this attribute instead localhost.