Schedule data transfer service in MySQL

I have two DB servers, i.e Server1 and Server2. I want to transfer data from Server1 to Server2 every morning (at 9:00AM lets say).

How can we a开发者_开发百科chieve it? Can this transfer of data be done automatically?

My choice on a windows machine is to create a batch file that runs mysqldump with the parameters that suite you best.

This batch file can be tied to the windows scheduler for an automated execution at any point in time.

you can consult this page for some guideline by the MySQL community.

Now that you have a dump of your DB your script should send it to the destination server and deploy it locally (can also be done automatically).

• i use the parameters for mysqldump that allows me to incrementally add data to the new server
• i transfer the dump using DeltaCopy which is a community windows wrapper around the rsync program (if interested you should check Syncrify on that page as well)

both of those last point allow for an extremely faster process than copying the entire DB everytime.