After spending hours of implementing and testing in hope to connect to a remote server hosting Mysql Databases, the key holds in flushing the privileges in MySQL console. That is it.
How to connect to a remote database server.
- Make sure you open up port 3306 on your local machine just to be sure connection is going through.
- On remote server, create a database or migrate a database.
- Create a user and grant necessary privileges and define an IP Address where the connection will be coming from.
GRANT ALL PRIVILEGES ON *.* TO [email protected]_ip IDENTIFIED BY ‘root_password‘ WITH GRANT OPTION;
- Make sure you flush the privileges in order for the new rule to take place
Make sure, you fill in the blanks with necessary information in order to connect.
- Make sure you comment out where it says "bind-address = 127.0.0.1" in mysqld.cnf file, what this does is it makes Mysql Database Engine listens from traffic coming from localhost only, it forget/ignore all traffic from a remote. So comment it out.
Using My SQL-Workbench, Set up New Connection
- Give it a name
- Select Standard (TCP/IP) method of connection
- The hostname should be the remote IP Address
- Username should be the one created on remote server Mysql database
- Put your password in the box says: "Store in Vault"
- Test your connection.