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.
% is a wildcard - you can also do
'%.123.123.123' and things like that if you need.
Assuming that the above step is completed and MySql port 3306 is free to be accessed remotely; Don't forget to bind the public ip address in the mysql config
For example on my ubuntu server:
In the file, search for the the [mysqld] section block and add the new bind address, in this example it is 192.168.0.116. It would look something like this
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
bind-address = 192.168.0.116
you can remove th localhost(127.0.0.1) binding if you choose, but then you have to specifically give an IP address to access the server on the local machine.
Then the last step is to restart the MySql server (on ubuntu)
or #/etc/init.d/mysql restart //for other systems
Now the mySQL database can be accessed remotely by:
mysql -u username -h 192.168.0.116 -p
Adding a link to one more relevant post here.
This post mentions adding permissions to iptables. In my case it worked only after I followed the steps in this post.
Edit : Fixed the Link
GRANT ALL PRIVILEGES ON *.* TO 'user'@'ipadress'
In your website panel like cpanel you may add a single "%" (Percentage sign) in allowed hostname to access your mySQL database.
By adding a single '%' you can access your database from any IP or website even from desktop applications.
For anyone who fumbled with this, here is how I got to grant the privileges, hope it helps someone
GRANT ALL ON yourdatabasename.* TO [email protected]'%' IDENTIFIED BY 'yourRootPassword';
As pointed % is a wildcard and this will allow any IP address to connect to your database. The assumption I make here is when you connect you'll have a user named root (which is the default though). Feed in the root password and you are good to go. Note that I have no single quotes (') around the user root.
You can disable all security by editing /etc/my.cnf: