Johan Andersson's Cluster and HA Blog

Galera Recovery - rsync or xtrabackup

noreply@blogger.com (Johan Andersson) — Mon, 21 Jan 2013 08:49:00 +0000

Is there any difference using rsync or xtrabackup when performing SSTs?

Yes, the main difference between the two methods is that the rsync method will block the Donor, hence it will not be possible to write to the Donor during the SST (because it is read-only).
Thus, if you have a three node cluster, and one node fails, effectively two are out of service.
When using xtrabackup this is not a problem, as reads and writes can happen on the Donor node during the SST.

Is there any difference in recovery times comparing the two methods?

To test this we set up a three node Galera cluster (based on the Codership build), populating the cluster, using sysbench, with 22GB of data. We are using standard Galera wsrep options.

The servers, created on Rackspace UK (all servers are in the same availability zone) using Ubuntu 12.04, has 2GB of RAM each. No changes were made on the data during the recovery, or between the runs.

Rsync:
17:24:49 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 12586)
17:55:26 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 12586)

Recovery Time: ~31 minutes

To test the xtrabackup we installed the necessary tools and changed the my.cnf files following the procedure outlined here.

xtrabackup:
8:35:45 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 12586)
9:06:04 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 12586)

Recovery Time: ~31 minutes

So for this data set and setup, using rsync or xtrabackup for optimizing recovery times does not matter, but for sure the benefit of having a read/write donor is an appealing case for using xtrabackup as the SST method. Over WAN it may be different, we will look into this in a later post.

On a side note, I actually expected rsync to be faster that xtrabackup (on paper/theory it should be..), but it may be due to the limited bandwidth between the Rackspace servers (it peaks at about 15MB/s on the instances used here), so in a private data center it may be different, However, having a read/write Donor is something I appreciate a lot. Thanks Massimo for bringing it up.

Recommended MySQL Cluster setup

noreply@blogger.com (Johan Andersson) — Fri, 14 Dec 2012 09:25:00 +0000

MySQL Cluster was first released in November 2004, in MySQL 4.1 - that’s a pretty mature nine-year old! There is a ton of information about it on the Internet, on topics like how to setup a good minimal cluster, how to distribute the nodes, the kind of hardware that is appropriate for each node type, etc. We published our MySQL Cluster training slides sometime back. However, despite all that information, we still see many testers who do not get the basics right.
In this blog, we will see what a good MySQL Cluster setup looks like.

MGM Node - holds the configuration for MySQL Cluster in a file called config.ini. It also writes a cluster log, and takes part in arbitration to prevent split-brain or network partitioning.
You are recommended to have two MGM Nodes for redundancy. Since MGM hardly use any resources, and is not involved in query processing or routing of requests, it can be colocated with the SQL Nodes/Access Nodes.

