Hello List: I have been crawling the docs, the mailing lists, and the discussion groups looking for options to my issues for several weeks and have found answers to most of them. I have one last nut to crack and if the answer is out there I must be blind. That's why I finally came to the list for help.
Let me give a little background: I need to replicate a "warehouse" database (sort of an OLAP summary) that combines the reporting output from several branch offices back to those offices. Because of our WAN architecture (and other more political reasons), each branch will write their "reports" to a local database (on the branch's LAN) that replicates their information to a central office. The central office will have a script/daemon/program (whatever) that will combine the various reports from each of the branches into a coordinated warehouse database. This warehouse database would be replicated in hub-and-spoke fashion back to each of the various branches that need access to that information. I have already determined that I will need multiple instances of MySQL running at the central office to act as slaves to the branches' "reporting" masters (one instance per branch) and one more instance to act as the master of the compiled "warehouse" database. Each branch *could* run just one MySQL instance and be both a master to their reports database and a slave to the warehouse (at least that is the working plan). If I have to use two instances of MySQL in each branch, that's acceptable, too. Here is where things get sticky. I would like to be able to merge the replicated reports into the warehouse database with the smallest practical delay (I have some very process-driven branch mangers and they feel they "must have" this data sooner than later). I think that I will need to hold off merging records from any particular branch until replication completes with that branch (to maintain relational integrity, transaction boundaries, etc. Nobody said the reports were simple.). Somehow, I need to detect the end of my central slaves' replication cycle so that I can trigger the "merge processing". I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW PROCESS LIST and watch for their status to change. I could start each slave with "--log-slave-updates" and watch the slaves' binlogs for changes. I could watch for the creation/deletion of the slave's relay logs. Basically I wind up using a timer to check the status of (something) and I am afraid that I will miss a status flip between timer ticks. Setting my timer too short will just consume excessive CPU cycles and also be counter productive. The target platform for my central server is tentatively Redhat 9.x with MySQL 4.1.xxx (the stable version when we go live). Can Linux help me here to hook into one of those events? If another OS can provide better hooks into this I can work that into the plan. Can anyone tell me why these ideas would or would not work? Is there a better way to synchronize an external process with the end of a replication event? Are there other options I didn't think of? (I even thought of modifying the MySQL source to produce a special "merge" slave but that will take too many people, too much time, be too task-specific, and may not be compatible with future versions of MySQL to be a good choice.) Please help! Thanks in advance! Shawn [EMAIL PROTECTED] ** delete all the parts with "no" in them to reveal my real address. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
