Node.js and MySQL on the Oracle Cloud

Let's explore how to deploy a node.js app with MySQL backend on the Oracle Cloud. I want to cover several aspects:

• How to deploy and initialize a MySQL instance
• How to adapt the source code
• How to deploy the application
• How to scale the application

There are different ways to configure this. I tested the easiest deployment with MySQL Cloud Service and the Application Container Cloud for the node.js part. All configurations are done via the cloud web GUI. There is also a REST interface available. But let's keep that for later.
If you don't have access to the Oracle Cloud you can get a trial access here.

How to deploy a MySQL instance

Once you logged into the Oracle cloud you can create new instances from the dashboard. The following screenshots describe the process.

On the next screen we upload the public key for admin access to the instance. Either upload your own public key or generate a new key pair. (If you generate a new key pair you need to download the private key to your local machine.)

I skipped the backup and monitoring configurations for this demo. Let's focus on the application instead. After creating the instance (approx. 10 min) you can navigate via the dashboard to this instance and get the IP address. This is needed for the next step.
To initialize the database I ran this little script that runs ssh to the instance (using the private key), switch user to "oracle" and then call the MySQL CLI to run a few SQL statements.

How to adapt the source code

The Application Container Cloud sets a few environment variables that should be used inside the application to adapt to the environment. In my case this are the following variables:

• PORT is the port number that the application should listen on
• MYSQLCS_USER_NAME is the MySQL user name for the database backend
• MYSQLCS_USER_PASSWORD is the corresponding password
• MYSQLCS_CONNECT_STRING is the hostname and port of the database backend

I could have hardcoded the database connection parameters but that is inflexible if the architecture changes. So let's use these variables. The Node.js code looks like this:

How to deploy the application

There are two simple steps needed: Creating an application and defining service bindings. In my case the only service binding is the MySQL backend. But one step after the other. First let's create the application. First you need to create a manifest.json file to describe the application. Here is mine:

Ideally you create a zip archive with the source code, the manifest.json file and all other resources that your application needs. If you want to use my zip archive, feel free. You find it on GitHub.

From the Oracle Cloud Dashboard click on "create instance -> application container" and then select "Create Instance" and "Node.js". (Java SE, Java EE, Python, Ruby and PHP are available as well.)

On the pop-up you define the application artifacts, number of application instances and the memory per instance. After you click "create" the application is deployed automatically within a few minutes.
The last step is to connect the application service with the database backend. To achieve that, click on the application in the application overview page. Here you find the URL under which your application will be available. And on the left hand side you see three tabs:

Overview, Deployments and Administration. Click on "Deployments". Here you can add the service binding as described in the following screenshot:

After modifying the service bindings you have to click "Apply changes". This will restart the application instances. Obviously needed because now the environment variables for the database backend are set correctly.
That's it. We have an application. The URL to access the new app is listed in the application overview tab. Because this URL is not so nice for offering a short url service, I registered a new domain and forwarded that to the anota application. Maybe it is still running? Check here.

How to scale the application

This is really easy. On the application overview tab you can just increase the number of instances and the memory per instance. After applying the changes, the Application Container Cloud platform will deploy new instances, stop spare instances or reconfigure the existing instances. If you use my ANOTA application, go to the report page. The last line prints the hostname of the application server. Requests are automatically load balanced between the available application instances. 

Summary

There are some minor changes to the application to run on the Oracle Cloud Platform: Reading the port variable and database connection parameters from the provided environment variables and that's it. Deployment is really easy via the GUI. And scalability is so simple now that the full Oracle Cloud Plattform is available and can be provisioned within minutes. 

Query Rewrite Plugin and Binlog for Replication

Starting with MySQL 5.7 we introduced the Query Rewrite Plugin. That tool is really useful for changing queries. Of course the best location to modify the query is the source code of the application, but this is not always possible. Either the application is not under your control or queries are generated from a framework like Hibernate and sometimes it is hard to change the query generation.
If you are interested in details about the Query Rewrite Plugin, I recommend this blogpost from the MySQL Engineering: http://mysqlserverteam.com/the-query-rewrite-plugins/
Recently I was asked how this works in replication environments. Which query goes into the binlog?