Data Node - stores the data and the tables. Tables are divided up into partittions, and the number of partitions depends on the number of data nodes (and the number of Local Query Handlers). Data distribution is automatic and by default is based on the hash of the primary key to conclude which partition a particular data item should be written. You can also partition the data on part of the Primary Key (using PARTITION BY KEY(part_of_pk) if you want to group e.g users together with its associated data.

The data nodes takes cares of managing the transactions, recovery, etc etc. Failover is automatic, but the application is responsible for retrying failed transactions. Data can either be written in in-memory tables (fastest) or on disk tables (slower).

For redundancy you need at least two data nodes. Data nodes usually operate in pairs, so the number of data nodes would be two, four, six, etc. The data nodes should have many cores, fast cores, enough RAM to store your data, and fast disks (SAS 10-15KRPM and/or SSD for disk data tables). On EC2, IOPS instances are prefered.

Since the Linear two-phase commit protocol is used to write data onto two partitions (synchronous replication between data nodes) it is recommended to have as fast network as possible, 1 Gig-E, 10Gig-E, Infiniband or even Dolphin ICS IX PCI Express adapters.

Distributing Data Nodes that belong to one cluster over WAN is generally not recommended, unless the WAN connection is very stable and the latency is very low (at most a few milliseconds).

SQL Node / Access Node - the most common way to read/write data to the Data Nodes is by using the MySQL Server (aka SQL Node). A query comes into the SQL Node. The SQL Node parses, optimizes and executes the query on the Data Nodes. Other ways of connecting to the Data Nodes includes Cluster/J (Java driver), Memcached, or directly from the NDBAPI (C++) api. All methods mentioned above accessing the Data Nodes boil down to the NDBAPI requests being sent across the wire.

You need at least two SQL Nodes for redundancy. Nodes in the access layer should have fast CPUs, disks are not important, fast network to the data nodes (same specs as for the data nodes), and about 2+ GB of RAM (depending on the number of connections, per thread buffers etc). Do not co-locate the SQL Nodes/Access Nodes with the Data Nodes.

SQL Load Balancing with HAProxy - HAProxy is a load balancer, and load balances application requests to multiple SQL Nodes. We recommend 2 instances of HAProxy for redundancy. HAProxy has neat features like queuing and throttling of connections towards one or more SQL Nodes, which is great for overload control. Unless you have 10 or more app servers, we recommend to deploy the HAProxy servers directly on the machine running the app servers.

ClusterControl - This is a management server that gives a single, consolidated view of all the nodes in the cluster. It collects status information from all the nodes, and reacts on this information to provide automatic recovery, scaling/add nodes, health reports, query monitoring, notifications, and backup. Requires approx 2 CPU cores, 16Gb of disk space, and at least 1GB of RAM.
We recommend that you do not co-locate the ClusterControl server with any node that is part of the MySQL Cluster setup.

Putting it all together:

Bare metal or Virtualized platform?

Generally, you can run MySQL Cluster on virtualized hardware if you keep attention to:

Latency
Disk speed

The Data Nodes are the ones that are most sensitive. Cloud servers work fine as well. As an example, Paypal deploys a number of MySQL Clusters on Amazon. They use small AWS instances for their MGM and SQL nodes, but XL instances for Data Nodes.

If you e.g use VMWare, then you want to have:

nodes in one node group running on separate instances, and those instances should be on different physical hosts. Else if the host goes down, the entire node group will go down, and then the entire cluster
the physical host running as few instances as possible
as less sharing of possible of the physical hosts resources (like CPU and disk) - you don't want two data nodes share the same physical core e.g.

Hotbackup of Galera Cluster for MySQL using Xtrabackup

noreply@blogger.com (Johan Andersson) — Tue, 23 Oct 2012 10:31:00 +0000

Hot backups are important for high availability, they can run without blocking the application. Percona Xtrabackup is a great tool for backing up InnoDB data.
We have created a backup tool called s9s_backup that makes Xtrabackup really easy to use, and is fully integrated with ClusterControl, which means that you can schedule backups with ease and view the backups that you have taken, and also restore the backups with no pain.
s9s_backup is available in the lastest version of ClusterControl or you can download it here.

Is mysqldump totally useless then?

No. If you would like to isolate and load only one table, mysqldump is great, or if you want to load your data into another storage engine or database (e.g. NDB). Then mysqldump comes in very hand. Within ClusterControl you can create schedules to make a mysqldumps, full and incremental backups using Xtrabackup.

How to use s9s_backup

s9s_backup is included in ClusterControl and automatically installed in either /usr/bin or /usr/local/cmon/bin.
Running/usr/bin/s9s_backupprints out the set of options that s9s_backup supports:

/usr/bin/s9s_backup <--backup|--list|--install|--purge|--restore>

Full Backup

/usr/bin/s9s_backup --backup full  /path/to/my.cnf     <backupdir>

This will take a full backup of the host where you run the tool and store the backup in <backupdir>/full/backupdir.
s9s_backup writes a log (you can change it easily in the s9s_backup script if you want it on screen instead) to /tmp/s9s_backup.log.
Status information is written into the CMON Database, so you can see if a backup has failed or succeeded, size information, md5 etc.

Incremental Backup

/usr/bin/s9s_backup --backup incr  /path/to/my.cnf    <backupdir>

This will take an incremental backup of the host where you run the tool and store the backup in <backupdir>/incremental.

In order to take an incremental backup you must have already made a full backup on the host. So when creating the backup schedule (in either cron or ClusterControl), make user the first backup that will run is a full backup, else the incremental backups will fail.
s9s_backup writes a log to /tmp/s9s_backup.log.

List backups

/usr/bin/s9s_backup --list

This prints out a list of backup sets (a full backup plus the subsequent incremental backups) together with a backup id. This is called the backup set:

root@server01:~# ./s9s_backup --list
===========================================================================
Full backup taken '2012-October-23_07:40:37':
   server:     server01
   backup set: 2
   status:     completed
   location:   /backups//full/backup-full-...tar.gz (796873 bytes)
   lsn:        8791838
   md5:        31f84543c997a28a6bca7917776a5fac
   >> incremental backup taken '2012-October-23_07:58:48':
      server:   server01
      parent:   2
      status:   completed
      location: /backups//incremental/backup-incr-...tar.gz (2037959 bytes)
      lsn:      35177200
      md5:      5f845dd7a478fa0a99b4d97f6868c747

The list of a backup set is ordered by the LSN (Logical Sequence Number) and the backup, once restored, will be restored in LSN order.
The incremental backups have as parent the full backup, so you can verify that the incremental backup indeed belongs to the correct backup set (parent). Orphaned backups cannot be restored.&nbsp

Restore backups

Do not try to unpack the files in the backup set files manually. Instead you should use s9s_backup’s restore option to do it in the correct way and in the correct order:

/usr/bin/s9s_backup --restore <backup id>  <my.cnf>  <restoredir>

To restore a backup, you must specify a backup set id. From the ClusterControl Web interface you can easily see which backup files are part of a backup set, or you can run the --list command.

The restoredir specifies where the restored backup files in the backup set will be applied and combined by xtrabackup. Please note that the restoredir is not, and must not be the same as the mysql datadir. The increments are restored in LSN order to ensure you have a consistent restore point. This means that you as a user don’t have to worry about what order to apply the increments in. s9s_backup takes care of that, which is great when you are under pressure.

When restoring, s9s_backup compares the md5sum of the backup files when the backup was taken with the backup files that are currently restored. If there is a mismatch, s9s_backup will abort the restoration.

Once the restore is completed, the restoredir contains a complete restored backup that can be copied back to the mysql datadir.

Assuming you have a total cluster crash, and the cluster is unable to repair itself, then do on one node only:

Make sure that the ClusterControl controller is stopped to prevent auto-recovery (you are now in charge of the recovery). On the controller do: service cmon stop
Verify the other mysql servers are stopped.
cd <restoredir>/<backup set id>
make sure the is empty (use e.g rm -rf /var/lib/mysql) before running the innobackupex –copy-back command in the next step
innobackupex --copy-back <restoredir>/<backup set id>
chown mysql:mysql –R <mysql datadir>
mysqld_safe --defaults-file=/path/to/my.cnf --wsrep-cluster-address='gcomm://' &
Start the ClusterControl controller (on the controller do: service cmon start),and it will orchestrate the synchronization of the other nodes.

Purge backups

If you want to purge old backups, or failed backups, you can do:

/usr/bin/s9s_backup --purge <retention_period>

Backups older than retention_period will be removed from the file system.
The retention, if not specified on the command line, will be fetched from the cmon database (in the cmon_configuration table), which you can set from the Web interface under Cluster Settings.

Installation of Xtrabackup and Cron Job

If Percona Xtrabackup is not already installed, then s9s_backup will automatically download and install Xtrabackup.
To install Xtrabackup and to also install a cron job do:

/usr/bin/s9s_backup --install cron

The 'cron' at the end means Install Cron job.
This will install Percona Xtrabackup on this server, but no cron job:

/usr/bin/s9s_backup --install

In both cases above, the Percona Xtrabackup will be downloaded and installed.
The cron job is defined in the s9s_backup script, and you can easily modify it to suit your needs. The cron job is installed in /etc/cron.d/s9s_backup
The cron job will take a full backup every Sunday at 0300 and then incremental backups Mon-Sat at 0300.

If you want to modify the cron job table you can do it directly in the s9s_backup script or post installation by changing /etc/cron.d/s9s_backup

Finally, you can of course edit, change and make whatever modifications you like to the s9s_backup script if you want to customize it further.

garbd - How to avoid network partitioning or split brain in a Galera Cluster

noreply@blogger.com (Johan Andersson) — Mon, 24 Sep 2012 08:50:00 +0000

Network partitioning (aka split brain) is something you don’t want to happen, but what is the problem really?

Sometimes, users set up a Galera Cluster (or Percona XtraDB Cluster) containing an even number
of nodes, e.g, four or six nodes, and then place half of the nodes in one data center and the other half in another data center. If the network link goes down between the two data centers, then the Galera cluster is partitioned and half of the nodes cannot communicate with the other half. Galera’s quorum algorithm
will not be able to select the ‘primary component’, since both sets have the same number of nodes. You then end up with 2 sets of nodes that are ‘non-primary’, and effectively, none of the nodes would be available for database transactions.

Below is a screenshot of ClusterControl for Galera. I have simulated a network partition by simply killing two of the nodes at the same time.

Network partitioning / split brain

Half of the nodes got disconnected from the other nodes. The nodes staying up cannot determine the state of the other nodes, e.g, if they are really down (dead process), or if it was just the network link that went down and the processes are still up. Continuing then would be unsafe, and the two separated halves could potentially drift away, and you would have an inconsistent database.

At this point, our 2 nodes are pretty useless; at least one node should be in state ‘synced’ before it can be accessed. ClusterControl will address this situation by first recovering the first two nodes in ‘non-Primary’ state and setting them to Primary. It will then resync the two crashed nodes when they are back.

In order to avoid this, you can install an arbitrator (garbd). garbd is a stateless daemon which acts as a lightweight group member. In a quorum situation where half of the nodes are not available, garbd will help avoid a split-brain.

In ClusterControl, garbd is a managed process (will be automatically restarted in case of failure) and can easily be installed from the deployment package:

./install-garbd.sh

Now that we have installed garbd in our 4-node test cluster, it will now consist of 1+4 nodes. Killing 2 nodes will not affect the whole cluster, as the remaining nodes will have the majority, see the picture below The minority will be excluded from the cluster, and when they are up and ready to rejoin the cluster, they will go through a recovery protocol before being accepted into the cluster.

Majority win - garbd + two survivors > two failed nodes

Hopefully this gives an idea how and why garbd comes in handy.

MySQL Cluster to InnoDB Replication Howto

noreply@blogger.com (Johan Andersson) — Tue, 11 Sep 2012 09:11:00 +0000

In this blog post I will show you how to setup a replication from MySQL Cluster (ndbcluster) to a regular MySQL Server (InnoDB). If you want to understand the concepts, check out part 7 of our free MySQL Cluster training.

First of all we start with a MySQL Cluster looking like this, and what we want to do is to setup replication server to the Reporting Server (InnoDB slave).

MySQL Cluster is great at scaling large numbers of write transactions or shorter key-based read querise, but not so good at longer reporting or analytical queries. I generally recommend people to limit analytical/reporting queries on the MySQL Cluster, in order to avoid slowing down the realtime access to the cluster. A great way of doing that is to replicate the MySQL Cluster data to a standalone MySQL Server.

To achieve that, we will need a replication server. All data written into NDBCLUSTER is sent as events to the replication server. A MySQL Server can be turned into a replication server by specifying --log-bin. The replication server then produces a binary log, which can be replicated to a standalone InnoDB.

(NOTE: For redundancy, it is possible to have 2 replication servers. We will cover that in a separate blog.)

Replication Layer Configuration
In the my.cnf of the replication server you should have the following:

[mysqld]
...
#REPLICATION SPECIFIC - GENERAL
#server-id must be unique across all mysql servers participating in replication.
server-id=101
#REPLICATION SPECIFIC - MASTER
log-bin=binlog
binlog-do-db=user_db_1
binlog-do-db=user_db_2
binlog-do-db=mysql
expire-logs-days=5
...

You may want to skip the binlog-do-db=.., if you want to replicate all databases, but, if you want to replicate a particular database, make sure you also replicate the mysql database in order to get some very important data on the slave.

Restart the replication server for the settings to have effect.
Grant access to the Slave:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ip/hostname of mysqld m' 
  IDENTIFIED BY 'repl';

InnoDB Slave Configuration
The first requirement on the InnoDb slave is that it must use the mysqld binary that comes from the MySQL Cluster package. If you already have a MySQL 5.5 installed that is not clustered, you need to upgrade it to the Cluster version of it. E.g, by doing:

sudo rpm -Uvh MySQL-Cluster-server-gpl-7.2.7-1.el6.x86_64.rpm
sudo rpm -Uvh MySQL-Cluster-client-gpl-7.2.7-1.el6.x86_64.rpm

The InnoDB slave should have the following:

[mysqld]
...
binlog-format=mixed
log-bin=binlog
log-slave-updates=1
slave-exec-mode=IDEMPOTENT
expire-logs-days=5
...

If you want the InnoDb to further replicate to a set of slaves, then you should set log-slave-updates=1 otherwise you can set it to 0 (log-slave-updates=0). Thatt is all, restart the slave.

You must also create the following table on the Innodb Slave:

use mysql;

CREATE TABLE `ndb_apply_status` (
`server_id` int(10) unsigned NOT NULL,
`epoch` bigint(20) unsigned NOT NULL,
`log_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`start_pos` bigint(20) unsigned NOT NULL,
`end_pos` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`server_id`) USING HASH)
ENGINE=INNODB DEFAULT CHARSET=latin1;

Then do CHANGE MASTER:

CHANGE MASTER TO MASTER_HOST='ip/hostname of the replication server', MASTER_USER='repl', MASTER_PASSWORD='repl';

Staging the InnoDB Slave with Data
Now you need to stage the InnoDB slave with data. What you need to do is to disable traffic to NDBCLUSTER in order to get a consistent snapshot of the data. As there are no clusterwide table locks in NDBCLUSTER you have two options:

Block the Loadbalancer from sending any transactions to MySQL Cluster
Make all SQL nodes READ ONLY, by locking all tables on ALL MySQL servers (if you use NDBAPI applications, then option 1) or shutting down the applications is the the only option): FLUSH TABLES WITH READ LOCK;

So on all MySQL Servers in the Access Layer do:

FLUSH TABLES WITH READ LOCK;

Ensure, by looking at the replication server, that no writes are made to the NDBCLUSTER by looking at the SHOW MASTER STATUS:

mysql> show master status;
+---------------+-----------+--------------+------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+-----------+--------------+------------------+
| binlog.000008 | 859092065 |              |                  |
+---------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

Run the SHOW MASTER STATUS; a couple of times until you see the Position not changing any more

Then RESET the replication server, so you have a good clean slate to start from:

mysql> RESET MASTER;

Now use mysqldump two times to get :

one dump with the schema
another dump with the data

mysqldump --no-data --routines --trigggers > schema.sql

mysqldump --no-create-info --master-data=1 > data.sql

Of course you can dump out only the databases you are interested in.

When the dumps have finished, you can enable traffic to NDBCLUSTER again. You can do on ALL SQL nodes:

UNLOCK TABLES

Point is that you can enable traffic to NDBCLUSTER again.

Now, change the ENGINE=ndbcluster to ENGINE=innodb in schema.sql:

sed -i.bak 's#ndbcluster#innodb#g' schema.sql

Copy the schema.sql and data.sql to the slave, and load in the dump file to the InnoDb slave.

Finally you can start replication, on the InnoDB slave you can now do:

START SLAVE;

SHOW SLAVE STATUS \G

And hopefully all will be fine :)

MySQL Cluster: Too many active scans

noreply@blogger.com (Johan Andersson) — Thu, 30 Aug 2012 10:35:00 +0000

Continuing with another blog about troubleshooting MySQL Cluster we will look at a common error message: "Too many active scans".

Now, you have already set MaxNoOfConcurrentScans=500 in your config.ini, and you can't increase it more (the max is 500), but you still get this error. Frustrating!

But looking into the code, it is not MaxNoOfConcurrentScans that sets the limitation, it is actually MaxNoOfLocalScans and the "Too many active scans" comes from the Local Query Handler of the NDB node, and that is where MaxNoOfLocalScans is used!

So what you can try to do is to actually set/increase MaxNoOfLocalScans, and if it is not set already in your config.ini, the default value is calculated as:

In 7.2.0 and later:
MaxNoOfLocalScans= tcInstances * lqhInstances * noOfDBNodes *
MaxNoOfConcurrentScans + 2
So if we have tcInstances=1, lqhInstances=4, noOfDBNodes=4, and MaxNoOfConcurrentScans=500 we get
MaxNoOfLocalScans=1*4*4*500 + 2 = 8002

Earlier versions:
MaxNoOfLocalScans = (noOfDBNodes * noOfScanRecords) + 2 ,

So if we have noOfDBNodes=4, and MaxNoOfConcurrentScans=500 we get

MaxNoOfLocalScans=4*500 +2 = 2002

If you use our configuration packages, then you can simply just increase/set the MaxNoOfLocalScans and do:

./rolling-restart.sh

Good luck!

MySQL Cluster: Troubleshooting Error 157 / 4009 Cluster Failure

noreply@blogger.com (Johan Andersson) — Wed, 29 Aug 2012 07:07:00 +0000

Suddenly your application starts throwing "error 157" and performance degrades or is non-existing. It is easy to panic then and try all sorts of actions to get past the problem. We have seen several users doing:

rolling restart
stop cluster / start cluster

because they also see this in the error logs:

120828 13:15:11 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure

That is not a really a nice error message. To begin with, it is a WARNING when something is obviously wrong. IMHO, it should be CRITICAL. Secondly, the message ‘Cluster Failure’ is misleading. The cluster may not really have failed, so there is no point trying to restart it before we know more.

So what does error 157 mean and what can we do about it?

By using perror we can get a hint what it means:

$ perror 157

MySQL error code 157: Could not connect to storage engine

What this means is simply as it says, one SQL node is not connected to the NDB storage engine. It can be that you have messed up the ndb-connectstring in the my.cnf of that SQL node.

It is often the case that someone has been doing ALTER TABLEs, and for some reason, the schema files have been corrupted or is of the wrong version on one or more SQL nodes. It would make sense to check the SQL nodes for potential problems there.

If you have multiple SQL nodes, you probably have the SQL nodes behind a load balancer. Which MySQL is really causing this problem? One of them, or all of them?

Let’s take a look from one of the SQL nodes:

mysql> show global status like 'ndb_number_of%';

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

| Variable_name | Value |

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

| Ndb_number_of_data_nodes | 2 |

| Ndb_number_of_ready_data_nodes | 2 |

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

2 rows in set (0.00 sec)

This is perfect, we have two Data nodes , and two of them are ready. This means that this particular SQL node is connected to the NDB storage engine. Now, let's look at another one:

mysql> show global status like 'ndb_number_of%';

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

| Variable_name | Value |

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

| Ndb_number_of_data_nodes | 0 |

| Ndb_number_of_ready_data_nodes | 0 |

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

2 rows in set (0.00 sec)

This does not look good, this SQL node is not connected to the Data nodes. Now iterate over the remaining 62 MySQL servers or how many you have to find the bad apples. However, I prefer myself to spot the faulty ones like this:

Screenshot from Severalnines ClusterControl

Now, we can stop the SQL nodes that are marked as "red":

1. Stop the MySQL server:
service mysql stop

2. Go into the database(s) you have in the datadir of the MySQL server:
cd /var/lib/mysql/mydatabase/

3. Remove .frm files and .ndb files:
for x in `ls *.ndb`; do rm `basename $x .ndb`.frm; rm `basename $x`; done

4. Start the MySQL server
service mysql start

Is step 2) safe to do?

Yes, the .frm files of the NDB tables are stored in the data dictionary inside the data nodes. By removing these files, and restarting the MySQL server, the MySQL server will fetch the missing .frm files from the data nodes, and you will end up with fresh ones.

Hope this helps someone,

Johan

MySQL Cluster: mysqld Sort aborted and error 4006

noreply@blogger.com (Johan Andersson) — Fri, 11 May 2012 12:55:00 +0000

Just a note on a problem that some people may find useful and may work for you if you have the same issue.

A client had a problem this morning with queries being aborted with the error message:

Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER

Here you would think that increasing the number of MaxNoOfConcurrentTransactions would help, but the root cause is something else (the client is not even close of hitting the default MaxNoOfConcurrentTransactions)

It turned out that during the night a couple of updates to the schema had been made (ALTER TABLEs to convert a couple of TEXT to VARCHAR, so that a particular index could be created).

Looking in the mysql error logs we had:

120511 10:47:05 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
120511 10:47:27 [ERROR] Got error 4006 when reading table './database_name/table_name'

I suspected there was something wrong (corrupted?) with the .frm files that the mysql server is caching in the datadir, and simply did, for all mysql servers:

stop mysqld
remove all .frm for all ndb tables in the database
start mysqld
and finally a "SHOW TABLES" to get all the .frm files back up from the data nodes into the local datadir of the mysqld

Now, the problem is gone, but I can't explain how reading a table can cause error 4006...

Hope this helps someone.

MySQL Cluster: Disk Data Config

noreply@blogger.com (Johan Andersson) — Fri, 27 Apr 2012 12:11:00 +0000

If you are using the Disk Data feature of MySQL Cluster you may find this useful. As you may know the default way of storing data in MySQL Cluster is in-memory. However, if you have tables that don't require realtime access and/or are growing larger than your available memory then you can use disk data tables. I have written a bunch of posts about disk data tables before:

However, it is quite hard to configure Disk Data tables correctly. There are some parameters to keep an eye on:

UNDO LOG - MySQL Cluster undo logs every write access to the disk data tables. You can grow this resource online (ALTER .. ADD ..)
REDO LOG - as for in-memory tables every write is REDO logged. You can grow the REDO log with a Rolling restart (NoOfFragmentLogFiles). The REDO LOG is shared between the disk data tables and the in-memory tables.
REDO BUFFER - specified as RedoBuffer=32M or higher in config.ini. The REDO BUFFER is shared between the disk data tables and the in-memory tables.
UNDO BUFFER - created from SharedGlobalMemory resource and is set at LOGFILE GROUP creation. WARNING!! Can not be changed at runtime! The LOGFILE GROUP must be recreated, and it requires that the table space (and all its data files) is dropped before.
TABLESPACE - can be extended at runtime. ALTER .. ADD..
DiskPageBufferMemory - like the innodb_buffer_pool. The bigger the better. Can be tuned with a Rolling Restart.

For stability the most common mistakes are to set UNDO LOG and UNDO BUFFER too small.

What can happen then?

Stability and Recovery

If UNDO BUFFER is too small symptoms are:

Failed writes to the Disk Data tables.
In worst case you can hit crashing bugs.

If UNDO LOG is too small symptoms are:

Failed writes to the Disk Data tables. In worst case you can hit crashing bugs.
Node recovery may not be possible - the recovering node will crash.
Severalnines has assisted a number of client to perform a number of recovery when this has been the case.
If there is a Cluster crash due to a too small UNDO LOG you will most likely have to restore from backup.

How can it be possible for MySQL Cluster to fail to recover the node, when it was possible to load it with data?

The problem is that your write pattern when writing data at runtime into MySQL Cluster is different from the write pattern at recovery.

At recovery the recovering data node will copy data (over the network) from the other node in the node group. It will do this as fast as it can, table by table.

Thus, it will be a lot of data coming into the recovering data node (like a bulk write) and in this case then the UNDO LOG may be too small to handle the write log (you can argue why UNDO and REDO log while doing recovery but but..). If the UNDO LOG is over run, then the node will crash.

Configuration

As usual this depends a lot on the access patterns you have so it is hard to give exact numbers but if you rely on disk data tables you should have:

[NDBD DEFAULT] SharedGlobalMemory=1024M
Disk operation records and other meta data is allocated from this buffer. The more records you have in the disk data tables the bigger this buffer must be. 1024M is a good start.
[NDBD DEFAULT] DiskPageBufferMemory=4096M
As large as you can (remember that in-memory tables, and for disk data tables, indexed attributes resides in DataMemory so you need to have enough DataMemory.
The important when tuning the DiskPageBufferMemory is to get a good hit ratio. If you use ClusterControl you will see the hit ratio (picture coming soon).
4096M is a good start.
UNDO_BUFFER_SIZE=32M or 64M (same size as you have for the RedoBuffer)
This is specified in the CREATE LOGFILE GROUP ...
UNDO LOG space should be 0.5 x NoOfFragmentLogFileSize x 4 x FragmentLogFileSize (this may take it to the extreme but better safe than sorry).
[NDBD DEFAULT] DiskIoThreadPool=8 (2 is default)
UndoIndexBuffer/UndoDataBuffer - those parameters are deprecated but still in the manual (I have grepped the 7.2.4 source and find no trace of it. It was a long time those were used. When LCP was done different in Cluster. So no need to set these.

Please not that you can always add more UNDO LOG files online by issuing

ALTER LOGFILE GROUP ADD UNDOFILE 'undo_X.dat INITIAL_SIZE=1024M ENGINE=NDBCLUSTER;

However, if the Cluster crashes, then it is too late.

Disk Layout

Put the Redo log + LCP on separate disk spindles ( A)
Put the Undo log on separate disk spindles (FileSystemPathUndoFiles) (B)
Put the Data files (table space) on separate disk spindles (FileSystemPathDataFiles) (C)

In many cases you can combine B and C on the same disks, and if you have a good disk array, you can of course combine A,B and C.

Bottom line is, watch your disks and utilized they are. That will determine if you have to make disk reconfigurations. Also, beware of trial and error before getting the DiskPageBufferMemory and SharedGlobalMemory right.

MySQL Cluster: How to load it with data

noreply@blogger.com (Johan Andersson) — Wed, 25 Apr 2012 09:29:00 +0000

After you have setup and properly configured MySQL Cluster you typically want to load data into it. There are a couple of ways to load data into MySQL Cluster, e.g, from dumpfiles or from csv files.

If you expect loading data into MySQL Cluster will be as fast as on MyISAM then you have the wrong expectations unless you parallelize your data loading.

Also, MYISAM/INNODB stores the data in local buffers/disk and in one copy only (asynchronous replication does not count as it is asynchronous) whereas MySQL Cluster (NDBCLUSTER engine) stores two copies of the data. Synchronous replication between the two copies adds ~ 2x overhead and you have network between mysql servers and data nodes.

So, to load a dump file into MySQL Cluster is bound to be slower than MYISAM/INNODB. Also loading data files can be error prone.

Here is what we at Severalnines usually do to avoid trial and terror of configuration options and to make sure we don't have to make X tries to load my Y GB dump file. We want to do it once, and get it right then, just because it takes time to make multiple iterations:

Load in the schema in MYISAM/INNODB on one of the MySQL Servers connected to the data nodes.Please Note that your data is NOT yet stored in the NDBCLUSTER storage engine and is at this point only accessible from the mysql server where you imported the MYISAM/INNODB tables.
Check for redundant indexes and remove them to reduce storage footprint. Some ORMs have a tendency for adding redundant indexes. Personally I think it is faster to make the transformations when the tables are in MYISAM/INNODB than in NDBCLUSTER, especially if there are data in the tables.
You can use Percona's pt-duplicate-key-checker (wget percona.com/get/pt-duplicate-key-checker). See an example below at the end. It makes it dead easy to find and drop the redundant indexes. Redundant indexes are good to remove also because writes will be faster (less indexes to update) and to reduce the risk that the optimizer will pick the wrong index.
Check data types - do you have BLOB/TEXT where you really can use VARBINARY/VARCHAR ? BLOBs/TEXTs can slow down performance with a factor 2-3x (at least). Sometimes BLOBs/TEXTs are overused in many applications.

When you have washed your schema then you can simply do:

ALTER TABLE tablename ENGINE=ndbcluster;

If you get an error message such as:

mysql> alter table events engine=ndbcluster;

ERROR 1297 (HY000): Got temporary error 1217 'Out of operation records in local data manager (increase MaxNoOfLocalOperations)' from NDBCLUSTER

Then you can change the ndb_batch_size (a lower batch size will make it longer time to ALTER the table from MYISAM/INNODB to NDBCLUSTER because more roundtrips are needed between the MySQL Server and the data nodes):

mysql> set ndb_batch_size=8*1024*1024;

Query OK, 0 rows affected (0.00 sec)

mysql> alter table events engine=ndbcluster;

Query OK, 67748 rows affected (4.00 sec)

Records: 67748 Duplicates: 0 Warnings: 0

32MB is the max value for ndb_batch_size.

Now, to furthermore increase the speed of the ALTER you can perform two or more ALTERs in parallel from two or more connections:

mysql conn 1 > alter table events engine=ndbcluster;

mysql conn 2 > alter table employees engine=ndbcluster;

mysql conn 3 > alter table randomdata engine=ndbcluster;

Finally do:

ANALYZE TABLE ;

on all tables to build index statistics (MySQL Cluster 7.2 will love you for this and you will hate MySQL Cluster 7.2 if you don't).

However, if you do too many in parallel you will most likely run into problems like:

Redolog files are overloaded (you have too small redo log and too slow disks to handle the write load generated by the ALTER)
Out of Redo buffer (you have too slow disks to handle the write load generated by the ALTER) - it will probably not matter if you increase the RedoBuffer as your disk subsystem it too slow and cannot "consume" what you "produce".
Out of operation records ( increase MaxNoOfConcurrentOperations or MaxNoOfLocalOperations), at Severalnines we set this to 100000 by default, you can of course increase this but it costs memory. If you increase it you should change:
MaxNoOfConcurrentOperations, then MaxNoOfLocalOperations will automatically be set to 1.1 x the value of MaxNoOfConcurrentOperations. With MaxNoOfConcurrentOperations=100000 it will take 100MB of RAM on each data node. MaxNoOfConcurrentOperations=200000 will take 200MB of RAM on each data node.

If you have a CSV file you can do the same: Load the data first into MYISAM/INNODB tables and then ALTER.

Why first load it into MYISAM/INNODB?

Well, i think it makes it easier and gives better control. If you have a 30GB dump file and many tables you want to load, then it is easier to track errors and to make use of the ndb_batch_size parameter to make it actually load without errors, i.e, you will save time, even though you will use some extra time to first load in the data into MYISAM/INNODB:

Example run of ./pt-duplicate-key-checker:

./pt-duplicate-key-checker --host=ubuntu02 --user=cmon --password=xxxxxxx --databases=test2 --tables=t1
# ##################################################################
# test2.t1

# ##################################################################

# a is a duplicate of PRIMARY

# Key definitions:

# KEY `a` (`a`)

# PRIMARY KEY (`a`),

# Column types:

# `a` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `test2`.`t1` DROP INDEX `a`;

# ##################################################################

# Summary of indexes

# ##################################################################

# Size Duplicate Indexes 0

# Total Duplicate Indexes 1

# Total Indexes 2

Deploy MySQL Cluster 7.2 GA in 288 seconds

noreply@blogger.com (Johan Andersson) — Thu, 23 Feb 2012 16:19:00 +0000

It seems that our friends at Oracle have been pretty busy with the GA release of MySQL Cluster 7.2.

This is not just old wine in new bottles.

While it may be a dot release, it does appear to be a more significant step forward than a dot release would imply.

First off, we are very excited to announce that the Severalnines Cluster Configurator now includes support for 7.2 GA.

As the title of this blog suggests, it is possible, as we have experienced, to deploy 7.2 GA in 288 seconds, i.e. just a few minutes. This was done on m1.large instances on Amazon. We have published a quick how-to deployment guide for Amazon here: http://support.severalnines.com/entries/20916823-installing-on-amazon-ec2.

This being said, let’s have a look at some of the new features in 7.2 and why they matter.

MySQL Cluster has always suffered from poor JOIN performance, mainly because the nested loop algorithm did not work very well in a parallel environment. That meant the join was executed entirely in the MySQL Server. This resulted in a lot of network round-trips between the MySQL Server and the data nodes.

Now with 7.2, this has changed. With Adaptive Query Localization, a JOIN query is now shipped down to the data nodes and executed in parallel on local copies of data.

Results look very promising, with up to 70x factor improvement on some queries as described in this excellent post.

By getting better index statistics, the SQL optimizer can also build a better execution plan for each query. This in turn reduces the need for manual query tuning. Prior to 7.2, a lot of index hinting was needed. However, 70x speed improvement does not mean that a single query on MySQL Cluster will perform as fast as on InnoDB, since there will always be the network latency between SQL and Data nodes. But it does mean that many queries that were running very slowly in 7.1 might now run fast enough.

The Memcached API is a new addition to an already extensive list of API connectors, aimed at existing Memcached users and the NoSQL community. For a product trying to be attractive to the NoSQL community, perhaps the addition of distributed parallel JOIN algorithms may be a bit confusing. NoSQL programmers typically avoid JOIN operations, for reasons including scalability and schema flexibility.

Is someone trying to have their cake and eat it too? :-)

Among the other features described here, the separation of Data Nodes across data centers (and specially Figure 3) is misleading. With a 2-phase commit protocol to guarantee synchronous replication within a Node Group, NDB would have to defy the laws of physics to support the below diagram. Unless the data centers are physically close to each other with a low-latency redundant link in-between them, splitting of data nodes across data centers is not a good idea.

It is also worth noting that 7.2 GA is based on MySQL 5.5, whereas 7.1 was based on MySQL 5.1 – probably more relevant for users with mixed NDB and InnoDB tables.

So: is MySQL Cluster 7.2 for you?

If yes, then try it out using the Severalnines Cluster Configurator: http://www.severalnines.com/cluster-configurator/).

It will take just a few minutes to generate a deployment package, which automates the installation of the cluster. Or it might take 288 seconds ... let us know ;-)

Walking on Cloud 9

noreply@blogger.com (Johan Andersson) — Thu, 15 Dec 2011 14:55:00 +0000

As the saying goes, we at Severalnines have been walking on several clouds this year, 9 to be precise!

Today, we are proud to say that we are on walking on Cloud 9!

And in the spirit of celebration, we would like to announce our:

Top 9 Clouds of the Year 2011 for Severalnines

Cloud 1 – releasing ClusterControl™ - our first commercial product in April!

ClusterControl™ is our flagship product. It enables developers and database administrators to Deploy, Manage, Monitor and Scale their clustered database platforms, free from the complexity and learning curves associated with database clusters. Read more ...

Cloud 2 – releasing ClusterControl™ for MySQL Replication

Designed to address issues and needs of MySQL users relying on MySQL Replication, ClusterControl™ for MySQL Replication offers a complete set of tools to assist developers and administrators of all skill levels to deploy, manage, monitor and scale their replicated MySQL databases. Read more ...

Cloud 3 - releasing ClusterControl™ for MySQL Galera together with Codership

ClusterControl™ for MySQL Galera enables customers to Deploy, Manage, Monitor and Scale a clustered MySQL database platform based on Galera Replication. MySQL Galera is a synchronous multi-master cluster for MySQL/InnoDB, and allows applications to read and write from any MySQL Server.

Developers and DBAs now have access to all of the features of Severalnines' flagship product ClusterControl™ specifically adapted to MySQL Galera. Read more ...

Cloud 4 – reaching over 7,000 installations via the Severalnines Configurator

The Severalnines Configurator allows you to generate a production class configuration for a clustered MySQL configuration. It also generates a deployment package that automates the deployment of the complete database setup. Topologies can be based on MySQL Replication, MySQL Cluster or MySQL Galera.

We have a great user base and in order to facilitate communication within our user community, we set up our Severalnines Customer Services platform with forums, online support, etc. both for paying customers as well as users. Check it out and join the conversation!

Cloud 5 – our Customers

At Severalnines, our goal is to reduce database costs, ease deployment, simplify management and increase DBA and developer productivity.

But rather than us telling you why we think we are great, we wanted to provide documented case studies on how our innovative products are helping customers meet and exceed business goals for their database operations. See what our customers have to say about us.

Cloud 6 – introducing Severalnines DataCloud™

Severalnines DataCloud™ enables DBaaS for public, private and hybrid clouds. It extends the advantages of cloud computing to the database infrastructure layer by enabling on-demand access, automated management, managed availability and elasticity of MySQL databases. This reduces cost and the complexities of deploying and managing databases. Read more ...

Cloud 7 – launching the first ever 100% European DbaaS with City Network

On November 23^rd, Severalnines and our partner City Network announced the first fully European Database as a Service (DBaaS) solution - in beta form. The City Cloud Database Service is based and operated in the European Union - offering European customers full compliance with EU Directive on Data Protection 95/46/EC and a safe haven from the reaches of the US Patriot Act and making it the first ever offering of its kind. Read more ...

Cloud 8 – being talked about at the European Commission – Severalnines in the News

Following our announcement of the City Cloud Database Service, the press took over and 20+ original articles later, we discovered that we were even being talked about at European Commission conferences. Which shows to prove that politicians do read the papers (or are well briefed by colleagues who do) and that we had hit the right spot with our announcement. Find out more and see all the press coverage in our News Center.

Cloud 9 – winning the EuroCloud Sweden & Europe Awards 2011 for Best Startup!!!

We did it! After winning the EuroCloud Award for Sweden, we won the EuroCloud Europe Award for Best Startup last week in Paris at a prestigious ceremony on the Seine. We attended the ceremony together with our friends from City Network and needless to say that we had a great night! The Awards have been widely covered by the European press in multiple languages – visit our News Center for all the details.

All in all, it's been a great year for us and we have all of you to thank for that!

So here is a BIG 'thank you' from everyone at Severalnines to all our customers, partners, friends and supporters out there.

Have a great year-end and and an even better start to the year 2012 - see you there!

Please do reach out to us with your feedback on Facebook, LinkedIn, XING or directly via these contact details for fruitful and interactive discussions on this latest release. For 'instant' communication, feel free to follow us on Twitter !

About Severalnines

Severalnines provides automation and management software for easily usable, highly available and auto-scalable cloud database platforms. ClusterControl™, the company’s flagship product, used by developers and administrators of all skill levels, addresses the full deploy-manage-monitor-scale cycle. Severalnines has enabled over 7,000 deployments to date via its popular online configurator for clustered MySQL databases.

To see who is using Severalnines today, please visit our references page.

New Euro Partnership offers safe-haven from US Patriot Act

noreply@blogger.com (Johan Andersson) — Wed, 23 Nov 2011 09:48:00 +0000

New Euro Partnership offers safe-haven from US Patriot Act
Severalnines and City Network launch first fully European ‘Database as a Service’

STOCKHOLM and LONDON, 23^rd November -Severalnines, provider of automation and management software for cloud database platforms, and City Network, the ‘data center in a browser’ company have announced the first fully European Database as a Service (DBaaS) solution - in beta form. The City Cloud Database Service is based and operated by companies in the European Union - offering European customers full compliance with EU Directive on Data Protection 95/46/EC and a safe haven from the reaches of the US Patriot Act.

EU customers can now benefit from the savings and flexibility enabled by cloud-based database services safe in the knowledge that they will not fall under the jurisdiction of the Patriot Act. Under the Patriot Act, data from EU users of US-owned cloud-based services can currently be shared with US law enforcement agencies without the need to tell the user.

“Small start-ups and large corporations are turning to the cloud in a bid to deploy and manage databases with as little overhead as possible - but they need to know their data is secure and that they retain the right and ability to control who accesses it,” says Vinay Joosery, chief executive officer at Severalnines.

“A fully managed database service will enable our customers to further reap the benefits of the cloud,” says Johan Christenson, Chairman at City Network. “We believe that a service owned and operated locally in the EU, and fully compliant with EU data protection laws, will be very particularly attractive for European companies. US companies with European operations will also benefit from the lower latency of a locally hosted solution.”

The MySQL based City Cloud Database Service enables companies to outsource the configuration and management of their databases and only pay for what they use. Specific benefits include:

• Web interface for easy provisioning

• Zero maintenance or administration – backups, patch management and replication handling are fully automated

• Performance Management – users have visibility of top queries and can drill-down into performance

• High Availability – failures are handled automatically to minimise downtime

• Scalability – users can add more instances at the click of a mouse

• Comprehensive user interface for administering databases; and

• Data import and export functionality to help move MySQL databases within the cloud.

The service relies on the Severalnines DataCloud platform, a database automation and management platform that helps companies run flexible databases anywhere, whether on their premises or in the cloud. It is based on City Network’s City Cloud computing platform which allows unlimited growth with a focus on stability, security and redundancy.

City Cloud Database Service is available in beta. Companies can apply for the trial by emailing dbaas@citycloud.eu. The service is available free of charge for the duration of the beta period, which will run until early 2012. More information is available on http://www.citycloud.eu/

[ENDS]

About Severalnines

With everyone at Severalnines coming from an Open Source background and with a view to supporting other start-up organisation, the company is introducing a start-up program, whereby organisations with less than 10 employees and less than one million Euro in funding will be given free access to its enterprise software for 12 months. For information, just email startup@severalnines.com.

To see who is using Severalnines today, please visit our references page: http://severalnines.com/references

About City Network

With more than 10,000 customers across Europe City Network is one of the leading European hosting companies. Core services include cloud hosting, dedicated environments as well as shared hosting and domain services. City Network also provides high end backup services via www.onlinebackup.io. City Cloud is the cloud computing brand which you can read more about at www.citycloud.eu. You can find more information about City Network at: www.citynetworkhosting.com

Severalnines releases ClusterControl™ for MySQL Galera in cooperation with Codership

noreply@blogger.com (Johan Andersson) — Tue, 25 Oct 2011 09:42:00 +0000

Stockholm – October 25^th 2011

Severalnines, provider of automation and management software for easily usable, highly available and auto-scalable cloud database platforms, today announces the release of ClusterControl™ for MySQL Galera in cooperation with Codership, the replication experts organisation that leverages the latest developments in computer science to produce fast and scalable synchronous replication solutions that "just work" for databases and similar applications.

Introducing ClusterControl™ for MySQL Galera

ClusterControl™ for MySQL Galera enables customers to Deploy, Manage, Monitor and Scale a clustered MySQL database platform based on Galera Replication.

Developers and DBAs now have access to all of the features of Severalnines' flagship product ClusterControl™ specifically adapted to MySQL Galera.

Designed to address issues and needs of MySQL users relying on MySQL Replication, ClusterControl™ for MySQL Galera offers a complete set of tools to assist developers and administrators of all skill levels to deploy, manage, monitor and scale their replicated MySQL databases.

What is MySQL Galera?

MySQL Galera is a synchronous multi-master cluster for MySQL/InnoDB or, in similar words, a synchronous multi-master replication plug-in for InnoDB. It is very different from the regular MySQL Replication and addresses a number of issues including write conflicts when writing on multiple masters, replication lag and slaves being out of sync with the master. Users do not have to know which server they can write to (the master) and which servers they can read from (the slaves). With MySQL Galera you can simply write and read from any MySQL Server.

Galera Cluster Use Cases

Galera replication works for a wide variety of use cases for high availability and scalability where you tolerate no lost of transactions or no slave lag or you look for write/read scalability Here are some common use cases where Galera can be used.

· Read Master

o Traditional MySQL master-slave topology, but with Galera all "slave" nodes are capable masters at all times, it is just the application who treats them as slaves. Galera replication can guarantee 0 slave lag for such installations and due to parallel slave applying, much better throughput for the cluster.

· Write Scalability

o Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.

· WAN Clustering

o Synchronous replication works fine over the WAN network. There will be a delay, which is proportional to the network round trip time (RTT), but it only affects the commit operation.

· Disaster Recovery

o Disaster recovery is a sub-class of WAN replication. Here one data center is passive and only receives replication events, but does not process any client transactions. Such a remote data center will be up to date at all times and no data loss can happen. During recovery, the spare site is just nominated as primary and application can continue as normal with a minimal fail over delay.

· Latency Eraser

o With WAN replication topology, cluster nodes can be located close to clients. Therefore all read & write operations will be super fast with the local node connection. The RTT related delay will be experienced only at commit time, and even then it can be generally accepted by end user, usually the kill-joy for end user experiences is the slow browsing response time, and read operations are as fast as they possibly can be.

Features and benefits of MySQL Galera

Galera features

· Active-active multimaster cluster

· Synchronous replication for InnoDB

· Read and write to any MySQL Server

· Automatic failover and node joining

· True Parallel replication

· Direct client connections

Galera benefits

· No slave lag or lost of data or out-of-sync situations.

o All nodes are consistent.

· Scales writes and reads

· Works synchronously over LAN/WAN/CLOUD

· No changes to applications

View all of the features here

Deploying a Galera cluster with ClusterControl™

A Galera cluster can be configured using the Severalnines Configurator

Our Online Tutorial provides an in-depth view and understanding of Galera cluster, covering the following topics:

· Introduction to Galera cluster

· Differences between MySQL Replication and Galera replication

· The pros and cons of Galera cluster

· Using Galera cluster to replicate between data centers

· Certification-based Replication

· Deploying a Galera cluster

· Failure handling with ClusterControl™ and Galera

· Adding a node to a Galera cluster

· Schema changes in Galera cluster

Try the ClusterControl™ Configurator for MySQL Galera today or book a demo to see it live in action!

· ClusterControl™ Configurator for MySQL Galera – try it today!

· Online Tutorial – ClusterControl™ for MySQL Galera

· Severalnines Knowledge Base

· Book a demo!

About Severalnines

To see who is using Severalnines today, please visit our references page.

About Codership

Codership Oy was founded in 2007 by database clustering experts to exploit the latest developments in computer science and produce fast & scalable synchronous replication solutions for databases and similar applications. Currently, Codership offers multi-master cluster for MySQL database as open source. For more information about our open source MySQL Galera software and support offering, visit www.codership.com

© 2011 Severalnines AB. Severalnines and the Severalnines logo(s) are trademarks of Severalnines AB. MySQL is a registered trademark of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Multi-master and read slaves using Severalnines

noreply@blogger.com (Johan Andersson) — Thu, 13 Oct 2011 07:00:00 +0000

This blog post shows how you can use the Severalnines Configurator for MySQL Replication to deploy a Multi-master replication setup, and install ClusterControl.
You can also watch videos showing what is described below or read an even more detailed tutorial.

When the deployment is finished you have a set of tools to manage and monitor replication, as well as to add new slaves, and to perform failover.
You can choose:

The number of slaves you wish to connect to the master->relay master.
whether you want to use MySQL 5.5.x or Percona 5.5.x
Semi-sync replication or not (semi sync really recommended)
cloud provider (on premise/EC2/Rackspace

Please note that your hosts must resolve to a valid ip. If 'hostname -i' resolves to nothing or to 127.0.0.1 it will not work.

We recommend also that you don't use bi-directional replication. It may sound like a good idea, but in practice it is challenging to get it working well.

When deployed you can easily perform tasks as:

Add new slaves
Failover
View replication health information
Stop/start replication links
Stage slaves

The Configurator for MySQL Replication is a wizard-like application and you have to enter details about the setup you want to have. When this is done you will get a tar.gz that contains the deployment and management scripts.

ClusterControl

The deployment process will also install ClusterControl which is a set of monitoring agents and functionality to manage the database installation. The agents are deployed on each server and collects host information and information from the local MySQL server a particular agent is monitoring (such as replication statistics and status information).

One server (dedicated) is denoted the ClusterControl server. It is holding a database, CMON DB, that contains data about the monitored hosts, and the reporting data from the local agents is stored in this database. On this server a Controller is running and faciliates failover etc.

Moreover, the ClusterControl server exposes a web interface that can be used to graphically see the health of the replication cluster (after the deployment is done, take a web browser and point it to http://clustercontrolserver/cmon :

From ClusterControl you can then upload a schema, and start loading in data into a database. As well as doing GRANTs etc. In the Enterprise version you an also define performance probes (to make sure you system is not degrading over time), tune queries, try out new queries before putting them in production. You also have a Query Monitor and explains at your hands.

Failover is automatic (if the master fails, the relay will become the new master). When the master comes up again, it will connect to the new master and sync up, become the relay, and the slaves will failover to the relay. We also make use of the replication features of MySQL 5.5 (such as relay recovery) and a bunch of other techniques.

You can also add slaves or remove slaves with the click of a button, as well as scheduling backups.

Installation
When you have finished the wizard you get a package that you should deploy on the ClusterControl server:

tar xvfz s9s-mysql-55.tar.gz
cd s9s-mysql-55/mysql/scripts/install
./deploy.sh

The 'deploy.sh' script will create data directories, install initd scripts, create mysql users, apply database GRANTs (those you defined in the Configurator), and install ClusterControl.

A while in 'deploy.sh' replication will be started, and it can look like this:

*******************************************************************************
*                   Starting replication                                      *
*******************************************************************************
Master hosts
------------
10.30.30.31: up
10.30.30.32: up
Slave hosts
------------
10.30.30.33: up
10.30.30.34: up
starting replication between 10.30.30.31 --> 10.30.30.32 (change master=1, reset master=1)
replication [started] 10.30.30.31 --> 10.30.30.32
starting replication between 10.30.30.32 --> 10.30.30.33 (change master=1, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.33
starting replication between 10.30.30.32 --> 10.30.30.34 (change master=1, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.34
master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
10.30.30.31 10.30.30.32 ok         binlog.000003:250 binlog.000003| 250| 250| 0
--- slaves follows ---
10.30.30.32 10.30.30.33 ok         binlog.000001:107 binlog.000001| 107| 107| 0
10.30.30.32 10.30.30.34 ok         binlog.000001:107 binlog.000001| 107| 107| 0

After this, ClusterControl will be installed. If you are using RPM based installation there can be rpm dependency problems (we install libmysqlclient.so.16), but it can conflict with what comes with your distribution. Please let us know if this happens, contact us on community@severalnines.com.

Starting/stopping Replication and basic examples

After having executed deploy.sh, you can view the status of the mysql servers:

cd s9s-mysql-55/mysql/scripts/
./status.sh  -a
Master hosts
------------
10.30.30.31: up
10.30.30.32: up
Slave hosts
------------
10.30.30.33: up
10.30.30.34: up

To view the replication status:

cd s9s-mysql-55/mysql/scripts/
./repl-status.sh -a
master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
10.30.30.31 10.30.30.32 ok         binlog.000003:250 binlog.000003| 250| 250| 0
--- slaves follows ---
10.30.30.32 10.30.30.33 ok         binlog.000001:107 binlog.000001| 107| 107| 0
10.30.30.32 10.30.30.34 ok         binlog.000001:107 binlog.000001| 107| 107| 0

To stop replication between 10.30.30.32 (relay master) to 10.0.30.33 you can do:

cd s9s-mysql-55/mysql/scripts/
./stop-repl.sh -m 10.30.30.32 -s 10.30.30.33
stopping replication between 10.30.30.32 --> 10.30.30.33
replication [stopped] 10.30.30.32 --> 10.30.30.33

To start replication again you can do:

cd s9s-mysql-55/mysql/scripts/
./start-repl.sh -m 10.30.30.32 -s 10.30.30.33
starting replication between 10.30.30.32 --> 10.30.30.33 (change master=0, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.33

Other options for 'start-repl.sh' is -r (for reset master, dangerous), -c (change master). Normally these options are not needed but you can use them in conjuction with -l (logpos) and -f (binlog file) if you want to start replication from a particular position.

And we can check the replication status:

ubuntu@ip-10-49-122-115:~/s9s-mysql-55/mysql/scripts$ ./repl-status.sh -a
serverid master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
1      10.48.207.130 10.48.139.24 ok         binlog.000005:494 binlog.000005| 494| 494| 0
2     replication not activated - you must start replication on this link.
--- slaves follows ---
3      10.48.139.24 10.49.122.56 ok         binlog.000005:485 binlog.000005| 485| 485| 0
4      10.48.139.24 10.49.110.183 ok         binlog.000005:485 binlog.000005| 485| 485| 0

Good luck and don't hesitate to contact us at feedback@severalnines.com or community@severalnines.com if you have any problems or whatever it may be. You can also book a demo this if you want to know more.

Severalnines releases ClusterControl™ for MySQL Replication v. 1.1.9

noreply@blogger.com (Johan Andersson) — Tue, 04 Oct 2011 07:58:00 +0000

Stockholm - October 4th 2011

Severalnines, provider of automation and management software for easily usable, highly available and auto-scalable cloud database platforms, today announces the latest release of its flagship product ClusterControl™ for MySQL Replication.

Introducing ClusterControl™ for MySQL Replication v.1.1.9

ClusterControl™ for MySQL Replication enables customers to Deploy, Manage, Monitor and Scale a clustered database platform based on the standard MySQL Replication.

Developers and DBAs now have access to all of the features of Severalnines' flagship product ClusterControl™ specifically adapted to MySQL Replication.

Designed to address issues and needs of MySQL users relying on MySQL Replication, ClusterControl™ for MySQL Replication offers a complete set of tools to assist developers and administrators of all skill levels to deploy, manage, monitor and scale their replicated MySQL databases.

Features and benefits

Config wizard
- Generates individual configurations based on application needs
- Creates deployment packages
- Deploys a Replication cluster in minutes
Config manager
- Sanity checks changes to configuration parameters
- Rolls out configuration changes to all servers and automates rolling restarts
Upgrade advisor
- Keeps users up-to-date by performing upgrades
- Applies minor patches or major upgrades
- Allows roll-back to previous versions
Availability manager
- Clusterware that detects process failures
- Automates MySQL master fail-over
- Performs recovery of failed database processes
Backup manager
- Schedule backups or allow users to start an immediate backup
- Monitor and browse existing backups
- Define retention policies
- No proprietary data formats
Monitor
- Granular end-to-end transaction and database operation monitoring
Query analyzer
- Analysis on cluster-wide running queries
- Global MySQL processlist
- Deep real-time and historical visibility into individual nodes
Alarms and notifications
Scale
- Add new Slaves (or remove) to the cluster without any downtime
- Easily specify hostnames of Slaves to be added

View all of the features here.

Automated fail-over with ClusterControl™

Standard MySQL Replication is easy to set up, and is probably the most widely used mechanism to provide high availability for MySQL. Unfortunately, it is somewhat fragile. There is no support for fail-over. Slaves can easily end up with different data from the master. Diverging datasets can cause replication to stop. Crashing masters can corrupt binary logs, which means replication is not possible any more.

ClusterControl™ addresses the problems associated with master fail-over and resynchronizations, and avoids data loss by taking advantage of semi-sync replication in MySQL 5.5.

ClusterControl™ automates fail-over and reconfiguration of the replication setup to keep the Replication cluster stable and running. This online tutorial explains the MySQL Replication architecture, and how different types of failures are handled.

Users can try it now by generating their deployment package.

Reources related to this release

Configurator for MySQL Replication
Tutorial: ClusterControl for MySQL Replication
Videos: Online Demos on MySQL Replication
Book a demo

Please do reach out to us with your feedback on Facebook, LinkedIn, XING or directly via these contact details for fruitful and interactive discussions on this latest release. For 'instant' communication, feel free to follow us on Twitter!

About Severalnines

Severalnines provides automation and management software for easily usable, highly available and auto-scalable cloud database platforms. ClusterControl™, the company’s flagship product, used by developers and administrators of all skill levels, addresses the full deploy-manage-monitor-scale cycle. Severalnines has enabled over 7,000 deployments to date via its popular online configurator for clustered MySQL databases.

To see who is using Severalnines today, please visit our references page.

MySQL Clusters on Amazon EC2 - verified AMIs

noreply@blogger.com (Johan Andersson) — Tue, 23 Aug 2011 13:30:00 +0000

We regularly receive questions from our user community with regards to which AMIs to use when deploying database clusters on Amazon EC2.

As part of our ongoing development work on the Severalnines Configurator and ClusterControl, we have recently done some testing on deploying MySQL Cluster on EC2 using Severalnines on three different AMIs. We thought we should share the results of these tests, hence the reason for this week's blog!

If you would like to test such a deployment yourself, feel free to use the parameters and guidelines below to do so. You can also check out these new videos to see Severalnines technology in action prior to getting started with your testing.

These are the three AMIs that we have tested the deployment on so as to have a good representative mix:

* RightImage_CentOS_5.4_x64_v5.5.9
* Amazon QuickStart AMI: amzn-ami-2011.02.1.x86_64-ebs
* Canonical AMI: ubuntu-maverick-5g-64bit

Of course, it would be great to hear from you on how your testing went and on what AMIs you are currently using yourself. Feel free to share your feedback with us on Facebook, Twitter, LinkedIn or leave a comment on this blog.

Here are the parameters that you can use to test your MySQL Cluster deployment using the Severalnines Configurator.

In all cases, the Internal IP was used as the Hostname in the Configurator and the following was used for all tests:

* Instance type: m1.larg
* Number of instances: 5 (1 for ClusterControl), 2 for data nodes, 2 for MySQL servers and management servers.

RightImage_CentOS_5.4_x64_v5.5.9 (ami-0f42a966)

* Cloud Provider [Configurator]: Amazon EC2
* OS user [Configurator]: root
* Operating System [Configurator]: Redhat/Fedora/Centos/Oracle
* Data directories [Configurator]: /mnt/data/mysqlcluster for data nodes and management servers and /mnt/data/mysql (for the mysql servers) both RPM and tar.gz installation was tested using 'deploy.sh'

Canonical AMI: ubuntu-maverick-5g-64bit (ami-ac41b7c5)

* Cloud Provider [Configurator]: Amazon EC2
* OS user [Configurator]: ubuntu
* Operating System [Configurator]: Ubuntu/Debian
* DataMemory [Configurator]: 512MB
Set it down in the Configurator - yes, it is tiny, but the default disk in this AMI is tiny, so you should add a 20GB EBS Volume if you want to test with the default suggested DataMemory
* Data directories [Configurator]: default suggested ones.
* Installation was tested using 'deploy.sh'

Amazon QuickStart AMI: amzn-ami-2011.02.1.x86_64-ebs (ami-8e1fece7)

* Cloud Provider [Configurator]: Amazon EC2
* OS user [Configurator]: ec2-user
* Operating System [Configurator]: Redhat/Fedora/Centos/Oracle
* DataMemory [Configurator]: 512MB
Set it down in the Confgurator. Yes, it is tiny, but the default disk in this AMI is tiny, so you should add a 20GB EBS Volume if you want to test with the default suggested DataMemory.
* Data directories [Configurator]: default suggested ones.
* .tar.gz installation using 'deploy.sh' no problems.
* RPM installation - dependency problems with libmysqlclient.so.16 vs libmysqlclient.so.14.
This can be fixed by editing the mysqlcluster-71-rpm/cluster/scripts/install/install-rpm.sh before running 'deploy.sh':
Change this, in the function named install_rpm_cmonmysql() :
MySQL-Cluster-gpl-shared-compat-${version}-1.${rhel}.${arch}.rpm
to
MySQL-Cluster-gpl-shared-${version}-1.${rhel}.${arch}.rpm
Now you can run ./deploy.sh

Finally, do not forget to set the EC2 Keypair in the Configurator and upload it to the ClusterControl server before running deploy.sh.

You should now be up and running with your MySQL Cluster on EC2; if you have any questions or comments, please do let us know. We welcome your feedback on Facebook, Twitter, LinkedIn or directly on this blog.

For more information on AMIs: http://aws.amazon.com/amis/
For more information on EC2: http://aws.amazon.com/ec2/

MySQL Clusters in the Cloud with Severalnines on OpenShift

noreply@blogger.com (Johan Andersson) — Tue, 16 Aug 2011 20:41:00 +0000

A blog post on how to deploy a sample JBoss application on OpenShift Flex - using a highly available, scalable backend that leverages MySQL Cluster

The Severalnines team has been busy during the summer months and as result, we have included OpenShift Flex support to our Severalnines Configurator (amongst other things). OpenShift, a Platform-as-a-Service operated by Red Hat, allows developers to develop and manage applications in the cloud. It is now possible to configure a clustered MySQL database for cloud services running on OpenShift. This brings high-availability and scalability at both the application and database layers.

As a guest contributor on Red Hat's OpenShift Community Blog, we provide a comprehensive, step-by-step walkthrough for this type of deployment. For those of you who would also like to view the process “in action”, feel free to view the video that complements this blog entry.

Read the full blog here!

Best Start-up: Severalnines - at EuroCloud Sweden Award

noreply@blogger.com (Johan Andersson) — Thu, 26 May 2011 13:53:00 +0000

Severalnines is proud to announce that it won the Best Startup prize at the EuroCloud Sweden Award in Stockholm this week. The company was chosen out of a number of promising startups targeting the emerging cloud computing market.

The jury noted that “Severalnines have a compelling idea to cloud-enable any customer-preferred database in mission-critical setups. Together with a simple-to-use configurator they put the user in control.”

We are quite honored and proud to receive the Best Startup award. Through our flagship product ClusterControl™, it is our objective to make complex database tasks easy and even enjoyable for our users, and above all, to make sure our users can realise the performance, high–availability and cost efficiencies that cloud computing promises.

Articles on the award can be found here:
EuroCloud Announcement (in english)
IDG article (in english)

Getting started with Cluster/J - index search and table scans

noreply@blogger.com (Johan Andersson) — Wed, 13 Apr 2011 22:23:00 +0000

This post follows the previous post, so set up the environment as described there.
In this example we will perform a full table scan (select * from my_data2) and an index scan (select * from my_data2 where userid=1).

The complete code is here from Severalnines.

Performing a full table scan (no search criteria)
Make sure you have records in the table my_data2.
To perform reads by primary key, we can just use Session::find(..), but here we will do a full table scan.

QueryBuilder qb = s.getQueryBuilder();

/*
* Run the query:
*/
List resultList = query.getResultList();
int count=0;

/*
* Loop over the results
*/

List resultList = query.getResultList();
int count=0;
for (MyData2 result: resultList) {
System.out.println(result.getFriendId());
count++;
}

That is it, if you run the example code, you will see the output.

Done reading X records in Y mssd

Performing a Index scan (search on part of the Primary Key)

The following snippet shows how to setup a EQUAL-filter:

/*
*  We want to compare userid=1 (select * from my_data2 where userid=2) 
*/
QueryBuilder qb = s.getQueryBuilder();
QueryDomainType dobj = qb.createQueryDefinition(MyData2.class);
/*
* Create a filter, we call this filter 'user_id_filter'
*/

PredicateOperand param = dobj.param("user_id_filter");
/*
* Set the column database column you want to filter on.
* Note: The setting dobj.get("userId") is really strange.
* In the table the column in the database is called userid, and in MyData2.
* The documentation of Cluster/J is very bad here, but
* the getter/setter are getUserId/setUserId, and you have to
* take the word after the get/set (in this case UserId) and change the first
* letter to lower case :(
*/
PredicateOperand column = dobj.get("userId");

/**
* Perform an equal compare
* we want to compare userid=1
*/

Predicate compare=column.equal(param);
dobj.where(compare);

Query query = s.createQuery(dobj);

/*
* Bind the search value to the filter
*/
query.setParameter("user_id_filter", new Long(1));

/*
* Run the query:
*/
List resultList = query.getResultList();
int count=0;

/*
* Loop over the results
*/
for (MyData2 result: resultList) {
System.out.println(result.getFriendId());
count++;
}

That is it for this time. I hope the clusterj documention will become better.

Getting started with Cluster/J - inserts (combined PK)

noreply@blogger.com (Johan Andersson) — Wed, 13 Apr 2011 21:54:00 +0000

This post follows the previous post, so set up the environment as described there.
In this example we will have a slightly different table with a combined PK.
The complete code is here.

Create the table

The first thing we should do is to create the table we need for this example:

CREATE TABLE `my_data2` (
`userid` bigint(20) NOT NULL DEFAULT '0',
`friendid` bigint(20) NOT NULL DEFAULT '0',
`data` varbinary(255) DEFAULT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`userid`,`friendid`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (userid) */

Mapping table to Java Interface
An interfae describing each table is needed. You annotate the code with @PrimaryKey and @Column do denote if the names of columns and the column names.
The only difference here is that you need to annotate all primary key columns with @PrimaryKey.

import com.mysql.clusterj.annotation.Column;
import com.mysql.clusterj.annotation.Index;
import com.mysql.clusterj.annotation.PersistenceCapable;
import com.mysql.clusterj.annotation.PrimaryKey;

@PersistenceCapable(table="my_data2")
@Index(name="id")
public interface MyData2 {
   
      @PrimaryKey
      long getUserId();
      void setUserId(long i);
    
      @PrimaryKey
      long getFriendId();
      void setFriendId(long i);
   
      @Column(name = "data")
      byte[] getData();
      void setData(byte[] b);

      @Column(name = "last_updated")
      long getLastUpdated();
      void setLastUpdated(long ts);
}

Performing an insert

Populate the fields in the object, and make it persistent. Don't forget to set all primary key columns or you will get a ClusterJException.

MyData my_data=s.newInstance(MyData.class);
/**
* Set the data on the object
*/
my_data.setUserId(i);
my_data.setFriendId(i);
my_data.setData(data);
my_data.setLastUpdated(System.currentTimeMillis());
/**
* Persist the object */
s.makePersistent(my_data);

That is it, if you run the example code, you will see the output.

Done inserting 100 records in X ms

Getting started with Cluster/J - inserts

noreply@blogger.com (Johan Andersson) — Wed, 13 Apr 2011 17:53:00 +0000

Cluster/J is a the Java direct API to MySQL Cluster which means it bypasses the MySQL Server and executes requests directly on the data nodes of MySQL Cluster. This gives very good performance (typically >2x faster than SQL) and low latency.

In this blog I will present how to get started and how to do simple lookups.

The complete source code (actually a bit extended to show batching) for this example can be found at Severalnines (here). The code you will see below is just snippets.

Environment

To start with you need MySQL Cluster up and running.
For development on localhost then you can get a sandbox from Severalnines.
If you want to have a test/production environment you should you the Configurator for MySQL Cluster.

First of all you have to import the following Cluster/J jars into your project.
In Eclipse, on the project, do "Properties -> Java Build Path --> Add External JARs"
The JARs you need to import are typically located in /usr/local/mysql/share/java/ (if you have installed MySQL Cluster in this location - this is the default location used by Severalnines).

You also need to set the JVM configuration in order to point out the libndbclient library :

-Djava.library.path=/usr/local/mysql/lib/ -Xms128m -Xmx512m

Create the table

The first thing we should do is to create the table we need for this example:

CREATE TABLE `my_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data` varbinary(255) DEFAULT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1

Mapping table to Java Interface
An interfae describing each table is needed. You annotate the code with @PrimaryKey and @Column do denote if the names of columns and the column names.

public interface MyData {     

@PrimaryKey     
long getId();
void setId(long i); 

@Column(name = "data")
byte[] getData();     
void setData(byte[] b);

@Column(name = "last_updated")
long getLastUpdated();
void setLastUpdated(long ts);
}

Setting up the connection to MySQL Cluster

Properties p = new Properties();
p.setProperty("com.mysql.clusterj.connectstring", "localhost:1186");
p.setProperty("com.mysql.clusterj.database", "test");
SessionFactory sf=ClusterJHelper.getSessionFactory(p);

Creating a Session

The SessionFactory provides Sessions. On a session instance you can do a couple of things, e.g, makePersistent, updatePersistent, and find.

/**
* Sessions are not thread safe!
*/
Session s = sf.getSession();

Performing an insert

Populate the fields in the object, and make it persistent.

MyData my_data=s.newInstance(MyData.class);
/**
* Set the data on the object
*/

my_data.setId(i);
my_data.setData(data);
my_data.setLastUpdated(System.currentTimeMillis());
/**
* Persist the object */
s.makePersistent(my_data);

That is it, if you run the example code, you will see the output.

Done inserting 1000 records in 230 ms

(UC2011) BOF - Setting up MySQL Cluster on EC2

noreply@blogger.com (Johan Andersson) — Tue, 12 Apr 2011 18:41:00 +0000

Severalnines will host a BOF at 7pm in Ballroom A, Tuesday 04/12.

After a succesful cluster tutorial we will demo how to use Severalnines Configurator to deploy MySQL Cluster on EC2 on X computers using one command (deploy.sh), complete with monitoring and management.

See you there!

Setting up Multi-master and read slaves using Severalnines

noreply@blogger.com (Johan Andersson) — Mon, 11 Apr 2011 13:30:00 +0000

Read the detailed replication tutorial instead.

This tutorial shows how you can use the Severalnines Configurator for MySQL Replication to deploy a Multi-master replication setup, and install ClusterControl.

When the deployment is finished you have a set of tools to manage and monitor replication, as well as to add new slaves, and to perform failover.
You can choose:

if you wish also setup bi-directional replication between the masters
the number of slaves you wish to connect to the master
whether you want to use MySQL 5.5.10 or Percona latest stable
Handlersocket (percona) or Semi-sync replication (MySQL 5.5)
cloud provider (on premise/EC2/Rackspace)

When deployed you can easily perform tasks as:

Add new slaves
Failover
View replication health information
Stop/start replication links
Stage slaves

tar xvfz s9s-mysql-55.tar.gz
cd s9s-mysql-55/mysql/scripts/install
./deploy.sh

The 'deploy.sh' script will create data directories, install initd scripts, create mysql users, apply database GRANTs (those you defined in the Configurator), and install ClusterControl.

Starting Replication and basic examples

After having executed deploy.sh should do:

cd s9s-mysql-55/mysql/scripts/
./list-servers.sh  -a
master     : 10.48.207.130 [  up]
master     : 10.48.139.24 [  up]
slave     : 10.49.122.56 [  up]
slave     : 10.49.110.183 [  up]

To verify the mysql servers are running and then:

cd s9s-mysql-55/mysql/scripts/
./start-repl.sh --master -i 1 -c

The command above will do a CHANGE MASTER (-c) and start replication from MySQL Server 1 to MySQL Server 2.

This step is not automated since you may want to start bi-directional replication between the masters. In that case you do:

./start-repl.sh --master -a -c

The slaves are then started with:

./start-repl.sh --slave -a -c

Which means, start all slaves (-a) and change master on those slaves.

Then you can run

./repl-status.sh  -a
serverid master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
1      10.48.207.130 10.48.139.24 ok         binlog.000005:107 binlog.000005| 107| 107| 0
2     replication not activated - you must start replication on this link.
--- slaves follows ---
3      10.48.139.24 10.49.122.56 ok         binlog.000005:107 binlog.000005| 107| 107| 0
4      10.48.139.24 10.49.110.183 ok         binlog.000005:107 binlog.000005| 107| 107| 0

We got two masters, and the master with server-id=1 replicates to server-id=2, and the master on server-id=2 replicates to the slaves (3 and 4).

Now we are ready to provision data

./mysql.sh -h 10.48.207.130 -ppassword

mysql> create table t1 (id integer auto_increment primary key, data varchar(255))engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(data) values('hello');

And we can check the replication status:

ubuntu@ip-10-49-122-115:~/s9s-mysql-55/mysql/scripts$ ./repl-status.sh -a
serverid master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
1      10.48.207.130 10.48.139.24 ok         binlog.000005:494 binlog.000005| 494| 494| 0
2     replication not activated - you must start replication on this link.
--- slaves follows ---
3      10.48.139.24 10.49.122.56 ok         binlog.000005:485 binlog.000005| 485| 485| 0
4      10.48.139.24 10.49.110.183 ok         binlog.000005:485 binlog.000005| 485| 485| 0

Good luck and don't hesitate to contact us at feedback@severalnines.com if you have any problems or whatever it may be. You can also book a demo this if you want to know more.

Severalnines launch customer focused website

noreply@blogger.com (Johan Andersson) — Thu, 07 Apr 2011 23:36:00 +0000

I am glad to announce our brand new website today (www.severalnines.com).

The goal of the company is to make it easy for database developers to build their clustered database platforms, either on-premise or in the cloud.

We invite you to explore the new resources on the website (white papers, videos, new configurator for MySQL Replication, new cluster configurator with cloud options, etc.).