Comparison of Databases for Data Replication: https://docs.google.com/document/d/1KR8rDzTteviE4A_Klt_-5Gp7otV4YOT9w6PvUbgJc4U/edit?usp=sharing
TL;DR: In terms of Replication capabilities, MySQL is probably our safest bet. Postgres/MariaDB seem to be have marginally better capabilities, but not too far from MySQL. Products like Cockroach DB would be awesome since they’re designed to natively support distribution and replication, but will be very difficult (if not impossible) to run on Raspberry Pi’s.
Running Multiple MySQL Servers on One System (to simulate replication):
👎 Setting separate ports and directories manually: https://dev.mysql.com/doc/refman/8.0/en/multiple-servers.html
Stopped working after the first time for me. Plus, this doesn’t really simulate how the MySQL servers would exist on 2 separate systems.
Second Reference: https://www.toptal.com/mysql/mysql-master-slave-replication-tutorial
❓ Docker
Wouldn’t immediately fix all of the issues with the method above; you still need to separate out the directories - https://stackoverflow.com/questions/46938303/docker-multiple-mysql-containers and https://stackoverflow.com/questions/49059109/docker-compose-run-two-instance-of-mysql
❓ DBDeployer - https://github.com/datacharmer/dbdeployer
Connecting MySQL Server with Python:
Possible Starting Point: Route Model
Code for connection:
import mysql.connector
cnx = mysql.connector.connect(
host="127.0.0.1",
port=3306,
user="admin",
password="password")
cur = cnx.cursor()
cur.execute("SELECT * FROM mainschema.coordinate")
row = cur.fetchone()
print(row)
cnx.close()