Connect to remote access raspberry pi with mariadb

[hobbyProgrammer]

Hi,

I have a raspberry pi that contains a MySQL database using mariadb.
I would like to get access to the raspberry pi from my database. My raspberry pi uses remote access.

I already had it linked to MySQL workbench and it asks me to login twice. One for getting remote access (or access in general) to the raspberry pi, the second one is the login for the database.

How do I configure this database connection in Qt?

[mrjj]

Hi
For the database login

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("REMOTEIP");
    db.setDatabaseName("test");
    db.setUserName("user");
    db.setPassword("pwd");

For the remote access thing. Im not sure what MySQL workbench allows with the remote access stuff so
i cant comment on that.

[hobbyProgrammer]

@mrjj Hi,yes I know how you would normally log into a database, but I was only wondering how the remote access works and where to implement that in the code.

[Gulivert]

You get access to your database exactly like @mrjj explain to connect.

I suppose that you get access to your database from Workbench by using a ssh connection, you have to fill first the SSH connection credential, which is the login and password of your raspberry and then, the login and password of MYSQL, is it correct ?
If you want an access from your QT app you have to open the MYSQL port in the firewall of your Raspberry, if there is firewall running. Which should be the case in my memory by default in a Raspbian disto for instance.

Maybe we did not understand what you are talking about a remote access ? Could you explain a bit more.

[hobbyProgrammer]

@Gulivert Hi, yes the workbench asks for the SSH connection. Is there anyway to fill the SSH connection credentials in Qt?

[jsulm]

@hobbyProgrammer You could use public key authentication: https://serverpilot.io/docs/how-to-use-ssh-public-key-authentication

[hobbyProgrammer]

@jsulm I believe it's already public as I haven't changed it yet and it's still
username: pi
password: raspberry

[jsulm]

@hobbyProgrammer I think you misunderstand what public key authentication means: if it is set up you don't have to enter your password when logging in via SSH.
If you did set up public key authentication properly then you should be able to log in via SSH without password. If it still asks you for password then public key authentication is not set up properly.
You have to set it up as you need to upload your public key to your RPi.

[Pablo J. Rogina]

@hobbyProgrammer said in Connect to remote access raspberry pi with mariadb:

I already had it linked to MySQL workbench

could you please confirm where you're running that application?

[hobbyProgrammer]

@Pablo-J-Rogina I have a raspberry pi that contains a mysql mariadb.

I also have a PC with MySQL workbench and Qt where I'd like to create and run the application from.

[Pablo J. Rogina]

@hobbyProgrammer said in Connect to remote access raspberry pi with mariadb:

I also have a PC with MySQL workbench and Qt where I'd like to create and run the application from.

Ok, so have you already tried @mrjj suggestion for Qt code to database login?

[SGaist]

Hi,

One important point: if you want to access your database directly from outside of your RPi, you must also configure it to allow that. Having a remote service that access the database locally is not the same as accessing it directly remotely.

[Gulivert]

From my point of view, if you want to connect your raspberry mariadb from your QT client over a local network you should not use SSH, it will be more pain to do it mainly if it is not needed and doing it like @mrjj explanation.
If it's for a local connection inside a company where there is policies which not allow direct connection or over Internet you could use SSH but you need to implement it. I'm not a king in QT but I think there is not "out of the box" class to connect on a SSH server.
It is possible to implement SSH connection by using for instance libssh or libssh2 with which you will handle only the connection to the raspberry over this protocol, then when the SSH connection is initialized and OK you have to connect with the regular connection from QsqlDatabase still the same as explain by @mrjj .

One more think, for an Internet connection you also can expose your Raspberry MariaDB to internet and have a direct access to it (this is not complicated), but this is not a solution for security reasons.