The 10--mariadb Galera Cluster of the first knowledge of MariaDB

First, Background introduction

Either Binlog or Gtid, the essence of which is in the form of I/o_thread and Sql_thread synchronization, because the replication delay can not avoid the criticism, based on the MARIADB introduced Galera cluster to solve the problem.

Second, Galera cluster introduction

Galera cluster and traditional replication methods, not through I/o_thread and Sql_thread synchronization, but at the bottom through the Wsrep to achieve file system level synchronization, can be achieved in almost real-time synchronization, and the MySQL on it is ignorant of this

This requires that MySQL be able to invoke Wsrep provided by the API to complete, before the Mariadb10.1 version, support Galera cluster version is released separately from MARIADB, its version name becomes Mariadb-galera, Mariadb10.1 later versions of MARIADB Galera Cluste are no longer released separately, but in the form of galera-25.3.12-2.el7.x86_64 packages

Iii. purpose of the experiment

The experimental operating system is CentOS7.4, the database version is MariaDB10.2.14, verify the 3 node through Galera cluster to achieve data synchronization. It should be stated that:

1.Galera cluster minimum requirement of 3 nodes above, recommended is more than 3 odd, 2 nodes although can be deployed, but cannot avoid the appearance of brain fissure

2. Although Galera cluster no longer needs to be synchronized in the form of Binlog, it is recommended that this feature be turned on in the configuration file because later, if a new node is to be added, the old node transmits the data to the new node through the whole amount of SST transmission, which is likely to drag down the performance. So let the new node first through the Binlog to complete the synchronization after the addition of Galera cluster is a good choice

3. Galera cluster used in the Mariadb10.1 version of the library file libgalera_smm.so to find its location through RPM-QL galera-25.3.12-2.el7.x86_64

Four, the Operation procedure

1.NODE3 Node host operation

(1) Installing MARIADB 10.2.14

(2) Edit the configuration file and start the service

[Email protected] ~]# VIM/ETC/MY.CNF.D/SERVER.CNF

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=on

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Relay_log_purge=off

Slow_query_log=on

server-id=10

Innodb_file_per_table=on

Binlog_format=row

Log_bin=mysql-binlog

Log_slave_updates=on

[Galera]

# Mandatory Settings

Wsrep_on=on

Wsrep_cluster_name=ark

Wsrep_provider=/lib64/galera/libgalera_smm.so

wsrep_cluster_address=gcomm://172.16.10.30,172.16.10.40,172.16.10.50

Binlog_format=row

Default_storage_engine=innodb

innodb_autoinc_lock_mode=2

Innodb_doublewrite=1

[email protected] ~]# Galera_new_cluster #第一次启用时在任意节点使用

(3) Send configuration files to Node4 and NODE5

[Email protected] ~]# scp-r/etc/my.cnf.d/server.cnf [email protected]:/etc/my.cnf.d/

[Email protected]ost3 ~]# scp-r/etc/my.cnf.d/server.cnf [email protected]:/etc/my.cnf.d/

2.NODE4 node operation

(1) Change the server_id in the configuration file to 20

(2) Start MySQL service

You can see in the log that the Galera cluster SST is used by default with rsync transfer

(3) Create an account for replication in NODE4

[[email protected] ~]# MySQL

MariaDB [(None)]> grant replication Slave on * * to ' bak ' @ ' 172.16.10.% ' identified by ' bakpass ';

MariaDB [(None)]> flush privileges;

(4) Feel free to create data and verify synchronization on Node3

MariaDB [(None)]> CREATE DATABASE ' Hellodb ';

MariaDB [(None)]> CREATE TABLE ' Students ' (

' Stuid ' int (ten) unsigned not NULL auto_increment,

' Name ' varchar () not NULL,

"Age" tinyint (3) unsigned not NULL,

' Gender ' enum (' F ', ' M ') not NULL,

' ClassID ' tinyint (3) unsigned DEFAULT NULL,

' Teacherid ' int (ten) unsigned DEFAULT NULL,

-PRIMARY KEY (' Stuid ')

);

MariaDB [(None)]> insert into hellodb.students (name,age) VALUES (' Qiaofeng ', 40);

MariaDB [(None)]> insert into hellodb.students (name,age) VALUES (' Duanyu ', 21);

MariaDB [(None)]> insert into hellodb.students (name,age) VALUES (' Xuzhu ', 24);

(5) Back up the current database and send it to NODE5

[Email protected] ~]# mysqldump-uroot--databases hellodb--master-data=2--single-transaction--quick >/tmp/hellod B.sql

[Email protected] ~]# scp-r/tmp/hellodb.sql [email protected]:/tmp/

(6) Re-insert data, simulate backup and new data

MariaDB [(None)]> insert into hellodb.students (name,age) VALUES (' Xuanci ', ' 66 ');

3.NODE5 operation

(1) Change the server_id in the configuration file to 30 and log off the [Galera] segment (The goal is to synchronize by Binlog first)

(2) Start MySQL service

(3) Check the Binlog and position of NODE4 during backup

(4) Restore the database and verify

MariaDB [hellodb]> Source/tmp/hellodb.sql

MariaDB [hellodb]> Change Master to master_host= ' 172.16.10.40 ', master_user= ' bak ', master_password= ' Bakpass ', Master_port=3306,master_log_file= ' mysql-binlog.000004 ', master_log_pos=2061,master_connect_retry=10;

MariaDB [hellodb]> start slave;

MariaDB [hellodb]> SELECT * from students;

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

| Stuid | Name | Age | Gender | ClassID | Teacherid |

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

| 3 |  Qiaofeng | 40 |    F |      NULL | NULL |

| 5 |  Duanyu | 21 |    F |      NULL | NULL |

| 7 |  Xuzhu | 24 |    F |      NULL | NULL |

| 9 |  Xuanci | 66 |    F |      NULL | NULL |

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

(5) Stop master-slave replication and MySQL services

MariaDB [hellodb]> start slave;

[Email protected] ~]# Systemctl stop Mariadb.service

(6) Cancel [Galera] paragraph logout and restart service

Then add data to any node 3 nodes can be synchronized, this operation is complete

Additional notes:

1.Galera cluster node After stop service, if the data is updated later, only need to restart the MySQL service, the data will be automatically synchronized

2. Later, if a new node is added, you can add your IP address to the wsrep_cluster_address variable in the configuration file of the new node and then start the database, so that the wsrep_cluster_ in the previous node configuration file The address variable can be synchronized even without the IP address of the new node, it is recommended to restart the service after adding the new node IP address to the old node wsrep_cluster_address variable.

Questions that exist:

Import a database at any galera cluster node, either through the command line through MySQL < xxx or using the SQL statement source XXX, only the current node has data, the other nodes only create the database and the table in the library, but there is no data in the table, No synchronization after restarting MySQL service for unknown reason

The 10--mariadb Galera Cluster of the first knowledge of MariaDB