Shulou(Shulou.com)06/01 Report--

Official documents:

Http://galeracluster.com/documentation-webpages/index.html 1. The working principle of Galera Cluster

The main concern is data consistency. Transactions can be applied to each node or not all of them. So, as long as they are configured correctly, the database remains synchronized.

Different from traditional MySQL replication, Galera replication plug-in can solve many problems, including multi-master write conflicts, replication lag and master-slave non-synchronization.

In a typical Galera cluster instance, the application can write to any node in the cluster and then apply the transaction commit (RBR event) to all servers through authentication-based replication.

Using group communication and transaction sorting techniques, authentication-based replication is another way to synchronize database replication

II. Galera cluster installation

Description: a Galera cluster requires at least three nodes of server hardware.

Node-12:10.71.11.12node-13:10.71.11.13node-14:10.71.11.14

Operating system:

Centos7

Kernel version:

3.10.0-693.21.1.el7.x86_64 installation begins

Description: perform the following steps for each node in the cluster. The document takes the node-12 configuration as an example

1. Edit / etc/hosts file, configure nodes to parse each other

[root@node-12 ~] # cat / etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 localhost localhost.localdomain localhost6 localhost6.localdomain610.71.11.12 node-1210.71.11.13 node-1310.71.11.14 node-14

two。 Turn off the node firewall

[root@node-12 ~] # cat / etc/sysconfig/selinux | grep di# disabled-No SELinux policy is loaded.SELINUX=disabled# minimum-Modification of targeted policy. Only selected processes are protected.

3. Configure Galera cluster yum source

[root@node-12 ~] # cat / etc/yum.repos.d/galera.repo [galera] name = Galerabaseurl = http://releases.galeracluster.com/galera-3/centos/7/x86_64gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.comgpgcheck = 1 [mysql-wsrep] name = MySQL-wsrepbaseurl = http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.comgpgcheck = 1

4. Update yum source cache

Yum makecache

5. Installation service

Yum install mysql-wsrep-shared-5.6

Note: if mysql was previously installed on the node, executing the above command may cause the installation package conflict to report an error

6. Take node-12 as an example, edit / etc/my.cnf, and add the following configuration

[root@node-12 ~] # cat / etc/ my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlbinlog_format=rowbind-address=10.71.11.12default_storage_engine=innodbinnodb_autoinc_lock_mode=2innodb_flush_log_at_trx_commit=0innodb_buffer_pool_size=122Mwsrep_provider=/usr/lib64/galera-3/libgalera_smm.sowsrep_provider_options= "gcache.size=300M Gcache.page_size=300M "wsrep_sst_method=rsyncwsrep_cluster_name=MyClusterwsrep_cluster_address=" gcomm://10.71.11.12,10.71.11.13,10.71.11.14 "wsrep_node_name=node-12wsrep_node_address=" 10.71.11.12 "[mysql_safe] log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Note: other node-13 and node-14 are configured according to step 1-6, and / etc/my.cnf needs to be modified corresponding to each node.

Description of system memory configuration

The memory requirements of Galera Cluster are difficult to predict accurately. The specific amount of memory it uses can vary widely, depending on the load received by a given node. If Galera Cluster tries to use more memory than the node, the mysqld instance crashes.

The way to protect nodes from system crashes is to ensure that the service has sufficient swap space partitions or swap files.

Check the available swap space of the system

If the system swap partition does not meet the requirements of the environment, you can configure the swap partition by doing the following

Description: each node in the cluster needs to set the relevant swap partition.

1. Create a 2G swap empty file in the root directory according to the requirement

[root@node-11] # fallocate-l 2048M / swapfile

Or execute

[root@node-11 ~] # dd if=/dev/zero of=/swapfile bs=1M count=2048

Set the swap swap partition size

two。 Set swap file permissions

[root@node-11] # chown 600 / swapfilell / swapfile-rw-r--r-- 1,600 root 2147483648 Apr 17 09:13 / swapfile

3. Format swapfile

[root@node-11] # mkswap / swapfileSetting up swapspace version 1, size = 2097148 KiBno label, UUID=6341a320-1804-4fe1-8c96-7c22fe270eeb

