Deploy fullstack application ( frontend : vue.js, backend: node.js, database: mysql )

Hello,

Are you using the new DigitalOcean Managed MySQL service? If so you might have to create a new user with authentication type mysql_native_password or alter your doadmin user so that it does not use zcaching_sha2_passwordz but mysql_native_password. That way your node should be able to connect to the MySQL database.

You can take a look at this answer here on how to do that:

https://www.digitalocean.com/community/questions/how-to-change-caching_sha2_password-to-mysql_native_password-on-a-digitalocean-s-managed-mysql-database

Also, I would recommend testing if you could connect from your backend droplet to your MySQL server using telnet:

• First SSH to your backend droplet and then run:

telnet (^)mysql_server_ip(^) (^)mysql_port(^)

If the telnet command connects you fine, then it is not an issue with your firewall, so I would recommend that you share your config files here so that I could advise you further.

Regards, Bobby

The issues that are being encountered are common when setting up separate frontend and backend servers and trying to connect them to a MySQL database. Let’s break down the problems and the steps to resolve them.

Issue 1: SequelizeConnectionError - Client does not support authentication protocol

This error typically occurs when the MySQL server is using a newer authentication protocol that the Sequelize or MySQL client library does not support. This is common with MySQL 8.0, which uses caching_sha2_password by default.

Solution:

1. Upgrade Your MySQL Client:

   • Ensure that you are using the latest version of the MySQL client library that supports the caching_sha2_password authentication plugin.
   • If you’re using Node.js and Sequelize, update the mysql2 package:

npm install mysql2@latest

2. Change MySQL User Authentication Method:

   • Alternatively, you can change the authentication method for your MySQL user to mysql_native_password, which is compatible with older clients.

   Run these commands in your MySQL server:

ALTER USER 'your_username'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

1. Replace your_username and your_password with your actual MySQL username and password.

2. Check Sequelize Configuration:

   • Make sure your Sequelize configuration correctly specifies the database host (the IP of the droplet running MySQL), port (3306 by default), username, and password.

Issue 2: net::ERR_CONNECTION_REFUSED in Vue.js Application

This error usually occurs when the Vue.js frontend cannot reach the backend server. The causes can include:

• The backend server is not running.
• The firewall on the backend server is blocking incoming connections.
• The backend is listening on a different port or only on localhost.

Solution:

1. Check if the Backend Server is Running:

   • Ensure that your backend service (e.g., Node.js, Express, etc.) is running and listening on the correct IP address and port.

   You can check which ports are being listened to with:

sudo netstat -tuln | grep LISTEN

2. Bind Backend Server to All Interfaces:

   • Ensure that your backend is configured to listen on 0.0.0.0 rather than 127.0.0.1 so it can accept connections from external IPs.

   Example for Express.js:

app.listen(3000, '0.0.0.0', () => {
    console.log('Server is running on port 3000');
});

• Firewall and Security Groups:

  • Make sure the firewall on your backend droplet allows incoming traffic on the port your backend is using (e.g., 3000 for a typical Express.js application).
  • You can use ufw to open the port:

sudo ufw allow 3000/tcp

• Cross-Origin Resource Sharing (CORS):

  • Ensure that your backend API has CORS enabled to allow requests from your frontend domain or IP.

  Example for Express.js:

const cors = require('cors');
app.use(cors());

1. Testing with Postman:

   • If you are testing with Postman and encountering errors, ensure that the correct IP and port are being used.
   • If Postman also throws ERR_CONNECTION_REFUSED, check if the backend service is running and reachable from the Postman client (which might be running on your local machine).

Summary of Steps:

1. Fix the Sequelize connection error by updating the MySQL client or changing the MySQL user authentication method.
2. Ensure your backend server is running and listening on all interfaces (0.0.0.0) so that it can accept connections from external IPs.
3. Configure firewall rules to allow traffic on the necessary ports.
4. Enable CORS on your backend to allow the Vue.js frontend to make requests.

Once you’ve gone through these steps, your Vue.js application should be able to communicate with your backend, and Postman should be able to successfully send requests to your backend API.