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

User · Answer

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 mysqld.conf file:

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

Search for the line starting with "bind-address" and set it's 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 root privilege.

mysql -u root -p

now create 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.

Hope this helps ;)

User · Answer

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

User · Answer

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

User · Answer

Just create the user to some database like   GRANT ALL PRIVILEGES ON  lt database name gt    TO   lt username gt       IDENTIFIED BY   lt password gt     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

User · Answer

GRANT ALL PRIVILEGES ON     TO  user   ipadress

User · Answer

Run the following     mysql -u root -p       mysql gt  GRANT ALL ON     to root  ipaddress  IDENTIFIED BY  mysql root password        mysql gt  FLUSH PRIVILEGES       mysql gt  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

User · Answer

You can disable all security by editing  etc my cnf   skip-grant-tables

User · Answer

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 gt  GRANT ALL ON     to snippetbucketdotcom     IDENTIFIED BY  tejastank      mysql gt  FLUSH PRIVILEGES      mysql gt  exit   All linux server works   For MSWin c   Find insatallation location   file path

User · Answer

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

User · Answer

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 gt  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 gt  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  amp  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 gt  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 gt  REVOKE ALL PRIVILEGES  GRANT OPTION FROM  USERNAME       Following will revoke all options for USERNAME from particular IP   mysql gt  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

User · Answer

To remotely access database Mysql server 8    CREATE USER  root      IDENTIFIED BY  Pswword 123    GRANT ALL PRIVILEGES ON     TO  root      WITH GRANT OPTION   FLUSH PRIVILEGES

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

TO  user          is a wildcard - you can also do    domain com  or    123 123 123  and things like that if you need

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] grant remote access of MySQL database from any IP address

Examples related to mysql

Examples related to grant