Control Your Database Infrastructure
Severalnines
 

Resources for Database Clusters: 9 DevOps Tips, ClusterControl 1.2.8 Release, HAProxy Webinar Replay & More

 
October 23, 2014
By Severalnines

Check Out Our Latest Technical Resources for MySQL, MariaDB & MongoDB Clusters

 

Here is a summary of resources & tools that we’ve made available to you in the past weeks. If you have any questions on these, feel free to contact us!

 

New Technical Webinar

If you are in DevOps, you will know that deploying and managing databases has its challenges! Monitoring, managing schema changes and pushing them in production, performance optimizations, configurations, version upgrades, backups; these are all aspects to consider – preferably before going live!

In this new webinar, we will walk you through 9 key tips to consider before specifically taking Galera Cluster into production. So if you are in devops, then this webinar is for you ;-)

Read more details and register here!

 

Technical Webinar - Replay

If you missed this webinar with our guest speaker from HAProxyTechnologies (or would just like to view it again), it’s online on demand.


New Webinar: 9 DevOps Tips for Going in Production with Galera Cluster for MySQL - November 11th

 
October 21, 2014
By Severalnines

 

Galera is a MySQL replication technology that can simplify the design of a high availability application stack. With a true multi-master MySQL setup, an application can now read and write from any database instance without worrying about master/slave roles, data integrity, slave lag or other drawbacks of asynchronous replication.

 

And that all sounds great until it’s time to go into production. Throw in a live migration from an existing database setup and devops life just got a bit more interesting ...

 

So if you are in devops, then this webinar is for you!

 

Operations is not so much about specific technologies, but about the techniques and tools you use to deploy and manage them. Monitoring, managing schema changes and pushing them in production, performance optimizations, configurations, version upgrades, backups; these are all aspects to consider – preferably before going live.

 

Let us guide you through 9 key tips to consider before taking Galera Cluster into production. 

 

New Webinar: 9 DevOps Tips for Going in Production with Galera Cluster for MySQL - November 11th

 

DATE & TIME

 

Europe/MEA/APAC

Tuesday, November 11th at 09:00 GMT (UK) / 10:00 CET (Germany, France, Sweden)

Register Now

 

North America/LatAm

Tuesday, November 11th at 9:00 Pacific Time (US) / 12:00 Eastern Time (US)

Register Now

 

SPEAKER

Johan Andersson, CTO, Severalnines

 


Integrating ClusterControl with FreeIPA and Windows Active Directory for Authentication

 
October 17, 2014
By Severalnines

Integrating ClusterControl with a corporate LDAP directory is a common task for many IT organizations. In an earlier blog, we showed you how to integrate ClusterControl with OpenLDAP. In this post, we will show you how to integrate with FreeIPA and Windows Active Directory. 

 

How ClusterControl Performs LDAP Authentication

 

ClusterControl supports up to LDAPv3 protocol based on RFC2307. More details on this in the documentation.

 

When authenticating, ClusterControl will first bind to the directory tree server (LDAP Host) using the specified Login DN user and password, then it will check if the username you entered exists in the form of uid or cn of the User DN. If it exists, it will then use the username to bind against the LDAP server to check whether it has the configured group as in LDAP Group Name in ClusterControl. If it has, ClusterControl will then map the user to the appropriate ClusterControl role and grant access to the UI.

 

The following flowchart summarizes the workflow:

 

FreeIPA

 

FreeIPA is a Red Hat sponsored open source project which aims to provide an easily managed Identity, Policy and Audit (IPA) suite primarily targeted towards networks of Linux and Unix computers. It is easy to install/configure, and is an integrated security information management solution combining Linux (Fedora), 389 Directory Server, MIT Kerberos, NTP, DNS, Dogtag (Certificate System).

 


Database Automation - Private DBaaS for MySQL, MariaDB and MongoDB with ClusterControl

 
October 9, 2014
By Severalnines

Installing, configuring, deploying databases and performing repetitive administrative tasks are all part of a DBA’s or sysadmin’s job. This can get pretty repetitive and overwhelming if you are part of a centralized IT team, running multiple databases for your organization’s different departments, or a managed hosting provider responsible for setting up and operating databases for external clients. One way to get out of this ‘manual, repetitive task’ business is through a Database as a Service (DBaaS).

DBaaS is a way of delivering database functionality as a service to one or more consumers. A DBaaS platform would provide automated procedures for database deployment, monitoring, backups, recovery/repair, scaling, security/multi-tenancy, etc. This type of automation is especially useful where agility is needed, e.g. for systems that require elasticity by scaling out or scaling back at short notice, or for temporary deployments associated with dev/test/QA. Now that you’ve automated the repetitive stuff, you can start using your time and skills to optimize your schemas and configurations, help developers write better queries that scale, and work on system architecture or strategic database initiatives.

In this post, we’ll have a look at how enterprise companies or managed hosters can use ClusterControl to implement a DBaaS for MySQL, MariaDB and MongoDB. 

 

Multitenancy - Organizations, Users, Roles and Clusters 

 

Some basics first, let’s have a look at how ClusterControl handles multiple users and clusters. ClusterControl has an admin module through which an admin can create users, specify their roles and the organization each user belongs to. 

By default, ClusterControl provides three types of roles:

  • Super Admin - Sees all clusters/DBs that are registered with ClusterControl. The Super Admin can also create organizations and users. Only the Super Admin can transfer a cluster from one organization to another.
  • Admin - Belongs to a specific organization, and sees all clusters registered in that organization.
  • User - Belongs to a specific organization, and only sees the cluster(s) that she registered.

It is also possible to create custom roles with specific access control, for more fine-grained access to functionality. 

As a roundup, here is how the different entities relate to each other:

 

More information can be found in the ClusterControl User Guide.

 

Example Organizational Structure

 

Let’s assume we have an organization with separate departments, each having their own applications and databases. An IT department is responsible for running all the databases, but the Marketing and HR departments also have their own developers who need access to their respective databases.

Department

Description

Position

Access Control

Marketing

Business analytical system with MongoDB replica set

System Analyst

Full access to assigned cluster

IT

Staging cluster running on MariaDB Galera Cluster

System Administrator

Full access to all clusters

Human Resource

HR system running on standalone MariaDB 10.1

Application Developer

Limited access to assigned cluster (read-only)

 


ePayment Processing Data over multi-Datacenter MariaDB Cluster - Paytrail chooses ClusterControl

 
September 26, 2014
By Severalnines

Paytrail is a leading e-payment method provider from Finland, and is expanding globally. Established in 2007, Paytrail currently has over 4,000 business customers, works with over 350 partners and its solution is available in all SEPA (Single Euro Payments Area) countries.

 

Paytrail offers a new online shopping solution that allows consumers to use one login for all of their online purchases. And it provides everything that is needed for online shopping in addition to traditional payment methods (bank e-payments, credit and debit card payments, invoicing and installments). 

 

This new case study describes how Paytrail came to work with Severalnines to achieve a fault-tolerant database cluster across two data centers.


paytrail-logo-200x200.png
"To go from initial evaluation to full production within 4 months would not have been possible without Severalnines”, says Niko Lehtonen. “Their support team was extremely helpful, and we got timely assistance when needed and that allowed us to keep the project moving."

 

Maintaining Uptime and Supporting Growth

 

Paytrail have two main concerns when it comes to their systems: maintaining uptime, at all times, and growth. They cannot afford any downtime, as that brings down the merchants who use their solution, which in turn prevents the merchants’ customers to shop online. The company recently made the Deloitte Fast 500 EMEA 2013 list, a ranking of the 500 fastest growing technology companies in EMEA.


mysqldump or Percona XtraBackup? Backup Strategies for MySQL Galera Cluster

 
September 25, 2014
By Severalnines

Coming up with a backup strategy that does not affect database performance or lock your tables can be tricky. How do you backup your production database cluster without affecting your applications? Should you use mysqldump or Percona Xtrabackup? When should you use incremental backups? Where do you store the backups? In this blog post, we will cover some of the common backup methods for Galera Cluster for MySQL/MariaDB, and how you can get the most out of these. 

 

Backup Method

 

There are various ways to backup your Galera Cluster data:

  • xtrabackup (full physical backup)
  • xtrabackup (incremental physical backup)
  • mysqldump (logical backup)
  • binary logging 
  • replication slave

 

Xtrabackup (full backup)

Xtrabackup is an open-source MySQL hot backup utility from Percona. It is a combination of xtrabackup (built in C) and innobackupex (built on Perl) and can back up data from InnoDB, XtraDB and MyISAM tables. 

Xtrabackup does not lock your database during the backup process. For large databases (100+ GB), it provides much better restoration time as compared to mysqldump. The restoration process involves preparing MySQL data from the backup files before replacing or switching it with the current data directory on the target node. However, the restoration process is not very straightforward. We have covered some backup best practices and an example of how to restore with xtrabackup in this blog post.

ClusterControl allows you to schedule backups using Xtrabackup and mysqldump. It can store the backup files locally on the node where the backup is taken, or the backup files can also be streamed to the controller node and compressed on-the-fly.

 


PagerDuty Incident Alerting for ClusterControl

 
September 22, 2014
By Severalnines

Need to add phone and SMS alerting to ClusterControl? ClusterControl 1.2.8 introduces support for PagerDuty, an alerting service for Ops teams to schedule on-calls and add phone and SMS notifications to IT tools. By integrating PagerDuty with ClusterControl, you can start receiving phone, SMS and email notifications for all important database events as monitored by ClusterControl. Alerts go directly to the right person who can solve the issue.

This integration is possible thanks to a new plugin interface, that takes ClusterControl alarms in JSON format and outputs to an external system via plugins. Plugins can be either scripts or executable binaries.

 

We have built a few example plugins utilizing this plugin interface, available from our Github repository:

  • pagerduty.py: This plugin forwards the alarm raise/close events to the PagerDuty system
  • syslog.py: This plugin writes the new alarms instantly to the syslog

 

Plug-in configuration options

 

Let’s have a quick look at how the plugin works, but feel free to go directly to the PagerDuty setup instructions.

A plugins directory can be set through the CMON configuration file (config file entry is plugin_dir), or in the cmon_configuration table found in CMON DB (PLUGIN_DIR key).

 

When no value is set, the /var/cmon/plugins path will be used. The controller tries to execute the 'executable' scripts/binaries from the directory, while the non-executable files are skipped.

 

How does the plugin execute?

 

Whenever an alarm is raised, CMON feeds the alarm event to its standard input (stdin) and all executable scripts or binaries (plugins) under plugin_dir path will be executed. A JSON message will be written to those standard inputs. The expected JSON message syntax is as follows:

{
    // currently only the "alarm" is supported
    "type": "alarm",
    // whether it is a new alarm, or an update for an old alarm or an alarm removal
    "action": "new|update|remove",
    // a JSON map which contains the alarm details
    "alarm": { },
    // the hostname of the cmon controller (could be used to determine the web-ui url)
    "cmon_hostname" : "ip-address/or-host-name"
}

 


Tweaking MySQL Galera Cluster to handle large databases - open_files_limit

 
September 18, 2014
By Severalnines

Galera Cluster is a popular choice for achieving high availability using synchronous replication. Though if you are planning to run huge sites with many DB objects (tables), a few tweaks are necessary. 

 

Yes, you might have been successful in loading your 1000s of databases and 1000s of tables, but what happens if you have a node failure and Galera recovery fails?

 

In this blog post we will show you how to determine one common error related to the open_files_limit that MySQL imposes, and also to spot another potential pitfall.

 

Open_files_limit

 

If you are using wsrep_sst_method=xtrabackup or wsrep_sst_method=xtrabackup-v2 then you will find a log file in the data directory of the donor node. This log file is called innobackup.backup.log.

140912 19:10:15  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
 
innobackupex: Using mysql server version 5.6.16
...
innobackupex: Created backup directory /tmp
...
140912 19:10:16  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/etc/mysql/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp --tmpdir=/tmp --extra-lsndir='/tmp' --stream=tar
innobackupex: Waiting for ibbackup (pid=4274) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended_2'
...
xtrabackup: open files limit requested 65535, set to 65535
...
>> log scanned up to (2145319596)
>> log scanned up to (2145319596)
2014-09-12 19:24:09 7f5d2080a740  InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file ./db_2760/t_0.ibd

 

