How to mirror mysql database traffic to another server
I have two server Mysql database in Cent OS 7. Server A(192.168.0.1) is live server and server B(192.168.0.2) is test server. I want to mirror traffic from server A to server B. I used this Commands:
Server A) iptables -A PREROUTING -t mangle -p tcp --dport 3306 -j TEE --gateway 192.168.0.2
Server A) iptables -A POSTROUTING -t mangle -p tcp --dport 3306 -j TEE --gateway 192.168.0.2
Server B) iptables -t nat -A PREROUTING -p tcp -d 192.168.0.1 --dport 3306 -j DNAT --to 192.168.0.2
My problem exist in server B. In server B when i see tcpdump on port 3306 i see same packet on server A. In other word result of tcpdump on both server is same.
I searching several days in web and i tried several solution. One of the solution is define address IP of server A in server B on lo interface. I did it but did not work. Mysql in server B not hit packet and i can not see any established connection.
I see this page and used it but this solution has overhead on server A and number of connection is 3 times.
Is there solution for my problem ?
If you're just trying to keep the two servers' databases in sync, why not just use MySQL replication and make Server B a replica/slave to Server A?
At least in that scenario if a connection faults and replication is stopped (or temporarily lagged), it can be repaired and the replica can be brought up to sync with the master. If you lose a network connection between Server A and Server B and you're simply "spraying" Server B with duplicated network traffic from Server A - there's no guarantee that your data sets will really remain in sync.
Check the docs for configuring replication between the two servers: https://dev.mysql.com/doc/refman/8.0/en/replication.html
You simply can't do this by mirroring TCP streams.
You need an additional "outboard" tool, either a proxy designed for such a purpose (if there is such a thing -- I am skeptical of this strategy, since such an approach would unnecessarily complicate the production environment, but it could be made to work) or something along the lines of Percona Query Playback¹ which uses the MySQL Slow Query Log to capture each query from the active server as soon as it finishes, and replay it on the secondary server.
The Slow Query Log is used -- I suspect -- because it has a cleaner, easier-to-parse format than the General Query Log, which is very difficult to machine-parse and does not provide as much useful information.
Setting @@long_query_time to 0 persuades MySQL Server to consider all queries as meeting the criteria for "slow" and thus eligible to be written to the slow log.
You could also use this same strategy to design your own utility to accomplish exactly the same purpose -- read the logs by tailing this file, and submit the queries for execution on the secondary server using a pool of client threads (for appropriate parallelism).
¹ Percona Query Playback is intended as an example, not an endorsement. I have read about this utility, but do not recall whether or not I have actually used it in the past.
-
09:10
-
09:00
-
08:50
-
08:40
-
08:30
-
08:20
-
08:10
-
08:00
-
07:50
What is different way to store order item and item can have sub item?
- Adding methods to es6 child class 11436 visits
- search for elements in a list 10796 visits
- In Chrome 55, prevent showing Download button for HTML 5 video 8903 visits
- typescript: tsc is not recognized as an internal or external command, operable program or batch file 7201 visits
- RxJS5 - error - TypeError: You provided an invalid object where a stream was expected 6312 visits
- Ionic 2 - how to make ion-button with icon and text on two lines? 5159 visits
- Conflict: Multiple assets emit to the same filename 5097 visits
