Cannot connect to local mysql database on a Raspberry Pi from Windows10

Question

I have a MySQL database setup on my Raspberry Pi 4, and it works perfectly when I execute the code on my Raspberry PI. But I want to code on my Windows 10 PC, so I copied the database connection code and pasted it on my Windows PC. When I run the code it gives me this error:

[nodemon] starting `node ./database/db.js`
Error: Access denied for user 'lasse'@'localhost' (using password: YES)
    at Object.createConnection (C:\Users\Lasse\Desktop\rradvanced\node_modules\mysql2\promise.js:241:31)
    at Object.<anonymous> (C:\Users\Lasse\Desktop\rradvanced\database\db.js:3:7)
    at Module._compile (internal/modules/cjs/loader.js:1137:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1157:10)
    at Module.load (internal/modules/cjs/loader.js:985:32)
    at Function.Module._load (internal/modules/cjs/loader.js:878:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:71:12)
    at internal/main/run_main_module.js:17:47 {
  code: 'ER_ACCESS_DENIED_ERROR',
  errno: 1045,
  sqlState: '28000'

The code:

const mysql = require('mysql2/promise')
 
mysql.createConnection({
    user: 'lasse',
    password: '',
    database: 'rradvanced'
}).then((connection) => {
    connection.execute('CREATE TABLE Guilds(guildId VARCHAR(255))')
})
.then(() => console.log('Database succesfully connected'))
.catch(err => console.log(err));

The Raspberry PI and my Windows PC run on the same internet.

If it's running on a different machine then you need to put in an address into your connection. You also need to take a look at this https://stackoverflow.com/questions/9257455/connecting-to-mysql-server-on-another-pc-in-lan — Worthy Alpaca, Oct 12 '20 at 12:01
@WorthyAlpaca how would I do that? If I did that it would just type: 'lasse@***.***.*.**'@'localhost' — Cocio Inquire, Oct 12 '20 at 12:21
well, address is probably the wrong word. You need to put in a `host` property. It should look like this `Host:` with the local address of your RasPi and the port the database listens to. Then the output should be `lasse@the IP address: PORT` I'm not an expert in this which is why this is only a comment. — Worthy Alpaca, Oct 12 '20 at 12:31

score 0 · Accepted Answer · answered Oct 12 '20 at 15:24

You need to do two things:

Create a MySQL user to access from a different host (your windows PC) and grant that user privileges to do whatever you need.

CREATE USER 'lasse'@'%' IDENTIFIED BY '******';
GRANT ALL PRIVILEGES ON *.* TO 'lasse'@'%'

Add mysql server info to your connection code (No empty password is allowed by default)

    const mysql = require('mysql2/promise')
     
    mysql.createConnection({
        host : 'Raspberry_Pi_IP_address',
        user: 'lasse',
        password : '******',
        database: 'rradvanced'
    }).then((connection) => {
        connection.execute('CREATE TABLE Guilds(guildId VARCHAR(255))')
    })
    .then(() => console.log('Database succesfully connected'))
    .catch(err => console.log(err));

Cannot connect to local mysql database on a Raspberry Pi from Windows10

1 Answers1