MySQL replication over SSH
Tags: mysql, devops, docker
Published: 2020-12-06 15:02:41

![Slave database server is pulling only the latest additions to the Master database over SSH](https://dev-to-uploads.s3.amazonaws.com/i/97csov9byisk1792vg32.png)

Below is a quick and dirty solution for keeping a database synced with a master copy over SSH that you can set up in 30 minutes on existing databases.

It's a much easier setup (but unsecure) than [a proper real-time MySQL replication one](https://dev.mysql.com/doc/refman/8.0/en/replication.html). Additionally you have full control over timing of syncing as you can use a cronjob to initiate the syncing process.

> Below `mysql` and `mysqldump` commands are optmised to work with MySQL instances running inside a Docker container.

# Master setup

Create a file named `make-snapshots.sh` with below content in your master server and then make it executable with `chmod +x ./make-snapshots.sh`.

```bash
#!/bin/bash
export MYSQL_PWD=PasswordMaster1
# Read the last snapshotted ID
source last_tablename_id
# Remove the old snapshot
rm dump_tablename.sql
# Write snapshot
mysqldump --protocol=TCP --ssl-mode=DISABLED -u username -t --where "id > $LAST_TABLENAME_ID" dbname tablename > dump_tablename.sql
# Remove the last snapshot
rm last_tablename_id
# Extract and write last ID
echo "export LAST_TABLENAME_ID=`grep -oP "\),\((\d+)" dump_tablename.sql | tac | head -1 | cut -b 4-`" > last_tablename_id
```

Before running your first snapshot create `last_tablename_id` with below:

```
export LAST_TABLENAME_ID=0
```

This will ensure that the first snapshot will contain all existing data, i.e. as the `mysqldump` SQL will equal to `WHERE id > 0`.

### How does the snapshot bit work

Thanks to the `--where` parameter passed to the `mysqldump` which tells to backup all rows in a table meeting a criteria. 

After each `mysqldump` backup our script is checking what was the last exported ID from a table which is then saved in `last_tablename_id` file. Each subsequent runs will read that number and only include rows that have not been exported yet.

The `grep -oP "\),\((\d+)` is searching for occurrences of  `),(123` string inside the dump. Then `tac` is reversing the grep's output, `head -1` is taking just the first line and `cut` is removing the starting `),(` characters leaving the the number of a first column (**which is assumed to be an** `id` - **a primary key**).

### Crontab

Add a daily snapshot creation at 2:00 AM by running `crontab -e` and adding below row into your crontab.

```crontab
0 2 * * * cd ~/master && ./make-snapshots.sh
```

# Slave setup

On your slave server, i.e. the one that will pull the newest data from the master database, create a file named `pull-snapshots.sh` and make it executable with `chmod +x`.

```bash
#!/bin/bash
export MYSQL_PWD=PasswordSlave1
# Remove last snapshot
rm dump_tablename.sql 
# Pull a snapshot from the master server
scp username@99.0.0.1:~/master/dump_tablename.sql .
mysql --protocol=TCP --ssl-mode=DISABLED -u username dbname < dump_tablename.sql
```

For `scp` to run without prompting for password you need to [setup key-based authentication](https://unix.stackexchange.com/a/182488) between the two servers.

### Crontab

Add below line into your crontab so it runs 30 minutes later than the master's one, e.g. at 2:30 AM.

```crontab
30 2 * * * cd ~/your/script/path && ./pull-snapshots.sh
```

Voilà! Your database is now synced with the master!

# Further improvements:
- don't rely on `grep` as if there wasn't any new entries in a database then the `last_tablename_id` will be missing the id, e.g. `LAST_TABLENAME_ID=`. It's better to rely on the `mysql` query just before running the `mysqldump`.
- create a special user in the slave's DB that is only authorised for doing `INSERT` in your database in case your master server is compromised and someone has injected a harmful SQL into the `dump_*.sql` files. You don't want to run `DROP DATABASE`, etc.
- check for errors in MySQL dump and do not overwrite `last_tablename_id` file if there was an error (to keep the last successful exported ID)
- if you need anything more then just use [a proper replication setup](https://dev.mysql.com/doc/refman/8.0/en/replication.html)