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

MySQL::Sandbox 3.0.24 was released yesterday, with many new features.

More than vanilla MySQL

If you have missed my previous announcement, here's the gist of it. MySQL Sandbox can now deal with tarballs from either Percona Server or MariaDB. The main difference after this change is that you can now create a directory called <PREFIX>5.5.16 and make_sandbox will recognize it as well as the plain 5.5.16.

$ make_sandbox --export_binaries --add_prefix=ps \
   Percona-Server-5.5.11-rel20.2-114.Darwin.i386.tar.gz  \
   -- --sandbox_directory=msb_ps5_5_11
unpacking Percona-Server-5.5.11-rel20.2-114.Darwin.i386.tar.gz
[…]
installing with the following parameters:
upper_directory                = /Users/gmax/sandboxes
sandbox_directory              = msb_ps5_5_11
[…]
basedir                        = $HOME/opt/mysql/ps5.5.11
tmpdir                         = 
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_ps5_5_11

After the binary export, subsequent installations will be easier:

$ make_sandbox ps5.5.11

The same commands can be used for MariaDB. At the moment, make_sandbox does not recognize other packages, but adding them should not be a big deal, provided that such packages look like MySQL. It wouldn't work with Drizzle, because it lacks the main ingredients for MySQL installation.

High Performance sandboxes

While testing parallel replication and prefetch slaves with Tungsten Replicator, I realized that I was doing too much manual fiddling with my scripts. Since I need more performant servers, I added the basic items that I need to modify to enable a faster server. Now, using the '--high_performance' option with make_sandbox, you get a server that is much better than out-of-the-box MySQL. To avoid problems with too much RAM, I am using a default of 512 MB for InnoDB, which is not enough for really demanding tests, but at least it is a good placeholder in the sandbox configuration file, should you need to modify it.

$ make_sandbox 5.1.60 -- --high_performance
[…]
innodb-flush-method=O_DIRECT ; \
innodb-log-file-size=50M ; \
innodb_buffer_pool_size=512M ; \
max_allowed_packet=48M ; \
max-connections=350 ; \
innodb-additional-mem-pool-size=50M ; \
innodb-log-buffer-size=50M ; sync_binlog=0 ; \
innodb-thread-concurrency=0 ; log-error=msandbox.err
[…]

Standalone masters and slaves

MySQL Sandbox has had the ability of creating replicated systems for years. Yet, sometimes you need a stand-alone master server that you want to use for some odd experiment. Similarly, you may want to create a slave of a specific master without having a full replication system. One case where you would like this ability is when you want to try replicating between servers of different versions.

$ make_sandbox 5.1.57 -- --master
[…]
my_clause                      = server-id=5157 ; log-bin=mysql-bin ; log-error=msandbox.err
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_5_1_57

$ make_sandbox 5.5.10 -- --slaveof='master_port=5157' 
[…]
my_clause                      = server-id=5510 ; log-bin=mysql-bin ; log-error=msandbox.err
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_5_5_10


$ ~/sandboxes/msb_5_1_57/use -e 'show master status'
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+

$ ~/sandboxes/msb_5_5_10/use -e 'show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 5157
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysql_sandbox5510-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000001
             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: 106
              Relay_Log_Space: 420
              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: 0
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: 5157

You can download MySQL::Sandbox from either launchpad or CPAN.
PlanetMySQL Voting: Vote UP / Vote DOWN

I will be a speaker at Percona Live - London 2011, and I am looking forward to the event, which is packed with great content. A whopping 40 session of MySQL content, plus 3 keynotes and 14 tutorials. It's enough to keep every MySQL enthusiast busy.Continuent speakers will be particularly busy, as between me and Robert Hodges, we will be on stage four times on Tuesday, October 25th. Robert, at 10:15am: Teaching an Old Dog New Tricks: Tungsten Enterprise Clusters for MySQL, Bishopsgate Suite Robert, at 1:30pm: MySQL Parallel Replication in 5 Minutes or Less, Newgate Suite me, at 2:30pm: MySQL Replication outside the box : multiple masters, fan-in, parallel apply, Ludgate Suite me, at 4:30pm: MySQL Sandbox: a framework for productive laziness,Ludgate Suite This event feels good from the beginning. There are plenty of participants, many names from all over the MySQL community, covering large and small companies, experienced speakers, well known names in the MySQL engineering arena, and a wealth of topics that will make me feel sorry for not being able to attend them all. It's the usual dilemma that attendees have at this kind of conferences. Not so much at Oracle Open World 2011, where there weren't that many MySQL sessions to choose from, although it was great for networking.

Our talks

Robert will open the dances with Teaching an Old Dog New Tricks: Tungsten Enterprise Clusters for MySQL, a talk about Tungsten Enterprise, my company's commercial product, which is a professional managing tool for demanding companies.Robert, again in the afternoon, with one of the most amazing features of our open source product, Tungsten Replicator: MySQL Parallel Replication in 5 Minutes or Less. This is a feature for large replication systems where the slave can't cope with large data streams, due to the singled-thread MySQL slave. This talk will show how easy is it to plug Tungsten Replicator to a lagging slave, start parallel replication until the lag has been zeroed, and then hand over the control to the native replication again.Then it will be my turn, with a general presentation about Tungsten Replicator, the open source product. I like the idea of calling it MySQL Replication outside the box : multiple masters, fan-in, parallel apply. The reasoning is that MySQL replication, although wildly successful in the web economy of the last decade, it is also constrained by several limits, which Tungsten, acting outside the boundaries, sets free. This will be a quick intro to Tungsten and its new user-friendly installation, with a few demos.Finally, a classic presentation with some new content, on MySQL Sandbox: a framework for productive laziness. The news is that MySQL Sandbox now supports Percona and MariaDB builds. Again, some demos will be shown, with old and new features mixed together.
PlanetMySQL Voting: Vote UP / Vote DOWN

If you like fresh features, you should not miss this one. MySQL 5.6.2 includes, among other improvements, the implementation of Time delayed replication, a feature that lets you tell the slave not to apply changes from the master immediately, but to wait N seconds.

The feature is documented in WL#344. (There was a manual online as well together with the binaries for MySQL 5.6.0, but they were removed after a few days for a good reason. I am confident that both the manual and some binaries will eventually show up soon).
Since as of today there are no binaries for MySQL 5.6.x, you need to get the code and compile it yourself. Just get the code from https://code.launchpad.net/mysql-server and compile it using the instructions in building MySQL 5.5 with cmake.
To get a taste of this new feature, the quickest way is to set up replication using the binaries that you have built and MySQL Sandbox.

make_replication_sandbox  mysql-5.6.2-m5-osx10.6-.tar.gz 
# the file name may change, depending on the operating system you are using

Soon you will have one master and two slaves in $HOME/sandboxes/rsandbox_5_6_2.
What you have to do is connect to one of the slaves and enter these commands:

STOP SLAVE;
change master to master_delay=60;
START SLAVE;

Let's say that you did this to slave #2.
Now whatever you do in the master will be replicated immediately in slave #1, but it will executed with 60 seconds delay in slave #2.
To be clear, the IO_THREADs of both slaves keep getting data from the master as fast as they can, same as they did until version 5.5, but slave #2 will hold the SQL_THREAD for the defined amount of seconds.
This new state is visible in the output of the SHOW SLAVE STATUS command, which lists this information after you do something in the master like creating a table or inserting data:

               SQL_Delay: 60
     SQL_Remaining_Delay: 43
 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

The main purpose of delayed replication is to protect the server against human mistakes. If I accidentally drop a table, the statement is instantly replicated to all the slaves, but it is not executed to the delayed slaves.

$ ./m -e 'drop table test.t1 '
$ ./use_all 'show tables from test'
# master  
# server: 1: 
# server: 2: 
Tables_in_test
t1

The table is gone in the master, and it is gone in the regular slave, but it is still there in the delayed slave. And if I detect the problem before the delayed statement gets executed (a delay time longer than 60 seconds would be advisable in this case, 3600=1 hour, seems healthier), then I may be able to recover the data.

I notice en passant that there is much more than delayed replication going on in MySQL 5.6. For example, the information_schema tables related to InnoDB have increased from 7 to 18:

 show tables from information_schema like 'innodb%';
+----------------------------------------+
| Tables_in_information_schema (innodb%) |
+----------------------------------------+
| INNODB_CMPMEM                          |
| INNODB_TRX                             |
| INNODB_BUFFER_PAGE                     |
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  |
| INNODB_CMP                             |
| INNODB_SYS_COLUMNS                     |
| INNODB_CMPMEM_RESET                    |
| INNODB_SYS_FOREIGN_COLS                |
| INNODB_BUFFER_PAGE_LRU                 |
| INNODB_BUFFER_POOL_STATS               |
| INNODB_CMP_RESET                       |
| INNODB_SYS_FOREIGN                     |
| INNODB_METRICS                         |
| INNODB_SYS_INDEXES                     |
| INNODB_LOCKS                           |
| INNODB_SYS_FIELDS                      |
| INNODB_SYS_TABLES                      |
+----------------------------------------+
18 rows in set (0.00 sec)

What they do and how to play with them will be matter for some more investigation.
PlanetMySQL Voting: Vote UP / Vote DOWN

A few days ago, a friend of mine asked me if I knew of a way of filtering a bunch of binary logs, to extract only statements related to a single table. The task was about filtering a few hundred binary log files.

