Mysql adding user for remote access

I created user [email protected]'%' with password 'password. But I can not connect with:

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

When I created user [email protected]'localhost', I was able to connect. Why? Doesn’t ‘%’ mean from ANY host?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

Method 2

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

Find mysql config to edit:

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

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

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)
Restart mysql service run on console: service mysql restart
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.

Method 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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bcdfd1cfc9cfd9cefcd0d3dfddd0d4d3cfc8">[email protected]</a> 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

Method 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

Method 5

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating