My Pi in the Sky - Running MySQL on a Raspberry Pi and Replicating Data to the Cloud

Did you know you can run MySQL on a Raspberry Pi and replicate the data to the cloud?

In this session, we will cover how to install MySQL on a Raspberry Pi, give a brief overview of how to store data on this MySQL instance, demonstrate an application that can visualize that data, and finally, how you can replicate that data to a MySQL instance running in the cloud.

Transcript

1. Running MySQL on a Raspberry Pi (and replicating the data to the cloud)
2. § MySQL Developer Advocate § Full stack Developer for longer than the term "full stack developer" has existed § The only constant in my dev stack has been MySQL § I am a die-hard NY Giants fan § I have the best office mate § I often force myself to learn new tech by submitting proposals to conferences
3. THE IDEA
4. 90% 10% Absolutely Sure No clue
6. SPS I §Raspberry Pi 4 Model B §AdaFruit Ultimate GPS Module §SunFounder RAP Breadboard
7. §Raspberry Pi 4 Model B §AdaFruit Ultimate GPS Module §Cardboard §Scotch tape §AdaFruit USB to TTY Adapter
8. §Raspberry Pi 4 Model B §AdaFruit Ultimate GPS Module § Soldered correctly §AdaFruit USB to TTY Adapter §Vilros Aluminum Pi 4 Case
9. §Raspberry Pi 4 Model B §AdaFruit Ultimate GPS Hat §C4Labs Zebra Case
10. SOFTWARE ON THE PI §Ubuntu 23.10 §MySQL 8.0.35 §Node.js § SerialPort Module § GPS Module § MySQL Connector § MySQL Document Store §PiTunnel §Not open-source nor free
12. Raspbian Ubuntu Oracle Linux Ubuntu
14. COMPUTE INSTANCE §Always Free Tier §1 CPU §6 GB RAM §Ubuntu 23.10 §Node.js §Express Module §MySQL Connector §OCI TypeScript SDK §Leaflet Map Viewer §Leaflet.motion plugin §OpenStreetMaps §Vanilla JS Datepicker
15. MYSQL HEATWAVE INSTANCE §MySQL E4 VM §1 CPU §8 GB RAM
17. sudo apt update sudo apt install mysql-server
18. §Located at /etc/mysql/mysql.conf.d/mysqld.cnf §Change: §bind-address to 0.0.0.0 §mysqlx-bind-address to 0.0.0.0 §Add §gtid_mode = ON §enforce_gtid_consistency = ON §Restart MySQL
19. CREATE REPLICATION USER §CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; §GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
21. DATA FROM GPS RECEIVER $GNRMC,164650.000,A,3916.8853,N,07754.6533,W,0.67,330.40,020124,,,A*6B $GNVTG,330.40,T,,M,0.67,N,1.24,K,A*21 $GNGGA,164651.000,3916.8853,N,07754.6532,W,1,07,1.61,162.8,M,33.2,M,,*4A $GPGSA,A,3,28,16,31,04,29,26,,,,,,,1.85,1.61,0.93*06 $GLGSA,A,3,74,,,,,,,,,,,,1.85,1.61,0.93*1D $GPGSV,2,1,07,26,80,061,21,16,66,204,31,31,55,056,22,04,47,312,24*76
29. DOCUMENT STORE CODE
30. DOCUMENT STORE CODE
31. DOCUMENT STORE CODE
32. DOCUMENT STORE CODE
33. DOCUMENT STORE CODE
36. SET UP PITUNNEL §Create an account at https://www.pitunnel.com/ §Add a device §Add persistent 'Custom Tunnels' for ports § 3306 § 33060
37. § Navigate to MySQL HeatWave à Channels § Click "Create Channel"
45. §Playing around with Raspberry Pi can be addicting. §And expensive… §Consumer-grade GPS is ridiculously accurate. §Except when it is not §Managing network connections in Ubuntu 23.10 Server on a Pi is…interesting
46. §Inbound Replication might not be the best implementation for this project. §Removing data from the Pi will remove it from the replica. §Ubuntu Linux images in OCI are locked down pretty tight. §Specifically, firewall config
47. MANY THANKS TO… Todd Sharp Ray Camden leFred Ryan Stroz
48. §scott.stroz@oracle.com §Twitter: @boyzoid §LinkedIn §https://www.linkedin.com/in/scott-stroz/