grant remote access of MySQL database from any IP address

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.

Before doing this, consider the security implications: http://security.stackexchange.com/questions/63881/is-it-not-safe-to-open-mysqls-port-to-the-internet — e18r, Mar 23 '16 at 16:29
not every system is a production system. some people need this for development. — Conrad Jones, Apr 07 '18 at 22:56

score 332 · Answer 1 · edited Jun 20 '22 at 10:49

332

TO 'user'@'%'

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

edited Jun 20 '22 at 10:49

Stephen Ostermiller

23,933
14
88
109

answered Dec 01 '11 at 21:29

Ariel

25,995
5
59
69

@kristopolous There are lots of things that could cause that. This command is about remote access (two different machines). If that's not what you are doing this is not the right command. You still need a correct password and other things. – Ariel Mar 16 '15 at 18:23
21

You have to use "flush privileges;" for the changes to take effect – Didier Sampaolo Nov 19 '15 at 06:01
The trick when connecting to a db instance running on the same OS instance (e.g. your development machine), is to pass in the `-h my_machine_name` parameter. This tricks the client to identify you as `'user'@my_machine_name.example.com`, rather than `'user'@localhost`. This way you don't need to create and maintain dual accounts and grants for both `localhost` and network access. And, of course, be sure `mysqld` is bound to `0.0.0.0` vs. `127.0.0.1`. – Charlie Reitzel Aug 08 '19 at 18:41
Operation CREATE USER failed for 'remote'@'%' ? – shamaseen Dec 21 '20 at 16:08

score 201 · Answer 2 · edited May 24 '21 at 19:39

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.

