mysql 5.7.28 galera 集群配置
配置 yum 源
# galera.repo
[galera]
name = Galera
baseurl = http://releases.galeracluster.com/galera-3/centos/7/x86_64/
gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
[mysql-wsrep]
name = MySQL-wsrep
baseurl = http://releases.galeracluster.com/mysql-wsrep-5.7/centos/7/x86_64/
gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
# epel.repo
wget -O /etc/yum.repos.d/epel.repo http://mirrors.cloud.tencent.com/repo/epel-7.repo
# Centos-Base.repo
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.cloud.tencent.com/repo/centos7_base.repo
离线 rpm 准备
percona-xtrabackup-24 采用 wget 方式下载
wget -c https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.16/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.16-1.el7.x86_64.rpm
galera-3, mysql-wsrep-5.7, mysql-wsrep-server-5.7 采用 yum 方式下载
yum install --downloadonly --downloaddir=./ galera-3 boost-program-options
yum install --downloadonly --downloaddir=./ mysql-wsrep-5.7 mysql-wsrep-libs-5.7 mysql-wsrep-libs-compat-5.7 libaio lsof mysql-wsrep-client-5.7 mysql-wsrep-common-5.7 mysql-wsrep-server-5.7 socat
# percona-xtrabackup-24，（注意：使用2.4版本，而不是2.3.6版本,也不是8.0+版本）
yum install --downloadonly --downloaddir=./ perl-Compress-Raw-Bzip2 perl-Compress-Raw-Zlib perl-DBD-MySQL perl-DBI perl-Data-Dumper perl-IO-Compress perl-Net-Daemon perl-PlRPC
安装
yum localinstall *.rpm
or
yum localinstall galera-3-25.3.28-1.el7.x86_64.rpm mysql-wsrep-5.7-5.7.28-25.20.el7.x86_64.rpm mysql-wsrep-server-5.7-5.7.28-25.20.el7.x86_64.rpm mysql-wsrep-libs-compat-5.7-5.7.28-25.20.el7.x86_64.rpm mysql-wsrep-client-5.7-5.7.28-25.20.el7.x86_64.rpm mysql-wsrep-common-5.7-5.7.28-25.20.el7.x86_64.rpm mysql-wsrep-libs-5.7-5.7.28-25.20.el7.x86_64.rpm perl-DBD-MySQL-4.023-6.el7.x86_64.rpm boost-program-options-1.53.0-27.el7.x86_64.rpm libaio-0.3.109-13.el7.x86_64.rpm libev-4.15-7.el7.x86_64.rpm lsof-4.87-6.el7.x86_64.rpm perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm perl-DBI-1.627-4.el7.x86_64.rpm perl-Data-Dumper-2.145-3.el7.x86_64.rpm perl-IO-Compress-2.061-2.el7.noarch.rpm perl-Net-Daemon-0.48-5.el7.noarch.rpm perl-PlRPC-0.2020-14.el7.noarch.rpm socat-1.7.3.2-2.el7.x86_64.rpm percona-xtrabackup-24-2.4.16-1.el7.x86_64.rpm perl-Digest-1.17-245.el7.noarch.rpm perl-Digest-MD5-2.52-3.el7.x86_64.rpm
配置
注意：
- 不要配置 basedir
- 不要配置 pid-file 。systemctl cat mysqld.service 中已经配置固定路径
- 配置 lc-messages-dir=/usr/share/mysql/english/ 。路径要确保正确，默认的路径不对
- 配置 socket
- 配置 datadir
- 配置 wsrep_sst_method=xtrabackup-v2。 rsync 在同步是为底层文件同步，会锁库不建议使用。
- 备份使用的帐户需要创建并赋权，确保配置使用的用户名和密码正确
# /etc/my.cnf
[mysqld]
# skip-grant-tables=1
log_timestamps=SYSTEM
user=mysql
port=3306
server_id=3
explicit_defaults_for_timestamp=true
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysqld.log
lc-messages-dir = /usr/share/mysql/english/
binlog_format=ROW
bind-address=0.0.0.0
character_set_server=utf8
collation-server=utf8_general_ci
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=122M
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_address="gcomm://<ip0>:4567,<ip1>:4567,<ip1>:4567"
#wsrep_cluster_address="gcomm://"
#wsrep_sst_method=rsync
wsrep_sst_auth=galera:<pwd>
wsrep_sst_method=xtrabackup-v2
#wsrep_sync_wait=1
wsrep_node_address="<ip0>"
wsrep_node_name="galera2"
wsrep_cluster_name="mysql01_galera_cluster"
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
将集群内的机器配置到 hosts 中，其中的 hostname 和/etc/my.cnf 配置中保持一直
# 举例：
<ip0> galera1
<ip1> galera2
<ip2> galera3
启动服务
集群内第一个启动节点，初始化数据库
# 有些服务器上已有蓝鲸设置的mysqld，所以需要使用绝对路径。查看方式： which mysqld
/usr/sbin/mysqld --initialize
获取初始化后服务密码
# 初始化后密码在,配置指定: /var/lib/mysql/mysqld.log 中
grep ‘temporary password‘ /var/lib/mysql/mysqld.log
启动集群内第一个节点
/usr/bin/mysqld_bootstrap
修改服务密码
# 报sock有问题的，请指定配置中设定的sock文件
# mysql -S /var/lib/mysql/mysql.sock -u root -p
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '<pwd>';
# CREATE USER 'root'@'%' IDENTIFIED BY '<pwd>';
CREATE USER 'galera'@'localhost' IDENTIFIED BY '<pwd>';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'galera'@'localhost';
FLUSH PRIVILEGES;
非集群内第一个节点启动
systemctl start mysqld
停止服务
systemctl stop mysqld
启动仲裁节点
# 请注意gcomm://后的配置
garbd -a "gcomm://<ip0>:4567,<ip1>:4567?gmcast.listen_addr=tcp://0.0.0.0:4568&pc.wait_prim=no" -g mysql01_galera_cluster -d
galear 集群状态查询
登陆 mysql
show status like 'wsrep%';