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

First, background introduction

Whether using binlog or GTID, its essence is synchronization in the form of I/O_thread and sql_thread, which has been criticized because it can not avoid replication delay. Based on the above MariaDB, Galera Cluster is introduced to solve this problem.

II. Introduction to Galera Cluster

Different from the traditional replication method, Galera Cluster does not synchronize through I/O_thread and sql_thread, but achieves file system-level synchronization through wsrep at a lower level, which can achieve almost real-time synchronization, but the MySQL on it knows nothing about it.

This requires MySQL to be able to call the API provided by wsrep. In the previous version of Mariadb10.1, the version that supports Galera Cluster is released separately from Mariadb, and the name of the version is that in the later version of Mariadb-Galera,Mariadb10.1, MariaDB Galera Cluste is no longer distributed separately, but in the form of galera-25.3.12-2.el7.x86_64 package.

III. Purpose of the experiment

The operating system of this experiment is CentOS7.4, and the version of the database is MariaDB10.2.14. It is verified that data synchronization is realized through Galera Cluster under 3 nodes. What needs to be explained is:

1.Galera Cluster requires at least 3 nodes. It is recommended to have an odd number greater than 3. Although 2 nodes can be deployed, brain fissure can not be avoided.

two。 Although Galera Cluster no longer needs to synchronize in the form of binlog, it is recommended to enable this function in the configuration file, because if a new node is to join later, the old node transmits data to the new node through SST, which is likely to drag down the performance, so it is a good choice for the new node to complete synchronization through binlog first and then join Galera Cluster.

3. Libgalera_smm.so, a library file used to implement Galera Cluster in later versions of Mariadb10.1, finds its location through rpm-ql galera-25.3.12-2.el7.x86_64

IV. Operation steps

1.node3 node host operation

(1) install MariaDB 10.2.14

(2) Edit the configuration file and start the service

[root@host3 ~] # 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.30172.16.10.40172.16.10.50

Binlog_format=row

Default_storage_engine=InnoDB

Innodb_autoinc_lock_mode=2

Innodb_doublewrite=1

[root@host3 ~] # galera_new_cluster # is used on any node when enabled for the first time

(3) send the configuration file to node4 and node5

[root@host3] # scp-r / etc/my.cnf.d/server.cnf root@172.16.10.40:/etc/my.cnf.d/

[root@host3] # scp-r / etc/my.cnf.d/server.cnf root@172.16.10.50:/etc/my.cnf.d/

2.node4 node operation

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

(2) start the MySQL service

You can see in the log that SST of Galera Cluster uses rsync transport by default.

(3) create an account in node4 for replication

[root@host4 ~] # mysql

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

MariaDB [(none)] > flush privileges

(4) create data at will and verify whether it is synchronized on node3

MariaDB [(none)] > CREATE DATABASE `hellodb`

MariaDB [(none)] > CREATE TABLE `students` (

-> `StuID` int (10) unsigned NOT NULL AUTO_INCREMENT

-> `Name` varchar (50) NOT NULL

-> `Age` tinyint (3) unsigned NOT NULL

-> `Gender` enum ('Fleming Magazine M') NOT NULL

-> `ClassID` tinyint (3) unsigned DEFAULT NULL

-> `TeacherID` int (10) 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

[root@host4] # mysqldump-uroot-- databases hellodb-- master-data=2-- single-transaction-- quick > / tmp/hellodb.sql

[root@host4] # scp-r / tmp/hellodb.sql root@172.16.10.50:/tmp/

(6) insert data again, and add new data after simulated backup.

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 out of the [galera] segment (in order to synchronize through binlog first)

(2) start the 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

[root@host5 ~] # systemctl stop mariadb.service

(6) cancel the logout and restart the service in the [galera] paragraph

Then add data to any node and synchronize among the three nodes, so that all the operations are completed.

Supplementary note:

After the 1.Galera Cluster node stops service, if the data is updated later, you only need to restart the MySQL service, and the data will be synchronized automatically.

two。 If a new node joins later, 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. In this way, synchronization can be achieved even without the IP address of the new node in the wsrep_cluster_address variable in the previous node configuration file. It is recommended to restart the service after adding the new node IP address to the wsrep_cluster_address variable of the old node.

There are questions:

Import a database in any Galera Cluster node, whether under the command line through mysql < xxx mode or using SQL statement source xxx mode, only the current node has data, other nodes only create the database and the table in the library, but there is no data in the table, and it will not be synchronized after restarting the MySQL service. The reason is unknown.

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.