User:Yiyiyongfu/sandbox

Introduction

Galera Cluster is a database cluster which is suitable in diverse database server, such as MySQL, MariaDB and so on.  Multi-master replication system and Synchronous Replication are used to update data of nodes in the database. Automatic nodes joining and membership control are essential features of Galera Cluster. When Galera Cluster is under operation, users can have access to nodes freely and compose it without interruption. 

Galera Cluster uses Synchronous replication systems to update every nodes in a single transaction through write-set replication. Galera Cluster has three main components:

• Database Management System (DBMS): use MySQL, MariaDB or Percona XtraDB as the databse server to operate self nodes.
• Write-Set Replication API: has wsrep hooks, dlopen() and Galera Replication Plugin
• Group Communication plugins: the plugins that make the write-set replication in proper working order.

Wsrep API: It is a connection link between databases. When changes appears in a database, same changes occur in other database as the result of Wsrep API. When changes happen in one database, wsrep hooks interpret the changes to write-set. Then Galera Replication plugin process the interpret and copy the data of nodes to other databases. To record each change of the state of the database, Global Transaction ID is used.

Galera Replication Plugin: It consists of Certification Layer, Replication Layer and Group Communication Framework.

Multi-master Replication

Synchronous replication systems

Features

Galera Cluster upgrades database server such as MySQL to reach an excellent performance. As it uses the multi-master system, when any changes are updated to any database, other databases will also have the same change simultaneously.

• High Reliability: when nodes crush, there is no data loss.
• High Accessibly: users can add and delete nodes at any time, even under the operation of the system.
• Excellent consistency of information: when the state of one database changed, all the state changed simultaneously.
• Automatic Node Provisioning: System automatically back up information in databases.

Installing

Galera Cluster will need you to prepare some additional software and configurations as the fundamental of itself. It supports any unix-like systems like OS X. After you finish installing the software on your server you would also need to configure the server as a node. It needs at least three nodes.

Installing the Galera Cluster for MySQL

You will also need to do a lot of steps to prepare for the installation like disabling SELinux and configuring the firewall.

Moreover you need:

• Enabling the Codership repository
• Enabling the apt Repository
• Enabling the yum Repository
• Enabling the zypper Repository

There are two packages which are wsrep and Galera Replication Plugin that you need to install for Galera Cluster.

For Red Hat, Fedora and CentOS distributions:

# yum install galera-3 \ 

                    mysql-wsrep-5.6

Galera Cluster has been installed on your machine by these two lines of code. 

Installing the Galera Cluster for MariaDB

MariaDB Galera Cluster is the MariaDB implementation of Galera Cluster for MySQL.

You also need to:

• Enabling the MariaDB Repository
• Enabling the apt Repository
• Enabling the yum Repository

Three packages involved in the installation of MariaDB Galera Cluster:

• MariaDB database client
• MariaDB database server
• Galera Replication Plugin

For Red Hat distributions:

$ yum install MariaDB-client \

                MariaDB-Galera-server \

                Galera

System configuration

After you installed this Galera Cluster you had to make your database as a node in your cluster. Editing the MySQL configuration file for this purpose.

• Configuring Database Server
• Configuring Swap Space

Using Galera Cluster

As we have finished installing the Galera Cluster and some configurations, we can introduce something about application for Galera Cluster. You will know how to take nodes into cluster, how to recover failed nodes, how to back up data from cluster and how to make the communications safe.

Nodes provision

Galera needs all the nodes to be synchronized with the cluster. So it is really important to join nodes to the cluster.

There are two options available to determine the state transfer donor:

• Automatic
• Manual

Also it comes to the topic of state snapshot transfers. We can divide all the methods to two facets:

• Logical State Snapshots(interface through the server and client)
• Physical State Snapshots(copy the data files directly from node to node)

Recovering the Primary Component

What is the primary component? If one of the nodes fails, the cluster may be divided to several parts. Then only one of the components can still perform normally and modify the database state. This is the primary component. The cluster will recover the primary component if there is an outage.

