Last updated on Feb 17, 2017 by edgar in category MySQL
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’;