== auto-restart cron job == #!/bin/sh HOME=/root export HOME analyse() { echo echo "Slave lag analysis" cat /tmp/400.mysql-slave.status echo 'SHOW PROCESSLIST' | /usr/local/bin/mysql echo echo echo echo "sleeping 10 seconds..." sleep 10 echo "SHOW SLAVE STATUS\G" | /usr/local/bin/mysql echo 'SHOW PROCESSLIST' | /usr/local/bin/mysql echo echo echo echo "sleeping 10 seconds..." sleep 10 echo "SHOW SLAVE STATUS\G" | /usr/local/bin/mysql echo 'SHOW PROCESSLIST' | /usr/local/bin/mysql echo echo echo echo "sleeping 10 seconds..." sleep 10 echo "SHOW SLAVE STATUS\G" | /usr/local/bin/mysql echo 'SHOW PROCESSLIST' | /usr/local/bin/mysql echo echo echo echo "Restarting slave..." echo 'STOP SLAVE' | /usr/local/bin/mysql sleep 10 echo 'START SLAVE' | /usr/local/bin/mysql echo echo echo echo "sleeping 10 seconds..." sleep 10 echo "SHOW SLAVE STATUS\G" | /usr/local/bin/mysql echo 'SHOW PROCESSLIST' | /usr/local/bin/mysql } echo "SHOW SLAVE STATUS\G" | /usr/local/bin/mysql > /tmp/400.mysql-slave.status secs=`cat /tmp/400.mysql-slave.status | /usr/bin/awk '/Seconds_Behind_Master/{print $2}'` if [ ${secs} = "NULL" ]; then analyse else if [ ${secs} -gt 1 ]; then analyse fi fi == auto-restart cron job output == Slave lag analysis *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 194.109.206.50 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000650 Read_Master_Log_Pos: 348527585 Relay_Log_File: saffron-relay-bin.000818 Relay_Log_Pos: 341985442 Relay_Master_Log_File: mysql-bin.000650 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: vbulletin,lemongrass Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 342180715 Relay_Log_Space: 348527992 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 1519 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Id User Host db Command Time State Info 153611 system user NULL Connect 565160 Waiting for master to send event NULL 153612 system user lemongrass Connect 1519 Updating UPDATE NetworkLocation SET lastPush='2011-06-18 23:34:41' WHERE host_id=9900 AND externalIp='87.94.4 174547 root localhost NULL Query 0 NULL SHOW PROCESSLIST sleeping 10 seconds... *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 194.109.206.50 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000650 Read_Master_Log_Pos: 348574408 Relay_Log_File: saffron-relay-bin.000818 Relay_Log_Pos: 342004994 Relay_Master_Log_File: mysql-bin.000650 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: vbulletin,lemongrass Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 342200267 Relay_Log_Space: 348574815 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 1521 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Id User Host db Command Time State Info 153611 system user NULL Connect 565170 Waiting for master to send event NULL 153612 system user NULL Connect 1521 Reading event from the relay log NULL 174560 root localhost NULL Query 0 NULL SHOW PROCESSLIST sleeping 10 seconds... *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 194.109.206.50 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000650 Read_Master_Log_Pos: 348620674 Relay_Log_File: saffron-relay-bin.000818 Relay_Log_Pos: 342021356 Relay_Master_Log_File: mysql-bin.000650 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: vbulletin,lemongrass Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 342216629 Relay_Log_Space: 348621081 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 1526 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Id User Host db Command Time State Info 153611 system user NULL Connect 565180 Waiting for master to send event NULL 153612 system user NULL Connect 1526 Reading event from the relay log NULL 174562 root localhost NULL Query 0 NULL SHOW PROCESSLIST sleeping 10 seconds... *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 194.109.206.50 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000650 Read_Master_Log_Pos: 348660066 Relay_Log_File: saffron-relay-bin.000818 Relay_Log_Pos: 342040184 Relay_Master_Log_File: mysql-bin.000650 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: vbulletin,lemongrass Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 342235457 Relay_Log_Space: 348660473 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 1533 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Id User Host db Command Time State Info 153611 system user NULL Connect 565190 Waiting for master to send event NULL 153612 system user NULL Connect 1533 Reading event from the relay log NULL 174564 root localhost NULL Query 0 NULL SHOW PROCESSLIST Restarting slave... sleeping 10 seconds... *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 194.109.206.50 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000650 Read_Master_Log_Pos: 348739533 Relay_Log_File: saffron-relay-bin.000818 Relay_Log_Pos: 342144569 Relay_Master_Log_File: mysql-bin.000650 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: vbulletin,lemongrass Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 342339842 Relay_Log_Space: 348740243 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 1532 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Id User Host db Command Time State Info 174567 system user NULL Connect 10 Waiting for master to send event NULL 174568 system user NULL Connect 1532 Reading event from the relay log NULL 174570 root localhost NULL Query 0 NULL SHOW PROCESSLIST