MySQL基于PXC方案实现数据库HA

发表于 | 更新于: | 阅读次数:
字数统计: 2.4k

说明

仅记录安装部署过程

不保证ctrl+c和ctrl+v可以跑起来，自己判断哪些要改的！

操作系统使用的CentOS-7.6.1810 x86_64

Percona XtraDB Cluster版本使用57-5.7.25-31.35.1.el7

【根据自己的环境灵活调整，生产环境请务必提供独立数据盘！】

集群架构模型

多主复制模型

环境准备

服务器配置

主机名IP地址
db1172.16.80.201
db2172.16.80.202
db3172.16.80.203

关闭SELINUX

1
2
sed -i 's,SELINUX=enforcing,SELINUX=disabled,' /etc/selinux/config
setenforce 0

关闭防火墙

1
2
3
systemctl stop firewalld
systemctl disable firewalld
systemctl mask firewalld

添加sysctl参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
cat > /etc/sysctl.d/99-centos.conf <<EOF 
# 最大文件句柄数
fs.file-max = 1048576
# 最大文件打开数
fs.nr_open = 1048576
# 单个共享内存段的最大值，这里设置为物理内存大小的一半
# 计算方式，以2G内存为例
# 2048 / 2 * 1024 * 1024 = 1073741824
kernel.shmmax = 1073741824
# 最大的TCP数据接收窗口（字节）
net.core.rmem_max = 4194304
# 最大的TCP数据发送窗口（字节）
net.core.wmem_max = 4194304
# 默认的TCP数据接收窗口大小（字节）
net.core.rmem_default = 262144
# 默认的TCP数据发送窗口大小（字节）
net.core.wmem_default = 262144
# 二层的网桥在转发包时也会被iptables的FORWARD规则所过滤
net.bridge.bridge-nf-call-arptables=1
net.bridge.bridge-nf-call-iptables=1
net.bridge.bridge-nf-call-ip6tables=1
# 关闭严格校验数据包的反向路径
net.ipv4.conf.default.rp_filter=0
net.ipv4.conf.all.rp_filter=0
# 修改动态NAT跟踪记录参数
net.netfilter.nf_conntrack_max = 655350
net.netfilter.nf_conntrack_tcp_timeout_established = 1200
# 加快系统关闭处于 FIN_WAIT2 状态的 TCP 连接
net.ipv4.tcp_fin_timeout = 30
# 系统中处于 SYN_RECV 状态的 TCP 连接数量
net.ipv4.tcp_max_syn_backlog = 8192
# 内核中管理 TIME_WAIT 状态的数量
net.ipv4.tcp_max_tw_buckets = 5000
# 端口最大的监听队列的长度
net.core.somaxconn=4096
# 打开ipv4数据包转发
net.ipv4.ip_forward=1
# 允许应用程序能够绑定到不属于本地网卡的地址
net.ipv4.ip_nonlocal_bind=1 
# 内存耗尽才使用swap分区
vm.swappiness = 0
vm.panic_on_oom = 0
# 配置主动发起连接时，端口范围，默认值32768-60000
net.ipv4.ip_local_port_range = 9000 65535
# 当系统pagecache脏页达到系统内存dirty_ratio的百分比值时，会阻塞新的写请求
# 直到内存脏页落盘
# 默认值为30
vm.dirty_ratio = 80
EOF

limits参数

1
2
3
4
5
6
7
8
9
10
cat > /etc/security/limits.d/99-mysql.conf <<EOF
* soft nofile  655360
* hard nofile  655360
* soft nproc   655360
* hard nproc   655360
* soft stack   unlimited
* hard stack   unlimited
* soft memlock 250000000
* hard memlock 250000000
EOF

更新系统软件

1
yum update -y

重启服务器

可选

1
reboot

安装部署

获取安装包

可以通过配置YUM源或者直接下载RPM包安装

配置YUM源

1
2
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y Percona-XtraDB-Cluster-full-57

下载安装包

  • 官方下载页面链接在这里