Note that I had to change `/etc/mysql/mysql.conf.d/mysqld.cnf` file instead of `/etc/mysql/my.cnf` to comment out the `bind-address` part. The `#skip-networking` line is missing and there should be no effect of adding and commenting the line. — Pawan, Dec 18 '16 at 17:14
On MariaDB 10.1.26, the configuration file is `/etc/mysql/mariadb.conf.d/50-server.cnf` — Kwadz, Jul 03 '18 at 08:46
If you have done all of the above and are still unable to connect, check your firewall settings, might be blocking port `3306` or whichever port you have set up — avn, Mar 18 '19 at 20:13
It's worth mentioning that 'USERNAME' is different from 'username'. So, watch out of the casing. — Shadi Alnamrouti, Dec 18 '20 at 22:53
Where is that article? Did you copy directly from it? If you copy it should be *quoted*, so it is very clear it isn't your own words; otherwise it is plagiarism. — Peter Mortensen, Jul 03 '23 at 15:45
The exact phrase *"If you get a mysql shell, don’t forget to run show databases"* is found in *[Enable Remote Access (Grant)](https://rtcamp.com/tutorials/mysql/remote-access/)* (2013). The same for the exact phrase *"By default, mysql username and password you are using is allowed to access mysql-server locally."* — Peter Mortensen, Jul 03 '23 at 15:47

score 47 · Answer 3 · edited Jul 03 '23 at 16:00

To be able to connect with your user from any IP address, do the following:

Allow MySQL server to accept remote connections. For this, open the mysqld.conf file:

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

Search for the line starting with "bind-address" and set its value to 0.0.0.0:

bind-address                    = 0.0.0.0

And finally save the file.

Note: If you’re running MySQL 8+, the bind-address directive will not be in the mysqld.cnf file by default. In this case, add the directive to the bottom of the file /etc/mysql/mysql.conf.d/mysqld.cnf.

Now restart the MySQL server, either with systemd or use the older service command. This depends on your operating system:

sudo systemctl restart mysql # for ubuntu
sudo systemctl restart mysqld.service # for debian

Finally, MySQL server is now able to accept remote connections.

Now we need to create a user and grant it permission, so we can be able to login with this user remotely.

Connect to MySQL database as root, or any other user with the root privilege.

mysql -u root -p

Now create the desired user in both localhost and '%' wildcard and grant permissions on all DB's as such.

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'@'%';

And finally don't forget to flush privileges

FLUSH PRIVILEGES;

Note: If you’ve configured a firewall on your database server, you will also need to open port 3306 MySQL’s default port to allow traffic to MySQL.

sudo systemctl restart mysqld.service is for debian, I had to use sudo systemctl restart mysql.service is for ubuntu — ani0904071, Jun 14 '19 at 09:01
I updated my answer and added mysql restart command for both distro. thanks @ani0904071 — Imtiaz Shakil Siddique, Jun 11 '20 at 15:59

score 39 · Answer 4 · edited Jul 03 '23 at 16:13

39

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

For example, on my Ubuntu server, as root:

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 the 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

edited Jul 03 '23 at 16:13

Peter Mortensen

30,738
21
105
131

answered May 20 '13 at 11:40

Waqas

3,763
1
30
18

`/etc/init.d/mysql reload` should suffice – Lorenz Lo Sauer Aug 08 '13 at 12:17
I just did this and could not connect to the mysql server remotely. I then tried commenting out the bind-addresses and it worked. I also tried logging into the server with mysql -u -p without specifying the host and it worked, so it appears that "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." is not correct. (ubuntu 12.04 LTS server mysql Ver 14.14 Distrib 5.5.34) – Programster Nov 02 '13 at 10:34
6

The `bind-address` directive determines the IP address that the mysql _server_ listens on; _not_ the IP addresses that the server accepts connections from. – GoZoner Sep 03 '14 at 01:57
2

After following every step it doesn't work. I get an error when commenting out `bind-address` either the localhost or the remotehost address. `mysql.serviceJob for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.` – sunwarr10r Feb 06 '17 at 16:36
Is it possible to provide address like abc.abc:1234 instead of 192.168.0.116? – bielas May 08 '17 at 19:49

score 31 · Answer 5 · edited Jul 03 '23 at 16:02

31

Configuration file changes are required to enable connections via localhost.

To connect through remote IP addresses, log in 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 server using:

sudo service mysql restart

Now you should be able to connect remotely to your MySQL server.

edited Jul 03 '23 at 16:02

Peter Mortensen

30,738
21
105
131

answered May 08 '16 at 17:29

harishannam

2,747
3
30
39

3

This doesn't answer the question. – CHarris Aug 19 '16 at 21:45
1

@ChristopheHarris why do you feel so? The answer clearly gives step by step instruction on how to create a new user in MySQL that allows both localhost and remote access to the DB using that user. Can you plesae explain? – harishannam Sep 18 '16 at 08:27
2

Doesn't work for me. Still have the same error. No matter whether I try it with root or a new user: Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' ([Errno 61] Connection refused) – sunwarr10r Feb 06 '17 at 16:44
I tried a lots of things. Didn't work. But don't know why and how this answer worked. But it worked, that's all I know. – Tushar Monirul Jul 07 '22 at 18:12

score 27 · Answer 6 · edited Jul 03 '23 at 15:43

27

Here is how I got to grant the privileges:

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 don't have any single quotes (') around the user root.

edited Jul 03 '23 at 15:43

Peter Mortensen

30,738
21
105
131

answered Mar 21 '15 at 09:31

Dev

1,146
2
19
30

1

Why is removing the single quotes from the user important? Why does this differ from other answers (and the manual)? – DMCoding Oct 31 '16 at 16:20

score 24 · Answer 7 · edited Feb 05 '19 at 04:42

24

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!

edited Feb 05 '19 at 04:42

Neuron

5,141
5
38
59

answered Nov 23 '17 at 20:35

Tomasz Czechowski

611
8
11

Very good one. Important the grant. If not, lot of errors for requests like `your-password-does-not-satisfy-the-current-policy-requirements` – Alejandro Teixeira Muñoz Sep 02 '19 at 16:48

score 17 · Answer 8 · edited Jul 03 '23 at 15:53

17

Run the following:

mysql -u root -p

MySQL client session:

GRANT ALL ON *.* to root@'ipaddress' IDENTIFIED BY 'mysql root password';
FLUSH PRIVILEGES;
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.

edited Jul 03 '23 at 15:53

Peter Mortensen

30,738
21
105
131

answered Jan 15 '16 at 05:34

akhil.o.v

179
1
3

score 16 · Answer 9 · edited May 23 '17 at 16:57

