grant remote access of MySQL database from any IP

Question

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.

Answer 1

Use this command:

GRANT ALL ON yourdatabasename.* TO root@'%' IDENTIFIED BY 'yourRootPassword';

Then:

FLUSH PRIVILEGES; 

Then comment out the below line in file "/etc/mysql/mysql.conf.d/mysqld.cnf" (is required!):

bind-address = 127.0.0.1 

Works for me! :-)

Answer 2

For example in my CentOS

sudo gedit /etc/mysql/my.cnf

comment out the following lines

#bind-address = 127.0.0.1

then

sudo service mysqld restart

Answer 3

For anyone who fumbled with this, here is how I got to grant the privileges, hope it helps someone

GRANT ALL ON yourdatabasename.* TO root@'%' IDENTIFIED BY
'yourRootPassword';

As noted % 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.

Answer 4

• START MYSQL using admin user
  • mysql -u admin-user -p (ENTER PASSWORD ON PROMPT)
• Create a new user:
  • CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; (% -> anyhost)
• Grant Privileges:
  • GRANT SELECT,DELETE,INSERT,UPDATE ON db_name.* TO 'newuser'@'%';
  • FLUSH PRIVILEGES;

If you are running EC2 instance don't forget to add the inbound rules in security group with MYSQL/Aurura.

Answer 5

Just create the user to some database like

GRANT ALL PRIVILEGES ON <database_name>.* TO '<username>'@'%' IDENTIFIED BY '<password>'

Then go to

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf and change the line bind-address = 127.0.0.1 to bind-address = 0.0.0.0

After that you may connect to that database from any IP.

Answer 6

You can disable all security by editing /etc/my.cnf:

skip-grant-tables