mysql data sync

[saggi_sam]

hi,

I have a doubt to clear on. I have two Mysql databases (Remote/live server which is source in this scenario and local/test server which is destination) to be synched for structure as well as data. Synching requirement is only one way i.e. from source to destination. Both are on linux platform. remote server is on some ISP and local is in my intranet. My workstation is windows XP on which i have SqlYog 3.11 version. Now by using this SQLYog, can i sync remote/live server database to the local/test server database. I may retain the unique records of test server (destination) or may delete those. Which is the best way our of the following??? Pls. note” I want exact replica of remote to be there on test server.

1) take a database backup of remote and restore it at test server->> i tried this from my window XP using SQLYog but got error while taking backup of remote server. The error reads

Table Op Msg_type Msg_text

---

---

---

---

test.active_users backup error Failed copying .frm file: errno = 2

2) Create a database script of remote database and then execute it at test server

3) Sync both server by using SQLYog's 'Database syn. utility'?? ->>i tries this as well, selected both database in the utility window, but none of the command buttons gets activated at the bottom?? what could be the problem?? Do i need to upgrade SQLYog. i am using 3.11 version.

4) Any other option/utility which can be used to achieve this goal??

thanx & rgds.

sam

[Shadow]

I'm not sure if the backup option works on remote servers as it copies the .frm, .myd, .myi files and you may not have the necessary rights to access them directly.

I would suggest you to use the 2nd and 3rd methods combined. The initial sync should be performed via script and the succeeding incremental syncs via SQLyog's built-in sync tool.

You may need to upgrade SQLyog as v3.11 uses old (v3.2x) client library and the servers may be of newer versions. You could perhaps try out v3.71 and see if it works!

[Ritesh]

Data Synchronization Tool is only available from SQLyog v3.6 upwards. SQLyog 3.11 is very old and we have added many new features after this release.

SQLyog Job Agent (SJA), the data sync tool of SQLyog is perfect for your case. I will suggest that you download SQLyog 3.71 and try-out the data sync tool.

[saggi_sam]

this is fine, ritesh.

one more doubt. What if i don't have database structure existing on my local machine, and i want to bring entire copy of remote mysql database to the local server. How to achieve that?? So i want entire copy of database to bring down from remote to the local. Once this entire database copy is avilable at local machine then i can apply the data sync tool of SQLyog.

Pls. guide for the above.

thanx in anticipation

[Ritesh]

If the table is not present in the target, SJA will automatically create the table and populate it with data from the source table.

[saggi_sam]

no what i meant was that what if even database is not existing….i.e. i am making a new local machine which will have replica of remote database. So then how do i go??

[Shadow]

You have to create the empty database first, then you'll be able to get it synced to your remote one.

[Author]

Posts