16

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.

edited May 23 '17 at 16:57

Giacomo1968

25,759
11
71
103

answered Feb 08 '17 at 03:14

sixsixsix

1,768
21
19

3

in mysql ,what you need to do is (1).change my.cnf content (2).grant remote user (3) deal with your firewall to listen 3306 port. – sixsixsix Feb 08 '17 at 03:30
You saved my day. Appreciate it! – Almett Oct 03 '17 at 09:31

score 10 · Answer 10 · edited Jul 03 '23 at 15:57

10

To remotely access a database with MySQL server 8:

CREATE USER 'root'@'%' IDENTIFIED BY 'Pswword@123';

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

FLUSH PRIVILEGES;

edited Jul 03 '23 at 15:57

Peter Mortensen

30,738
21
105
131

answered Apr 10 '19 at 08:21

Anil Singh

333
3
7

score 8 · Answer 11 · edited Oct 07 '14 at 15:42

8

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

edited Oct 07 '14 at 15:42

fedorqui

275,237
103
548
598

answered Oct 07 '14 at 15:20

Felix

89
1
3

score 5 · Answer 12 · answered Oct 24 '16 at 07:06

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.

score 5 · Answer 13 · answered Jan 24 '17 at 11:03

5

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

answered Jan 24 '17 at 11:03

Tejas Tank

1,100
2
16
28

[mysqld] need to add in file, else not work properly – Tejas Tank Jan 24 '17 at 11:04

score 3 · Answer 14 · edited Jun 06 '18 at 10:11

3

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.

edited Jun 06 '18 at 10:11

Kos

4,890
9
38
42

answered Jun 06 '18 at 09:49

Diogo Mendes

31
1

score 3 · Answer 15 · edited Nov 26 '18 at 12:13

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 ;)

score 2 · Answer 16 · edited Feb 28 '18 at 15:49

2

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

edited Feb 28 '18 at 15:49

Marvin Fischer

2,552
3
23
34

answered Feb 28 '18 at 13:04

bhushan pardhi

99
1
5

score 1 · Answer 17 · answered Jan 29 '19 at 06:13

what worked for on Ubuntu is granting all privileges to the user:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;

and setting the bind address in /etc/mysql/mysql.conf.d/mysqld.cnf:

bind-address            = 0.0.0.0

then restarting the mysql daemon:

service mysql restart

score 1 · Answer 18 · answered Jun 02 '22 at 16:24

1

Go to this directory "/etc/mysql/mysql.conf.d" then edit this file " mysqld.cnf"

$nano mysqld.cnf

bind-address            = 127.0.0.1

mysqlx-bind-address     = 127.0.0.1

edit to

bind-address            = 0.0.0.0

mysqlx-bind-address     = 0.0.0.0

answered Jun 02 '22 at 16:24

Shihab Uddin Shakil

85
3

score 0 · Answer 19 · edited Jul 03 '23 at 16:03

0

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 address or website even from desktop applications.

edited Jul 03 '23 at 16:03

Peter Mortensen

30,738
21
105
131

answered Mar 29 '14 at 16:28

Muhammad Saqib

2,185
3
35
48