1
2
3
4
5
6
7
8
9
10
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.25-31.35/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-devel-57-5.7.25-31.35.1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.25-31.35/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-57-5.7.25-31.35.1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.25-31.35/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-client-57-5.7.25-31.35.1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.25-31.35/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-full-57-5.7.25-31.35.1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.25-31.35/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-test-57-5.7.25-31.35.1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.25-31.35/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-server-57-5.7.25-31.35.1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.25-31.35/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-shared-compat-57-5.7.25-31.35.1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.25-31.35/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-shared-57-5.7.25-31.35.1.el7.x86_64.rpm
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm
wget http://repo.percona.com/percona/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
  • 手动安装YUM包
1
yum localinstall -y *rpm

初始化数据库

注意！

这里的初始化操作可以在集群任意节点进行，这里为了方便，在db1上进行操作

1
systemctl start mysql.service

启动完成后之后，需要获取临时密码

1
grep 'temporary password' /var/log/mysqld.log

使用mysql_secure_installation初始化MySQL数据库

  • 修改root密码
  • 修改密码复杂度要求
  • 禁止root远程登录
  • 删除匿名用户和测试数据
  • 刷新权限
1
mysql_secure_install

登录数据库

1
mysql -u root -p

创建SST用户并授权

1
2
3
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;

关闭数据库

1
systemctl stop mysql.service

配置Galera

配置过程参照官方文档

额外定义一些参数

节点1

清理一下配置文件

1
2
find /etc/my.cnf.d/ -type f -name *cnf
find /etc/percona-xtradb-cluster.conf.d/ -type f -name *cnf

将配置内容合并

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
# Template my.cnf for PXC
# Edit to your requirements.
[client]
# 定义客户端从哪里获取mysql.sock的路径
socket=/var/run/mysqld/mysql.sock

[mysql]
prompt="\u@db1 \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
# 监听地址
bind-address = 0.0.0.0
# 监听端口
port = 3306
# 默认数据库引擎
default-storage-engine = innodb
# 字符集
character-set-server = utf8mb4
# 排序规则
collation-server = utf8mb4_general_ci
# 最大连接数
max_connections = 1000
# 限制打开文件数
open_files_limit = 65535
# server-id每个服务器唯一即可，一般用IP末位数字
server-id = 201
# 默认时区
default-time-zone = '+8:00'
# 设置数据库事务隔离级别
transaction_isolation = REPEATABLE-READ
# 配置超时时间
interactive_timeout = 600
wait_timeout = 600
connect_timeout = 20
lock_wait_timeout = 3600
# 限制数据库服务器接收数据包的大小
max_allowed_packet = 64M
# 配置排序和join的buffer大小
sort_buffer_size = 4M
join_buffer_size = 4M
# 这里定义数据目录和日志目录
datadir = /var/lib/mysql/
socket = /var/run/mysql/mysql.sock
log-error = /var/log/mysql/error.log
pid-file = /var/run/mysql/mysqld.pid
log-bin = /var/lib/mysql/mybinlog
# 配置binlog
binlog_format = ROW
expire_logs_days = 7
# 开启binlog校验功能
binlog_checksum = 1
binlog_cache_size = 4M
# 每1次提交变更后立刻将binlog落盘
sync_binlog = 1
# 配置performance_schema
performance_schema = on
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
skip-external-locking = on
# 禁用域名解析
skip-name-resolve = on
# 禁用软链接
symbolic-links = 0
back_log = 1024
# 慢日志查询配置
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysqld/slow.log
# 配置每个EVENT都要执行刷盘操作
sync_master_info = 1
sync_relay_log_info = 1
sync_relay_log = 1
log_slave_updates = on
relay_log_recovery = 1
relay_log_purge = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
# InnoDB引擎配置
innodb_file_per_table = on
innodb_checksums = 1
innodb_checksum_algorithm = crc32
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 0
innodb_stats_on_metadata = 0
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_flush_sync = 0
innodb_flush_neighbors = 0
# 每个事务提交后立即刷新binlog文件
innodb_flush_log_at_trx_commit = 1
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
# SQL mode配置，这里是默认值
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# GTID配置
#gtid_mode = on
#enforce_gtid_consistency = 1
# wsrep配置
innodb_autoinc_lock_mode = 2
wsrep_cluster_name = pxc-cluster
wsrep_cluster_address = gcomm://172.16.80.201:4567,172.16.80.202:4567,172.16.80.203:4567
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_node_name = db1
wsrep_node_address = 172.16.80.201:4567
wsrep_sst_receive_address = 172.16.80.201:4444
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sstuser:sstuser_password
wsrep_slave_threads = 4
# 禁用Percona XtraDB Cluster中实验特性和不受支持的功能
pxc_strict_mode = ENFORCING

