Groovy script to restart stopped MySql slave

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..

Connecting to multiple datasources in grails application

Grails inherently doesn’t support multiple data sources. We cannot specify for example like domain objects D1 and D2 should talk to DataSource1 and rest should talk to Datasource2.

To have this kind of flexibility one has to install Datasources Plug-in into the application.

Once installed create a Datasources.groovy file under configuration directory of the application. This groovy file will be a companion file for DataSource.groovy file.

Now we can add as many data-sources as we want in the new Darasource.groovy file as follows.

datasources = {
  datasource(name: 'aaaaDev') {
    domainClasses([D1,D2])
    driverClassName('com.mysql.jdbc.Driver')
    environments(['development'])
    url('jdbc:mysql://aaaa:3306/aaa')
    username('xxxxxx')
    password('xxxxx')
    dialect(org.hibernate.dialect.MySQL5InnoDBDialect)
    hibernate {
      cache {
        use_second_level_cache(true)
        use_query_cache(true)
      }
    }
  }
  datasource(name: 'aaaaTest') {
    domainClasses([D1,D2])
    driverClassName('com.mysql.jdbc.Driver')
    environments(['test'])
    url('jdbc:mysql://aaaa:3306/aaa')
    username('xxxxxx')
    password('xxxxx')
    dialect(org.hibernate.dialect.MySQL5InnoDBDialect)
    hibernate {
      cache {
        use_second_level_cache(true)
        use_query_cache(true)
      }
    }
  }
}

Couple of interesting tags are domainClasses and environments.

domainClasses take in a list of domain classes.
environments take in a list of environments this data source will be applicable for.

The domain classes that are not listed in this file will use the default data-source defined in DataSource.groovy.

Once this information is defined in the new Datasources.groovy everything will work magically.