How to enable connections to MySQL from Remote Machine

Last updated by edgar on Feb 17, 2017 In MySQL This knowledge base article has 1,713 views

MySQL considers connections from the same local machine different than connections from a remote machine, even if using the same root user and password.  

When installing and configuring MySQL, the root account and password being setup is by default, only for connections from the same server that MySQL is installed on.  

If you're unable to establish a connection to MySQL from a remote server (e.g. not the same server as MySQL is installed on) and seeing Error Code 1130, this means MySQL isn't configured to allow remote connections via the specified user account. 

Step 1. Login via SSH to Insight Server

Step 2. Navigate to location of MySQL Config file (my.cnf):  cd /etc/mysql/

Step 3. Open my.cnf with vi editor:  sudo vi my.cnf

Step 4. Scroll to [mysqld] section and make sure ‘skip-networking’ is commented (or removed), comment out bind-address = 127.0.0.1 (Change to #bind-address = 127.0.0.1)

Save and close file. 

***FOR SECURITY CONSIDER REVERTING THIS SETTING BACK ONCE YOUR REMOTE WORK IS COMPLETE*** Un-commment ‘bind-address = 127.0.0.1’ and re-add skip-networking (only if you deleted it in Step 4.)

Step 5. Restart mysql service:  sudo service mysql restart

Step 6. Connect to mysql server:  mysql -u root -p 

Step 7. Switch to ‘mysql’ database:  use mysql

Step 8. Create new user for remote server: CREATE USER 'newuser’@‘FQDN.local’ IDENTIFIED BY 'password';  FQDN.local is the fully qualified domain name or IP address of remote/source server.

Step 9. Grant privileges to new user:

GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@‘FQDN.local’ IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

If you have issues connecting from remote server change ‘FQDN.local’ to ‘%’ (wildcard, all hosts)

Step 10. Reload all privileges:  FLUSH PRIVILEGES;

***FOR SECURITY CONSIDER REVERTING THIS SETTING BACK ONCE YOUR REMOTE WORK IS COMPLETE**** DROP USER ‘newuser’@‘FQDN.local’;