a peek into my MIND

January 26, 2010

Groovy script to restart stopped MySql slave

Filed under: Groovy & Grails — Tags: , , — Bharat Kondeti @ 7:08 pm

We are having an issue where the slave instance of MySql stops replicating with an error “Deadlock found when trying to get lock; try restarting transaction’ on query“. All our tables use InnoDB engine with default settings. So our current configuration uses Statement based replication. The table in question that is causing this problem is using auto-increment for primary key.

After bit of investigation realized that when there are many insert statements being replicated on the slave for the same table, each insert obtains an auto-increment lock and releases it only after the statement is executed. Supposedly this is a problem for statement based replication. So when multiple threads are inserting into the same table in parallel we can get this deadlock.

Of-course we need to do more investigation and fine tune our MySql infrastructure. So when ever the replication stops we have to restart the slave. Blog post Monitoring and Resetting MySQL Replication details how it can be done.

To do this in an automated way we came up with a groovy scripts. A cron job is scheduled for every 15 minutes to execute this script. Following are some higher level steps.

If replication is not working
–stop slave
–reset slave
–issue change master command
–start slave
–check again if replication is working fine, if so
—-send an email that every thing is good and reset was a success
–else
—-send an email slave reset is a failure
else
–do nothing

Following is the groovy code..

import groovy.sql.Sql

def delay = 2000
Sql.loadDriver("com.mysql.jdbc.Driver")
def sql = Sql.newInstance("jdbc:mysql://servername:3306/", "username", "password")

def initialfinalCheck = sql.firstrow("show slave status;")

if('Yes'.equals(initialfinalCheck.Slave_IO_Running) && 'Yes'.equals(initialfinalCheck.Slave_SQL_Running)) {
  println('Everything is working fine')
} else {
  sql.execute("STOP SLAVE;")
  println 'Slave stopped......'
  sleep(delay)

  def row = sql.firstrow("show slave status;")

  def changeMaster = "CHANGE MASTER TO MASTER_HOST=?, MASTER_USER=?, MASTER_PASSWORD='master', MASTER_LOG_FILE=?, MASTER_LOG_POS=?;"

  sql.execute("RESET SLAVE;")
  println 'Reset slave......'
  sleep(delay)

  sql.execute(changeMaster,[row.Master_Host,row.Master_User,row.Master_Log_File,row.Exec_Master_Log_Pos.toLong()])
  println 'Change master executed......'
  sleep(delay)

  sql.execute("START SLAVE;")
  println 'Slave started......'
  sleep(delay)

  def finalCheck = sql.firstrow("show slave status;")

  def today = new Date()
  def people = ['aaa@bbc.com','bbb@bbac.com']
  if('Yes'.equals(finalCheck.Slave_IO_Running) && 'Yes'.equals(finalCheck.Slave_SQL_Running)) {
  def body = """
 Hi All,
   Replication was re-started successfully on ${today}

   ${row}

 Thanks,
xxxxxxxx
  """
  SendMail.email(people,'Replication Re-Started Successfully',body.toString())
  } else {
  def body = """
 Hi All,
    Replication FAILED to re-start on ${today}

    ${row}
	Thanks,
 xxxxxxxxxx
 """
  SendMail.email(people,'Replication FAILED to Re-Start',body.toString())
  }
}
sql.close()

Groovy code to send out an email

import javax.mail.Message
import javax.mail.Session
import javax.mail.Transport
import javax.mail.internet.InternetAddress
import javax.mail.internet.MimeMessage

class SendMail {

  public static void email(recipients,subject,content) {
    def props = new Properties()
    props.put('mail.smtp.host', 'host')
    def session = Session.getDefaultInstance(props, null)

    // Construct the message
    def msg = new MimeMessage(session)

    InternetAddress[] addressTo = new InternetAddress[recipients.size];
    for (int i = 0; i < recipients.size; i++) {
      addressTo[i] = new InternetAddress(recipients[i]);
    }
    msg.setRecipients(Message.RecipientType.TO, addressTo);
    msg.from = new InternetAddress('xxxxxx')

    msg.subject = subject

    msg.setHeader('Organization', 'company name')
    msg.setContent(content,'text/plain')

    // Send the message
    Transport.send(msg)
  }

}
Advertisements

2 Comments »

  1. thx its help me alot

    Comment by Link by umeee — February 5, 2010 @ 2:09 pm

  2. Hi there, just became aware of your blog through Google, and found that it is truly informative. I’m going to watch out for brussels. I will be grateful if you continue this in future. A lot of people will be benefited from your writing. Cheers!

    Comment by http://andcarinsurancequotes.com — March 19, 2012 @ 5:57 pm


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: