PXC+keepalived高可用mysql集群搭建

0. PXC介绍

（1）工作原理

client端向server端发送dml更新操作请求时，server的native本地进程处理请求，并返回OK准备接收，client发送commit更新事务给server，server将replicate writeset复制写数据集发给group（cluster集群），cluster将该数据集对应产生的唯一的GTID（global transaction ID）发送给集群每个server（节点）。当前server节点验证通过后，执行commit_cd动作更新本地数据库，并返回OK；若其他节点验证不通过，则执行rollback_cd，回滚刚提交的事务。其他server(other server)接收并验证通过后，执行apply_cd和commit_cd动作更新本地数据库；若验证不通过，则丢弃该数据集

（2）主要名词解释：

WS    write set写数据集，写/更新事务

IST   Incremental State Transfer增量同步

SST   State Snapshot Transfer增量同步。传输SST的几种方法：mysqldump/xtrabackup/rsync

UUID    节点状态改变及顺序的唯一标识

GTID    Global Transaction ID，由UUID和sequence number偏移量组成。wsrep api中定义的集群内部全局事务id，用于记录集群中发生状态改变的唯一标识以及队列中的偏移量。

wsrep API    在DBMS库和wsrep provider之间提供接口

commit       把事务所做的修改提交到数据库，即在库中执行用户的sql请求  

（3）PXC/Galera Cluster集群端口

3306    数据库对外提供服务的端口

4444    镜像数据传输SST，集群数据同步端口，全量同步，新节点加入时起作用

4567    集群节点间相互通信的端口

4568    增量数据同步IST，节点下线、重启后使用该端口，增量同步数据。

（4）PXC的优点：

①：实现mysql数据库集群架构的高可用性和数据的 强一致性。

②：完成了真正的多节点读写的集群方案。

③：改善了传统意义上的主从复制延迟问题，基本上达到了实时同步。

④：新加入的节点可以自动部署，无须提供手动备份，维护起来很方便。

⑤：由于是多节点写入，所以数据库故障切换很容易。

（5）PXC的缺点：

①：新加入的节点开销大，需要复制完整的数据。采用SST传输开销太大。

②：任何更新事务都需要全局验证通过，才会在每个节点库上执行。集群性能受限于性能最差的节点，也就是经常说的短板效应。

③：因为需要保证数据的一致性，所以在多节点并发写时，锁冲突问题比较严重。

④：存在写扩大问题，所有的节点上都会发生些操作。

⑤：只支持innodb存储引擎的表。

⑥：没有表级别的锁定，执行DDL语句操作会把整个集群锁住，而且也 kill 不了（建议使用Osc操作，即在线DDL）

⑦：所有的表必须含有主键，不然操作数据时会报错。

 

1. PXC环境准备

 

IP	hostname	系统版本	备注
10.45.7.128	rac3	redhat7.4	Master节点（node1）	Keepalived双节点
10.45.7.129	rac4	redhat7.4	Standby节点（node2）
10.45.53.31	rac2	redhat7.4	备份节点（node3）

 

node1和node2设置VIP(keepalived)对外提供数据写入接口，node3做为维护节点(例如备份，监控等)

 

2. PXC和xtrabackup，toolkit rpm包下载地址

 

https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/

Percona-XtraDB-Cluster-5.7.23-31.31-r436-el7-x86_64-bundle.tar

 

https://www.percona.com/downloads/XtraBackup/LATEST/

percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

 

https://www.percona.com/downloads/percona-toolkit/LATEST/

percona-toolkit-3.0.12-1.el7.x86_64.rpm

 

3. 安装PXC和xtrabackup前必要的rpm包（3节点都操作）

yum -y install rsync

yum -y install perl-DBD-mysql

yum -y install perl-Digest-MD5

yum -y install perl-DBI

yum -y install lsof

yum -y install openssl

yum -y install openssl-devel

yum -y install socat

yum -y install perl-IO-Socket-SSL

yum -y install perl-ExtUtils-MakeMaker

下载 libev-4.15-6.el7.x86_64.rpm，qpress-1.1-7.15.x86_64.rpm，

jemalloc-3.6.0-1.el7.x86_64，jemalloc-devel-3.6.0-1.el7.x86_64（用于多线程下内存分配管理）

rpm -ivh libev-4.15-6.el7.x86_64.rpm

rpm -ivh qpress-1.1-7.15.x86_64.rpm --nodeps

rpm包下载地址：http://rpmfind.net/linux/rpm2html/search.php

清理系统自带的mariadb的rpm包

rpm -qa|grep maria

rpm -qa|grep maria|awk '{print "rpm -e ",$1,"--nodeps"}'|sh

4. 安装PXC,和xtrabackup,jemalloc和percona toolkit（3节点都操作）

tar -xvf Percona-XtraDB-Cluster-5.7.23-31.31-r436-el7-x86_64-bundle.tar

 

rpm -ivh Percona-XtraDB-Cluster-client-57-5.7.23-31.31.1.el7.x86_64.rpm

rpm -ivh Percona-XtraDB-Cluster-devel-57-5.7.23-31.31.1.el7.x86_64.rpm

rpm -ivh Percona-XtraDB-Cluster-shared-compat-57-5.7.23-31.31.1.el7.x86_64.rpm

rpm -ivh Percona-XtraDB-Cluster-shared-57-5.7.23-31.31.1.el7.x86_64.rpm

rpm -ivh Percona-XtraDB-Cluster-garbd-57-5.7.23-31.31.1.el7.x86_64.rpm

rpm -ivh Percona-XtraDB-Cluster-server-57-5.7.23-31.31.1.el7.x86_64.rpm

 

rpm -ivh percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

 

rpm -ivh jemalloc-3.6.0-1.el7.x86_64.rpm

rpm -ivh jemalloc-devel-3.6.0-1.el7.x86_64.rpm

 

rpm -ivh percona-toolkit-3.0.12-1.4.noarch.rpm  --nodeps

5. 创建用户和组，设置Huge page（3节点都操作）

groupadd -g 300 mysql

useradd -u 303 -g mysql -d /mysql mysql

innodb_buffer_pool_size+tmp_table_size+key_buffer_size+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)+max_connections*2MB+gcache_size+1G<hugepage

<90%的主机内存

grep Huge /proc/meminfo

Hugepagesize:       2048 kB

编辑/etc/sysctl.conf 添加：

vm.nr_hugepages=hugepage/2048k

vm.hugetlb_shm_group=300 #(mysql groupid)

修改/etc/security/limits.conf增加

mysql         soft    memlock   unlimited

mysql         hard    memlock   unlimited

 

sysctl -p使得hugepage生效，若不生效或者HugePages_Total小于vm.nr_hugepages，尝试重启主机

root@rac3[/soft]#grep Huge /proc/meminfo

AnonHugePages:         0 kB

HugePages_Total:    4210

HugePages_Free:     4183

HugePages_Rsvd:      562

HugePages_Surp:        0

Hugepagesize:       2048 kB

 

6. 创建mysql配置文件（节点1)

 

su - mysql

mkdir bin_log

mkdir log

mkdir relay_log

mkdir data

mkdir tmp

mkdir undo_tbls

 

mysql --help | grep .cnf

 

vi /etc/my.cnf

# The Percona XtraDB Cluster 5.7 configuration file.

# * IMPORTANT: Additional settings that can override those from this file!

#   The files must end with '.cnf', otherwise they'll be ignored.

#   Please make any edits and changes to the appropriate sectional files

#   included below.

#!includedir /etc/my.cnf.d/

#!includedir /etc/percona-xtradb-cluster.conf.d/

[client]

socket=/var/lib/mysql/mysql.sock

[mysqld_safe]

malloc-lib=/usr/lib64/libjemalloc.so #这个模块可以让MySQL在高并发下内存占用更加稳定

[mysqld]

auto_increment_offset=1  #自增长的初始值

auto_increment_increment=3 #自增长的增量

log_timestamps=SYSTEM  #设为操作系统时间戳格式

large_pages     #设置访问内存使用大页

innodb_numa_interleave=1

#为1则mysql进程的numa内存分配策略设置为MPOL_INTERLEAVE，而一旦Innodb #buffer pool分配完毕，则策略#重新设置回MPOL_DEFAULT

thread_handling=pool-of-threads #开启线程池

wsrep_provider_options = "gcs.fc_limit = 256; gcs.fc_factor = 1.0; gcs.fc_master_slave = yes;gcache.size =10G"

explicit_defaults_for_timestamp

#若TIMESTAMP列没有显示的指定not null属性，那么默认该列可以为null，向该列中插入null值时，会直接记录#null，而不是current timestamp

log_bin_trust_function_creators = 1

#如果数据库没有使用主从复制，那么就可以将参数log_bin_trust_function_creators设置为1

transaction-isolation = READ-COMMITTED

#只有在事务提交后，才会对另一个事务产生影响，并且在对表进行修改时，会对表数据行加上行共享锁

server-id=1 ##PXC集群中MySQL实例的唯一ID，不能重复

datadir=/mysql/data

socket=/var/lib/mysql/mysql.sock

log-error=/mysql/log/mysqld.log

pid-file=/var/lib/mysql/mysqld.pid

log-bin=/mysql/bin_log/PXC1-node-1-binlog  #根据集群名称做修改

relay_log=/mysql/relay_log/PXC1-node-1-relaylog  #根据集群名称做修改

slow_query_log_file=/mysql/log/slow.log   #log下来运行的比较慢的sql语句

general_log_file=/mysql/log/general.log

#记录下来所有到达mysql的sql语句，默认关，show variables like 'general_log';set global general_log=on;

log_slow_admin_statements=1  #将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志

slow_query_log=1#开启慢查询记录

long_query_time=2#当查询时间多于设定的阈值时，记录日志

log_slave_updates # 使从服务器把复制的事件记录到自己的二进制日志中

expire_logs_days=7 #距离当前时间正好7天前的二进制文件会被系统自动删除

performance_schema = ON

#通过事件机制利用performance schema引擎将mysql服务的运行时状态采集并存储在performace_schema数据库

character-set-server=utf8

collation-server=utf8_bin

skip-name-resolve #禁用DNS反向解析，大大加快MySQL连接的速度

lower_case_table_names #表名存储在磁盘是小写的，但是比较的时候是不区分大小写

skip-external-locking #多服务器环境最好打开，访问时避免MySQL的外部锁定，减少出错几率增强稳定性

key_buffer_size = 128M #指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能，太大会降低性能

max_allowed_packet = 16M

table_open_cache = 1024

# MySQL打开表，都会读入一些数据到table_open_cache中，当MySQL在缓存中找不到相应信息时，才会读磁盘

table_open_cache_instances=16

sort_buffer_size = 8M

#查询排序时所能使用的缓冲区大小。排序缓冲被用来处理类似 ORDER BY 以及 GROUP BY 队列所引起的排序

net_buffer_length = 8K

#包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节

myisam_sort_buffer_size = 8M

#MyISAM设置恢复表使用的缓冲区,当REPAIR TABLE 或 CREATE INDEX或 ALTER TABLE 排序

max_connections = 10000 #设置过小会报Too many connections，代表允许连接数据库的所有用户的连接数总和

max_user_connections = 2000#代表允许单个用户的连接数最大值，即并发值

query_cache_size = 0

query_cache_type = 0

tmp_table_size = 128M #控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定

tmpdir=/mysql/tmp

max_heap_table_size = 1024M

#用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。

innodb_log_files_in_group = 4 #在日志组中的文件总数.通常来说 2~3 是比较好的.

innodb_log_file_size = 1024M

#事物日志大小.在日志组中每个日志文件的大小，你应该设置日志文件总合大小到你缓冲池大小的5%~100%

innodb_undo_directory=/mysql/undo_tbls

innodb_undo_tablespaces=4 #用于设定创建的undo表空间的个数 undo001—undo004

innodb_undo_log_truncate=on #开启在线回收（收缩）undo log日志文件，支持动态设置

innodb_flush_method= O_DIRECT

