grant remote access of MySQL database from any IP

问题:

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.

回答1:

TO \'user\'@\'%\'

% is a wildcard - you can also do \'%.domain.com\' or \'%.123.123.123\' and things like that if you need.

回答2:

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.

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.

回答3:

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 file.

For example on my ubuntu server:

#nano /etc/mysql/my.cnf

In the file, search for 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)

stop mysql

start mysql

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

回答4:

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.

回答5:

Config file changes are required to enable connections via localhost.

To connect through remote IPs, Login as a \"root\" user and run the below queries in mysql.

CREATE USER \'username\'@\'localhost\' IDENTIFIED BY \'password\';

GRANT ALL PRIVILEGES ON *.* TO \'username\'@\'localhost\' WITH GRANT OPTION;

CREATE USER \'username\'@\'%\' IDENTIFIED BY \'password\';

GRANT ALL PRIVILEGES ON *.* TO \'username\'@\'%\' WITH GRANT OPTION;

FLUSH PRIVILEGES;

This will create a new user that is accessible on localhost as well as from remote IPs.

Also comment the below line from your my.cnf file located in /etc/mysql/my.cnf

bind-address = 127.0.0.1

Restart your mysql using

sudo service mysql restart

Now you should be able to connect remotely to your mysql.

回答6:

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! :-)

回答7:

Run the following:

$ mysql -u root -p      
mysql> GRANT ALL ON *.* to root@\'ipaddress\' IDENTIFIED BY \'mysql root password\';     
mysql> FLUSH PRIVILEGES;     
mysql> exit

Then attempt a connection from the IP address you specified:

mysql -h address-of-remove-server -u root -p   

You should be able to connect.

回答8:

You can slove the problem of MariaDB via this command:

Note:

GRANT ALL ON *.* to root@\'%\' IDENTIFIED BY \'mysql root password\';

% is a wildcard. In this case, it refers to all IP addresses.

回答9:

GRANT ALL PRIVILEGES ON *.* TO \'user\'@\'ipadress\'

回答10:

Edit File:

/etc/mysql/percona-server.cnf

Append below code in file.

[mysqld] bind-address = 0.0.0.0

Create user for remote access.

$ mysql -u root -p      
mysql> GRANT ALL ON *.* to snippetbucketdotcom@\'%\' IDENTIFIED BY \'tejastank\';   
mysql> FLUSH PRIVILEGES;    
mysql> exit

All linux server works,

For MSWin c:\\ Find insatallation location \\ file path

回答11:

• 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.

回答12:

You need to change the mysql config file:

Start with editing mysql config file

vim /etc/mysql/my.cnf

add:

bind-address = 0.0.0.0

回答13:

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.

回答14:

Open your mysql console and execute the following command (enter your database name,username and password):

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

Then Execute:

FLUSH PRIVILEGES;

Open command line and open the file /etc/mysql/mysql.conf.d/mysqld.cnf using any editor with root privileges.

For example:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Then comment out the below line:

bind-address = 127.0.0.1

Restart mysql to reflect the changes using command:

sudo service mysql restart

Enjoy ;)

回答15:

In website panels like cPanel you may add a single % (percentage sign) in allowed hostnames to access your MySQL database.

By adding a single % you can access your database from any IP or website even from desktop applications.

回答16:

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

回答17:

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

skip-grant-tables