PlanetMysql.ru – информация о СУБД MySQL

If you have tried Quick start guides: MySQL cluster in 10 minutes, you may have realized that it is really quick and easy. However, it leaves some typing to be done. Users of MySQL Sandbox have a horror of repetitive typing, and this got me thinking. "Could I integrate MySQL Sandbox and Cluster?" The answer was: "Sure." But then I started thinking of all the minor and major changes that I wanted to do to the Sandbox and have delayed for too long. What I need, is a radical refactoring. And then I remembered that it has been almost two years since I learned a new programming language and that perhaps I could expand my horizons and the Sandbox architecture at once.

Thus, thanks to an irresistible offer from O'reilly about ebooks, last week I bought both Learning Python, fourth edition and Programming Python, Third edition.
During the week end I produced my first tool: a Python script that installs and starts a small cluster, following the instructions given in the MySQL Cluster quick start guides. The script unpacks the cluster tarball, installs a server sandbox from it, then starts the cluster nodes and the MySQL server, and then it monitors the cluster until all the nodes are connected before finally testing the server.
Here is a sample run:

$ make_cluster.py ~/Downloads/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64.tar.gz

++ tar -xzf ~/Downloads/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64.tar.gz

++ low_level_make_sandbox --basedir=$HOME/python/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64 --sandbox_directory=mcluster --install_version=5.1 --sandbox_port=5144 --no_ver_after_name --no_run --force --my_clause=log-error=msandbox.err --my_clause=ndbcluster 
    The MySQL Sandbox,  version 3.0.12
    (C) 2006-2010 Giuseppe Maxia
installing with the following parameters:
upper_directory                = $HOME/sandboxes
sandbox_directory              = mcluster
sandbox_port                   = 5144
check_port                     = 
no_check_port                  = 
datadir_from                   = script
install_version                = 5.1
basedir                        = $HOME/python/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64
tmpdir                         = 
my_file                        = 
operating_system_user          = gmax
db_user                        = msandbox
db_password                    = msandbox
my_clause                      = log-error=msandbox.err ; ndbcluster
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > 
force                          = 1
no_ver_after_name              = 1
verbose                        = 
load_grants                    = 1
no_load_grants                 = 
no_run                         = 1
no_show                        = 
loading grants
.. sandbox server started
stopping server
Your sandbox server was installed in $HOME/sandboxes/mcluster

++ mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndb_mgmd -f $HOME/sandboxes/mcluster/my_cluster/conf/config.ini --initial --configdir=$HOME/sandboxes/mcluster/my_cluster/conf/
2010-06-28 21:29:57 [MgmtSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.44 ndb-7.1.4b
2010-06-28 21:29:57 [MgmtSrvr] INFO     -- Reading cluster configuration from '$HOME/sandboxes/mcluster/my_cluster/conf/config.ini'

++ mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndbd -c localhost:1186
2010-06-28 21:29:57 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1

++ mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndbd -c localhost:1186
2010-06-28 21:29:57 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1

++ mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 (not connected, accepting connect from localhost)
id=4 (not connected, accepting connect from localhost)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)


++ $HOME/sandboxes/mcluster/clear

++ $HOME/sandboxes/mcluster/start
... sandbox server started
Please wait. Giving the cluster time to catch up
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0, Master)
id=4 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)


It may take up to 2 minutes to initialize ... ( 0 )
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0, Master)
id=4 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)


It may take up to 2 minutes to initialize ... ( 5 )
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0, Master)
id=4 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @localhost  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 (not connected, accepting connect from any host)

[...]

It may take up to 2 minutes to initialize ... ( 100 )
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0, Master)
id=4 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4)

[mysqld(API)] 1 node(s)
id=50 @127.0.0.1  (mysql-5.1.44 ndb-7.1.4)


++ $HOME/sandboxes/mcluster/use -vvv -e "create table test.t1(i int not null primary key)engine=ndb"
--------------
create table test.t1(i int not null primary key)engine=ndb
--------------
Query OK, 0 rows affected (0.45 sec)

++ $HOME/sandboxes/mcluster/use -vvv -e "show create table test.t1\G"
--------------
show create table test.t1
--------------

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

to shut down the cluster, type:
$HOME/python/mysql-cluster-gpl-7.1.4b-osx10.6-x86_64/bin/ndb_mgm -e shutdown

It works! Ans this is a good start to make me feel confident with Python, which I will use to develop MySQL Sandbox version 4. This cluster snippet will probably be made from scratch once the new architecture is in place. For now, it was enough to get the feeling of the language.
PlanetMySQL Voting: Vote UP / Vote DOWN

The latest release of MySQL Sandbox, 3.0.12, has integrated plugin installation features, as mentioned in my previous post. Not only that. This version has also more tests, fixes a couple of bugs, and introduces basic instrumentation. Now each script released with MySQL Sandbox, and every one that the Sandbox itself installs, can leave a trail in a file.

Let's start with the plugin. The documentation has been updated to cover this new feature. And 27 new tests give me some confidence that it should work as advertised.
While I was waiting for the test suite to finish its 238 tests, I was wondering how much was going on under the hood. So I spent one hour implementing some basic instrumentation, not only in the make_* scripts, but also in every script that the sandbox installs. The code is quite modular, and adding this feature was easy.
Now, if you want to use this instrumentation, you need to create a file, and set the operating system variable $SBINSTR to the full path of that file prior to using the Sandbox. Then, every script will leave an entry in that file, saying its name, the current time, and which parameters was using.
This is what I got after running the test suite. 66 instances of MySQL installed to perform over 200 tests, in about 18 minutes.

MySQL Sandbox scripts	calls
make_sandbox	66
low_level_make_sandbox	66
make_replication_sandbox	8
make_multiple_sandbox	7
make_multiple_custom_sandbox	2
Installed scripts	calls
use	440
stop	192
start	128
clear	56
sandbox_action	56
sbtool	34
stop_all	30
use_all	20
clear_all	13
start_all	12
send_kill	11
restart	9
initialize_slaves	8
restart_all	4
change_paths	2
change_ports	1
total	1165

The new release is available from Launchpad or directly from the CPAN
PlanetMySQL Voting: Vote UP / Vote DOWN

I 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
PlanetMySQL Voting: Vote UP / Vote DOWN

On January 15th I will be in Sydney, Au, on my way to Wellington, New Zealand. I will be at the MySQL User Group, hosted at the Sydney Mechanics’ School of Arts at 5.30pm. I will talk about testing complex database systems with MySQL Sandbox. The meeting is open to all. If you want to attend, please register at the Sydney MySQL User Group meetup page.

PlanetMySQL Voting: Vote UP / Vote DOWN

Jeremy Zawodny of Craiglist has written a kind article about MySQL Sandbox. The article, MySQL Sandbox: Treat MySQL Instances like Virtual Machines, is a practical test of MySQL Sandbox with usage examples and warm appreciation. Thanks, Jeremy!

The article was published in July but I noticed it only today. I guess I should pay more attention to my favorite topics when I travel.
PlanetMySQL Voting: Vote UP / Vote DOWN

On Friday afternoon, I went to give a presentation about MySQL advanced features at the Sardegna Ricerche technology park. The presentation included a quick introduction to MySQL Sandbox, something that I have been doing for years, and I thought I could do blindfold, if required. However, something didn't go as expected.

Just when I was showing off how easy is it to install a MySQL sandbox from a tarball, I was faced by an unexpected error. The tar application was not among the recognized ones. As soon as I saw the error, I immediately knew what had happened. That morning I upgraded my Mac OSX to Snow Leopard. And, unknown to me, Apple has changed the default tar, which is not a symlink to the GNU tar, but points at bsdtar. Not a difficult fix (I released a new version of MySQL Sandbox this morning) but not something that you want to deal with during a live demo either.
Apart from that, Snow Leopard seems to behave nicely. Somebody else had nasty compatibility problems but so far I have been spared.
One annoying problem is that Safari crashes with Java applications, because of the faulty Java 1.6 package released with Snow Leopard. Apparently, Java 1.5 and 1.6 both point at the same binaries, on the grounds that 1.6 is backward compatible. Well, it is, but it crashes the browser, so I looked around for a remedy, and I found out that someone else has fixed the issue and shared the recipe. Thanks, folks. It worded for me as well.
PlanetMySQL Voting: Vote UP / Vote DOWN