[mysqldump]
quick
max_allowed_packet = 32M

节点2

配置过程跟节点1大体上是一样，区别在于要修改某些配置项

1
2
3
4
5
6
7
[mysql]
prompt="\u@db2 \R:\m:\s [\d]> "
[mysqld]
server-id = 202
wsrep_node_name = db2
wsrep_node_address = 172.16.80.202:4567
wsrep_sst_receive_address = 172.16.80.202:4444

节点3

配置过程跟节点1一样，区别在于要修改某些配置项

1
2
3
4
5
6
7
[mysql]
prompt="\u@db3 \R:\m:\s [\d]> "
[mysqld]
server-id = 203
wsrep_node_name = db3
wsrep_node_address = 172.16.80.203:4567
wsrep_sst_receive_address = 172.16.80.203:4444

初始化PXC集群

节点1

作为集群第一个节点引导启动集群，此节点必须包含完整的数据！否则会出现数据不一致

这里以节点1作为bootstrap node

1
systemctl start mysql@bootstrap.service

启动完成之后，可以查看日志

1
tail -f /var/log/mysql/error.log

在看到ready for connection就可以启动其他节点了

1
YYYY-MM-DDTHH:mm:ss.534955Z 0 [Note] /usr/sbin/mysqld: ready for connections.

节点2

启动数据库

1
systemctl start mysql.service

节点3

启动数据库

1
systemctl start mysql.service

注意

如果提示WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.

那么可以将配置文件里面的wsrep_sst_method=xtrabackup-v2修改为wsrep_sst_method=rsync

然后再重新初始化集群

检查集群启动状态

登录数据库

1
mysql -u root -p

检查wsrep节点数量

1
2
3
4
5
6
7
8
9
10
show global status like 'wsrep_cluster_size';


# 示例输出
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

检查wsrep集群信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
show global status like 'wsrep%';


