Currently I am considering implementing a MySQL table replication solution for a client, this client has 2 servers in 2 different locations:
- Server 1 running an application with mysql databases (Running Ubuntu 14.04 LTS)
- Server 2 has a PHP webserver frontend to serve it's customers with a website and customer panel (also Ubuntu, same version).
The databases on server 1 total around 2.5 GB in size. The servers are in different data centers but within 5ms latency of one another both with gigabit uplinks and heavy dataplans.
Currently the webserver(2) relies completely on the availability of the application server (1). Though this is essential it does mean that the webserver is effectively useless in case of maintenance or unscheduled downtime, the client does not wish to synchronize the databases to both locations but would allow the synchronization of a single table of account login data (just the one table, no other tables from the same database) to at least keep the bare functionality of the website intact.
Now the 2 options I currently am considering are: A. Setting up MySQL Replication Master->Slave setup for this table B. Using a "smart/efficient" table sync script (https://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html)
My consideration is also that the binary log of the application mysql server is around 8.5Gb a day and I was unable to find out the exact performance impact of this is on replication. I can say that the table of which I speak is 88MB and has around ~250.000 rows. The daily changes to the account table I wish to synchronize are very minor, I expect less than 1000 rows a day).
I am currently leaning towards option B because
- It would avoid future issues on mysql version conflicts between servers (which I do not both manage for the client)
- The amount of data changes are rather light on the specific table I wish to synchronize.
- The methods MySQL offered to synchronize just specific tables seemed to fall short as the replication technology puts more emphasis for use as a full replication solution (in this my fear that big binary logs would have a negative impact based on mysql forum/article comments)
- "to the second" data accuracy is not an concern for the client, a sync every hour would already suffice (his words).
My question then of course is if anyone can provide information on if option A or B (or another) would be preferred in this scenario and more importantly why?
Please note: I aim to keep the core setup which the client runs intact, the application or web server cannot switch to another database engine.