DB: mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using
readline 5.1
I have two separate servers running MariaDB 5.5, both in production.
Both setups are live, and users might access any setup they want.
I need to sync certain table columns from these two databases.
The tables I need to be synced are Userprofile and Devicedetails. Both have a lastlogin and lastused column respectively, and the one with the later date would need to be replaced in the other database.
Schema for Userprofile:
+-------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+------------------+------+-----+---------+----------------+
| ProfileId | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Username | varchar(255) | NO | | NULL | |
| Disabled | int(11) | NO | | 0 | |
| Tokenhash | varchar(50 | NO | | 0 | |
| LastLoginTime | datetime | NO | | NULL | |
+-------------------------+------------------+------+-----+---------+----------------+
Schema for Deviceprofile:
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| Userid | varchar(255) | NO | | NULL | |
| Registerdate | date | YES | | NULL | |
| Approved | int | YES | | NULL | |
| MACaddress | varchar(512) | NO | | NA | |
| Hostname | varchar(255) | NO | | NA | |
| Domain | varchar(255) | NO | | NA | |
+------------------+--------------+------+-----+---------+-------+
As a user logs into a server, their lastlogintime would update. If they create a new secret, that would also update (only used for login, think something like a TOTP Token). This user could tomorrow login to the other server, and they should be able to use their existing tokens.
Similarly, their device details would be captured, and Approved would be set to 1, via an external process. This should also be carried over, in case the user accessed the other setup the next day.
If the data from the remote is latest, it should replace in the current table.
I have looked into I..ODKU, but it seems that requires a primary key to clash to update the row, but Profileid is auto-increment, so that would just add rows to the table.
So far, I've exported the data I need into a CSV, and I'm stuck at how I would check which is greater, by looping over the file and checking each individual entry, but there must be a better way of doing this that is far more efficient.
Best Answer
What you likely want to use is called Replication, which is a methodology for keeping two databases across two servers in sync with each other. Please see the MariaDB documentation here on how to set it up: Setting Up Replication
Also I think you'll probably want to understand the different Binary Log Formats before you set it up as well.