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?
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.
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
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass'; CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
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.
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+)
bind-address=127.0.0.1in 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
mysqland if you set password for root run
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.
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
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';
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
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.