vlambda博客
学习文章列表

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';

mysql-wsrep+galera集群实现真正意义上的多主架构

集群状态:

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;

mysql-wsrep+galera集群实现真正意义上的多主架构


4、故障验证

停掉node1节点的mysql,模拟节点挂掉:

# systemctl stop mysqld

mysql-wsrep+galera集群实现真正意义上的多主架构

随意上登录另外俩任意节点查看mysql查看状态:

mysql-wsrep+galera集群实现真正意义上的多主架构

任然可以正常读写。


恢复node1节点mysqld服务,然后查询刚刚插入的数据已经同步过来了:



以上就是关于mysql-wsrep+galera集群的部署方式和测试,今天就分享到这里了。如果觉得还不错的话记得点赞关注加收藏哦,哈哈~~