It's a tricky problem. Even with my experience with regular expressions, I knew that using a script to extract statements related to a single table was going to be a nasty business.
However, I know of an entity that can filter by table name efficiently, and that's the MySQL replication system. So I suggested using replication to a sandbox with a replicate-wild-do-table statement to get the job done.
My friend was skeptical and did not want to go that way. I was busy writing an article for an Italian magazine and did not follow up immediately. But today, with the article safely in the editor's hands, I did a quick test, and guess what? It works!

Here is a step-by-step procedure to do it. I started with a server built with MySQL Sandbox, using MySQL 5.5.7. I used the employees test database to create a large enough binlog, and soon I had a database containing 160 MB of data and a binary log of about the same size.
Then I decided that I wanted to filter the binlog, to get only statements about the employees table. Thus, I issued this command:

$ make_sandbox 5.5.7 --sandbox_port=6000 \
  --sandbox_directory=trans_repl \
  -c log-slave-update \
  -c replicate-wild-do-table=employees.employees \
  -c log-bin=mysql-bin \
  -c server-id=101

The "-c" option transfers its argument to the sandbox configuration file.
At the end of this operation, I had one server with the same version of the server that I had filled with the employee database. The server is ready to filter replicated streams, accepting only commands that affect the table 'employees' within the database 'employees'.

The second step was to create an empty database in the second server, with the Innodb tables converted to BlackHole (to avoid wasting unnecessary space).

Inside the first sandbox, I did this:

$ ./my sqldump -B --no-data employees \
  | perl -pe 's/=innodb/=blackhole/i' \
  | ~/sandboxes/trans_repl/use

Combining the flexibility of the sandbox with some command line skills, the operation requires just one command.
Before starting the replication, I needed to avoid re-creating the tables, or my blackhole trick would have been useless. So I looked at the binary log, and found where the CREATE TABLE statements ended:

$ ./my sqlbinlog ./data/mysql-bin.000001 |less
BEGIN
/*!*/;
# at 3057
#101112 9:48:45 server id 1 end_log_pos 3364 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1289551725/*!*/;
INSERT INTO `departments` VALUES ('d001','Marketing'),('d002','Finance'),('d003','Human Resources'),('d004','Production'),('d005','Development'),('d006','Quality Management'),('d007','Sales'),('d008','Research'),('d009','Customer Service')/*!*/;

Armed with this knowledge, I logged in the second server and did the following:

FLUSH BINARY LOGS; # to get a binlog with only the statements that I need

CHANGE MASTER TO
master_host='127.0.0.1',
master_port=5570,
master_user='msandbox',
master_password='msandbox',
master_log_file='mysql-bin.000001',
master_log_pos=3057;    # this is the position after all the CREATE TABLE
                        # statements in the master
START SLAVE;

After a few seconds, I issued a "SHOW SLAVE STATUS". All was OK.
I flushed the logs again and inspected the second binary log. As expected, it contained only the statements related to the employees table.
Total cost of the operation: 5 minutes. Way quicker than writing this report!
PlanetMySQL Voting: Vote UP / Vote DOWN

A few days ago I saw an article about Semi-Synchronous Replication in MySQL 5.5. It asks questions, and doesn't give answers beyond gut feeling. So I thought that I would do some practical testing of this new feature.
Before we go that way, though, let's revisit the theory.

How semi-synchronous replication works

Figure 1. A transaction with regular replication
With regular replication, you send a transaction to the master (1). When the COMMIT is received, the master executes it (2), and if successful it logs the transaction to the binary log (3). The the master answers the client request (4) with a successful result. In the meantime, the slaves replicate the record (5).
What happens if the master crashes after point #4 and before a slave has had a chance of getting the data in point #5?
The client will have a result for that transaction, but that data is lost, because it has never reached one slave.


Figure 2. A transaction with semi-synchronous replication

Let's see the same scenario with semi-synchronous replication. All is the same until point #3. Then, things change. The master does not return to the client. Instead, it alerts the slave that a transaction is available (4). The slave gets it and stores it to the relay log (5). Without further action, the slave tells the master that the transaction was received (6) and only then the master returns the result to the client (7).
What is the benefit? If the master crashes, the transaction is lost. but the client does not get the false information that the transaction was stored. Instead, if the master crashes before it returns the result to the client, the client gets an error, and knows that that transaction needs to be reworked when a new master is back.

Semi-synchronous replication in practice

Now, the practicalities.
How do you tell if semi-synchronous replication is working? If you leave the default timeout of 10 seconds, you have an immediate clue that something is wrong when a query takes too long to return. Investigation is possible by looking at the GLOBAL STATUS variables.
Rpl_semi_sync_master_status tells you if the master is ready for semi-synchronous replication.
Rpl_semi_sync_master_yes_tx is the number of positive transactions that were delivered using semi-synchronous replication.
Rpl_semi_sync_master_no_tx is the number of failed attempts at delivering a transaction via semi-synchronous replication. When that happens, Rpl_semi_sync_master_status becomes "OFF", and you need investigating.

