Wednesday, February 06, 2008

Monitor MySQL and restart it if it has stopped

I had a need to monitor a MySQL database instance running as a Windows service and then restart it if for whatever reason it had stopped or died unexpectedly. From within my java web application, I created a Quartz job and scheduled it to run once per minute. I manually set the connection timeout to 10 seconds for the jdbc driver so as not to have to wait 30+ seconds if the MySQL instance were down. Here is the code that runs as part of the Quartz job:

Connection con = null;
try {
  DriverManager.setLoginTimeout(10);
  class.forName("com.mysql.jdbc.Driver");
  con = DriverManager.getConnection("jdbc:mysql://[server]:[port]/[dbname]", "[db user]", "[password]");
  // If we did not get a connection, the db must be down. Start it up again.
  if (con == null) {
    Runtime rt = Runtime.getRuntime();
    try {
      rt.exec("net start mysql");
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
} catch (Exception e) {
  // If we did not get a connection and an exception is thrown, the db must
  // be down. Start it up again.
  if (con == null) {
    Runtime rt = Runtime.getRuntime();
    try {
      rt.exec("net start mysql");
    } catch (IOException e2) {
      e2.printStackTrace();
    }
  }
  e.printStackTrace();
} finally {
  // Clean up.
  try {
    if (con != null) {
      con.close();
    }
  } catch (SQLException ignored) {
  
}


This will only work if the instance of MySQL is running on the same box as the java web application. Also, the "mysql" in the command "net start mysql" is the name of the Windows service that I am wanting to start. One last thing, in my case the java web application was running under Tomcat. I had to make sure that the Tomcat Windows service was started with the same Windows account as the MySQL Windows service.

If you have better ways of doing something like this, please share.

No comments: