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

Slide 1

Slide 1 text

Running MySQL on a Raspberry Pi (and replicating the data to the cloud)

Slide 2

Slide 2 text

§ 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

Slide 3

Slide 3 text

THE IDEA

Slide 4

Slide 4 text

90% 10% Absolutely Sure No clue

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

SPS I §Raspberry Pi 4 Model B §AdaFruit Ultimate GPS Module §SunFounder RAP Breadboard

Slide 7

Slide 7 text

§Raspberry Pi 4 Model B §AdaFruit Ultimate GPS Module §Cardboard §Scotch tape §AdaFruit USB to TTY Adapter

Slide 8

Slide 8 text

§Raspberry Pi 4 Model B §AdaFruit Ultimate GPS Module § Soldered correctly §AdaFruit USB to TTY Adapter §Vilros Aluminum Pi 4 Case

Slide 9

Slide 9 text

§Raspberry Pi 4 Model B §AdaFruit Ultimate GPS Hat §C4Labs Zebra Case

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

Raspbian Ubuntu Oracle Linux Ubuntu

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

MYSQL HEATWAVE INSTANCE §MySQL E4 VM §1 CPU §8 GB RAM

Slide 16

Slide 16 text

No content

Slide 17

Slide 17 text

sudo apt update sudo apt install mysql-server

Slide 18

Slide 18 text

§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

Slide 19

Slide 19 text

CREATE REPLICATION USER §CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; §GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Slide 20

Slide 20 text

No content

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

No content

Slide 23

Slide 23 text

No content

Slide 24

Slide 24 text

No content

Slide 25

Slide 25 text

No content

Slide 26

Slide 26 text

No content

Slide 27

Slide 27 text

No content

Slide 28

Slide 28 text

No content

Slide 29

Slide 29 text

DOCUMENT STORE CODE

Slide 30

Slide 30 text

DOCUMENT STORE CODE

Slide 31

Slide 31 text

DOCUMENT STORE CODE

Slide 32

Slide 32 text

DOCUMENT STORE CODE

Slide 33

Slide 33 text

DOCUMENT STORE CODE

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

No content

Slide 36

Slide 36 text

SET UP PITUNNEL §Create an account at https://www.pitunnel.com/ §Add a device §Add persistent 'Custom Tunnels' for ports § 3306 § 33060

Slide 37

Slide 37 text

§ Navigate to MySQL HeatWave à Channels § Click "Create Channel"

Slide 38

Slide 38 text

No content

Slide 39

Slide 39 text

No content

Slide 40

Slide 40 text

No content

Slide 41

Slide 41 text

No content

Slide 42

Slide 42 text

No content

Slide 43

Slide 43 text

No content

Slide 44

Slide 44 text

No content

Slide 45

Slide 45 text

§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

Slide 46

Slide 46 text

§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

Slide 47

Slide 47 text

MANY THANKS TO… Todd Sharp Ray Camden leFred Ryan Stroz

Slide 48

Slide 48 text

§[email protected] §Twitter: @boyzoid §LinkedIn §https://www.linkedin.com/in/scott-stroz/

Slide 49

Slide 49 text

No content