Galera 集群介绍
MariaDB 集群是 MariaDB 同步多主机集群. 它仅支持 XtraDB/ InnoDB 存储引擎.
主要功能
同步复制
真正的 multi-master, 即所有节点可以同时读写数据库
自动的节点成员控制, 失效节点自动被清除
新节点加入数据自动复制
真正的并行复制, 行级
用户可以直接连接集群, 使用感受上与 MySQL 完全一致
优势
因为是多主, 所以不存在 Slavelag(延迟)
不存在丢失事务的情况
同时具有读和写的扩展能力
更小的客户端延迟
节点间数据是同步的, 而 Master/Slave 模式是异步的, 不同 slave 上的 binlog 可能是不同的
技术
Galera 集群的复制功能基于 Galeralibrary 实现, 为了让 MySQL 与 Galera library 通讯, 特别针对 MySQL 开发了 wsrep API.
Galera 插件保证集群同步数据, 保持数据的一致性, 靠的就是可认证的复制, 工作原理如下图:
当客户端发出一个 commit 的指令, 在事务被提交之前, 所有对数据库的更改都会被 write-set 收集起来, 并且将 write-set 纪录的内容发送给其他节点.
write-set 将在每个节点进行认证测试, 测试结果决定着节点是否应用 write-set 更改数据.
如果认证测试失败, 节点将丢弃 write-set ; 如果认证测试成功, 则事务提交.
安装环境准备
准备三台服务器
操作系统版本: centos7
数据库版本: 5.5.56-MariaDB
主节点: 192.168.64.4
节点 1:192.168.64.5
节点 2:192.168.64.7
各个节点之间需要解析
- [root@ken-node1 ~]# cat /etc/hosts
- 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
- ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
- 192.168.64.4 ken-node1
- 192.168.64.5 ken-node2
- 192.168.64.7 ken-node3
配置 yum 源
- [mariadb]
- name = MariaDB
- baseurl = http://yum.mariadb.org/10.3.5/centos74-amd64
- gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
- gpgcheck=1
- enabled=0
安装配置
第一步: 在各个节点下载下面的软件包
[root@ken ~]# yum install -y MariaDB-server MariaDB-client galera -y
第二步: 初始化数据库
只需要在一个节点执行即可 192.168.64.4
- [root@ken ~]# systemctl restart mariadb
- [root@ken ~]# mysql_secure_installation
- NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
- SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
- In order to log into MariaDB to secure it, we'll need the current
- password for the root user. If you've just installed MariaDB, and
- you haven't set the root password yet, the password will be blank,
- so you should just press enter here.
- Enter current password for root (enter for none):
- OK, successfully used password, moving on...
- Setting the root password ensures that nobody can log into the MariaDB
- root user without the proper authorisation.
- Set root password? [Y/n] y
- New password:
- Re-enter new password:
- Password updated successfully!
- Reloading privilege tables..
- ... Success!
- By default, a MariaDB installation has an anonymous user, allowing anyone
- to log into MariaDB without having to have a user account created for
- them. This is intended only for testing, and to make the installation
- go a bit smoother. You should remove them before moving into a
- production environment.
- Remove anonymous users? [Y/n] y
- ... Success!
- Normally, root should only be allowed to connect from 'localhost'. This
- ensures that someone cannot guess at the root password from the network.
- Disallow root login remotely? [Y/n] n
- ... skipping.
- By default, MariaDB comes with a database named 'test' that anyone can
- access. This is also intended only for testing, and should be removed
- before moving into a production environment.
- Remove test database and access to it? [Y/n] n
- ... skipping.
- Reloading the privilege tables will ensure that all changes made so far
- will take effect immediately.
- Reload privilege tables now? [Y/n] y
- ... Success!
- Cleaning up...
- All done! If you've completed all of the above steps, your MariaDB
- installation should now be secure.
- Thanks for using MariaDB!
第三步: 关闭数据库, 修改 / etc/my.cnf.d/server.cnf
在主配置节点配置如下
- [root@ken ~]# systemctl stop mariadb
- [root@ken ~]# VIM /etc/my.cnf.d/server.cnf
- ...
- [galera]
- wsrep_on=ON
- wsrep_provider=/usr/lib64/galera/libgalera_smm.so
- wsrep_cluster_address="gcomm://192.168.64.4,192.168.64.5,192.168,64.7" #节点 IP 地址
- wsrep_node_name= ken-node1 #主节点主机名
- wsrep_node_address=192.168.64.4 #主节点 IP
- binlog_format=row
- default_storage_engine=InnoDB
- innodb_autoinc_lock_mode=2
- wsrep_slave_threads=1
- innodb_flush_log_at_trx_commit=0
- innodb_buffer_pool_size=120M
- wsrep_sst_method=rsync
- wsrep_causal_reads=ON
第四步: 将此文件复制到 mariadb-2,mariadb-3, 注意要把 wsrep_node_name 和 wsrep_node_address 改成相应节点的 hostname 和 ip.
第五步: 启动集群
主节点 192.168.64.4 启动命令如下:
[root@ken-node1 ~]# galera_new_cluster
各个节点监听的端口
集群服务监听了 4567 和 3306 端口 (每个节点都监听了 4567 和 3306 端口)
- [root@ken-node1 ~]# ss -tnl
- State Recv-Q Send-Q Local Address:Port Peer Address:Port
- LISTEN 0 128 *:22 *:*
- LISTEN 0 128 *:4567 *:*
- LISTEN 0 100 127.0.0.1:25 *:*
- LISTEN 0 80 :::3306 :::*
- LISTEN 0 128 :::80 :::*
- LISTEN 0 128 :::22 :::*
- LISTEN 0 100 ::1:25 :::*
其他节点启动命令如下:
[root@ken-node2 ~]# systemctl restart mariadb
验证集群
在主节点执行如下操作
连接 mariadb, 查看是否启用 galera 插件
显示 ON 表示开启
- MariaDB [(none)]> show status like "wsrep_ready";
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | wsrep_ready | ON |
- +---------------+-------+
- 1 row in set (0.005 sec)
查看目前集群机器数
机器数为 3 个显示正确
- MariaDB [(none)]> show status like "wsrep_cluster_size";
- +--------------------+-------+
- | Variable_name | Value |
- +--------------------+-------+
- | wsrep_cluster_size | 3 |
- +--------------------+-------+
- 1 row in set (0.003 sec)
查看集群状态
- 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 | 2 |
- | wsrep_cert_deps_distance | 0.000000 |
- | wsrep_cert_index_size | 0 |
- | wsrep_cert_interval | 0.000000 |
- | wsrep_cluster_conf_id | 14 |
- | wsrep_cluster_size | 3 |
- | wsrep_cluster_state_uuid | 541248ce-56d0-11e9-b41b-3a9775d73ca7 |
- | 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/0/0/0/0 |
- | 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 | 540ee869-56d0-11e9-955e-9b7f30e437a6 |
- | wsrep_incoming_addresses | 192.168.64.5:3306,192.168.64.4:3306,192.168.64.7: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 | 1 |
- | wsrep_local_recv_queue | 0 |
- | wsrep_local_recv_queue_avg | 0.100000 |
- | 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 | 541248ce-56d0-11e9-b41b-3a9775d73ca7 |
- | wsrep_protocol_version | 8 |
- | wsrep_provider_name | Galera |
- | wsrep_provider_vendor | Codership Oy <info@codership.com> |
- | wsrep_provider_version | 25.3.23(r3789) |
- | wsrep_ready | ON |
- | wsrep_received | 20 |
- | wsrep_received_bytes | 3651 |
- | 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.002 sec)
查看连接的主机
- MariaDB [(none)]> show status like "wsrep_incoming_addresses";
- +--------------------------+-------------------------------------------------------+
- | Variable_name | Value |
- +--------------------------+-------------------------------------------------------+
- | wsrep_incoming_addresses | 192.168.64.5:3306,192.168.64.4:3306,192.168.64.7:3306 |
- +--------------------------+-------------------------------------------------------+
- 1 row in set (0.001 sec)
测试集群是否同步
在各个节点创建数据库, 可以发现每个节点都可以同步
我在 192.168.64.4 创建 ken 数据库
- [root@ken-node1 ~]# MySQL -uroot -p
- Enter password:
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 12
- Server version: 10.3.5-MariaDB MariaDB Server
- Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- MariaDB [(none)]> create database ken;
- Query OK, 1 row affected (0.041 sec)
- MariaDB [(none)]> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | ken |
- | ken1 |
- | MySQL |
- | performance_schema |
- | test |
- +--------------------+
- 6 rows in set (0.001 sec)
在 192.168.64.5 节点上查看数据库
- [root@ken-node2 ~]# MySQL -uroot -p
- Enter password:
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 11
- Server version: 10.3.5-MariaDB MariaDB Server
- Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- MariaDB [(none)]> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | ken | #ken 数据库已经同步过来
- | MySQL |
- | performance_schema |
- | test |
- +--------------------+
- 5 rows in set (0.002 sec)
192.168.64.7 节点上查看数据库
- [root@ken-node3 ~]# MySQL -uroot -p
- Enter password:
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 10
- Server version: 10.3.5-MariaDB MariaDB Server
- Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- MariaDB [(none)]> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | ken | #ken 数据库已经同步 |
- | MySQL |
- | performance_schema |
- | test |
- +--------------------+
- 6 rows in set (0.002 sec)
来源: https://www.cnblogs.com/kenken2018/p/10651435.html