4. Activate swapfile

[root@node-11] # swapon / swapfileswapon: / swapfile: insecure permissions 0644, 0600 suggested.swapon: / swapfile: insecure file owner 600,0 (root) suggested. Edit / etc/fstab and add the following configuration / swapfile none swap defaults 0 0 at the bottom of the file

6. Verify that the swap swap partition profile is successful

[root@node-11] # swapon-- summaryFilename Type Size Used Priority/swapfile file 2097148 0-1

Note: since other environments have been deployed in the node environment where the Galera cluster is deployed, here are the steps to delete the swap partition

[root@node-11 ~] # swapon-- summaryFilename Type Size Used Priority/openstack/swap.img file 4194300 00 / swapfile file 2097148 0-1 [root@node-11 ~] # swapoff / openstack/swap.img # # first stop the swap partition [root@node-11 ~] # rm-rf / openstack/swap.img # # Delete swap partition file [root@node-11 ~] # swapon-- summaryFilename Type Size Used Priority/swapfile file 2097148 0-1

Start the Galera cluster

Description: after completing the installation and configuration of Galera Cluster, you need to use-- wsrep-new-cluster to select a node to start mysqld. This will initialize the core components of the cluster. Each node started after that will connect to the component and begin to replicate

1. Start the database service on node-12 using the parameter-- wsrep-new-cluster

[root@node-12 mysql] # service mysql start-- wsrep-new-cluster

Database initialization error solution, delete logfile under / var/lib/mysq/ directory

Note the warning: the-- wsrep-new-cluster parameter is used only when initializing the main component. Do not execute this command if you want the node to connect to an existing cluster.

two。 After the node-12 node starts the database service, log in to the mysql database database and judge whether the startup is successful or not by the wsrep_cluster_size parameter.

SHOW STATUS LIKE 'wsrep_cluster_size'

Note: the parameter wsrep_cluster_size represents the current number of nodes online in the galera cluster.

3. To add nodes to the galera cluster, execute the following command on node-12 and node-13 to start the database service

[root@node-13 mysql] # service mysql startStarting MySQL.Logging to'/ var/lib/mysql/node-13.err'. SUCCESS! [root@node-14 yum.repos.d] # service mysql startStarting MySQL.Logging to'/ var/lib/mysql/node-14.err'. SUCCESS!

Then log in to the mysql database on node-13 or node-14 to view the value of wsrep_cluster_size

[root@node-14 yum.repos.d] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > SHOW STATUS LIKE 'wsrep_cluster_size'

So far, the galera cluster has been successfully built.

4. Galera cluster replication test

Description: to test whether the Galera cluster is working properly

1. On the database client, verify that all nodes are connected to each other

[root@node-12] # mysql-urootWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show status like 'wsrep_%'

Parameter description:

Synced in the parameter wsrep_local_state_comment indicates that the node is connected to the cluster and is running normally.

The value 3 of wsrep_cluster_size indicates that three nodes in the galera cluster are online.

On in wsrep_ready indicates that this node is connected to the cluster and can handle transactions.

two。 Create a table on node-12 and insert data

[root@node-12 mysql] # mysqlWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 5Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'c'to clear the current input statement.mysql > CREATE DATABASE galeratest;Query OK, 1 row affected (0.01 sec) mysql > USE galeratest;Database changedmysql > CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT,msg TEXT) ENGINE=InnoDB;Query OK, 0 rows affected (0.00 sec) mysql > INSERT INTO test_table (msg) VALUES ("Hello my dear cluster."); Query OK, 1 row affected (0.00 sec) mysql > INSERT INTO test_table (msg) VALUES ("Hello, again, cluster dear.") Query OK, 1 row affected (0.00 sec) View on node-13 or ndoe-14 that three nodes can synchronize data operations on node-12 [root@node-13 mysql] # mysqlWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | galeratest |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > use galeratest

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > SELECT * FROM test_table

+-+-

| | id | msg |

+-+-

| 1 | Hello my dear cluster. | |

| 4 | Hello, again, cluster dear. | |

+-+-

2 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.