How to keep two mysql databases in sync when both of them have new records
I have a mysql database installed on localhost for our local network (php application) and another copy of the same application and database for remote users is installed on my online server.
There are several records that have problems with primary keys: the scenario is if the user enters a new record with primary key # 4 on the online server and there is already another record on the local server with primary key # 4, so the application will run into problems.
Note. I am unable to put my local server on the network due to limited bandwidth. So the question is that I should be using an online server for local and remote
So can someone tell me how to keep both my databases in sync without getting into a problem?
source to share
What I would recommend you to use is Master Master Master replication. I myself use it on my laptop and host computer to sync MySQL databases with each other (both development systems).
Suppose the online server is Server 1 and the LAN server is Server 2. The following code you have to add to the MySQL my.ini file on Server 1
#Config for server 1
server-id=1
log-bin=mysql-logbin
log-slave-updates
replicate-same-server-id=0
auto_increment_increment=2
auto_increment_offset=1
report-host=#IP ADDRESS of server 1#
binlog-ignore-db=MySQL
and my.ini file on server 2
#Config for server 2
server-id=2
log-bin=mysql-logbin
log-slave-updates
replicate-same-server-id=0
auto_increment_increment=2
auto_increment_offset=2
report-host=#IP ADDRESS of server 2#
binlog-ignore-db=MySQL
Than in phpmyadmin you can create "replication" users. Add a user to server 1 and connect from server 2 with the user data of server 1 to server 1. Server 2 is now slave server 1. The opposite is also: create a replication user on server 2 and add it to server 1. Both servers are now master of each other And they will constantly sync!
It is important to consider that the online server must have access to the LAN server, so you may need to set up network address translation or some port forwarding.
IF you don't want both servers to sync all the time, but you want to access the primary key issue, and not just add this to my.ini on both servers.
#Config for server 1
auto_increment_increment=2
auto_increment_offset=1
#Config for server 2
auto_increment_increment=2
auto_increment_offset=2
Now server 1 will only use 1, 3, 5, 7, 9, 11 ... as the primary key, and server 2 will only use 2, 4, 6, 8, 10, 12 ... as the primary key. This way your problem is solved.
Note. I believe the best way to do Master-Master replication is to delete everything from the database on both servers. Than reset all the log files (this can be done manually by deleting them from the data directory and editing a few numbers in other files), but can also be done with the command (a lot of savings). Then configure the replication users. Since both servers are empty and have no logs, nothing happens. Finally, add all databases to one of the servers, within a few seconds, both databases should be in sync!
Good luck!
source to share