MySQL common_schema framework – Great find!


I thought I would share an awesome find that was passed to me by Rich Theobald, one of the kick-ass DBAs at Miniclip.com, We had a pretty simple need, that once we started looking at it, turned not so simple:  how do you get a MySQL replication slave’s “seconds behind master” value from a simple SQL command?

This is very easy with the show slave status command, as shown here:

admin@mysqlslave02 [mysql] show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: mysqlmaster01
 Master_User: slave
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysqlmaster01-bin.001360
 Read_Master_Log_Pos: 90397305
 Relay_Log_File: mysqlslave02-relay-bin.004080
 Relay_Log_Pos: 90392890
 Relay_Master_Log_File: mysqlmaster01-bin.001360
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB:
 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: 90392596
 Relay_Log_Space: 90397956
 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: 1
 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: 101

You can see where it says “Seconds_Behind_Master”? Good information there… BUT, what if you wanted to get just that value, and none of the other output you see above?  This would be extremely handy, especially inside a stored procedure where you wanted to modify large sets of data, and wanted to monitor replication lag and throttle the workload accordingly, which is exactly what we wanted to do.  But for some odd reason, its not something readily available other than in the output above.

This led good ol’ Mr. Theobald (who looks uncannily like Danny Bonaduce! Not kidding!) to search for a solution, and he found this gem:

http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/introduction.html

That’s the link for the common_schema framework created by Shlomi Noach (cheers & gratitude, sir!)  If you want to skip my ramblings and go straight to the code, get it here.

This little puppy gave us what we needed, and a whole lot more.  It’s a self-contained module that augments MySQL/MariaDB’s existing administrative abilities with some much sought-after functionality.  Here’s a snippet from the website:

  • Views library: set of powerful views, typically operating and presenting server metadata
  • Routines library: set of complementary routines to MySQL, including metadata handling
  • QueryScript: programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.
  • rdebug: debugger and debugging API for MySQL stored routines.

It supports all MySQL and MariaDB versions >= 5.1.

Amongst all the other cool things it does, it gave us exactly what we needed, which is to capture just replication lag with a simple query. After install, there will be a new common_schema database in your MySQL instance. Within there, there is a view called slave_status which provides direct SQL access to the details of the current slave processes:


mysql> DESC common_schema.slave_status;
 +-----------------------+---------------+------+-----+---------+-------+
 | Field                 | Type          | Null | Key | Default | Extra |
 +-----------------------+---------------+------+-----+---------+-------+
 | Slave_Connected_time  | decimal(32,0) | YES  |     | NULL    |       |
 | Slave_IO_Running      | int(1)        | NO   |     | 0       |       |
 | Slave_SQL_Running     | int(1)        | NO   |     | 0       |       |
 | Slave_Running         | int(1)        | NO   |     | 0       |       |
 | Seconds_Behind_Master | decimal(32,0) | YES  |     | NULL    |       |
 +-----------------------+---------------+------+-----+---------+-------+

It’s interesting to look through the code to see how this particular view was created. This view itself is based on another view called process_repl, and that view is simply based off of information_schema.processlist.  It’s one of those “ah ha!!” moments that you wish you had thought of!

Take a few minutes to dig through what else is in the common_schema framework.  You might find a few gems yourself that you didn’t know you needed until you found it.  Good stuff!!

Leave a Reply