As your needs change, and you start managing more database instances or larger centralized backups with ClusterControl, you might find that your controller host is over-utilized. Or you might need to migrate your infrastructure to the cloud. If this is the case, you can migrate to another instance.

In this blog post, we’ll show you how to migrate ClusterControl to another server. Here is our architecture diagram:

We would like to migrate an existing ClusterControl instance to another server with bigger disk space and RAM. The ClusterControl node currently monitors a two-node Galera cluster, stores centralized database backups under /root/backups directory, runs garbd (Galera arbitrator daemon) as well as an HAProxy instance to load balance connections to the Galera nodes.

ClusterControl Migration

MySQL Replication is very simple to set up. In this post I’ll discuss its importance and five handy tools for monitoring and managing MySQL replication.

What is MySQL Replication? It’s the process of copying the (real-time events) data from one master instance to another slave instance and maintaining the redundant consistent data in a different machine. This enables a distributed database system that shares the same level of information.

In MySQL the replication works based on the three threads as shown below.

1) I/O thread on the slave server:  To start on receiving replication events, an I/O thread starts on the slave server and connects to the master server.

2) Master connection handler thread:  As a connection handier, master starts a thread whenever a replication slave connects to a master. The master server sends the events from its binary log file to the slave I/O thread, notifying slave …

I will be presenting group replication on March 14, 2015 in the FOSSASIA conference. It is a 25 minute session and you can find the schedule here.  Here is an abstract of the talk:

MySQL Replication provides a solution for High Availability and Read
Scale-Out. Replication ensures that data written on one MySQL server
is made available on other MySQL servers at runtime in a fast,
consistent and fault tolerant manner with minimal impact to the
overall performance of the server.

Traditionally, MySQL Replication supports a single master and many
slaves, and it is either asynchronous or semi-synchronous. Recently, a
preview of a new replication plugin for MySQL was released and this is
named MySQL Group Replication. This plugin provides multi-master
update everywhere …

What is JSON
JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL
It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don't solve the really tough …

Here is one more additional documentation effort in the MySQL Cluster 7.4
source code. This describes a fairly detailed view of what is performed in
the various restart phases of MySQL Cluster and in particular for node
restarts.

In MySQL Cluster the restart is processed in phases, the restart of a node
is driven by a set of phases. In addition a node restart is also synchronised
with already started nodes and other nodes that are starting up in parallel
with our node. This comment will describe the various phases used.

Data node process startup
The first step in starting a node is to create the data node run-time
environment. The data node process is normally running with an angel process,
this angel process ensures that the data node is automatically restarted in
cases of failures. So the only reason to run the data node again is after an …

MariaDB 5.5.42 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.42/

This is a maintenance release, and so there were not too many changes at all and only 3 changes (enhancements) I felt noteworthy:

• The new version of the Audit Plugin is 1.2 is included with the following new features:
  • In the audit log passwords are now masked, i.e. the password characters are replaced with asterisks.
  • It’s now possible to filter logging to include only DDL (CREATE, ALTER, etc.) or DML (INSERT, UPDATE, etc.) statements.
  • For more information please refer to the About the MariaDB Audit Plugin page. The plugin is …

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

Like every month, we have created new content and tools for you; here is a summary of what we’ve published. Please do check it out and let us know if you have any comments or feedback.

New Live Technical Webinar

Introducing the New ClusterControl with Live Demo on 24th of March

Johan Andersson, CTO at Severalnines will walk us through the latest edition of ClusterControl and its new features in the form of a live demo. Johan will go over the latest features in ClusterControl including the recently announced support for Postgres. …

MariaDB 10.0.17 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.17/

This is the eighth GA release of MariaDB 10.0, and 18th overall release of MariaDB 10.0.

For the most part, there are not a whole lot of new changes to report for this release, but there is a new version of the Audit Plugin and many other engines have been updated as well.

Here are the main items of note:

1. The new version of the Audit Plugin is 1.2 is included with the following new features:
   • In the audit log passwords are now masked, i.e. the password characters are replaced with asterisks.
   • It’s now possible to filter logging to include only DDL (CREATE, ALTER, etc.) or DML (INSERT, UPDATE, etc.) statements.
   • For more information …

Last week I had to update several million rows in a single MySQL table. Rather that executing the update directly in the database, I decided to use common_schema's split() function.

There are two reasons I chose to use common_schema for this task:

Chunking

Chunking breaks a single update statement into multiple statements, each updating at most a certain specified number of rows. The default chunk size is 1000, and I changed it to 2000 by setting the size parameter.

Throttling

Full text is a critical point when it comes to mysql. It used to have that feature in MyISAM but that’s not really maintained anymore nor it is advised to use unless you have a very specific use case in which it might make sense. There are 3rd party solution which takes the problem away (Lucene, Sphinx, Solr, ElasticSearch) but all bring extra complexity to your setup which has its own cost. So do you need to give up on fulltext search if you’re using MySQL + InnoDB? No! Definitely not.

Alternatives for full-text search in MySQL

I have an live database with 9000+ products with category, brand and short description which is perfect to test my searches on. I’m going to run the queries many times and use profile information to collect more granular and accurate timing information.

LIKE

Well… It’s not a real alternative but just for a sec see how well it behaves.

SELECT SQL_NO_CACHE b.name, …