How to copy tables from one DB to another DB Of Di

Question

I Have Two servers where we want to copy some tables from DB1 Of one server to DB2 Of another serve on daily basis

On DB1 We are updating database regularly

Van any one have Idia about same

Answer 1

If you want to keep both server/db in sync then best option is master/slave replication.

But if you don't want to setup replication then you can use a script, which will take dump from server1/db1 and restore on server2/db2 on daily basis, even you can avoid locking on your production server by --single-transaction option.

You can use below statement in your script and schedule it in less traffic hours (night) through cronjob.

mysqldump --single-transaction -h server1_ip -uroot -proot123 mydb1 table1 table2 table3 | mysql -h server2_ip -uroot -p root123 mydb2

Answer 2

Use Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).