mysql 5.7.28 galera集群配置

发布时间: 更新时间: 总字数:704 阅读时间:2m 作者: IP上海 分享 网址

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

配置

  • /etc/my.cnf 配置请参考以下配置

注意:

  1. 不要配置 basedir
  2. 不要配置 pid-file 。systemctl cat mysqld.service 中已经配置固定路径
  3. 配置 lc-messages-dir=/usr/share/mysql/english/ 。路径要确保正确,默认的路径不对
  4. 配置 socket
  5. 配置 datadir
  6. 配置 wsrep_sst_method=xtrabackup-v2。 rsync 在同步是为底层文件同步,会锁库不建议使用。
  7. 备份使用的帐户需要创建并赋权,确保配置使用的用户名和密码正确
# /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
  • config hosts

将集群内的机器配置到 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%';
Home Archives Categories Tags Statistics
本文总阅读量 次 本站总访问量 次 本站总访客数