Database synchronization using two-way mySQL between a local and local production server

Question

So the scenario is this:

I have a mySQL database on a local server running on Windows 2008 Server. The server is intended only for users on our network and contains information about the production schedule of our company. I have what is essentially the same database that runs on a Linux-based hosting server that needs to be available online so that our customers can connect to it and update their orders.

What I want to do is two-way synchronization of two tables in the database, so that the orders are current in both databases and one-way synchronization from our server to the one placed with data in other tables, The front of the database is written in PHP. I’ll tell you what I’m working with so far, and I would be grateful if people could tell me if I’m on the right track or barking the wrong tree, and I hope I will point me in the right direction.

My first idea is to do (at the end of the PHP scripts that generate the changes in the order tables) export the changes that were made, possibly using INSERT in OUTFILE WHERE account = account or something like that. This will keep the file size small, and not export the entire order table. I am in how (A) to export this as an SQL file, not a CSV (B), how to include information about what was deleted and what was inserted (C), how to extract this file on another server and execute the SQL statement.

I am currently studying SSH and PowerShell, but I cannot formulate a clear vision of how this will work. I am looking at cron jobs and scheduled windows tasks. However, it would be better if some updates simply occurred whenever, instead of changing the schedule, and not according to the schedule, in order to synchronize them in real time, but I can’t understand what it is. I would like to run the scheduled task / cronjob at least once every few minutes, although, I think, all he had to do was check if there are any dump files that needed to be placed on the opposite server synchronizing nothing if nothing has changed.

Has anyone ever done something like this? We are talking about changing / adding / removing from 1 (min) to 160 rows (max.) In the tables at a time. I would like to hear people's thoughts about this, because I continue to study my options. Thanks.

Also, to clarify, I'm not sure if one of them is really a master or slave. There is not always accurate data; these are the most recent data that should be in both.

+1 Another note Another thing that I’m thinking about right now is to add at the end of the order an update script on one side of the other config / connect script pointing to another server database, and then repeat the same requests, since they have the same structure Now it just sounds easy .... Thoughts?

Answer 1

You may not know that MySQL itself can be configured with databases on separate servers that are opportunistically synchronized with each other. See here for some details; also, search around for MySQL ring replication. The setup is a bit fragile and will require you to learn a little about MySQL replication . Or you can create a cluster; a much higher learning curve, but less fragile.

If you really want to give it up on your own, you have quite an adventure in design ahead of you. The biggest problem you have to solve is not how to make it work, but how to make it work correctly after one of the servers goes down for an hour, or your DSL modem melts, or the hard drive fills up or...

Answer 2

Running a request on the local and remote server can be a problem if the connection is interrupted. Better each request is locally stored in a file such as GG-MM-DD-HH.sql, and then send data every hour when the hour has expired. For example, the update period can be reduced to 5 minutes.

Thus, if the connection is interrupted, the re-establishment takes care of all the left files. At the end of the file, insert CRC to check the contents.