mysql-wsrep+galera集群实现真正意义上的多主架构
这几天在面试,不少面试官都问到了mysql的高可用问题,除了常见的主从、MHA,今天分享下mysql的一种最新的多主架构galera集群的搭建方式。
我们都知道mysql主从其实是会带来一些问题的:
(1)用户要关注集群细节,实施读写分离;
(2)写库仍是单点,性能无法线性扩充;
(3)读库有延时,数据不一致;
(4)写库挂了,从库顶上,可能出现数据丢失;
(5)如果引入中间件,SQL能力会受影响;
(6)运维复杂性;
为什么用galera?Galera特性:
(1)真正的多主(True Multi_Master),任何数据修改会被复制到其他节点;
(2)没有所谓的从库延时(slave lag),同步复制;
(3)强一致性,所有节点处于一致的状态;
(4)热备,高可用,不需要处理所谓的主从故障转移,也不需要虚IP;
(5)无需读写分离,随时可以读写任何节点;
(6)支持InnoDB;
(7)对应用程序透明;
在开始之前,我们再来了解下mariadb:
首先MariaDB是一个数据库,可以看成是MySQL的一个分支,由于MySQL被SUN收购,所以MySQL面临着闭源的风险,当时MySQL之父Widenius并没有加入SUN,而是基于MySQL的代码开发新的分支,命名为MariaDB,并全部开源。
Galera是Galera Cluster,是一种为数据库设计的新型的、数据不共享的、高度冗余的高可用方案,galera mariadb就是集成了Galera插件的MariaDB集群,Galera本身是具有多主特性的,所以galera mariadb不是传统的主备模式的集群,而是多主节点架构。
一、下载地址
Maridb-galera-cluster下载地址:
https://mariadb.org/download/?t=mariadb&o=true&p=mariadb-galera&r=10.0.36&os=Linux&cpu=x86_64&i=systemd
注意:Galera Cluster的二进制安装软件包包括带有wsrep API补丁的数据库服务器。
mysql-resp +galera下载地址:
http://releases.galeracluster.com/
Galera Cluster由两部分组成:Galera复制库(galera-3)和使用写集复制(WSREP)API扩展的MySQL版本(mysql-wsrep)
Galera3对应的mysql版本主要是5.5~5.7,Galera4对应的版本是mysql8
这里全部采用二进制包的方式来搭建,包名如下:
galera-3-25.3.35-Linux-x86_64.tar.gz
mysql-wsrep-5.7.35-25.27-linux-x86_64.tar.gz
二、部署
1、环境准备
部署该集群至少准备三节点:
主机名 |
内部ip |
os |
node1 |
192.168.1.168 |
Centos 7.6 |
node2 |
192.168.1.171 |
Centos 7.6 |
node3 |
192.168.1.172 |
Centos 7.6 |
2、三个节点都做以下操作
1)关闭selinux
# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
# setenforce 0
2)关闭防火墙
#systemctl stop firewalld.service
#systemctl disable firewalld.service
3)安装依赖
#yum -y install lsof net-tools perl socat openssl openssl-devel boost-devel rsync
4)卸载无关包
# rpm -e mariadb-libs
5)其他
禁用AppArmor(ubuntu系统需要):
#ln -s /etc/apparmor.d/usr /etc/apparmor.d/disable/.sbin.mysqld
3、三节点共同配置
1)准备部署文件
galera-3-25.3.35-Linux-x86_64.tar.gz
mysql-wsrep-5.7.35-25.27-linux-x86_64.tar.gz
2)解压
#tar -mzxvf mysql-wsrep-5.7.35-25.27-linux-x86_64.tar.gz -C /opt
#ln -sv /opt/ mysql-wsrep-5.7.35-25.27-linux-x86_64 /opt/mysql
#tar -mzxvf galera-3-25.3.35-Linux-x86_64.tar.gz -C /opt
#ln -sv /opt/ galera-3-25.3.35-Linux-x86_64 /opt/galera
3)配置环境变量
#echo 'export MYSQL_HOME=/opt/mysql' >> /etc/profile
#echo 'export PATH=$MYSQL_HOME/bin:$PATH' >> /etc/profile
#echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mysql/lib' >> /etc/profile
#source /etc/profile
4)创建数据目录
#mkdir -p /opt/mysql/data
#mkdir -p /opt/mysql/logs/binlog
#mkdir -p /opt/mysql/run
5)创建用户
#useradd mysql -s /sbin/nologin
6) 配置/etc/my.cnf
# cat /etc/my.cnf
[client]
port=3306
socket=/opt/mysql/run/mysql.sock
default-character-set = utf8mb4
[mysqld]
port=3306
#关闭密码强度审计
#validate_password=off
#禁用SSL功能
skip_ssl
lc-messages-dir=/opt/mysql/share
tmpdir=/tmp
socket=/opt/mysql/run/mysql.sock
datadir=/opt/mysql/data
log-error=/opt/mysql/logs/mysql_error.log
pid-file=/opt/mysql/run/mysqld.pid
#数据库默认字符集,主流字符集支持一些特殊表情符号(特殊表情符占用4个字节)
character-set-server = utf8mb4
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
collation-server = utf8mb4_general_ci
#是否对sql语句大小写敏感,1表示不敏感
lower_case_table_names=1
log_bin_trust_function_creators = 1
#skip-external-locking
skip-name-resolve = 1
skip-symbolic-links
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
server-id = 171
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
log-bin=/opt/mysql/logs/binlog/mysql-slave-bin
binlog_format=ROW
max_binlog_size = 512M
expire_logs_days = 10
sync_binlog = 1
relay_log = mysql-relay-bin
relay_log_recovery
max_prepared_stmt_count = 65535
key_buffer_size = 256M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
join_buffer_size = 1M
thread_cache_size = 32
table_open_cache = 2048
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 1000
tmp_table_size = 256M
max_heap_table_size = 512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
external-locking = FALSE
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
default-storage-engine = InnoDB
thread_stack = 256K
transaction_isolation = READ-COMMITTED
#MySQL连接闲置超过一定时间后(单位:秒)将会被强行关闭
interactive_timeout = 28800
wait_timeout = 28800
#TIMESTAMP如果没有显示声明NOT NULL,允许NULL值
explicit_defaults_for_timestamp = 1
#SQL数据包发送的大小
max_allowed_packet = 32M
slow_query_log
slow-query-log-file=/opt/mysql/logs/slow.log
log_queries_not_using_indexes
log_slow_admin_statements
log_slow_slave_statements
expire_logs_days = 7
long_query_time = 2
########innodb settings########
innodb_buffer_pool_size = 4G
innodb_data_file_path=ibdata1:2G:autoextend
#innodb_flush_method = O_DIRECT
innodb_data_home_dir=/opt/mysql/data
innodb_log_group_home_dir=/opt/mysql/logs
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 5
innodb_file_per_table
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_instances = 8
innodb_strict_mode
[mysqldump]
quick
max_allowed_packet = 512M
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
7) 配置系统服务
# cat /usr/lib/systemd/system/mysqld.service
# systemd service file for MySQL forking server
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
Environment=LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mysql/lib
Environment=PATH=/opt/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
User=mysql
Group=mysql
Type=forking
PIDFile=/opt/mysql/run/mysqld.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
# ExecStartPre=/opt/mysql/bin/mysql_upgrade
# Start main service
ExecStart=/opt/mysql/bin/mysqld --daemonize --pid-file=/opt/mysql/run/mysqld.pid $MYSQLD_OPTS
# Use this to switch malloc implementation
# EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE=infinity
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
8) 初始化mysql
# /opt/mysql/bin/mysqld --initialize --datadir=/opt/mysql/data/ --basedir=/opt/
# chown -R mysql.mysql /opt/mysql
# systemctl daemon-reload
# systemctl restart mysqld
# tmppassword=$(grep -w 'temporary password' /opt/yozo/mysql/logs/mysql_error.log | awk '{print $11}')
# mysql -uroot -p${tmppassword} --connect-expired-password << EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY "yozosoft";
grant all privileges on *.* to yozo@'%' identified by 'yozosoft';
flush privileges;
exit
EOF
# systemctl enable mysqld
4、Node1配置
1)配置文件添加galera相关内容
# vim /etc/my.cnf
[mysqld]
…
# galerap配置
wsrep_on=ON
wsrep_provider= /opt/galera/lib/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.168,192.168.1.172,192.168.1.172"
wsrep_cluster_name = 'wuqy'
wsrep_node_name = 'node1'
wsrep_node-address = '192.168.1.168'
2)配置初始化集群服务
# cat /usr/lib/systemd/system/galera.service
# systemd service file for MySQL forking server
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
Environment=LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mysql/lib
Environment=PATH=/opt/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
User=mysql
Group=mysql
Type=forking
PIDFile=/opt/mysql/run/mysqld.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
# ExecStartPre=/opt/mysql/bin/mysql_upgrade
# Start main service
ExecStart=/opt/mysql/bin/mysqld --daemonize --pid-file=/opt/mysql/run/mysqld.pid --wsrep-new-cluster $MYSQLD_OPTS
# Use this to switch malloc implementation
# EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE=infinity
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
3) 初始化集群
# systemctl daemon-reload
# systemctl stop mysqld
仅执行一次:
# systemctl disable galera.service
# systemctl start galera.service
注意:此时不要启动mysqld服务
5、Node2配置
1)配置文件添加galera相关内容
# vim /etc/my.cnf
[mysqld]
…
# galerap配置
wsrep_on=ON
wsrep_provider= /opt/galera/lib/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.168,192.168.1.172,192.168.1.172"
wsrep_cluster_name = 'wuqy'
wsrep_node_name = 'node2'
wsrep_node-address = '192.168.1.171'
2)重启mysqld服务
# systemctl restart mysqld
# systemctl status mysqld
6、Node3配置
1)配置文件添加galera相关内容
# vim /etc/my.cnf
[mysqld]
…
# galerap配置
wsrep_on=ON
wsrep_provider= /opt/galera/lib/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.168,192.168.1.172,192.168.1.172"
wsrep_cluster_name = 'wuqy'
wsrep_node_name = 'node3'
wsrep_node-address = '192.168.1.172'
2)重启mysqld服务
# systemctl restart mysqld
# systemctl status mysqld
7、启动node1的mysql服务
关闭初始化服务:
# sysetmctl stop galera
重启mysqld:
# systemctl restart mysqld
三、测试
1、集群验证
# mysqladmin -u root -p 'xxxxxxx'
> show status like 'wsrep_local_state_comment';
> show status like 'wsrep_cluster_size';
集群状态:
Open:节点启动成功,尝试连接到集群
Primary:节点已处于集群中,在新节点加入时,选取donor进行数据库同步时会产生的状态
Joiner:节点处于等待接收或正在接收同步文件的状态
Joined:节点完成数据同步,但还有部分数据不是最新的,在追赶与集群数据一致的状态
Synced:节点正常提供服务的状态,表示当前节点数据状态与集群数据状态是一致的
Donor:表示该节点被选为Donor节点,正在为新加进来的节点进行全量数据同步,此时该节点对客户
2、数据复制验证
Node1:
# mysql -u root -p
create database test;
use test;
create table t1(a int);
insert into t1 values(1);
Node2:
# mysql -u root -p
use test;
create table t2(a int);
insert into t2 values(2);
Node3:
# mysql -u root -p
use test;
create table t3(a int);
insert into t3 values(3);
随机选一个节点,来查询刚刚输入的信息:
select t1.a,t2.a,t3.a from test.t1,test.t2,test.t3;
4、故障验证
停掉node1节点的mysql,模拟节点挂掉:
# systemctl stop mysqld
随意上登录另外俩任意节点查看mysql查看状态:
任然可以正常读写。
恢复node1节点mysqld服务,然后查询刚刚插入的数据已经同步过来了:
以上就是关于mysql-wsrep+galera集群的部署方式和测试,今天就分享到这里了。如果觉得还不错的话记得点赞关注加收藏哦,哈哈~~