How to copy data from one server (mysql) to another server(mysql) on regular basis. Need to replicate data

[sushovan majumdar]

Hello,

I have to move data from database server to new database server. Schema is same for both.

I want to write a cron job which would keep replicating data from old server to newer one.

In future I will throw away the old database server. 

I was looking into database routers in django to achieve this (https://docs.djangoproject.com/en/1.9/topics/db/multi-db/) but I couldn't decide how the solution would look like,

I can write a new django app in old server and setup database router in that?

Can some point me to existing solutions regarding this or how can I achieve this?

Thanks

[Mc. Holid]

i think it would be able to be handle by generating syncronisation  script with sql yog and run the script from linux cron, or by using the events if you use mysql as the db

[James Schneider]

On Tue, Jun 14, 2016 at 8:30 AM, sushovan majumdar <sushov...@gmail.com> wrote:

Hello,

I have to move data from database server to new database server. Schema is same for both.

I want to write a cron job which would keep replicating data from old server to newer one.

In future I will throw away the old database server. 

In general, you probably don't want to do this with a cron job, unless you are moving the data for archival purposes, and are not relying on it as live data for a separate instance of your application.

Is this part of a backup strategy?

Are both databases in production?

If you really want/need cron to handle the job, then the mysqldump command will be your friend.

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

I was looking into database routers in django to achieve this (https://docs.djangoproject.com/en/1.9/topics/db/multi-db/) but I couldn't decide how the solution would look like,

I can write a new django app in old server and setup database router in that?

Given that you are concerned with the same data/structure in both databases, DB replication is likely to be much more resilient and more importantly, accurate. It also doesn't require any changes at the application (Django) level, all synchronization is handled by MySQL. I can't think of any reason this would be done at the app level, and it sounds like it would add unnecessary complication to your application, not to mention the performance hit, if it would even work.

 

Can some point me to existing solutions regarding this or how can I achieve this?

http://dev.mysql.com/doc/refman/5.7/en/replication.html

http://dev.mysql.com/doc/refman/5.7/en/replication-solutions.html

 

http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

If you are replicating across the Internet, ensure that the replication traffic is sent over a secure channel (such as SSH), otherwise an attacker can literally listen and grab any data that you are inserting/updating in your DB.

-James