please don't refer to a thing like cPanel when a straight query is presented, this will never show a working result for anyone else – Tobias Hagenbeek Jul 07 '15 at 13:11
@TobiasHagenbeek Thank you for your feedback. This method/trick is not invented by me (It's common on internet). But I'm one of the tester of this method and I found it is the easiest and working method that is why I posted it here. I know there are some security risks in this method which can be neglected. – Muhammad Saqib Jul 15 '15 at 19:09

score 0 · Answer 20 · answered Oct 21 '16 at 07:29

0

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

answered Oct 21 '16 at 07:29

zawhtut

8,335
5
52
76

score 0 · Answer 21 · edited Jan 12 '19 at 23:53

0

If you want to grant remote access of your database from any IP address, run the mysql command and after that run the following command.

GRANT ALL PRIVILEGES ON *.*
TO 'root'@'%' 
IDENTIFIED BY 'password' 
WITH GRANT OPTION;

edited Jan 12 '19 at 23:53

Michael

3,093
7
39
83

answered Jan 12 '19 at 22:03

Atul6.Singh

221
2
5

score 0 · Answer 22 · answered Feb 24 '22 at 04:03

I see there are many answers, but they are quite long ones except for the accepted answer, which is quite short and lacks explanation. As I can't edit it, I am adding my answer. Adit asked about:

making this database public so everyone can access it

GRANT ALL PRIVILEGES
ON database.*
TO 'username'@'remote_host'
IDENTIFIED BY 'password';

Above code grants permissions for a user from a given remote host, you can allow a user to connect from any remote host to MySQL by changing TO 'username'@'yourremotehost' to TO 'username'@'%'.

So, the corrected query for granting permissions to a user to connect from any remote host is:

GRANT ALL PRIVILEGES
ON database.*
TO 'username'@'%'
IDENTIFIED BY 'password';

score -1 · Answer 23 · answered Jun 21 '23 at 05:20

To grant remote access of MySQL database from any IP address, you can follow the steps below:

Connect to your MySQL server using a MySQL client such as MySQL Workbench or the MySQL command line client.
Run the following command to grant access from any IP address:

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Replace USERNAME and PASSWORD with your MySQL username and password. This command will grant all privileges to the user USERNAME from any IP address.

If you want to grant access from a specific IP address, replace % with the IP address you want to grant access to:

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Replace 1.2.3.4 with the IP address you want to grant access to.

If you want to grant access from multiple IP addresses, you can run the command multiple times with different IP addresses.
Finally, run the following command to flush the privileges:

FLUSH PRIVILEGES;

This will ensure that the changes take effect immediately.

Note that granting access from any IP address can be a security risk, so it is recommended to only grant access from specific IP addresses that you trust. Additionally, you may need to configure your firewall to allow incoming connections to the MySQL port (default is 3306) from the remote machines[1].

Citations: [1] https://linuxize.com/post/mysql-remote-access/

Many of your answers here, including several which you've made Community Wikis for some reason, appear likely to have been entirely or partially written by AI (e.g., ChatGPT). Please be aware that [posting AI-generated content is not allowed here](//meta.stackoverflow.com/q/421831). If you used an AI tool to assist with any answer, I would encourage you to delete it. Thanks! — NotTheDr01ds, Jul 03 '23 at 00:29
**Readers should review this answer carefully and critically, as AI-generated information often contains fundamental errors and misinformation.** If you observe quality issues and/or have reason to believe that this answer was generated by AI, please leave feedback accordingly. The moderation team can use your help to identify quality issues. — NotTheDr01ds, Jul 03 '23 at 00:29
This answer looks like it was generated by an AI (like ChatGPT), not by an actual human being. You should be aware that [posting AI-generated output is officially **BANNED** on Stack Overflow](https://meta.stackoverflow.com/q/421831). If this answer was indeed generated by an AI, then I strongly suggest you delete it before you get yourself into even bigger trouble: **WE TAKE PLAGIARISM SERIOUSLY HERE.** Please read: [Why posting GPT and ChatGPT generated answers is not currently acceptable](https://stackoverflow.com/help/gpt-policy). — tchrist, Jul 06 '23 at 00:45

score -11 · Answer 24 · edited Jul 03 '23 at 15:42

-11

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

skip-grant-tables

edited Jul 03 '23 at 15:42

Peter Mortensen

30,738
21
105
131

answered Feb 14 '15 at 10:11

Danyial Shahid Ali

517
8
24

this removes all password requirements – kristopolous Mar 16 '15 at 05:49
5

**Note that it is not advisable to have this option enabled in a production environment, as this allows anyone to connect without a password!** – AStopher Dec 03 '15 at 20:39
2

however, this is ideal for a dev environment – DMCoding Oct 31 '16 at 16:19
This allows people to hack your database without using a password. – MilkyWay90 Aug 26 '18 at 16:19
2

@DanielJames even for development environment it's a terrible idea – Dragas Sep 10 '18 at 10:58

grant remote access of MySQL database from any IP address

24 Answers24

Note:

Edit File:

Append below code in file.

Create user for remote access.

All linux server works,

For MSWin c:\ Find insatallation location \ file path

Linked

Related