In this case the DONOR failed because of the error:

2014-09-12 19:24:09 7f5d2080a740 InnoDB: Operating system error number 24 in a file operation

At this point you have to increase the open_files_limit in your my.cnf file. Unfortunately, open_files_limit is not a global variable, and you must restart the MySQL server in order for the change to be effective.

 


ClusterControl 1.2.8 Released

 
September 17, 2014
By Severalnines

The Severalnines team is pleased to announce the release of ClusterControl 1.2.8. This release contains key new features along with performance improvements and bug fixes. We have outlined some of the key new features below. 

 

Highlights of ClusterControl 1.2.8 include:

  • YUM/APT repositories for ClusterControl
  • Deployment and scaling of single-node MySQL, MariaDB and MongoDB
  • Alerts and incident tracking with PagerDuty 
  • Unified Event Viewer
  • New flexible alarms/email notification system
  • Audit logging - Administrator activity tracking
  • Global MySQL User Management
  • New default dashboards for MySQL/MariaDB
  • Puppet Module for ClusterControl 
  • Improved LDAP support for Active Directory and FreeIPA

 

For additional details about the release:

 

YUM/APT repositories for ClusterControl: We are excited to announce the availability of YUM/APT repos for ClusterControl, making new releases of ClusterControl easily accessible using YUM or APT package managers. The repo is found at http://repo.severalnines.com. For step by step instructions on how to use the repo, see this blog post.

 

Deployment of single-node MySQL, MariaDB and MongoDB: It is now possible to deploy single database nodes from ClusterControl. Once a single node is deployed, it can then be managed from the ClusterControl interface. Single nodes can be scaled into clusters with a single click of a button. MySQL and MariaDB are turned into Galera Clusters, and MongoDB into a ReplicaSet. So you can now start small, and easily add scalability/HA when the business or your development/test environment requires it.

 

ClusterControl integration with PagerDuty - Alert and Incident Tracking: PagerDuty is an operations performance management platform used by thousands of Ops teams. You can now integrate ClusterControl’s alerting with your PagerDuty account. By integrating with PagerDuty, you can be alerted when alarms are triggered within your ClusterControl environment. 

For detailed step by step instructions see this blog post.


Webinar Replay & Slides: Performance Tuning of HAProxy for Database Load Balancing

 
September 11, 2014
By Severalnines

 

Thanks to everyone who attended and participated in this week’s webinar on ‘Performance Tuning of HAProxy for Database Load Balancing’. And special thanks to our guest speaker, Baptiste Assmann of HAProxy Technologies. 

 

If you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

 

Watch the replay of this webinar to learn about what HAProxy can tell you about your application and database instances. And understand the difference between short-lived connections and persistent connections, and how they affect the performance and availability of your applications. 

 

Watch the replay

 

Read the slides

 

This webinar replay covers the performance tuning basics for HAProxy and explains how to take advantage of some of the new features in 1.5, released in June 2014 after 4 years of development work.

 

Full Agenda

  • New features in HAProxy 1.5
  • What can HAProxy tell you about your application and your database
  • OS/Network performance tuning for databases with short living connections
  • Handling persistent connections
  • Dynamic re-configuration
  • HAProxy multi-process: advantages and limitations
  • HAProxy active/active failover setups
  • Security considerations

 


Search
Keep in touch
Signup for our newsletter:
Name:
*Email:

Alex's Blog

Quickstart $ git clone https://github.com/alyu/docker.git $ cd docker/percona-xtradb-5.6/centos $ ./build.sh $ ./start-servers.sh 3 $ ./bootstrap-cluster.sh $ ssh root@172.17.0.2 (root123) $ mysql -...
33 weeks 3 days ago

Johan's Blog

FOREIGN KEYs in MySQL Cluster is a big step forward. It is now possible to run enterprise software with NDB Cluster as the storage backend. Over the years, the lack of FOREIGN KEYs have been one of...
1 year 17 weeks ago