If you are using the Rewriter plugin that comes with MySQL 5.7, the answer is easy: This plugin only supports rewriting SELECT queries. SELECT queries don't get into the binlog at all. Simple.

But you might write your own preparse or postparse plugin. In that case you can define the behavior with the server option --log-raw. See documentation here: https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-raw
You can either bring the original query to the binlog or the rewritten query. So all flexibility you need. However be aware that --log-raw also affects logging of passwords in the general log file. With --log-raw passwords are written in plain text to the log files. So consider this side effect when switching --log-raw on or off.

MySQL 5.7: Optimizer finds best index by expression

The optimizer in MySQL 5.7 leverages generated columns. Generated columns will physically store data in two cases: Either the column is defined as STORED or you create an index on a virtual column. The optimizer will leverage such an index automatically if it encounters the same expression in a statement. Let's see an example:

mysql> DESC squares;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| dx    | int(10) unsigned | YES  |     | NULL    |       |
| dy    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM squares;
+----------+
| COUNT(*) |
+----------+
|  2097152 |
+----------+
1 row in set (0.77 sec)

We have a large table with 2 million rows. Selecting rows by the surface area of squares can hardly leverage an index on dx or dy:

mysql> EXPLAIN SELECT * FROM squares WHERE dx*dy=221\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2092860
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
Now let's add an index over a generated, virtual column that defines the area:

