As part of integration solutions we often use MySQL with replication and offset indexing.
99% of the time this works fine. Just do not shut down the 2 Servers at the same time without giving them time to settle etc.
Monitoring / generating an alarm for Mysql Replication within ignition is a bit of a problem, let alone the whole re-synchronizing issue.
What i was thinking of doing is having a module for this purpose.
The module will need to meet following requirements / milestones:
Gateway configuration:
1.1 give the addresses / connection details etc of the two redundant databases.
1.2 Local drive space which is shared across a network (will be used later for restoring replication if it brakes)
Gateway display wicket on the Status or main page:
2.1 Displays the connection is OK/Valid to both Databases
2.2 Displays Main Parameter / Timeout information for replication (Similar to show slave status \ G)
2.3 Displays Last error
2.4 If an error occurred where the replication is broken Have a Fix Button available for Admin only.
2.5 Fix Button Action (Re-synching the two DB’s):
2.5.1 Get the “show master” Status of the Database that is OK and stop its slave
2.5.2 Backup same database to shared drive location
2.5.3 Restore Database backup to broken Database
2.5.4 Give the Restored Database the correct Masters Log position
2.5.5 Start up the Restored Databases slave
2.5.6 Get Restored Databases Master Position
2.5.7 Give the Masters Database the Slaves Database Master Position
2.5.8 Start up the Master Database slave
Vision Component to display items like the Gateway wicket, but without the Fix Button
3.1 Also allow a SQLTag to be created to display Alarm Message etc
Possibly email fault / Gateway log trace etc
Now i know this seems like a lot of work, but i think its doable.
Watch this space as i may have loads of questions coming as i am only starting to get this SDK up and running.
Haven’t programmed that much in java since 2004.
As a start some pointer on where to start would be nice.
Welcome to the party! This sounds like a great idea for a module. I’ve used circular replication with customers as well, and just use some custom screens with the show slave status / show master status data pulled out and available. It sounds like your module would be a much more robust way to do this.
The module SDK is pretty good. It is missing documentation in a few areas, but the developers are really good about helping out and answering questions on these forums for parts that aren’t quite clear. The manual is also pretty good for the sections that have specific information written about them.
My recommendation to get started is to pull the sample projects into Eclipse, and then grab the Playground module and pull it into Eclipse as well. The Playground project is a little Kevin McClusky special. You can find it here:
You can think of the Module SDK as a 3-in-1 SDK. You’ll write a separate program for the Gateway, the Designer and the Client, and programming against a different API for each. (Depending on how you build your module, you can certainly share code amongst the three though.) Ignition calls these “scopes”, so you’ll be writing part of your module for the Gateway scope, the Designer scope, and the Client scope.
When Ignition loads your module, it loads up one or more classes for each scope, and then runs your code in your hook class. That code can do anything. (You can even launch your own swing windows, load in separate java libraries, program a full game; anything.)
Have fun! Post back with progress if you’d like. Heck, if you make it open source, I might even contribute
I have come accross your blogspot alread. Good Work and insight for for initial getting started.
Have a long way to go yet as i am only downloading all the stuff i need as i write this.
i already have a module written to monitor MySQL database statistics, and publishes that data to system SQLTags.
On a side note, I think circular replication will go the way of the dodo, now that MySQL + Galera is available. I believe this will eventually be committed to the main branch of mysql.
Does your module monitor the replication also?
Having spoken with MySQL developers recently i don’t think galera will become part of the main branch for quiet some time, since MySQL Enterprise supports a lot of these features.
Even Mysql Comunity supports them if you know how.
Unless i have overlooked somthing. which may well be the case.
Any chance of your module code being published?
Does your module monitor the replication also?
Having spoken with MySQL developers recently i don’t think galera will become part of the main branch for quiet some time, since MySQL Enterprise supports a lot of these features.
Even Mysql Comunity supports them if you know how.
Unless i have overlooked somthing. which may well be the case.
Any chance of your module code being published?
Regards
Sascha Hlubek[/quote]
Yes, it does monitor mysql replication. It monitors statistics per data source.
As for publishing the module, it will be a commercial module so if I post it, it will be in a trial mode. As for general availability, it will be available mid to late November.
I will have to look into the repair portion of the code. Off the top of my head, there is already great tools that exist to re-sync the databases (SQLYog is the one I use), so I cant see the ROI on writing this portion of code.
There will be extra cost to this module, as it is not written by Inductive, and we have chosen to not open source the module. It will eventually include other databases as well.
