How to mirror mysql database traffic to another server

19
January 13, 2019, at 06:10 AM

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 ?

Answer 1

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

Answer 2

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.

READ ALSO
How to calculate time diffrence in mysql

How to calculate time diffrence in mysql

I am storing the user activity in the table by start and end time , now i want to calculate how much overall time a user spends under each subject

19
Supress information in mysql prompt

Supress information in mysql prompt

I ran the query in mysql prompt , I need to supress the "1 row in set (000 sec)" in output prompt

11
Union working in localhost MySQL Version 5.5.8 but not working on server MySQL - 5.7.24

Union working in localhost MySQL Version 5.5.8 but not working on server MySQL - 5.7.24

I have a table in which i am fetching Left & Right child of Parent via union query but there is a problem its working fine on localhost but union query fetch only first select statement on live server

38