mysql> ALTER TABLE squares ADD COLUMN (area INT AS (dx*dy));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE squares ADD INDEX (area);
Query OK, 0 rows affected (5.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now we can run query again:

mysql> EXPLAIN SELECT * FROM squares WHERE dx*dy=221\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
   partitions: NULL
         type: ref
possible_keys: area
          key: area
      key_len: 5
          ref: const
         rows: 18682
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
 I did not change the query! The WHERE condition is still dx*dy. Nevertheless the optimizer finds the generated column, sees the index and decides to leverage that.
So you can add complex indexes and without changing the application code you can benefit from these indexes. That makes life much easier.

One limitation though: It seems the optimizer recognizes expressions only in the WHERE clause. It will not use the generated column and index for the SELECT expression:

mysql> EXPLAIN SELECT SUM(dx*dy) FROM squares\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2092860
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT SUM(area) FROM squares\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
   partitions: NULL
         type: index
possible_keys: NULL
          key: area
      key_len: 5
          ref: NULL
         rows: 2092860
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Auto Generated Columns in MySQL 5.7: Two Indexes on one Column made easy

One of my customers wants to search for names in a table. But sometimes the search is case insensitive, next time search should be done case sensitive. The index on that column always is created with the collation of the column. And if you search with a different collation in mind, you end up with a full table scan. Here is an example:

The problem

 mysql> SHOW CREATE TABLE City\G  
 *************************** 1. row ***************************  
 Table: City  
 Create Table: CREATE TABLE `City` (  
 `ID` int(11) NOT NULL AUTO_INCREMENT,  
 `Name` char(35) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  
 `CountryCode` char(3) NOT NULL DEFAULT '',  
 `District` char(20) NOT NULL DEFAULT '',  
 `Population` int(11) NOT NULL DEFAULT '0',  
 PRIMARY KEY (`ID`),  
 KEY `CountryCode` (`CountryCode`),  
 KEY `Name` (`Name`),  
 ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1  
 1 row in set (0,00 sec)  

The collation of the column `Name` is utf8_bin, so case sensitive. Let's search for a City:

 mysql> SELECT Name,Population FROM City WHERE Name='berlin';  
 Empty set (0,00 sec)  
 mysql> EXPLAIN SELECT Name,Population FROM City WHERE Name='berlin';  
 +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+  
 | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |  
 +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+  
 | 1  | SIMPLE      | City  | NULL       | ref  | Name          | Name | 106     | const |  1   |  100.00  | NULL  |  
 +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+  
 1 row in set, 1 warning (0,00 sec)  

Very efficient statement, using the index. But unfortunately it did not find the row as the search is based on the case sensitive collation.
Now let's change the collation for the WHERE clause:

 mysql> SELECT Name,Population FROM City WHERE Name='berlin' COLLATE utf8_general_ci;  
 +--------+------------+  
 | Name   | Population |  
 +--------+------------+  
 | Berlin |    3386667 |  
 +--------+------------+  
 1 row in set (0,00 sec)  
 mysql> EXPLAIN SELECT Name,Population FROM City WHERE Name='berlin' COLLATE utf8_general_ci;  
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
 | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
 | 1  | SIMPLE      | City  | NULL       | ALL  | Name          | NULL | NULL    | NULL | 4108 |  10.00   | Using where |  
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
 1 row in set, 3 warnings (0,00 sec)  

The result is what we wanted but the query creates a full table scan. Not good. BTW: The warnings point you to the fact that the index could not be used.

The solution

Now let's see how auto generated columns in the new MySQL 5.7 Development Milestone Release can help us. First let's create a copy of the Name column but with a different collation:

 mysql> ALTER TABLE City ADD COLUMN Name_ci char(35) CHARACTER SET utf8 AS (Name) STORED;  
 Query OK, 4079 rows affected (0,50 sec)  
 Records: 4079 Duplicates: 0 Warnings: 0  

"AS (Name) STORED" is the new stuff: In the brackets is the expression to calculate the column value. Here it is a simple copy of the Name column. The keyword STORED means that the data is physically stored and not calculated on the fly. This is necessary to create the index now:

 mysql> ALTER TABLE City ADD INDEX (Name_ci);  
 Query OK, 0 rows affected (0,13 sec)  
 Records: 0 Duplicates: 0 Warnings: 0  

As utf8_general_ci is the default collation with utf8, there is no need to specify this with the new column. Now let's see how to search:

mysql> SELECT Name, Population FROM City WHERE Name_ci='berlin';
+--------+------------+
| Name   | Population |
+--------+------------+
| Berlin |    3386667 |
+--------+------------+
1 row in set (0,00 sec)
mysql> EXPLAIN SELECT Name, Population FROM City WHERE Name_ci='berlin';nbsp;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ 
|  1 | SIMPLE      | City  | NULL       | ref  | Name_ci       | Name_ci | 106     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+ 
1 row in set, 1 warning (0,00 sec)  

Now we can search case sensitive (...WHERE Name=...) and case insensitive (WHERE Name_ci=...) and leverage indexes in both cases.

tl;dr

Use auto generated columns in MySQL 5.7 to create an additional index with a different collation. Now you can search based on different indexes.

What is ndb doing?

In MySQL cluster each SQL statement is translated inside the NDB storage engine to NDB low level protocol that is sent to the data nodes. For the performance it is most interesting how many data is moved between data nodes and MySQL. To monitor this there are several NDB status variables that you can monitor. See this link for more documentation.

(There are also the NDBINFO tables that reflect cluster status. But these are only global values. The status variables also show session status. More about NDBINFO is here.)

To easily report the NDB status on an individual SQL statement, I wrote a little script that gives you the ndb status variables and automatically calculates the diffs before and after the statement in question:

#! /bin/bash

./mysql -t -h olga64 test <<EOF

CREATE TEMPORARY TABLE tmp_before LIKE INFORMATION_SCHEMA.SESSION_STATUS;

CREATE TEMPORARY TABLE tmp_after LIKE INFORMATION_SCHEMA.SESSION_STATUS;

INSERT INTO tmp_before SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'ndb_api%session%';

$1

INSERT INTO tmp_after SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'ndb_api%session%';

SELECT tmp_before.VARIABLE_NAME, tmp_after.VARIABLE_VALUE - tmp_before.VARIABLE_VALUE AS 'VALUE' FROM tmp_after INNER JOIN tmp_before USING (VARIABLE_NAME) WHERE tmp_after.VARIABLE_VALUE <> tmp_before.VARIABLE_VALUE;

EOF

And here are some small examples of understanding what NDB does:

[root@olga64 bin]# ./ndb_prof.sh "SELECT COUNT(*) FROM t;"

+----------+

| COUNT(*) |

+----------+

|    32771 |

+----------+

+--------------------------------------------+----------+

| VARIABLE_NAME                              | VALUE    |

+--------------------------------------------+----------+

| NDB_API_WAIT_SCAN_RESULT_COUNT_SESSION     |        3 |

| NDB_API_WAIT_META_REQUEST_COUNT_SESSION    |        2 |

| NDB_API_WAIT_NANOS_COUNT_SESSION           | 19495775 |

| NDB_API_BYTES_SENT_COUNT_SESSION           |      132 |

| NDB_API_BYTES_RECEIVED_COUNT_SESSION       |      280 |

| NDB_API_TRANS_START_COUNT_SESSION          |        1 |

| NDB_API_TRANS_CLOSE_COUNT_SESSION          |        1 |

| NDB_API_TABLE_SCAN_COUNT_SESSION           |        1 |

| NDB_API_SCAN_BATCH_COUNT_SESSION           |        2 |

| NDB_API_READ_ROW_COUNT_SESSION             |        2 |

| NDB_API_TRANS_LOCAL_READ_ROW_COUNT_SESSION |        2 |

| NDB_API_ADAPTIVE_SEND_FORCED_COUNT_SESSION |        1 |

+--------------------------------------------+----------+

 So SELECT COUNT(*) only returns two rows (NDB_API_READ_ROW_COUNT_SESSION). Probably one row per fragment. (I have a two node cluster). COUNT(*) is optimized! But if you add a WHERE condition:

[root@olga64 bin]# ./ndb_prof.sh "SELECT COUNT(*) FROM t WHERE a<100;"

+----------+

| COUNT(*) |

+----------+

|       99 |

+----------+

+--------------------------------------------+----------+

| VARIABLE_NAME                              | VALUE    |

+--------------------------------------------+----------+

| NDB_API_WAIT_SCAN_RESULT_COUNT_SESSION     |        3 |

| NDB_API_WAIT_META_REQUEST_COUNT_SESSION    |        2 |

| NDB_API_WAIT_NANOS_COUNT_SESSION           | 18267962 |

| NDB_API_BYTES_SENT_COUNT_SESSION           |      140 |

| NDB_API_BYTES_RECEIVED_COUNT_SESSION       |     3248 |

| NDB_API_TRANS_START_COUNT_SESSION          |        1 |

| NDB_API_TRANS_CLOSE_COUNT_SESSION          |        1 |

| NDB_API_RANGE_SCAN_COUNT_SESSION           |        1 |

| NDB_API_SCAN_BATCH_COUNT_SESSION           |        2 |

| NDB_API_READ_ROW_COUNT_SESSION             |       99 |

| NDB_API_TRANS_LOCAL_READ_ROW_COUNT_SESSION |       99 |

| NDB_API_ADAPTIVE_SEND_FORCED_COUNT_SESSION |        1 |

+--------------------------------------------+----------+

 'a' is the primary key. This statement sends all 99 rows, well only the primary keys and mysqld is counting. This one can get more expensive, depending on the row count and primary key size. Look at NDB_API_BYTES_RECEIVED_COUNT_SESSION: 3248 bytes sent. This is roughly 32 bytes per row that is sent.

MySQL is so slow on Windows... Really?

Last week a customer called me and reported that MySQL was 30 times slower than MS SQL server. Oooops. That's a lot. No way to argue or throw smoke bombs. 30 times slower!

It was a standard installation of MySQL (typical install option) on plain Windows 7 and the same for MS SQL Server 2008. The test run was a batch of 30.000 INSERT commands in an SQL script. Runtime was 1 minute on MSSQL and 30 minutes on MySQL.

Some tests later we found out that it was only bad on InnoDB. MyISAM was as fast as MSSQL. (I didn't care which one was a bit faster. I didn't care as long as InnoDB was 30 times slower) Finally we nailed the problem down to one parameter in MySQL:

innodb_flush_log_at_trx_commit

Each INSERT statement is a single transaction (autocommit mode). MySQL is configured very faithfully and ensures that each transaction is really stored on disk. This is necessary for ACID compliance. D in ACID stands for 'durability'. To store data durable, at least the log file has to be written physically. That's why MySQL when a transaction commits forces the operating system to flush its buffers and even forces the disk cache to flush its buffer. That's the meaning of flush_log_at_trx_commit = 1 in the my.ini or my.cnf file.

MSSQL is much more relaxed with your data. It writes the data to disk device. But it may stay in the disk cache, and MSSQL does not care. If you have a crash, your data is not up-to-date on the physical disk and you may lose data. This is definitely not ACID compliant. Microsoft documented this here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;234656

• By default, the disk cache is enabled. Use the 'Disk Properties', Hardware tab to access the 'Properties', 'Policy' tab to control the disk cache setting. (Note Some drives do not honor this setting. These drives require a specific manufacturer utility to disable cache.)
• ...
• Disk caching should be disabled in order to use the drive with SQL Server.

So to have a fair comparison beween MSSQL and MySQL either

• set innodb_flush_log_at_trx_commit = 0 (or 2)
  This forces the flush to disk only once per second and brings good performance but data is not 100% safe on disk (unless you have a battery backed write cache)
• disable the disk cache in Windows 7
  This will force MSSQL to write physically to disk. And then MSSQL is 30 times slower than before. ;-)

Lessons learned:

• Think a lot about how to do a fair comparison.
• Either run (unsafe and fast), or (safe and slow) or (safe and fast and expensive) with a battery backed write cache controller
• Read the manual for MSSQL. There may be important news on page 3647+x.

The funny thing, that confused me a lot: On my Mac InnoDB was at around 1 minute. And even MySQL on Windows 7 in Virtual Box on my Mac was about 1 minute. The reason is: MySQL on Mac makes a fsync() that does not flush the disk cache. It flushes only the buffer cache of the operating system. And Windows 7 on Virtual Box: The disk is a plain file on the host OS. And this file is of course buffered in the host OS. So Virtualization may destroy your ACID compliance...

If you need more info on the different cache levels for file IO here is a very good link:

http://smallvoid.com/article/hard-disk-cache.html

"How to find the source of queries in MySQL Query Analyzer" or "SQL comments in Query Analyzer"

MySQL Enterprise Monitor offers a tool called "Query Analyzer" (QuAn). QuAn sits between any client app and the MySQL server and logs every query and its runtime statistics. A very cool tool for analyzing your SQL. More information is available here.

If you identify a query, that needs some improvement, sometimes it is hard to identify the source of that query as well. With hundreds of different PHP scripts for example it is not easy to know, which one issued the query, that you want to modify.

A good way to achieve this is adding C-style SQL comments. Let's look at an example:

SELECT * FROM mytable /*main.php*/;

Query Analyzer will strip that comment off before archiving the query. This is ok, because QuAn wants to consolidate all similar queries and this comment is irrelevant for the query.

But you can use version specific comments in MySQL. QuAn cannot ignore these comments,

because they may be relevant for query execution. And this is the solution for our problem:

SELECT * FROM mytable /*!99999 main.php*/;

This SQL comment will be executed on MySQL version 9.99.99 and later. But this version does not exist. So in reality the comment gets not executed at all. Currently we are at MySQL 5.1.42 so it will take some time before we reach MySQL 9.99.99 ;-)

The comment is in QuAn and will be logged. So you see the comment when monitoring the query and you know immediately, where that query came from.

How to install MySQL Enterprise Monitor agents in a failover environment

MySQL Enterprise Monitor is a tool to watch and analyze multiple MySQL environments from a single web based dashboard. More information is available on the MySQL homepage. Each MySQL instance is monitored by a small agent that connects to the MySQL instance and reads statistics that is sent to the MySQL Enterprise Monitor (MEM) Server.

That setup is very easy. But if the MySQL server is in a cluster failover configuration, there are some things to consider when installing the MEM agent:

What do you want?

Do you want to have two entries in the MEM dashboard for both physical servers?

This is good because:

• You can monitor them separately, you can define different rules for both servers in case they offer different capabilities.
• You can immediately see, which physical server runs the MySQL instance. The other entry will always report either "MySQL server down" or "MEM agent not reachable".

This is not so good because:

• You cannot watch the data if a failover occurred. E.g. you can only see graphs for a specific physical host.
• You get red alarms because the agent on the passive node cannot reach the MySQL instance. This alarm is harmless. But it will train you to ignore red alarms. Not good.

If you like this approach here is the description how to install.

Do you want to see only one entry in the MEM dashboard that displays the data no matter, which physical server is running the instance at the moment?
This is good because:

• You have continuous data even if a failover occurred during the period. E.g. you can watch the cache hit rate graph of your MySQL server and you will see only a dip where the failover took place.
• You will not see false events like "MySQL server is down on the passive node". Of course it is down. That's why it is called the passive node ;-)

This is not so good because:

• It's not so easy to see which physical server currently runs the MySQL instance: In the meta info on the dashboard screen you see the physical hostname that runs the MySQL instance.
• You need to apply the same rules to both physical servers. This may be a problem if they have different capabilities, e.g. if the backup node is smaller.

If you prefer this approach here is the description how to install.
By the way: I recommend this way, as the disadvantages are really small and the installation is even a little easier. Both procedures work for all common HA frameworks like SunCluster, OpenHA Cluster, Linux Heartbeat, Microsoft Windows Cluster.

Installing MEM agent on a cluster on the logical host

The goal is to have only one entry in the Enterprise Monitor Dashboard that shows the status of the MySQL instance, no matter on which physical server in runs. There are two ways to achieve this:

• You can install the agent on both physical nodes
• You can install the agent on a shared storage.

In either case you have to make sure, that only one agent runs at a time. You have to integrate the agent into your cluster framework. I will not describe how this works, as it is highly dependant on your cluster framework.

The following description assumes, that you will install the agent on both physical nodes.

1. Install the agent but DO NOT START the agent yet.
   
   
2. Edit the [agent-installdir]/etc/mysql-monitor-agent.ini
   In the [mysql-proxy] section add the following line:
   agent-host-id=[logical hostname]
3. Do steps 1. and 2. for the other cluster node as well.
   
   
4. Include the agent in the cluster's failover group so that it will start automatically on that node, where the MySQL instance is running.
   
   
5. Start the agent via the cluster framework.
   
   
6. The entry in MEM will get the hostname of the first node that runs MySQL. Even after a failover this name will stay. You should change that name to the virtual hostname of the virtual ip adress of your MySQL service. Use the MEM BUI, choose the tab "Settings". From the secondary menu right below the tabs choose "Manage Servers". If you move the mouse over the server name a submenu will appear. Choose "Rename" and enter the name of the virtual IP.

Installing MEM agent in a cluster on the physical hosts

To install the MEM agent in a way that both physical servers are listed in the MEM dashboard, you have to install the agent on both physical nodes. But: Do not start the agent after the installation!

There are three different IDs in MEM: agent-uuid, mysql-uuid and host-id. Usually they are generated automatically and you will never notice these IDs. For more information about the meaning of the different IDs look at this very good explanation from Jonathon Coombes.

The agent stores the uuid and the hostid in a MySQL table called mysql.inventory. After a failover the other agent on the new node will notice "wrong" hostid and uuid entries in the inventory table. The agent will stop and ask you to TRUNCATE mysql.inventory. But with this procedure MEM creates a new instance, so all old data is lost. Not good for a failover environment.

So in case of a failover you have to provide the mysql.inventory table, that the agent expects.

And here is how you can achieve this:

1. Install the MEM agent on the node that currently runs MySQL. Start the agent.
   
   
2. Make a table that stores the hostid and uuid for every physical host:
   USE mysql;
   CREATE TABLE inventory_hostname LIKE inventory;
   DROP INDEX `PRIMARY` ON inventory_hostname;
   ALTER TABLE inventory_hostname ADD COLUMN (hostname VARCHAR(64));
   ALTER TABLE inventory_hostname ADD PRIMARY KEY (hostname,name);
   INSERT INTO inventory_hostname SELECT *,@@hostname FROM inventory;
   SELECT * FROM inventory_hostname;
   
   The newly created table should look like this:
   

   +--------+--------------------------------------+----------+
   | name   | value                                | hostname |
   +--------+--------------------------------------+----------+
   | uuid   | 96936e90-56bd-4eb1-aef3-e708d149a4cb | wclus-1  |
   | hostid | mac:{005056a138c10000}               | wclus-1  |
   +--------+--------------------------------------+----------+

   
   (Notice that the hostid is based on the mac address in my case. Usually this is the public ssh host key.)
   
   
3. Stop the agent
   
   
4. Empty the inventory_table:
   TRUNCATE mysql.inventory;
   
   
5. Failover the MySQL instance to the other node.
   
   
6. Install and start the agent on the other node. It will save new values in inventory.
   
   
7. Copy these new values to the inventory_hostname table:
   USE mysql;
   INSERT INTO inventory_hostname SELECT *,@@hostname FROM inventory;
   Both nodes should be visible in MEM dashboard right now.
   
   With every failover we need to populate the inventory table with the host-specific rows. Easiest way (and independent on the operating system and cluster framework) is to define an init-file:
   
   
8. On both nodes create a file named [MySQL Basedir]/mysql_init_HA_MEM.sql with the following statements:
   USE mysql;
   REPLACE INTO inventory SELECT name,value FROM inventory_hostname WHERE hostname=@@hostname;
   
   
9. On both nodes edit you my.cnf or my.ini file. In the section [mysqld] add the following line:
   init-file=[MySQL Basedir]/mysql_init_HA_MEM.sql
   (If you already have an init-file defined you can add the commands "USE mysql; REPLACE..." to you init-file.)
   
   
10. Start all agents and try to failover the MySQL instance. Check that you init-file really modifies the inventory table.

Installing MySQL in Solaris 10 zones / containers

