Monday, May 24, 2010

MySQL Sandbox meets plugins

Sandbox and pluginsI saw it coming.
In the past year I have been working more and more with plugins. The InnoDB plugins, the semi-synchronous replication plugins, the Gearman UDFs, the Spider plugins, and every time I found myself doing a long installation process, with inevitable mistakes.

So I told myself "I wish I had a tool that installed plugins instantly and painlessly, the way MySQL Sandbox installs a server.
There you go. So yesterday I had enough of suffering and have put together an installation script that does a good job of installing several plugins with little or no effort.

Overview

How does it work? For now, it's a separate script, but it will soon end into SBtool, the Sandbox tool.
Plugins need different operations, and the difficult part is finding a clear way of describing what you want to do, and how. But once you have come up with that set of instructions, there is seldom need to revisit it.
So the principle is th create a set of templates, one for every plugin, where you explain to the installation script what you want to do.
Having installed several plugins repeatedly in several versions of MySQL, I now have a good understanding of the process, and having gone through the motions of explaining the procedure to a Perl script, I feel that I know the process even more. That is, if you want to understand a process, script it. For if you want to script a process, you really need to understand what's going on.

The template


The template was not easy to write. After some bargaining with myself, I decided that the best format was Perl itself.
Let's see, for example, the InnoDB plugin

innodb => {
all_servers =>
{
operation_sequence => [qw(stop options_file start sql_commands )],
options_file =>
[
'ignore_builtin_innodb',
'plugin-load='
.'innodb=ha_innodb_plugin.so;'
.'innodb_trx=ha_innodb_plugin.so;'
.'innodb_locks=ha_innodb_plugin.so;'
.'innodb_lock_waits=ha_innodb_plugin.so;'
.'innodb_cmp=ha_innodb_plugin.so;'
.'innodb_cmp_reset=ha_innodb_plugin.so;'
.'innodb_cmpmem=ha_innodb_plugin.so;'
.'innodb_cmpmem_reset=ha_innodb_plugin.so',
'default-storage-engine=InnoDB',
'innodb_file_per_table=1',
'innodb_file_format=barracuda',
'innodb_strict_mode=1',
],
sql_commands =>
[
'select @@innodb_version;',
],
startup_file => [ ],
},
},

The first thing that you notice is that there is an all_servers section. This means that any server can get the same treatment, as opposed to the semi-synchronous plugin, where master and slave need different plugins and commands.
Then comes the operation_sequence, where we decide the order of the operations.
Inside options_file we put the commands that we want inside a my.cnf.
The sql_commands section has a list of queries that the script runs when instructed.

semisynch => {
master =>
{
operation_sequence => [qw(stop options_file start sql_commands )],
options_file =>
[
'plugin-load=rpl_semi_sync_master=semisync_master.so',
'rpl_semi_sync_master_enabled=1'
],
sql_commands =>
[
'select @@rpl_semi_sync_master_enabled;'
],
startup_file => []
},
slave =>
{
operation_sequence => [qw(stop options_file start sql_commands )],
options_file =>
[
'plugin-load=rpl_semi_sync_slave=semisync_slave.so',
'rpl_semi_sync_slave_enabled=1'
],
sql_commands =>
[
'select @@rpl_semi_sync_slave_enabled;'
],
startup_file => []
},
},

By contrast, the semisynch plugin looks comparatively more complex, with its two sections for master and slave. But as you look closely, you recognize the two operations described in the manual, and you feel that you could deal with them easily.

The script


The script was not much difficult to write. Since it only works with MySQL Sandbox instances, it leverages on the predictability of each server.
There is quite a lot of complexity inside, though, because the script checks every possible source of trouble before actually running the instructions from the template.
The script needs two parameters: a directory containin a sandbox, and the name of the plugin. It expects the plugin definition template (named plugin.conf to be in the destination directory, or in the $SANDBOX_HOME directory.
It recognizes if the target path is a single or multiple sandbox. If it is multiple, it installs the given plugin in every server. It also recognizes if the server is a master or a slave, and pulls the appropriate section from the template when required.

$ perl set_plugin.pl $HOME/sandboxes/rsandbox_5_1_47 innodb
executing "stop" on slave 1
executing "stop" on slave 2
executing "stop" on master
Installing <innodb> in </$HOME/sandboxes/rsandbox_5_1_47/master/>
. sandbox server started
--------------
select @@innodb_version
--------------

+------------------+
| @@innodb_version |
+------------------+
| 1.0.8 |
+------------------+
1 row in set (0.00 sec)

Bye
Installing <innodb> in </$HOME/sandboxes/rsandbox_5_1_47/node1/>
. sandbox server started
--------------
select @@innodb_version
--------------

+------------------+
| @@innodb_version |
+------------------+
| 1.0.8 |
+------------------+
1 row in set (0.00 sec)

Bye
Installing <innodb> in </$HOME/sandboxes/rsandbox_5_1_47/node2/>
.. sandbox server started
--------------
select @@innodb_version
--------------

+------------------+
| @@innodb_version |
+------------------+
| 1.0.8 |
+------------------+
1 row in set (0.00 sec)

Now there is no excuse for testing servers with plugins.
There is still some TODO, most notably testing, fixing conflicts that may happen when two plugins fight for the same plugin-load statement, and integrating with sbtool, as said before. But for now, it is enough.
You can try i, by using the script and the template

1 comment:

Pavel said...

That's some great news! Thanks for writing the script/template, This will be quite a time saver for me :)