特点

  1. 同步复制
  2. 可以在任意节点上进行读写
  3. 自动控制成员,自动删除故障节点
  4. 自动加入节点
  5. 真正给予行级别的并发复制
  6. 调度客户连接

架构图

部署mariadb+galera高可用
mariadb 可以看做是常见的数据库,负责连接应用(web, API 等)
单纯的 mariadb 无法实现多个主服务器数据同步
多台数据库中数据同步由 wsrep 接口实现

最终目标,实现多个 MySQL 同时读写

wsrep API

wsrep API 是一种数据库插件接口,比较类似一种应用程序,主要针对写复制
该程序主要用于定义应用程序如何调用复制库实现回写
wsrep API 由支持改库的应用程序动态装载

全局事务ID(GTID)

wsrep API 描述下面复制模型,一个应用程序,如数据库当前的一个对象,当前被客户端修改,对象改变导致事务产生一系列的原子性改变, 在集群中所有的节点都具备相同的对象,并由同步复制应用都各自节点,按照相同的顺序产生相同变化从而实现数据同步

GTID 包含

一个 UUID 作为对象标识及经历改变的序号,序号会发生连续的改变
GTID 允许比较应用程序状态,建立对象改变的顺序,决定对象的变化是否需要更新 GTID

部署环境

node-1:192.168.200.4 mariadb:10.1.30  galera:25.3.22
node-2:192.168.200.5 mariadb:10.1.30  galera:25.3.22

安装mariadb+galera

[root@node-1 ~]# yum install MariaDB-server galera
[root@node-2 ~]# yum install MariaDB-server galera

配置主节点node-1

[root@node-1 ~]# vi /etc/my.cnf
[mysqld]
max_connect_errors=1000
[root@node-1 ~]# vi /etc/my.cnf.d/server.cnf 
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="192.168.200.4"
wsrep_sst_method=rsyn

配置备份节点node-2

[root@node-2 ~]# vi /etc/my.cnf
[mysqld]
max_connect_errors=1000
[root@node-2 ~]# vi /etc/my.cnf.d/server.cnf 
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.200.4,192.168.200.5"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_node_address="192.168.200.5"
wsrep_cluster_name="MariaDB_Cluster"
wsrep_sst_method=rsync

启动服务

[root@node-1 ~]# /bin/galera_new_cluster 
[root@node-2 ~]# systemctl start mariadb

测试集群状态

[root@node-1 ~]# mysql -uroot -p
MariaDB [(none)]> show status like "wsrep_%";
+------------------------------+-----------------------------------------------+
| Variable_name                | Value                                         |
+------------------------------+-----------------------------------------------+
| wsrep_apply_oooe             | 0.000000                                      |
| wsrep_apply_oool             | 0.000000                                      |
| wsrep_apply_window           | 0.000000                                      |
| wsrep_causal_reads           | 0                                             |
| wsrep_cert_deps_distance     | 0.000000                                      |
| wsrep_cert_index_size        | 0                                             |
| wsrep_cert_interval          | 0.000000                                      |
| wsrep_cluster_conf_id        | 2                                             |
| wsrep_cluster_size           | 2                                             |
| wsrep_cluster_state_uuid     | 3fa7ddbb-6839-11e9-a6f5-2b4eac1b9da2          |
| wsrep_cluster_status         | Primary                                       |
| wsrep_commit_oooe            | 0.000000                                      |
| wsrep_commit_oool            | 0.000000                                      |
| wsrep_commit_window          | 0.000000                                      |
| wsrep_connected              | ON                                            |
| wsrep_desync_count           | 0                                             |
| wsrep_evs_delayed            |                                               |
| wsrep_evs_evict_list         |                                               |
| wsrep_evs_repl_latency       | 0.000522839/0.0016527/0.00400798/0.00117158/6 |
| wsrep_evs_state              | OPERATIONAL                                   |
| wsrep_flow_control_paused    | 0.000000                                      |
| wsrep_flow_control_paused_ns | 0                                             |
| wsrep_flow_control_recv      | 0                                             |
| wsrep_flow_control_sent      | 0                                             |
| wsrep_gcomm_uuid             | 3fa68225-6839-11e9-9abe-478e95b9264a          |
| wsrep_incoming_addresses     | 192.168.200.4:3306,192.168.200.5:3306         |
| wsrep_last_committed         | 0                                             |
| wsrep_local_bf_aborts        | 0                                             |
| wsrep_local_cached_downto    | 18446744073709551615                          |
| wsrep_local_cert_failures    | 0                                             |
| wsrep_local_commits          | 0                                             |
| wsrep_local_index            | 0                                             |
| wsrep_local_recv_queue       | 0                                             |
| wsrep_local_recv_queue_avg   | 0.166667                                      |
| wsrep_local_recv_queue_max   | 2                                             |
| wsrep_local_recv_queue_min   | 0                                             |
| wsrep_local_replays          | 0                                             |
| wsrep_local_send_queue       | 0                                             |
| wsrep_local_send_queue_avg   | 0.000000                                      |
| wsrep_local_send_queue_max   | 1                                             |
| wsrep_local_send_queue_min   | 0                                             |
| wsrep_local_state            | 4                                             |
| wsrep_local_state_comment    | Synced                                        |
| wsrep_local_state_uuid       | 3fa7ddbb-6839-11e9-a6f5-2b4eac1b9da2          |
| wsrep_protocol_version       | 7                                             |
| wsrep_provider_name          | Galera                                        |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>             |
| wsrep_provider_version       | 25.3.22(r3764)                                |
| wsrep_ready                  | ON                                            |
| wsrep_received               | 6                                             |
| wsrep_received_bytes         | 414                                           |
| wsrep_repl_data_bytes        | 0                                             |
| wsrep_repl_keys              | 0                                             |
| wsrep_repl_keys_bytes        | 0                                             |
| wsrep_repl_other_bytes       | 0                                             |
| wsrep_replicated             | 0                                             |
| wsrep_replicated_bytes       | 0                                             |
| wsrep_thread_count           | 2                                             |
+------------------------------+-----------------------------------------------+
58 rows in set (0.00 sec)
#如果 "wsrep_local_state_comment" is "Synced" 则表明集群创建成功。

数据同步

#在node-1创建数据库
[root@node-1 ~]# mysql -uroot -p
MariaDB [(none)]> create database galera_test;
Query OK, 1 row affected (0.06 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| galera_test        |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

#在node-2上查询
[root@node-2 ~]# mysql -uroot -p
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| galera_test        |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

#可以查询到galera_test数据库,说明数据库同步没问题。