4 ms·

I'd love to see your shell script for checking a slave status.

thaumaturgy 14y ago
Sure. It was a little fiddly to set up but nothing too fancy: #!/bin/sh # MySQL Status check module # Retrieve mysql status from target server statusinfo=`sudo -u tron ssh "tron@$server" "sudo tron mysqlpass \"$mysqlpass\" mysqlstatus"` # Status of Slave_IO_Running status_io=`echo "$statusinfo" | grep -o -e "Slave_IO_Running:[[:space:]]*[[:alpha:]]*" | cut -d ' ' -f 2` # Status of Slave_SQL_Running status_sql=`echo "$statusinfo" | grep -o -e "Slave_SQL_Running:[[:space:]]*[[:alpha:]]*" | cut -d ' ' -f 2` # Seconds behind master status_sec=`echo "$statusinfo" | grep -o -e "Seconds_Behind_Master:[[:space:]]*[[:alnum:]]*" | cut -d ' ' -f 2` # Verify status, send notification if it's bad. status_ok=1 if [ "$status_io" != "Yes" ]; then status_ok=0 echo "Slave_IO_Running on $server: $status_io\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Slave_IO_Running on $server" rob@associatedtechs.com fi if [ "$status_sql" != "Yes" ]; then status_ok=0 echo "Slave_SQL_Running on $server: $status_sql\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Slave_SQL_Running on $server" rob@associatedtechs.com fi if [ "$status_sec" -gt "300" ]; then status_ok=0 echo "Seconds_Behind_Master on $server: $status_sec\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Seconds_Behind_Master on $server" rob@associatedtechs.com fi if [ "$status_ok" -eq "1" ]; then echo "OK" fi This is a module from a set of scripts that I collectively refer to as "tron" (they "fight for my users" :-). The "mysqlstatus" command on the remote server gets routed to a tron client that ends up running, mysql_status () { echo "show master status\G; show slave status \G" | mysql -u root -p"$mysqlpass" } It's probably a bad idea to pass the mysql root user password as a parameter in a command (you have to be certain it's not getting logged anywhere), so don't do that if you decide to make something similar. I prefer it to having a readable file on the server with the password, but it would probably be better yet to set up a specific MySQL user with limited access to specific stuff. It's on my to-do list.
- falcolas 14y ago
  You can use a .my.cnf in the monitoring user's home directory to hold the login and password for all of you mysql actions.
  - deleted 14y ago
    [deleted]
  - thaumaturgy 14y ago
    Oh! Excellent. I didn't know that. Thank you.
thezilch 14y ago
Consider the Percona Toolkit: http://www.percona.com/software/percona-toolkit http://www.percona.com/software/percona-toolkit

Sure. It was a little fiddly to set up but nothing too fancy: #!/bin/sh # MySQL Status check module # Retrieve mysql status from target server statusinfo=`sudo -u tron ssh "tron@$server" "sudo tron mysqlpass \"$mysqlpass\" mysqlstatus"` # Status of Slave_IO_Running status_io=`echo "$statusinfo" | grep -o -e "Slave_IO_Running:[[:space:]]*[[:alpha:]]*" | cut -d ' ' -f 2` # Status of Slave_SQL_Running status_sql=`echo "$statusinfo" | grep -o -e "Slave_SQL_Running:[[:space:]]*[[:alpha:]]*" | cut -d ' ' -f 2` # Seconds behind master status_sec=`echo "$statusinfo" | grep -o -e "Seconds_Behind_Master:[[:space:]]*[[:alnum:]]*" | cut -d ' ' -f 2` # Verify status, send notification if it's bad. status_ok=1 if [ "$status_io" != "Yes" ]; then status_ok=0 echo "Slave_IO_Running on $server: $status_io\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Slave_IO_Running on $server" rob@associatedtechs.com fi if [ "$status_sql" != "Yes" ]; then status_ok=0 echo "Slave_SQL_Running on $server: $status_sql\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Slave_SQL_Running on $server" rob@associatedtechs.com fi if [ "$status_sec" -gt "300" ]; then status_ok=0 echo "Seconds_Behind_Master on $server: $status_sec\n\nSlave status for $server:\n$statusinfo" | mail -s "[Tron] ALERT: Seconds_Behind_Master on $server" rob@associatedtechs.com fi if [ "$status_ok" -eq "1" ]; then echo "OK" fi This is a module from a set of scripts that I collectively refer to as "tron" (they "fight for my users" :-). The "mysqlstatus" command on the remote server gets routed to a tron client that ends up running, mysql_status () { echo "show master status\G; show slave status \G" | mysql -u root -p"$mysqlpass" } It's probably a bad idea to pass the mysql root user password as a parameter in a command (you have to be certain it's not getting logged anywhere), so don't do that if you decide to make something similar. I prefer it to having a readable file on the server with the password, but it would probably be better yet to set up a specific MySQL user with limited access to specific stuff. It's on my to-do list.

falcolas 14y ago
You can use a .my.cnf in the monitoring user's home directory to hold the login and password for all of you mysql actions.
- deleted 14y ago
  [deleted]
- thaumaturgy 14y ago
  Oh! Excellent. I didn't know that. Thank you.

thezilch 14y ago

Consider the Percona Toolkit: http://www.percona.com/software/percona-toolkit http://www.percona.com/software/percona-toolkit