Allow all remote connections MySQL

Question

I had been using SQL Server and am now using MySQL for a project  With SQL Server  our developers can connect to the remote database on their local machines if they know the host  username  password  With MySQL  though  to give a developer access from their local machines  I have been having to log in to MySQL and execute   GRANT ALL ON     to user address IDENTIFIED BY  password    flush privileges    Where address is the IP address of the developer s machine  Of course  if they change networks  I have to execute it again  Is there a way to allow all remote connections like I have experienced with SQL Server  or is this a bad idea for some reason  We have username and password still   I m obviously a little confused   Also  this is a development database and is only accessible from our internal network  I understand why it is a bad idea to give everyone access to a production database

User · Answer

Install and setup mysql to connect from anywhere remotely DOES NOT WORK WITH mysql_secure_installation ! (https://dev.mysql.com/doc/refman/5.5/en/mysql-secure-installation.html)

On Ubuntu, Install mysql using:

sudo apt-get install mysql-server

Have just the below in /etc/mysql/my.cnf

[mysqld]
#### Unix socket settings (making localhost work)
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock

#### TCP Socket settings (making all remote logins work)
port            = 3306
bind-address = 0.0.0.0

Login into DB from server using

mysql -u root -p

Create DB user using the below statement

grant all privileges on *.* to ‘username’@‘%’ identified by ‘password’;

Open firewall:

sudo ufw allow 3306

Restart mysql

sudo service mysql restart

User · Answer

Also you need to disable below line in configuration file  bind-address             127 0 0 1

User · Answer

GRANT ALL ON     to user     IDENTIFIED BY  password      Will allow a specific user to log on from anywhere   It s bad because it removes some security control  i e  if an account is compromised

User · Answer

mysql gt  CREATE USER  monty   192 168      IDENTIFIED BY  some pass   mysql gt  GRANT ALL PRIVILEGES ON     TO  monty   192 168

User · Answer

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

User · Answer

Mabey you only need   Step one   grant all privileges on     to  user   IP  identified by  password     or  grant all privileges on     to  user      identified by  password     Step two   sudo ufw allow 3306   Step three   sudo service mysql restart

User · Answer

As pointed out by Ryan above  the command you need is   GRANT ALL ON     to user     IDENTIFIED BY  password      However  note that the documentation indicates that in order for this to work  another user account from localhost must be created for the same user  otherwise  the anonymous account created automatically by mysql install db takes precedence because it has a more specific host column    In other words  in order for user user to be able to connect from any server  2 accounts need to be created as follows    GRANT ALL ON     to user localhost IDENTIFIED BY  password    GRANT ALL ON     to user     IDENTIFIED BY  password      Read the full documentation here   And here s the relevant piece for reference      After connecting to the server as root  you can add new accounts  The   following statements use GRANT to set up four new accounts    mysql gt  CREATE USER  monty   localhost  IDENTIFIED BY  some pass   mysql gt  GRANT ALL PRIVILEGES ON     TO  monty   localhost      - gt      WITH GRANT OPTION  mysql gt  CREATE USER  monty      IDENTIFIED BY  some pass   mysql gt  GRANT ALL PRIVILEGES ON     TO  monty          - gt      WITH GRANT OPTION  mysql gt  CREATE USER  admin   localhost   mysql gt  GRANT RELOAD PROCESS ON     TO  admin   localhost   mysql gt  CREATE USER  dummy   localhost        The accounts created by these statements have the following   properties       Two of the accounts have a user name of monty and a password of   some pass  Both accounts are superuser accounts with full privileges   to do anything  The  monty   localhost  account can be used only when   connecting from the local host  The  monty      account uses the       wildcard for the host part  so it can be used to connect from any   host       It is necessary to have both accounts for monty to be able to connect   from anywhere as monty  Without the localhost account  the   anonymous-user account for localhost that is created by   mysql install db would take precedence when monty connects from the   local host  As a result  monty would be treated as an anonymous user    The reason for this is that the anonymous-user account has a more   specific Host column value than the  monty      account and thus comes   earlier in the user table sort order   user table sorting is discussed   in Section 6 2 4     Access Control  Stage 1  Connection Verification

[mysql] Allow all remote connections, MySQL