Now that installing MySQL in Solaris zones is even officially supported by the MySQL support group (see http://www.mysql.com/about/legal/supportpolicies/policies-06.html#q03), the question is: What is the right way of installing MySQL in a zone. Of course this depends on what you want to achieve. The following description is based on Solaris 10. On Opensolaris this is different (somewhat easier, as there are no more sparse root zones.)

If you run a local zone as a whole root zone, you can easily install MySQL from tarball or the package installer.

If you run a local zone as a sparse root zone, there are different options:

First you cannot use the package installer, as this procedure will copy binaries to /usr/bin. But /usr/bin is inherited from the global zone and write protected. You have to use the tarball installation.

1. Make /usr/local/mysql writable
The tar ball will install in /usr/local/mysql. You can create a symbolic link in your GLOBAL ZONE:
> ln -s /localsoftware/mysql  /usr/local/mysql
This link points to a directory, that is not inherited. So in every zone /usr/local/mysql will point to a dedicated directory with write permission. You untar the software in the zone in /localsoftware

2. Make /usr/local writable
If you want no software from /usr/local in the global zone to be available in the local zones as well, you can use solution#1 even with /usr/local. So in every zone, /usr is inherited and write protected. But /usr/local points to a directory, that is writable. This is my personal favorit:

In the global zone:

> mv /usr/local /LOCAL
> ln -s /LOCAL /usr/local
and in the local zone
> mkdir /LOCAL
> cd /usr/local
> gtar -xzf /anywhere/mysql.tgz

3. Install in different location
You can untar the MySQL tarball in any other location, that is not inherited from the global zone. Maybe /opt/ ? Check with mount, what directories are loopback mounted from the global zone with read-only flag.

4. Install MySQL globally for all zones
You can untar MySQL in the global zone's /usr/local. Then every local zone has a MySQL installation as well. But then it is important, that all write access in MySQL is done to a writable directory, like /var/lib/mysql. Otherwise MySQL in the local zone will stop, because it cannot write it's logfile/errorfile/datafiles in /usr/local/mysql/data

Creating a datacenter in one Solaris machine

There is a big event coming: Cebit!

And with that I took the task to implement a demo for MySQL Enterprise Monitor to show at the Sun booth. So I need a machine for the enterprise monitor and some machines to run MySQL databases. After all I need something to monitor.

So the setup will be an Ultra 24 desktop machine installed with OpenSolaris 2008.11 . For every machine I will install a zone to run a separate database and one zone to run the enterprise monitor.

So here is my first round of experiences: Installing a template container on OpenSolaris and cloning it to build four or five separate database machines. Let's go:

####################################################################################
# Creating ZFS filesystems for all zone root-fs and a zfs that's shared between global and all local zones for easy data exchange
####################################################################################

root@ap-mysql:/# zfs create -o mountpoint=/zones rpool/zones
root@ap-mysql:/# zfs create -o mountpoint=/zones/share rpool/zones/share

####################################################################################
# Create a template zone, that will be cloned for all demo zones
####################################################################################

root@ap-mysql:/# zonecfg -z TEMPLATE
TEMPLATE: No such zone configured
Use 'create' to begin configuring a new zone.
zonecfg:TEMPLATE> create
zonecfg:TEMPLATE> set zonepath=/zones/TEMPLATE
zonecfg:TEMPLATE> add inherit-pkg-dir
zonecfg:TEMPLATE:inherit-pkg-dir> set dir=/zones/share
zonecfg:TEMPLATE:inherit-pkg-dir> end
zonecfg:TEMPLATE> add net
zonecfg:TEMPLATE:net> set physical=e1000g0
zonecfg:TEMPLATE:net> set address=192.168.14.225/24
zonecfg:TEMPLATE:net> end
zonecfg:TEMPLATE> verify
zonecfg:TEMPLATE> commit
zonecfg:TEMPLATE> exit
root@ap-mysql:/# zoneadm list -cv
ID NAME STATUS PATH BRAND IP
0 global running / native shared
- TEMPLATE configured /zones/TEMPLATE ipkg shared

####################################################################################
# Now we have the TEMPLATE zone. Next step is to install the zone and boot it.
# CAVEAT: The machine needs internet access to install a zone. The software respository must be www.opensolaris.org in the moment... :-(
####################################################################################

root@ap-mysql:/# zoneadm -z TEMPLATE install
A ZFS file system has been created for this zone.
Authority: Using http://pkg.opensolaris.org/release/.
Image: Preparing at /zones/TEMPLATE/root ... done.
Installing: (output follows)
DOWNLOAD PKGS FILES XFER (MB)
Completed 52/52 7862/7862 72.41/72.41

PHASE ACTIONS
Install Phase 12939/12939
PHASE ITEMS
Reading Existing Index 9/9
Indexing Packages 52/52

Note: Man pages can be obtained by installing SUNWman
Postinstall: Copying SMF seed repository ... done.
Postinstall: Working around http://defect.opensolaris.org/bz/show_bug.cgi?id=741
Done: Installation completed in 518.993 seconds.

Next Steps: Boot the zone, then log into the zone console
(zlogin -C) to complete the configuration process


####################################################################################
# time to boot the zone
# prepare a second terminal window and start the zone's console with
# zlogin -C TEMPLATE
####################################################################################

root@ap-mysql:~# zoneadm -z TEMPLATE boot

####################################################################################
# In TEMPLATE's console, do the interactive sysidcfg procedure
# I like to install some other packages. The default set is too small for me
# Adding packages is easy: 'pkg install SUNWxyz'. Unfortunately you have to
# be in the internet to have access to www.opensolaris.org
# hopefully this will change in the future.

# Here is the list of packages I installed for the showcase:

# SUNWgnu-coreutils

# SUNWgcmn

# SUNWgtar    needed for installing MySQL tarball

# SUNWuiu8

# SUNWman    can't live without it

# SUNWless

# SUNWpkgcmds   to use pkgadd later with MySQL
####################################################################################

That's it. We have a zone called TEMPLATE. Next blog will be cloning to define 4 identical machines.