MariaDB Galera Cluster 部署 + keepalived实现高可用
MariaDB Galera Cluster 部署
MariaDB作为Mysql的一个分支，在开源项目中已经广泛使用，例如大热的openstack，所以，为了保证服务的高可用性，同时提高系统的负载能力，集群部署是必不可少的。
MariaDB Galera Cluster 介绍
MariaDB集群是MariaDB同步多主机集群。它仅支持XtraDB/ InnoDB存储引擎（虽然有对MyISAM实验支持 - 看wsrep_replicate_myisam系统变量）。
主要功能:
同步复制
真正的multi-master，即所有节点可以同时读写数据库
自动的节点成员控制，失效节点自动被清除
新节点加入数据自动复制
真正的并行复制，行级
用户可以直接连接集群，使用感受上与MySQL完全一致
优势:
因为是多主，所以不存在Slavelag(延迟)
不存在丢失事务的情况
同时具有读和写的扩展能力
更小的客户端延迟
节点间数据是同步的,而Master/Slave模式是异步的,不同slave上的binlog可能是不同的
技术:
Galera集群的复制功能基于Galeralibrary实现,为了让MySQL与Galera library通讯，特别针对MySQL开发了wsrep API。
Galera插件保证集群同步数据，保持数据的一致性，靠的就是可认证的复制，工作原理如下图： MariaDB Galera Cluster 部署
当客户端发出一个commit的指令，在事务被提交之前，所有对数据库的更改都会被write-set收集起来,并且将write-set纪录的内容发送给其他节点。
write-set将在每个节点进行认证测试，测试结果决定着节点是否应用write-set更改数据。
如果认证测试失败，节点将丢弃write-set；如果认证测试成功，则事务提交。
1. 安装环境准备
安装MariaDB集群至少需要3台服务器（如果只有两台的话需要特殊配置，请参照 官方文档 ）
在这里，我列出试验机器的配置：
操作系统版本：centos7
maria-server1 192.168.1.153
maria-server2 192.168.1.154
为了保证节点间相互通信，需要禁用防火墙设置（如果需要防火墙，则参照 官方网站 增加防火墙信息设置）
在三个节点分别执行命令：
systemctl stop firewalld
然后将/etc/sysconfig/selinux的selinux设置成disabled,这样初始化环境就完成了。
2. 安装 MariaDB Galera Cluster
配置MariaDByum
yum install -y epel-release
wget https://repos.fedorapeople.org/repos/openstack/openstack-kilo/rdo-release-kilo-2.noarch.rpm
rpm -ivh rdo-release-kilo-2.noarch.rpm
yum update -y
yum install -y mariadb mariadb-libs mariadb-devel mariadb-galera-common mariadb-galera-server galera rsync
systemctl enable mariadb
systemctl enable rsyncd
systemctl start mariadb
mysql_secure_installation --root 允许远程登陆 密码:password
[root@maria-server1 mysql]# mysql -u root -ppassword
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.40-MariaDB-wsrep MariaDB Server, wsrep_25.11.r4026
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
flush privileges;
MariaDB [(none)]> select host, user from mysql.user;
+---------------+------+
| host | user |
+---------------+------+
| % | root |
| 127.0.0.1 | root |
| ::1 | root |
| localhost | |
| localhost | root |
| maria-server1 | |
| maria-server1 | root |
+---------------+------+
7 rows in set (0.00 sec)
3. 配置 MariaDB Galera Cluster
http://blog.sina.com.cn/s/blog_6de3aa8a0102w00d.html
---192.168.1.153
vim /etc/my.cnf.d/galera.cnf +27
bind-address=0.0.0.0 --> bind-address=192.168.1.153
wsrep_cluster_name="dbcluster"
vim /etc/my.cnf.d/server.cnf
[mysqld]
datadir=/var/lib/mysql
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=1G"
wsrep_cluster_address=gcomm://192.168.1.153,192.168.1.154[i1]
wsrep_cluster_name='dbcluster'
wsrep_node_address='192.168.1.153'
wsrep_node_name='maria-server1'
wsrep_sst_method=rsync
---192.168.1.154
vim /etc/my.cnf.d/galera.cnf +27
bind-address=0.0.0.0 --> bind-address=192.168.1.154
wsrep_cluster_name="dbcluster"
vim /etc/my.cnf.d/server.cnf
[mysqld]
datadir=/var/lib/mysql
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=1G"
wsrep_cluster_address=gcomm://192.168.1.153,192.168.1.154
wsrep_cluster_name='dbcluster'
wsrep_node_address='192.168.1.154'
wsrep_node_name='maria-server2'
wsrep_sst_method=rsync
wsrep_cluster_address ==注意一定要保证有节点存在
第一次初始化为wsrep_cluster_address= gcomm://
4. 查看集群状态
MariaDB [(none)]> show status like '%wsrep_%';
+------------------------------+-----------------------------------------+
| Variable_name | Value |
+------------------------------+-----------------------------------------+
| wsrep_local_state_uuid | 2489b818-219b-11e6-9021-b61cb5e054fb |
| wsrep_protocol_version | 5 |
| wsrep_last_committed | 18 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 3 |
| wsrep_received_bytes | 238 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 18446744073709551615 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_incoming_addresses | 192.168.1.154:3306,192.168.1.153:3306 |
| wsrep_cluster_conf_id | 24 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | 2489b818-219b-11e6-9021-b61cb5e054fb |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.5(rXXXX) |
| wsrep_ready | ON |
| wsrep_thread_count | 2 |
+------------------------------+-----------------------------------------+
48 rows in set (0.00 sec)
我们可以关注几个关键的参数：
wsrep_connected = on 链接已开启
wsrep_local_index = 1 在集群中的索引值
wsrep_cluster_size =3 集群中节点的数量
wsrep_incoming_addresses =192.168.1.154:3306,192.168.1.153:3306集群中节点的访问地址
5. 验证数据同步
maria-server1
MariaDB [(none)]> create database galera_test7;
MariaDB [(none)]> create database galera_test8;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| galera_test7 |
| galera_test8 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
maria-server2上查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| galera_test7 |
| galera_test8 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
6. keepalived实现高可用
安装keepalived --2个节点
yum -y install keepalived
---192.168.1.153
[root@maria-server1 keepalived]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script check_haproxy {
script "sh /etc/keepalived/check_mysql.sh"
interval 2
weight -4
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 67[i2]
priority 100[i3]
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.157
}
track_script {
check_haproxy
}
}
vim /etc/keepalived/check_mysql.sh
#===================start==========================
pro=`ps -ef | grep mariadb | grep -v grep | wc -l`
if [ $pro -eq 0 ];then
echo `date` "Keepalived -mariadb is not alived" >> /var/log/messages
systemctl stop keepalived
fi
#===================end==========================
---192.168.1.154
[root@maria-server2 mysql]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script check_haproxy {
script "sh /etc/keepalived/check_mysql.sh"
interval 2
weight -4
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 67
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.157
}
track_script {
check_haproxy
}
}
7. 问题汇总
配置完虚拟ip后出现
[root@kvmserver2 ~]# mysql -u root -ppassword -h 192.168.1.157
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.157' (111)
vim /etc/my.cnf.d/galera.cnf
bind-address = 注释掉即可
当2个mariadb节点同时关掉启动是报错如下:
[ERROR] WSREP: gcs/src/gcs.c:gcs_open():1291: Failed to open channel 'dbcluster' at 'gcomm://192.168.1.153, 192.168.1.154': -110 (Connection timed out)
重新初始化gcomm
vim /etc/my.cnf.d/server.cnf
wsrep_cluster_address=gcomm://
--153
systemctl start mariadb
--154
systemctl start mariadb
--153
vim /etc/my.cnf.d/server.cnf
wsrep_cluster_address=gcomm://192.168.1.153,192.168.1.154
systemctl restart mariadb