# 示例输出
+----------------------------------+----------------------------------------------------------+
| Variable_name                    | Value                                                    |
+----------------------------------+----------------------------------------------------------+
| wsrep_local_state_uuid           | 77463f8f-82bf-11e9-b41f-92927bef6908                     |
| wsrep_protocol_version           | 9                                                        |
| wsrep_last_applied               | 25                                                       |
| wsrep_last_committed             | 25                                                       |
| wsrep_replicated                 | 14                                                       |
| wsrep_replicated_bytes           | 3640                                                     |
| wsrep_repl_keys                  | 15                                                       |
| wsrep_repl_keys_bytes            | 456                                                      |
| wsrep_repl_data_bytes            | 2237                                                     |
| wsrep_repl_other_bytes           | 0                                                        |
| wsrep_received                   | 6                                                        |
| wsrep_received_bytes             | 1225                                                     |
| wsrep_local_commits              | 0                                                        |
| wsrep_local_cert_failures        | 0                                                        |
| wsrep_local_replays              | 0                                                        |
| wsrep_local_send_queue           | 0                                                        |
| wsrep_local_send_queue_max       | 1                                                        |
| wsrep_local_send_queue_min       | 0                                                        |
| wsrep_local_send_queue_avg       | 0.000000                                                 |
| wsrep_local_recv_queue           | 0                                                        |
| wsrep_local_recv_queue_max       | 1                                                        |
| wsrep_local_recv_queue_min       | 0                                                        |
| wsrep_local_recv_queue_avg       | 0.000000                                                 |
| wsrep_local_cached_downto        | 12                                                       |
| wsrep_flow_control_paused_ns     | 0                                                        |
| wsrep_flow_control_paused        | 0.000000                                                 |
| wsrep_flow_control_sent          | 0                                                        |
| wsrep_flow_control_recv          | 0                                                        |
| wsrep_flow_control_interval      | [ 173, 173 ]                                             |
| wsrep_flow_control_interval_low  | 173                                                      |
| wsrep_flow_control_interval_high | 173                                                      |
| wsrep_flow_control_status        | OFF                                                      |
| wsrep_cert_deps_distance         | 1.000000                                                 |
| wsrep_apply_oooe                 | 0.000000                                                 |
| wsrep_apply_oool                 | 0.000000                                                 |
| wsrep_apply_window               | 1.000000                                                 |
| wsrep_commit_oooe                | 0.000000                                                 |
| wsrep_commit_oool                | 0.000000                                                 |
| wsrep_commit_window              | 1.000000                                                 |
| wsrep_local_state                | 4                                                        |
| wsrep_local_state_comment        | Synced                                                   |
| wsrep_cert_index_size            | 3                                                        |
| wsrep_cert_bucket_count          | 22                                                       |
| wsrep_gcache_pool_size           | 5520                                                     |
| wsrep_causal_reads               | 0                                                        |
| wsrep_cert_interval              | 0.000000                                                 |
| wsrep_open_transactions          | 0                                                        |
| wsrep_open_connections           | 0                                                        |
| wsrep_ist_receive_status         |                                                          |
| wsrep_ist_receive_seqno_start    | 0                                                        |
| wsrep_ist_receive_seqno_current  | 0                                                        |
| wsrep_ist_receive_seqno_end      | 0                                                        |
| wsrep_incoming_addresses         | 172.16.80.201:3306,172.16.80.202:3306,172.16.80.203:3306 |
| wsrep_cluster_weight             | 3                                                        |
| wsrep_desync_count               | 0                                                        |
| wsrep_evs_delayed                |                                                          |
| wsrep_evs_evict_list             |                                                          |
| wsrep_evs_repl_latency           | 0/0/0/0/0                                                |
| wsrep_evs_state                  | OPERATIONAL                                              |
| wsrep_gcomm_uuid                 | 5ed853a2-834a-11e9-a850-17ef655007c8                     |
| wsrep_cluster_conf_id            | 11                                                       |
| wsrep_cluster_size               | 3                                                        |
| wsrep_cluster_state_uuid         | 77463f8f-82bf-11e9-b41f-92927bef6908                     |
| wsrep_cluster_status             | Primary                                                  |
| wsrep_connected                  | ON                                                       |
| wsrep_local_bf_aborts            | 0                                                        |
| wsrep_local_index                | 0                                                        |
| wsrep_provider_name              | Galera                                                   |
| wsrep_provider_vendor            | Codership Oy <info@codership.com>                        |
| wsrep_provider_version           | 3.35(rddf9876)                                           |
| wsrep_ready                      | ON                                                       |
+----------------------------------+----------------------------------------------------------+
71 rows in set (0.00 sec)

简单验证集群

在节点1创建数据库

1
mysql@db1> CREATE DATABASE percona_test;

在节点2上创建表

1
2
mysql@db2> USE percona_test;
mysql@db2> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));

在节点2上插入数据

1
mysql@db2> INSERT INTO percona_test.example VALUES (2, 'db2');

在节点3上查数据

1
2
3
4
5
6
7
mysql@db3> SELECT * FROM percona_test.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       2 | db2       |
+---------+-----------+
1 row in set (0.00 sec)

集群维护操作

集群整体关机

  • 按顺序逐台关停，例如db1→db2→db3

集群整体开机

  • 启动时，则按照db3→db2→db1
    • 其中db3启动时，需要作为bootstrap node引导集群systemctl start mysql@bootstrap.service

集群少数节点维护

  • PXC集群能容忍少数节点离线而不影响集群数据库服务
  • 这里的少数节点是指少于50%，即< 50%
  • 少数节点重新上线之后，会自动完成数据同步，在完成同步之后，wsrep状态被设置为true，才对外提供服务