2

I am looking for suggestions on the best way to sync mySQL tables (myISAM) from 2 different databases.

Currently we use Navicat to sync tables from our production server to our test server but we have been running into many problems. Just about everyday we have been running into a sync failure on a table.

We get the error below a lot of the times, not to mention Navicat spams our e-mails with successful and unsuccessful syncs(is there anyway to just receive only the unsuccessful syncs?). I also know altering the table in anyway will cause a failure to sync. So altering the table in anyway must be done to the master first (This makes sense but is there any way around this?).

-[Sync] Finished - Unsuccessful Synchronization: List index out of bounds (0)

Is there any reason to not use the Navicat sync? My boss suggested using mySQL replication instead but my first concern is finding why we have so many problems because it seems like we just are misusing the sync thus giving us all these problems.

Thanks.

Bobby
  • 113
  • 1
  • 12

1 Answers1

0

sync tables from our production server to our test server

It sounds like you're trying to replicate your production environment in your test environment, right?

A common pattern to follow in this situation is using a tool like mysqldump to create a backup of the entire database, then later import the backup into the test environment. By doing a complete backup and restore, you're not only ensuring that you have at least one backup method that's known to work, you're also ensuring that the test database can never contain modifications that a sync tool might miss. (Sync tools generally require a primary or unique key on each table to operate effectively.)

The backup and reimport process should be an easy thing for you to automate. At my workplace, we perform a mysqldump-based database dump every night, and perform optional imports into each developer's personal copy of the dev environment early the following morning.

Charles
  • 50,943
  • 13
  • 104
  • 142
  • I guess we have two types of environments. One would be the one you stated where it is truly a test environment of the live site (Which we don't sync). The other is taking data from the live site and making reports for internal use (which we do sync because we need the data up to date) – Bobby Mar 28 '11 at 17:04
  • 1
    @Bobby, for the second case, if you find running the reports disruptive to the main database, that's clearly a case where a read-only slave is a great solution. – Charles Mar 28 '11 at 17:28
  • Thanks for the reply I will give this a shot in the next day or two and let you know how it works out for us. Thanks for the help – Bobby Mar 30 '11 at 12:31