Search code examples
mysqldatabaseserverconnectionport

Remote MySQL: How to set up remote mysql server in windows 10

// New Update Below

I just released an app for early alpha testing but am getting this error => address: "127.0.0.1" code: "ECONNREFUSED" errno: -111 fatal: true port: 3306 syscall: "connect". I've searched and found that this is because MySQL is not allowing connections aside from localhost. After searching again, I came across these two tutorials (1st) (2nd) but have run into some trouble and its still not working. My front end is [here][3] and the server is [here][4] (removed). After .\mysql.exe -u username -h root -p. Replace mnhs-shs.herokuapp.commysql: it says [Warning] Using a password on the command line interface can be insecure.. It doesn't error but I still can't connect to the database by server

Steps I did:

  1. Set bind-address = 0.0.0.0
  2. stop and start MYSQL80
  3. Configured firewalls
  4. Tried to connect by .\mysql.exe -u username -h root -p. Replace mnhs-shs.herokuapp.com
  5. CREATE USER 'username'@'mnhs-shs.herokuapp.com' IDENTIFIED BY 'password';
  6. GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on . TO 'username'@'mnhs-shs.herokuapp.com' WITH GRANT OPTION;
  7. ... still error 111 on the front end

Database Connection pic: Database connection pic

I'm already connected to the database by

const mysql = require('mysql')
const connection = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
})
module.exports = connection

.env

DB_HOST=remotemysql.com

Update: Which do I put as the server? Code from this site

From there, type .\mysql.exe -u username -h X.X.X.X:XXXX -p. Replace X.X.X.X:XXXX with your remote server IP address and port number (eg. 100.200.100.200:3306) and username with a MySQL username that allows remote access (such as root). Follow any additional on-screen instructions.

I just put in step 4 from above

Solution

  • Figured it out. For future self: It's not the localhost only error. .env, gitignore and dotenv module is not enough if one is going to deploy to heroku. Heroku config vars makes it work, refer to the link below. https://devcenter.heroku.com/articles/config-vars.