I am aware of this command:
GRANT ALL PRIVILEGES ON database.* TO 'user'@'yourremotehost' IDENTIFIED BY 'newpassword';
But then it only allows me to grant a particular IP address to access this remote MySQL database. What if I want it so that any remote host can access this MySQL database? How do I do that? Basically I am making this database public so everyone can access it.
Select Connections from the SQL navigation menu. In the Authorized networks section, click Add network and enter the IP address of the machine where the client is installed. Note: The IP address of the instance and the mysql client IP address you authorize must be the same IP version: either IPv4 or IPv6. Click Done.
TO 'user'@'%'
% is a wildcard - you can also do '%.domain.com'
or '%.123.123.123'
and things like that if you need.
Enable Remote Access (Grant) Home / Tutorials / Mysql / Enable Remote Access (Grant) If you try to connect to your mysql server from remote machine, and run into error like below, this article is for you.
ERROR 1130 (HY000): Host ‘1.2.3.4’ is not allowed to connect to this MySQL server
Change mysql config
Start with editing mysql config file
vim /etc/mysql/my.cnf
Comment out following lines.
#bind-address = 127.0.0.1 #skip-networking
If you do not find skip-networking line, add it and comment out it.
Restart mysql server.
~ /etc/init.d/mysql restart
Change GRANT privilege
You may be surprised to see even after above change you are not getting remote access or getting access but not able to all databases.
By default, mysql username and password you are using is allowed to access mysql-server locally. So need to update privilege. (if you want to create a separate user for that purpose, you can use CREATE USER 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD';
)
Run a command like below to access from all machines. (Replace USERNAME
and PASSWORD
by your credentials.)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
Run a command like below to give access from specific IP. (Replace USERNAME
and PASSWORD
by your credentials.)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
You can replace 1.2.3.4 with your IP. You can run above command many times to GRANT access from multiple IPs.
You can also specify a separate USERNAME
& PASSWORD
for remote access.
You can check final outcome by:
SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";
Finally, you may also need to run:
mysql> FLUSH PRIVILEGES;
Test Connection
From terminal/command-line:
mysql -h HOST -u USERNAME -pPASSWORD
If you get a mysql shell, don’t forget to run show databases; to check if you have right privileges from remote machines.
Bonus-Tip: Revoke Access
If you accidentally grant access to a user, then better have revoking option handy.
Following will revoke all options for USERNAME from all machines:
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'%'; Following will revoke all options for USERNAME from particular IP: mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'1.2.3.4'; Its better to check information_schema.user_privileges table after running REVOKE command.
If you see USAGE privilege after running REVOKE command, its fine. It is as good as no privilege at all. I am not sure if it can be revoked.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With