stanwin00
Posts: 23
Joined: Tue Aug 06, 2019 9:55 am

SQL and raspberry pi

Mon Oct 14, 2019 7:35 am

Ok, so i've been trying to figure out a few things via googling on the web and i'm pulling my hairs out.

1) when i try to move my database configuration out of my python script into a separate file for cleaner code and structure, i cannot connect to the database even though it was the same connections as that in the python script.

2) Is there a way to move the SQL database directly off the pi and each time the script runs, it accesses data from a remote sql database that i plan to host on a windows 10 machine else where?

I know i'm supposed to comment out the "bind address" property in the mariadb configuration file and restarted sql service. I've done that but now i'm confused as to what or where to put the connection details and how to actually go about connecting to the sql database stored in another server and use that database for verification work in the script.

Sorry if this has been asked before. I'm just trying to create better structures around codes. Any help is appreciated as i do not want to screw up any configuration files on the pi.

User avatar
DougieLawson
Posts: 36330
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: SQL and raspberry pi

Mon Oct 14, 2019 8:11 am

What SQL database are you using?

If MySQL (aka MariaDB) then it's trivial to have the database on any remotely connected machine. It's just a case of getting the mysqld daemon listening on the inaddr-any address and having the right remote user credentials.
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

stanwin00
Posts: 23
Joined: Tue Aug 06, 2019 9:55 am

Re: SQL and raspberry pi

Mon Oct 14, 2019 8:16 am

@dougie yes it is mysql. Just need to understand the configurations required and why it's done like that. Articles i found online seem to indicate the sql server is on the pi itself and i'm connecting to it rather than the pi connecting to my database outside. The reason why i'm doing this is to avoid updating each and every pi with the credentials that i input into the master database. I rather centrally update it and then the pi reads data from the central database.
Last edited by stanwin00 on Mon Oct 14, 2019 8:21 am, edited 1 time in total.

User avatar
DougieLawson
Posts: 36330
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: SQL and raspberry pi

Mon Oct 14, 2019 8:19 am

Search Google for "MySQL bind-address" and "remote connection".
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

stanwin00
Posts: 23
Joined: Tue Aug 06, 2019 9:55 am

Re: SQL and raspberry pi

Mon Oct 14, 2019 9:41 am

@dougie, not sure if you've seen my edited answer above but i'm trying to move the database out of the pi itself and get the pi to access it from a centralized database because when i deploy, there will be multiple pis and i don't want to have to update it locally each time i get new entries into the database.

User avatar
DougieLawson
Posts: 36330
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: SQL and raspberry pi

Mon Oct 14, 2019 10:18 am

Moving a database is simply a case of looking in the /etc/mysql/*.cnf files to find where the datadir lives.

I've moved my database to my NAS drive (mounted at /srv)

Code: Select all

dougie@apollo:/etc/mysql$ sudo  grep -R -i 'datadir' *
mariadb.conf.d/50-server.cnf:datadir                 = /srv/mysql
dougie@apollo:/etc/mysql$
tar & gzip everything in that directory, move the TGZ file, gunzip and un-tar in the new location. Update my.cnf to point to the new directory, start mariadb. Then grab a cup of tea and keep calm and carry on.

You can move to a completely different machine running mariadb using the same method, stick the tgz file on a USB stick to transport it.
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

stanwin00
Posts: 23
Joined: Tue Aug 06, 2019 9:55 am

Re: SQL and raspberry pi

Tue Oct 15, 2019 3:13 am

thanks @dougie. I can now have a beer or tea lol. I did a simpler method. I installed mariadb on my windows pc and then imported the database over. The rest is all about configuring and flushing privileges lol. Thanks for the tip!

Return to “General programming discussion”