Skip to main content
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

With regard to the first point

check (and alert) replication is broken?

just check the following

MYSQL_CONN="-uroot -ppassword"
SLAVESTATUS_OUTPUT=/tmp/ShowSlaveStatus.txt
mysql ${MYSQL_CONN} -ANe"SHOW SLAVE STATUS\G" > ${SLAVESTATUS_OUTPUT}
IO_RUNNING=`grep "Slave_IO_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`
SQL_RUNNING=`grep "Slave_SQL_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`

Just check for both IO_RUNNING and SQL_RUNNING being Yes.

If either value is No, check the error message.

You could check for other things such as Seconds_Behind_Master ( Does `Seconds_Behind_Master` show exact Slave Lag from Master?Does `Seconds_Behind_Master` show exact Slave Lag from Master? )

With regard to the second point

get a list of tables that are out of synch across various databases?

The best tool in the world for this is pt-table-checksum. Please read the documentation on it because the output will be all the tables in the specified databases. You will have to parse the text output to compare the checksum values between Master and Slave.

I wrote a part post about using this tool:

With regard to the first point

check (and alert) replication is broken?

just check the following

MYSQL_CONN="-uroot -ppassword"
SLAVESTATUS_OUTPUT=/tmp/ShowSlaveStatus.txt
mysql ${MYSQL_CONN} -ANe"SHOW SLAVE STATUS\G" > ${SLAVESTATUS_OUTPUT}
IO_RUNNING=`grep "Slave_IO_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`
SQL_RUNNING=`grep "Slave_SQL_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`

Just check for both IO_RUNNING and SQL_RUNNING being Yes.

If either value is No, check the error message.

You could check for other things such as Seconds_Behind_Master ( Does `Seconds_Behind_Master` show exact Slave Lag from Master? )

With regard to the second point

get a list of tables that are out of synch across various databases?

The best tool in the world for this is pt-table-checksum. Please read the documentation on it because the output will be all the tables in the specified databases. You will have to parse the text output to compare the checksum values between Master and Slave.

I wrote a part post about using this tool:

With regard to the first point

check (and alert) replication is broken?

just check the following

MYSQL_CONN="-uroot -ppassword"
SLAVESTATUS_OUTPUT=/tmp/ShowSlaveStatus.txt
mysql ${MYSQL_CONN} -ANe"SHOW SLAVE STATUS\G" > ${SLAVESTATUS_OUTPUT}
IO_RUNNING=`grep "Slave_IO_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`
SQL_RUNNING=`grep "Slave_SQL_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`

Just check for both IO_RUNNING and SQL_RUNNING being Yes.

If either value is No, check the error message.

You could check for other things such as Seconds_Behind_Master ( Does `Seconds_Behind_Master` show exact Slave Lag from Master? )

With regard to the second point

get a list of tables that are out of synch across various databases?

The best tool in the world for this is pt-table-checksum. Please read the documentation on it because the output will be all the tables in the specified databases. You will have to parse the text output to compare the checksum values between Master and Slave.

I wrote a part post about using this tool:

Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

With regard to the first point

check (and alert) replication is broken?

just check the following

MYSQL_CONN="-uroot -ppassword"
SLAVESTATUS_OUTPUT=/tmp/ShowSlaveStatus.txt
mysql ${MYSQL_CONN} -ANe"SHOW SLAVE STATUS\G" > ${SLAVESTATUS_OUTPUT}
IO_RUNNING=`grep "Slave_IO_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`
SQL_RUNNING=`grep "Slave_SQL_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`

Just check for both IO_RUNNING and SQL_RUNNING being Yes.

If either value is No, check the error message.

You could check for other things such as Seconds_Behind_Master ( Does `Seconds_Behind_Master` show exact Slave Lag from Master? )

With regard to the second point

get a list of tables that are out of synch across various databases?

The best tool in the world for this is pt-table-checksum. Please read the documentation on it because the output will be all the tables in the specified databases. You will have to parse the text output to compare the checksum values between Master and Slave.

I wrote a part post about using this tool: