Database Connection & Replication KB

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.

Connecting MySQL Server with Python:

https://dev.mysql.com/doc/connector-python/en/
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()

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: MySQL Master-Slave Replication on the Same Machine | Toptal®
❓ Docker
- Wouldn’t immediately fix all of the issues with the method above; you still need to separate out the directories - Docker multiple MYSQL containers and docker-compose run two instance of mysql
✅ DBDeployer - https://github.com/datacharmer/dbdeployer - See Below!
❓ Cloud Computing (MS Azure) for Testing

DBDeployer: Out of all of the methods tested, DBDeployer is probably the easiest way to set up replication:

Install DBDeployer: Installation Wiki
1. You’ll have to download one of the dbdeployer releases, which from what I can see, is only available for Linux/MacOS If you’re on Windows, you could potentially try using WSL.
2. As of writing this, the third alternative to install DBDeployer (gobinaries) isn’t functioning.
At this point, typing in dbdeployer in your terminal should give you the help commands required.
Next, you want to install the MySQL binary tarball from here: MySQL :: Download MySQL Community Server
Follow the unpacking instructions on here: Main Operations Wiki