MySql - replication monitoring tool

Question

I have mysql master / slave replication.

Im looking for a tool that will allow me to track replication (see no errors, lag checking, etc.)

I prefer a visual tool that will allow all team members to receive status visibility rather than a script.

any ideas?

Answer 1

We use the following bash script. You can do the same idea in php and web-based code.

#!/bin/sh ## Joel Chaney## ## [e-mail] (look at robots.txt) ## ## 2012-02-03 ## repeat_alert_interval=30 # minutes for lock file life lock_file=/tmp/slave_alert.lck # location of lock file EMAIL=[e-mail] # where to send alerts SSTATUS=/tmp/sstatus # location of sstatus file ### Code -- do not edit below ## NODE=`uname -n` ## Check if alert is locked ## function check_alert_lock () { if [ -f $lock_file ] ; then current_file=`find $lock_file -cmin -$repeat_alert_interval` if [ -n "$current_file" ] ; then # echo "Current lock file found" return 1 else # echo "Expired lock file found" rm $lock_file return 0 fi else touch $lock_file return 0 fi } SLAVE=mysql $SLAVE -e 'SHOW SLAVE STATUS\G' > $SSTATUS function extract_value { FILENAME=$1 VAR=$2 grep -w $VAR $FILENAME | awk '{print $2}' } Master_Binlog=$(extract_value $SSTATUS Master_Log_File ) Master_Position=$(extract_value $SSTATUS Read_Master_Log_Pos ) Master_Host=$(extract_value $SSTATUS Master_Host) Master_Port=$(extract_value $SSTATUS Master_Port) Master_Log_File=$(extract_value $SSTATUS Master_Log_File) Read_Master_Log_Pos=$(extract_value $SSTATUS Read_Master_Log_Pos) Slave_IO_Running=$(extract_value $SSTATUS Slave_IO_Running) Slave_SQL_Running=$(extract_value $SSTATUS Slave_SQL_Running) Slave_ERROR=$(extract_value $SSTATUS Last_Error) ERROR_COUNT=0 if [ "$Master_Binlog" != "$Master_Log_File" ] then ERRORS[$ERROR_COUNT]="master binlog ($Master_Binlog) and Master_Log_File ($Master_Log_File) differ" ERROR_COUNT=$(($ERROR_COUNT+1)) fi POS_DIFFERENCE=$(echo ${Master_Position}-${Read_Master_Log_Pos}|bc) if [ $POS_DIFFERENCE -gt 1000 ] then ERRORS[$ERROR_COUNT]="The slave is lagging behind of $POS_DIFFERENCE" ERROR_COUNT=$(($ERROR_COUNT+1)) fi if [ "$Slave_IO_Running" == "No" ] then ERRORS[$ERROR_COUNT]="Replication is stopped" ERROR_COUNT=$(($ERROR_COUNT+1)) fi if [ "$Slave_SQL_Running" == "No" ] then ERRORS[$ERROR_COUNT]="Replication (SQL) is stopped" ERROR_COUNT=$(($ERROR_COUNT+1)) fi if [ $ERROR_COUNT -gt 0 ] then if [ check_alert_lock == 0 ] then SUBJECT="${NODE}-ERRORS in replication" BODY='' CNT=0 while [ "$CNT" != "$ERROR_COUNT" ] do BODY="$BODY ${ERRORS[$CNT]}" CNT=$(($CNT+1)) done BODY=$BODY" \n${Slave_ERROR}" echo $BODY | mail -s "$SUBJECT" $EMAIL fi else echo "Replication OK" fi 

Answer 2

 #!/bin/bash HOST=your-server-ip USER=mysql-user PASSWORD=mysql-password SUBJECT="Mysql replication problem" EMAIL=[e-mail] RESULT=`mysql -h $HOST -u$USER -p$PASSWORD -e 'show slave status\G' | grep Last_SQL_Error | sed -e 's/ *Last_SQL_Error: //'` if [ -n "$RESULT" ]; then echo "$RESULT" | mail -s "$SUBJECT" $EMAIL fi 

Answer 3

You can use any programming language to query mysql and get results from:

 show slave status; <-- execute on slave show master status; <-- execute on master 

If you think this is a bad idea, install phpmyadmin , there is already a built-in graphical interface for monitoring replication, for example: http://demo.phpmyadmin.net/master-config/ (replication)

Answer 4

I used several different approaches, the simplest of which is using a PHP web page to check the status of the slave, and then getting standard monitoring tools to monitor the page. This is a good approach, as it means that existing monitoring tools can be used for alerts by checking the web page.

Example: checking the status of a database server on the db1.internal node

http://mywebserver.com/replicationtest.php?host=db1.internal

Always need to return "Yes"

replicationtest.php:

 <?php $username="myrepadmin"; $password=""; $database="database"; mysql_connect($_REQUEST['host'],$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="show slave status;"; $result=mysql_query($query); $arr = mysql_fetch_assoc($result); echo $arr['Slave_SQL_Running'] ; mysql_close(); ?> 

You can also control Seconds_Behind_Master, Last_IO_Errno, Last_SQL_Errno, etc. You can control this web page from the outside or add it to many standard monitoring tools that can check the web page. I used the free service http://monitor.us

Alternatively, if you do not mind running third-party code in your internal http://newrelic.com infrastructure, we offer excellent server monitoring tools using the network interface and include the MySQL plugin, which provides a lot of great information, such as Query Analysis, InnoDB metrics and replication status using delay monitors. The new Relic specializes in monitoring web applications, but the free service allows you to track an unlimited number of servers.

I am currently using a combination of these tools with the above web page used to launch emergency alerts and NewRelic tools to view long-term performance and trend analysis.

Answer 5

The question arises:

• You want to know if your Mysql replication is ok.
• Or do you want to know if your data is compatible?

You cannot rely solely on the output of SHOW SLAVE STATUS to find out if your subordinate matches Master: a (bad) attempt to solve the problem that stopped your replication may mean that some INSERT or UPDATE or something else did not happen on your slave.

To check this, you should read the SHOW SLAVE STATUS, of course, everything should be fine on this output, but you also need to compare the data (for example, the number of lines, checksum, ...).

I wrote a PHP tool for this: https://bitbucket.org/verticalassertions/verticalslave It has:

• check replication (checks the status values ​​of the slave, check table, checksum table, ...)
• check replication using automatic repair (same as above + dump of replicated tables in error)
• dump of unreplicated databases (specified in the configuration)
• reset replication when you broke everything to the ground - basically, dumping replicated databases and starting a slave).
• send the abbreviated report by mail, a link to the full report on the version of the website
• keep past reports
• can be run from the CLI (crontab) or manually from the website you created.

Feel free to develop and improve. I'm sure some tools are better (especially in the xD layout), but I need a tool that does exactly what I ask for, and no fancy things that I could not understand.

Answer 6

If you are just wondering if the slave is being updated or not:

mysql 'your connection information' -e 'show slave status \ G' | grep -i seconds_behind