#innodb数据文件及redo log的打开、刷写模式，为O_DIRECT会最小化缓冲对io的影响

innodb_buffer_pool_size = 1G #类似sga

innodb_buffer_pool_instances = 8

#设置多个缓冲池，将请求分散处理，比较合理的值为CPU的核心数，innodb_buffer_pool_size设为1G以上才生效

innodb_buffer_pool_chunk_size=128MB

innodb_log_buffer_size = 64M

#MAX(innodb_buffer_pool_chunk_size) = innodb_buffer_pool_size / innodb_buffer_pool_instances

innodb_file_per_table #独立表空间模式，每个数据库的每个表都会生成一个数据空间

innodb_data_file_path=ibdata1:1000M:autoextend

#指定表数据和索引存储的空间，可以是一个或者多个文件，最后一个数据文件8M为单位自动扩充

innodb_flush_log_at_trx_commit = 2

#设置2时在每个事物提交时,日志缓冲被写到日志文件,但不对日志文件做向磁盘刷新的操作,对日志文件每秒向磁盘做#一次刷新操作

sync_binlog = 1 #为了在最大程序上保证复制的InnoDB事务持久性和一致性

#DBA设置的sync_binlog并不是最安全的1，而是100或者是0。牺牲一定的一致性，可以获得更高的并发和性能

binlog-group-commit-sync-delay=10

binlog_group_commit_sync_no_delay_count=5

innodb_lock_wait_timeout = 50

innodb_rollback_on_timeout = ON

innodb_io_capacity = 2000

innodb_read_io_threads = 8

innodb_write_io_threads  = 8

innodb_thread_concurrency = 0

innodb_sync_spin_loops = 200

innodb_spin_wait_delay = 6

innodb_status_file = 1

innodb_max_dirty_pages_pct = 75

innodb_support_xa=1

innodb_doublewrite=1

innodb_checksum_algorithm=crc32

innodb_adaptive_hash_index = 1

innodb_purge_threads=4

innodb_use_native_aio = 1

innodb_autoinc_lock_mode = 2

innodb_change_buffering = all

gtid_mode=ON

enforce_gtid_consistency=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

slave_parallel_type=logical_clock

slave_parallel_workers=4

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

#指定Galera库的路径和文件名

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains IPs of nodes

#If no IP is found, this implies that a new cluster needs to be created,

#这个参数通过一种特殊的 gcomm:// 协议地址来访问集群中的节点

wsrep_cluster_address=gcomm://10.45.7.128,10.45.7.129,10.45.53.31 #填写集群主机IP

# In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

# 指定可并行运行的从节点线程数量

wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

# Node IP address

wsrep_node_address=10.45.7.128  #填写本机ip地址

# Cluster name

wsrep_cluster_name=PXC1 #填写集群名称

#If wsrep_node_name is not specified,  then system hostname will be used

wsrep_node_name=rac3  #填写本机主机名

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER

pxc_strict_mode=ENFORCING

# SST method，State Snapshot Transfer，是指数据库一个备份全量文件的传输，定义了一种执行初始状态传输的方法, 用来保证当有新的#节点要加入集群时，新节点会被初始化并和集群中其他已知节点同步

wsrep_sst_method=xtrabackup-v2

#Authentication for SST method

wsrep_sst_auth="sstuser:sstuser"

7.  启动mysql（节点1）并用xtrabackup全备份

systemctl start mysql@bootstrap.service

修改mysql密码，初始密码在/etc/mysql/mysql.log：A temporary password is generated for root@localhost:Bsss#H*_K1V-

mysql -uroot -p

alter user root@'localhost' identified by '123456';

CREATE USER 'root'@'%' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION

 

建SST用户：

CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser';

GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';

 

测试xtrabackup全备：

xtrabackup --defaults-file=/etc/my.cnf -uroot -p123456  --target-dir=/mysql/backup --backup --binlog-info=on --galera-info

 

利用percona-toolkit分析PXC环境

pt-mysql-summary --user=root --password='123456'

8. 配置mysql（节点2，节点3）

（1）节点1关闭PXC实例

systemctl stop mysql@bootstrap.service