The important thing to understand about this feature is that semi-synchronous replication does not guarantee that your transaction is executed in the slave. It will only tell you that the data has been transferred to the slave relay log. It can still happen that the transaction fails to execute on the slave (which could be either a bug in your application or a bug in MySQL replication). But this is not a cluster. Don't expect a two-phase commit.

Testing semi-synchronous replication

If you want to test this feature without suffering too much, you can use a tarball binary and MySQL Sandbox. Once you have installed MySQL Sandbox and have downloaded the server tarball, you can install a test replication system with

make_replication_sandbox --how_many_slaves=4 /path/to/mysql-5.5.6-yourOS.tar.gz

This will create a system with 1 master and 4 slaves.
The Sandbox has a shortcut to install the plugin quickly and painlessly:

sbtool -o plugin --plugin=semisynch -s $HOME/sandboxes/rsandbox_5_5_6

Now you will have the semi-synchronous plugin installed in the master and all the slaves. For our tests, we will make a shell script, an easy task thanks to the sandbox utilities.

#!/bin/sh

echo "disabling semi-synch replication in all slaves except 1"
./s1 -e 'set global rpl_semi_sync_slave_enabled=1'
./s1 -e 'slave stop io_thread; slave start'

for E in 2 3 4
do
    ./s$E -e 'set global rpl_semi_sync_slave_enabled=0'
    ./s$E -e 'slave stop io_thread; slave start'
done

#
# this query will show the main variables that tell
# if semi-synch replication is working
#
Q1='select variable_name, variable_value'
Q2='from information_schema.global_status'
Q3='where variable_name in'
Q4='("RPL_SEMI_SYNC_MASTER_YES_TX", "RPL_SEMI_SYNC_MASTER_NO_TX")'
I_S_Q="$Q1 $Q2 $Q3 $Q4"

echo ""
echo "creating a table. it should replicate through the semi-synch"
./m -vvv -e 'create table test.t1( i int)'
./m -e "$I_S_Q"

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (1)'
./m -e "$I_S_Q"

echo ""
echo "disabling semi-synch replication in slave 1"
./s1 -e 'set global rpl_semi_sync_slave_enabled=0'
./s1 -e 'slave stop io_thread; slave start'

echo ""
echo "enabling semi-synch replication in slave 3"
./s3 -e 'set global rpl_semi_sync_slave_enabled=1'
./s3 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (2)'
./m -e "$I_S_Q"

echo ""
echo "disabling semi-synch replication in slave 3"
./s3 -e 'set global rpl_semi_sync_slave_enabled=0'
./s3 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'NO' should increase"
./m -vvv -e 'insert into test.t1 values (3)'
./m -e "$I_S_Q"

echo ""
echo "enabling semi-synch replication in slave 2"
./s2 -e 'set global rpl_semi_sync_slave_enabled=1'
./s2 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (4)'
./m -e "$I_S_Q"

This script will first disable semi-synchronous replication in all the slaves except one. Then it will create a table, and check for the telling status variables.
This should work quickly and without problems. Then it will disable the plugin on the only slave that was active, and enable another slave instead.
Inserting a record on the master will work again quickly, as the newly enabled slave will get the record immediately.
Then the slave gets disabled, and we can witness what happens. The query takes a bit longer than 10 seconds, and the status variable tells us that semi-synchronous replication has failed.
We finally enable yet another slave, and when we try a further insertion, we can see that the semi-synchronous replication has resumed.

Very important:
To enable or disable semi-synchronous replication on a slave it is not enough to set the appropriate variable. You need also to restart the slave by issuing a STOP SLAVE IO_THREAD followed by a START SLAVE commands.

Here is a sample run:

disabling semi-synch replication in all slaves except 1

creating a table. it should replicate through the semi-synch
--------------
create table test.t1( i int)
--------------

Query OK, 0 rows affected (0.87 sec)

Bye
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 1              |
+-----------------------------+----------------+

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 2              |
+-----------------------------+----------------+

disabling semi-synch replication in slave 1

enabling semi-synch replication in slave 3

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 3              |
+-----------------------------+----------------+

disabling semi-synch replication in slave 3

inserting a record. The number of 'NO' should increase
--------------
insert into test.t1 values (3)
--------------

Query OK, 1 row affected (10.12 sec)

Bye
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 1              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 3              |
+-----------------------------+----------------+

enabling semi-synch replication in slave 2

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 1              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 4              |
+-----------------------------+----------------+

Using the above steps, you should be able to use semi-synchronous replication and do some basic monitoring to make sure that it works as expected.
PlanetMySQL Voting: Vote UP / Vote DOWN

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