What if we need to modify the saved primary component state? A node will give itself a unique ID to identify itself from others after a sudden shutdown. For instance, if you want three nodes to join together as a new primary component you will need to provide unique ID for them respectively. And the file record these values is gwstate.dat.

Resetting the number of nodes

Nodes sometimes would be not the primary component any more. At this time all nodes would return an error to all queries. You can check this by using the wsrep_cluster_status.

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';

If none of the nodes consider itself as a primary component, you need to reset the quorum. There are two ways to do this:

• Automatic Bootstrap
• Manual Bootstrap

As for the manual bootstrap, we will bootstraps the primary component to the most current node. First to shut down the cluster and then start it up and make the most current node at the beginning.

# Shut down
service mysql stop
systemctl stop mysql
# Start the most the current node
service mysql start --wsrep-new-cluster
systemctl start mysql --wsrep-new-cluster
# Start all nodes
service mysql start
systemctl start mysql

Upgrading Galera Cluster

You will need a better cluster sometimes and you need to upgrade the Galera Cluster.

• Rolling Upgrade
• Bulk Upgrade
• Provider Upgrade

When you use rolling upgrades, the cluster would not be interrupted but the speed is very low. On the contrary, if you don't want to wait for so much time and accept all the clusters down at the same time you can choose Bulk Upgrade but there must be an outage. There is still another choice that not taking so much time called provider upgrade. You could only upgrade the Galera Cluster provider in this way.

Scriptable state snapshot transfers

There is some general parameters for all state transfer scripts to tell you the condition of the nodes and clusters.

• -- -- role(refer a node to be sender or receiver)
• -- -- address(return IP address)
• -- -- auth(return authentication)
• -- -- datadir(path to the data directory)
• -- -- defaults-file(the script is given the path to the my.cnf configuration file)

Differences with MySQL

Although Galera Cluster is really like MySQL and some other database systems,  there are still some fundamental differences.

Differences in Server

Not like MySQL, Galera Cluster does not support so many database systems. There are also many differences in the way it handles binary logs and character sets. Also it does not use the binlog-do-db options and character_set_server with UTF-16, UTF-32 or UCS-2.

Differences in Table Configurations

The same problems also happen with respect to storage engine support, certain queries and the query cache.

INSERT INTO mysql.user (Host, User, Password) 
    VALUES ('localhost', 'stranger', 'password');

For example the above code cannot realize its purpose that replicate the changes to the database to the cluster. You also cannot check a log directly. You must forward the logs to a file for Galera Cluster.

log_output = FILE

Differences in Transactions

The differences also exist in the way they process transactions. For example, the standard MySQL server supports the distributed transaction processing while it does not make sense with Galera Cluster.

Transaction commit is also different. Galera uses optimistic concurrency control, which can issue a commit aborting at the stage. When this happens, Galera will return a deadlock error.

code (Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)

Migrating from MySQL to

If your local server already has versions of MySQL, MariaDB or some others, you need to take some additional steps before migration. Before upgrading, you also need to do some basic steps to use mysql_upgrade.

First you need to start the database server without replication.

service mysql start --wsrep_on=OFF
mysql_upgrade
systemctl stop mysql

After finishing upgrading the table you need to initialize the cluster as well as stoping the mysqld process. The Galera Replication Plugin can be used only if there is a transactional storage engine .

Infrastructure Preparation

Before migration, you first need to think about the infrastructure.

• Launch three new servers at least
• Install Galera on each new server
• Configure the database server

• Start the cluster after finishing above steps

To check if it is working properly, use the following code:

SHOW STATUS LIKE 'wsrep_cluster_size';

After all these steps over, Galera Cluster is now working well as well as your MySQL. However it does not have data for it is now unused.

Data Migration

You need to transfer the data from your existing structure to the Galera Cluster. There are several steps:

• Stop the master server
• Load the migration.sql file to one of the new nodes.

• Transfer the data from the master server to the cluster nodes
• Restart the application servers

  mysqldump -u root -p --skip-create-options --all-databases > migration.sql     # Stop the server
  scp migration.sql user@galera-node-IP   # Load
  mysql -u root -p < migration.sql    # Tranfer

  You also need to transfer the database from MySQL to Galera Cluster .