（2）将节点1配置文件和 /mysql下的文件拷贝到节点2，3，大约16G

scp /etc/my.cnf  10.45.7.129:/etc/

scp /etc/my.cnf  10.45.53.31:/etc/

su - mysql

scp -r * mysql@10.45.7.129:~

scp -r * mysql@10.45.53.31:~

（3）修改配置文件

节点2：

log-bin=/mysql/bin_log/PXC1-node-2-binlog

relay_log=/mysql/relay_log/PXC1-node-2-relaylog

wsrep_node_address=10.45.7.129  #填写本机ip地址

wsrep_node_name=rac4  #填写本机主机名

 

节点3：

log-bin=/mysql/bin_log/PXC1-node-3-binlog

relay_log=/mysql/relay_log/PXC1-node-3-relaylog

wsrep_node_address=10.45.53.31  #填写本机ip地址

wsrep_node_name=rac2  #填写本机主机名

 

systemctl start mysql

9. 测试是否同步

show global status like 'wsrep%';

任意节点：create database test;

CREATE TABLE test.test (id char(10),PRIMARY KEY (id));

insert into test.test values(1);---不用commit，mysql默认自动commit

show variables like '%commit%';

 

10. 重启mysql服务的几种情况

1. PXC某节点mysql停止

若PXC某节点mysql突然停止，而其他节点运行正常，不管此节点是以何种方式启动的（systemctl start mysql@bootstrap.service或systemctl start mysql），重启此节点均用systemctl start mysql

1. PXC集群三节点全部停止

三节点停止后，grastate.dat中seqno由-1变为有效值，有效值最大的哪个safe_to_bootstrap为1，此时这个节点启动命令如下：

service mysql@bootstrap stop

另两个节点正常启动

 

11. 节点1和节点2配置keepalived实现vip浮动

yum install -y keepalive*

cd /etc/keepalived

节点1

vi 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 192.168.200.1

 smtp_connect_timeout 30

 router_id rac3

}

vrrp_script check_apps {

  script "/etc/keepalived/ha_check.sh"

  interval 3

}

vrrp_instance vip1 {

  state MASTER

  interface ens192

  virtual_router_id 51

  priority 100

  advert_int 1

  authentication {

      auth_type PASS

      auth_pass 1111

  }

  virtual_ipaddress {

      10.45.7.126/24 dev ens192 scope global label ens192:1

  }

  track_script {

          check_apps

      }

   nopreempt

}

节点2：

vi 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 192.168.200.1

 smtp_connect_timeout 30

 router_id rac4

}

vrrp_script check_apps {

  script "/etc/keepalived/ha_check.sh"

  interval 3

}

vrrp_instance vip1 {

  state BACKUP

   interface ens192

  virtual_router_id 51

  priority 99

  advert_int 1

  authentication {

      auth_type PASS

      auth_pass 1111

  }

  virtual_ipaddress {

      10.45.7.126/24 dev ens192 scope global label ens192:1

  }

  track_script {

          check_apps

      }

   nopreempt

}

 

ha_check.sh实现vip切换

cat ha_check.sh

#!/bin/bash

counter=$(ps -C mysqld --no-heading|wc -l)

if [ "${counter}" = "0" ]; then

  systemctl stop keepalived

  echo `date`,keepalived killed>>/var/log/keepalived.log

fi

flag=$(mysql -uroot -p123456  -AEe "show global status like 'wsrep_cluster_status'"|grep Value|awk -F':' '{print $2}')

if [ ${flag} != Primary ]; then

  systemctl stop keepalived

  echo `date`,keepalived killed>>/var/log/keepalived.log

fi

11.测试keepalived是否能实现vip漂移

service keepalived start

由于节点1的priority 高于节点2，vip优先在节点1上

节点1：

systemctl stop mysql@bootstrap

然后再去查节点1和节点2，发现vip实现了漂移

节点1：

节点2：

此时若想将节点1上的mysql进程启动，应执行

systemctl start mysql而不是systemctl stop mysql@bootstrap（若执行会报错）

systemctl start  keepalived

此时vip会再次漂移到节点1上