mysqlhostwebminvirtualmin

How to add remote host for my SQL Database to connect it with the app I develop


I'm facing trouble to do local development of an express app to access my WordPress database (which is on https://example.com). Here's my configuration (which work because I could make it work with a local database. I already tried without the 3306) :

module.exports = {
HOST: "https://example.com:3306",
USER: "user",
PASSWORD: "password",
DB: "mydb"

};

const mysql = require("mysql2");
const dbConfig = require("../config/db.config.js");

// Create a connection to the database
const connection = mysql.createConnection({
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  database: dbConfig.DB
});

// open the MySQL connection
connection.connect(error => {
  if (error) throw error;
  console.log("Successfully connected to the database.");
});

module.exports = connection;

I'm using webmin/virtualmin to manage my different VPS. Here's what I've done :

  1. Webmin -> Servers -> MySQL Database Servers -> Users permissions -> Create new User.
  2. Create the user with my adress ip as host and with all permissions.
  3. Webmin -> Servers -> MySQL Database Servers -> Database permissions -> Create new database permissions
  4. Create a user for the database with the same username than above and with my ip adress as host and with all permissions.
  5. Webmin -> Servers -> MySQL Database Servers -> MySQL Server Configuration
  6. Set "MySQL server listening address" to "any"

Even like that, I can't make the connection. Anyone know why? The error I get is this:

Error: getaddrinfo ENOTFOUND https://example.com:3306
    at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:69:26) {
  errno: -3008,
  code: 'ENOTFOUND',
  syscall: 'getaddrinfo',
  hostname: 'https://example.com:3306',
  fatal: true
}

Solution

  • I fixed my problem. The "error not found" was because I put the host as "example.com" instead of "190.x.x.x" (the ip address).

    Then, I got a "timed out" error. What I did below was useless. You have to authorize MySQL to listen to other ports, then authorize the databse herself to do so, and finally configure the firewall. Here are the steps:

    1. Webmin -> Servers -> MySQL database server -> MySQL Server configuration -> Set listening addres to "0.0.0.0".
    2. Virtualmin -> Edit database -> Remote host -> Add my ip address.
    3. Webmin -> Networking -> Firewall and add the rule "if protocol is TCP and destination port is 3306"