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?
-
Note that the password for user@localhost and user@% is in general different, as well as privileges.Jose Manuel Gomez Alvarez– Jose Manuel Gomez Alvarez2019-09-21 23:17:27 +00:00Commented Sep 21, 2019 at 23:17
Add a comment
|
6 Answers
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.
14 Comments
spex
This helped me with using a WebFaction private MySQL instance. I followed your CREATE USER and GRANT ALL steps, set
mysql.default_port = <private instance port> in my php.ini, and then used 127.0.0.1 throughout for my db hostname
louffi
don't forget to FLUSH PRIVILEGES ;)
Vishnu S. Divetia
Also check /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf location for changing bind-address=0.0.0.0
nights
Why both localhost and % ?
stackprotector
@VishnuS.Divetia
0.0.0.0 is not mandatory. The IP address of the interface through which remote access will happen, is enough. |
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.1in config file changebind-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 restartCreate 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 runmysql -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.
5 Comments
IgorAlves
change bind-address=0.0.0.0... that worked for me. and grant privileges
guru rajender
allowing bind-address globally will lead to big security concern. instead-of we can add specific IP address or address range.
quanly_mc
third step should be: service mysql restart
MSS
@quanly-mc you are right, I just fix it. thanks
Kevin Wheeler
@gururajender According to this post (serverfault.com/a/257517) , assuming you are going to allow external / non local connections at all, 0.0.0.0 is fine, and is even the default in later versions of mysql
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
3 Comments
user2333586
generally, for all db. I just want to connect, not to select DB.
codemysql_connect('localhost:3306', 'user', 'password'); code
apesa
what OS are you on, Windows, Linux??
apesa
You will need to make sure your IP address is bound to port 3306. In windows you might use netstat -n to see what ports are bound to your IP. If you see 127.0.0.1:3306 you will not be able to connect from anything other than localhost
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
Comments
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.
Comments
It's simple.
-- Create a user with remote access (unline root)
CREATE USER 'your_username'@'%' IDENTIFIED BY 'your_pass';
-- Grant the privilege to the tables of a database for a user
GRANT ALL PRIVILEGES ON your_database.* TO 'your_username'@'%';
-- reload the grant tables, ensuring that the changes take effect immediately
FLUSH PRIVILEGES;
After running the above SQL commands, you can connect to your mysql/mariadb database remotely without any problems.
