Skip to main content

Initialize a MySQL slave server located on another network than the master [Resolved]

it's my first question here and English is not my native language but I'll try to explain.

I've a master MySQL server with a public IP address running in my provider infrastructure and I want to run a local MySQL slave server in my office that will replicate all the data for testing purpose.

Setting up the replication works perfectly, I created a SSH tunnel to have my slave reading the binlog from the master, here everything is fine.

My problem is to set up the data from the master. Usually when I want to load the data from the master to any slave on the same network, I run the following command on the master :

mysqldump 'master' --master-data=1 | mysql 'slave' 

but here I can't have any IP for the slave because it's located in my office behind a series of NAT routers...

Does anybody have a solution, knowing that I can't stop the master and there is about 50GB of data on it. If you have any other solution to make a 'hot' data transfer from a master to slave I'm also very interested.

Thank you by advance.

Question Credit: Remiz
Question Reference
Asked July 18, 2019
Posted Under: Network
2 Answers

Why you don't just forward it to aan file and transfer it later by scp or rsync?

mysqldump master --master-data=1 >master.dump

on slave

mysql <master.dump

Alternate, run the mysqldump command on the slave not on the master. Maybe it will work better without the error above.

Hint: Maybe you should dump all databases not just one?

credit: Patrick Mevzek
Answered July 18, 2019
Your Answer