This is a module for monitoring a pool of replicated master and slave MySQL SQL database servers (latest versions in the 4 series) and insuring that the servers are synchronized.
On UNIX type platforms, it requires the mysqltcl package (at http://www.xdobry.de/mysqltcl/) for connection via the native MySQL protocol, or the tclodbc package (at http://tclodbc.sourceforge.net/) for connection via ODBC (Open DataBase Connectivity).
On Windows platforms, please read the install.txt file.
Data is drawn using the SHOW MASTER STATUS, SHOW SLAVE STATUS, SHOW VARIABLES and SHOW MASTER LOGS query results and initially displayed in 1 table:
There are 9 data columns:
- The database server host name (or IP address) or ODBC DSN (Data Source Name)
- The unique identifier of the server instance in the community of replication partners (all values in this column should be different)
- The replication delay in seconds (shows how late the server is in the replication process, 0 if the server is synchronized with the master(s))
- The database server role (master or slave or both)
- The master database server host name (or IP address) (only if a slave, empty for a master)
- Whether the slave thread is running (yes or no, only if a slave, empty for a master)
- The binary update log file name (the non-index part of which should be common to all servers)
- The replication position in the binary log
- The latest replication related error message
The delay is calculated as follows:
- Among all the monitored servers, the highest log file index with the greatest position is used as a reference (this method ensuring forward compatibility since it does not care whether there is one or several masters for example)
- When a server is found late (its log file index or position do not match the reference determined above), the poll time is added to its delay value (which means that the delay precision is the poll time value)
- When a server is no longer late, its delay is reset to 0
Error handling:
When an error occurs (communicating with the database servers or any other type), cells in the id, delay and position are set to void numeric values (displayed as ?), while all other cells are emptied. A descriptive error message is also generated in such a case.
Module options:
- --dsns
Comma separated list of ODBC Data Source Names (see your database/system administrator if in doubt). In this case, ODBC is used for connecting to the database servers. This option is incompatible with the --hosts option.
- --hosts
Comma separated list of host names or IP addresses (it does not matter whether they are slaves, masters or both, they are displayed in the specified order (first on the top row)). Each entry can include the port number used to connect to the database server, using the traditional host:port notation (see examples below). The 3306 port number is used internally if not specified.
- --user
database servers user name (must be common to all monitored servers, defaults to current user).
- --password
database password for user (must be common to all monitored servers, no default).
Thresholds:
In order to monitor the replicated servers pool, you could, for example:
- Set a threshold (up type, compared to 300) on each of the slave servers delay. For example, if you have chosen a poll time of 30 seconds, setting a threshold of 300 seconds on each of the slaves delay would insure that the administrator gets warned when any of the slaves is more than 5 minutes late in the replication process
- Set a threshold (differ type, compared to an empty string) on each of the slave servers error message. This would insure that the administrator gets warned when any of the slaves is in error, which generally means that it is no longer replicating
- Set a threshold on each of the slave servers running state (differ type, compared to yes)
- Contribute other examples once you yourself become a replication master ;-)
Examples:
$ moodss myreplication --hosts 192.168.0.1,192.168.0.10,192.168.0.11
$ moodss myreplication --hosts master.company.com,slave.company.com:3308
$ moodss myreplication --hosts master.company.com,slave.company.com,backup.company.com --user replicator --password xxxxxx
$ moodss myreplication --dsns mymaster,myslave1,myslave2,myslave3 --user jdoe --password xxxxxx