Introduction

When grappling with database synchronization within internal networks, we may encounter a myriad of challenges. The conventional approach involves exporting data from the source database, compressing and transferring it to the target network, and finally decompressing and importing the data. This method, albeit effective, is cumbersome and requires substantial disk space, especially for larger databases. To circumvent these dilemmas, we can utilize SSH tunnels and the MySQL data compression feature to streamline the data synchronization process.

Constructing an SSH Tunnel

Initially, we need to establish an SSH tunnel between the two networks. This can be accomplished through the following command:

ssh -L 3306:10.10.100.22:3306 -p 22 [email protected] -i id_rsa

This command maps the local port 3306 to the database server’s (10.10.100.22) port 3306 in the target network.

Data Synchronization

Subsequently, we can commence the data synchronization. This can be achieved through the following command:

mysqldump -u root -h 10.10.1.22 --port 3306 \
    --databases db1 db2 \
    --compress \
    --single-transaction \
    --order-by-primary  \
    -ppassword | mysql -u root \
        --port=3306 \
        --host=127.0.0.1 \
        -ppassword

This command initially exports data from the source databases (db1 and db2) located at 10.10.1.22, then pipes the data into the target database. Here, we employ the –compress option of the mysqldump command to reduce the volume of data transmitted across the network. Additionally, we utilize the –single-transaction option to ensure data consistency and the –order-by-primary option to optimize import performance.

It’s worth noting that although the target database address is 127.0.0.1, the data is actually being transported to the target network’s database server via the SSH tunnel.

By following these steps, we can simplify the data synchronization process between databases while also minimizing the required disk space.

Feel free to follow my blog at www.bboy.app

Have Fun