Backup Mysql with ssh & mysqldump
Introduction
When dealing with database synchronization within internal networks, we may face various challenges. The traditional method involves exporting data from the source database, compressing it, transferring it to the target network, and then decompressing and importing the data. While this approach is effective, it can be cumbersome and requires a significant amount of disk space, particularly for larger databases. To overcome these challenges, we can simplify the data synchronization process by using SSH tunnels and the MySQL data compression feature.
Setting up an SSH Tunnel
To begin, we need to create an SSH tunnel between the two networks. This can be done using the following command:
ssh -L 3306:10.10.100.22:3306 -p 22 root@x.x.x.x -i id_rsa
This command maps the local port 3306 to the database server’s IP address (10.10.100.22) and port 3306 in the target network.
Data Synchronization
Next, we can start the data synchronization process using 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 exports data from the source databases (db1 and db2) located at 10.10.1.22 and pipes it into the target database. The mysqldump command is used with the –compress option to reduce data transmission volume over the network. The –single-transaction option ensures data consistency, and the –order-by-primary option optimizes import performance.
It’s important to note that even though the target database address is 127.0.0.1, the data is actually being transported to the target network’s database server via an SSH tunnel.
By following these steps, we can simplify the process of synchronizing data between databases while minimizing the required disk space.
Reference
https://www.bboy.app/2023/12/25/introduce-an-operation-for-backing-up-a-database/