首页 > 其他分享 >Percona XtraDB Cluster 集群安装(PXC)

Percona XtraDB Cluster 集群安装(PXC)

时间：2024-04-17 14:23:27浏览次数：28  
标签：log buffer monitor Cluster Percona innodb mysql XtraDB size

Percona XtraDB Cluster 集群安装

安装环境：

CentOS Linux release 7.6.1810 (Core)
16核 64G

在线安装
离线安装
配置文件

在线安装

添加仓库内容（在线安装)

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y

安装：

yum install Percona-XtraDB-Cluster-57 -y

离线安装

下载离线安装包：

#下载地址
https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/LATEST/
综合包
Percona-XtraDB-Cluster-5.7.31-31.45-r10-el7-x86_64-bundle.tar
或：
Percona-XtraDB-Cluster-devel-57
Percona-XtraDB-Cluster-test-57
Percona-XtraDB-Cluster-debuginfo-57
Percona-XtraDB-Cluster-galera-3-debuginfo
Percona-XtraDB-Cluster-shared-57
....

安装依赖：

# 安装依赖
yum install https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
# 安装备份工具XtraBackup
yum localinstall -y percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm

本地安装：

tar xvf Percona-XtraDB-Cluster-5.7.31-31.45-r10-el7-x86_64-bundle.tar && yum localinstall -y Percona*.rpm

systemctl stop firewalld && systemctl disable firewalld && sed s/^SELINUX=.*$/SELINUX=disabled/ -i /etc/selinux/config && setenforce 0

修改配置文件

cd /etc/percona-xtradb-cluster.conf.d

mysqld.cnf

各节点修改

prompt
server-id

[client]

port    = 3306
socket  = /data/mysql/mysql.sock

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

[mysqld]
server-id=2123

user    = mysql
port    = 3306

datadir = /data/mysql
socket  = /data/mysql/mysql.sock
pid-file = /data/mysql/mysqldb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
lower_case_table_names=1

#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"

open_files_limit = 65535
back_log = 1024
max_connections = 2000
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M

sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 3000
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1

log-bin = /data/mysql/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G

#注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项
expire_logs_days = 30

master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
#innodb_sync_spin_loops = 100
#innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 45875M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/undolog
innodb_undo_tablespaces = 95

# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_undo_log_truncate = 1

#注意：MySQL 8.0.16开始删除该选项
internal_tmp_disk_storage_engine = InnoDB

# some var for MySQL 5.7
innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
query_cache_size = 0
query_cache_type = 0
innodb_undo_logs = 128

innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 1

innodb_sort_buffer_size = 67108864

#performance_schema
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
#performance_schema_instrument = '%lock%=on'

#innodb monitor
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"

mysqld_safe.cnf

[mysqld_safe]
pid-file = /data/mysql/mysqldb.pid
socket   = /data/mysql/mysql.sock
nice     = 0

wsrep.cnf

各节点修改

wsrep_cluster_address
wsrep_node_address
wsrep_node_name

[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://172.34.21.23,172.34.21.28,172.34.21.61,172.34.21.92
binlog_format=ROW
default_storage_engine=InnoDB
#cpu的1到1.5倍
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=172.34.21.23
wsrep_cluster_name=pxc-cluster
wsrep_node_name=mysql-23
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser1:s3cretPass2"

启动初始化

启动第一个节点

# 启动
systemctl start mysql@bootstrap
# 查看密码
grep password /data/mysql/error.log
# 登录mysql
mysql -uroot -p
# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Zhishen@2020';
# 创建SST账号
create user 'sstuser1'@'localhost' identified by 's3cretPass2';
grant reload, lock tables, replication client, process on *.* to 'sstuser1'@'localhost';

启动剩余节点

systemctl start mysql

创建其他账户

CREATE USER 'zhrc'@'%' IDENTIFIED BY 'Zhrc@2021';
GRANT ALL ON zhrc.* TO 'zhrc'@'%' IDENTIFIED BY 'Zhrc@2021' WITH GRANT OPTION;

CREATE USER 'proxysql'@'172.34.23.%' IDENTIFIED BY 'ProxySQL@123';
GRANT USAGE ON *.* TO 'proxysql'@'172.34.23.%';

create user 'monitor'@'127.0.0.1' identified by 'Monitor@123456';
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'monitor'@'127.0.0.1';

验证集群同步

标签：log,buffer,monitor,Cluster,Percona,innodb,mysql,XtraDB,size
From： https://www.cnblogs.com/wszzn/p/18140618

相关文章

  • Evolutionary many-objective optimization algorithm based on angle and clustering
    Evolutionarymany-objectiveoptimizationalgorithmbasedonangleandclustering本文的工作本文提出了一种新的MaOEA，它使用锐角作为相似度量。通过聚类方法，最终将种群划分为若干个聚类，每个聚类中仅选择一个个体，以保持环境选择的趋同性和多样性。据我们所知，我们首先尝试利......
  • Redis--主从，哨兵，cluster模式介绍
    redis提供了三种模式来构建高性能、高可用性和可扩展性的数据存储集群，分别是主从复制模式（Master-SlaveReplication），哨兵模式（SentinelMode），集群模式（ClusterMode）。特性/配置主从哨兵cluster主要目的数据备份与读写分离高可用性与故障自动切换高并发与数据分散处理......
  • 将service类型由"ClusterIP"改为"NodePort"无法使有nodeip+端口访问服务解决方法
    1.背景.我打算在K8S集群部署一套Prometheus监控系统，以监控系统和各应用的各项指标，如资源、性能及自定义监控指标，具体部署方案和细节就不和大家详细说了，后面再和大家分享，这次先说我遇到问题。在Prometheus各组件都部署成功时候，我发现grafana的service的类型为"ClusterIP"，这意味......
  • 使用Docker搭建Redis Cluster集群
    Cluster模式是Redis的一种高级集群模式，它通过数据分片和分布式存储实现了负载均衡和高可用性。在Cluster模式下，Redis将所有的键值对数据分散在多个节点上。每个节点负责一部分数据，称为槽位。通过对数据的分片，Cluster模式可以突破单节点的内存限制，实现更大规模的数据存储。Redis......
  • 故障转移群集（Failover Cluster Instances）和AlwaysOn是SQL Server中两种不同的高可用性
    故障转移群集（FailoverClusterInstances）和AlwaysOn是SQLServer中两种不同的高可用性解决方案。它们在实现高可用性的方式上有一些显著的区别：故障转移群集（FailoverClusterInstances）：故障转移群集是一种基于WindowsServer故障转移群集技术的解决方案，它使用共享存储并在主......
  • openGauss Cluster Manager RTO Test
    一、环境介绍软件环境类别 版本 下载链接 备注OS openEuler20.03(LTS) https://repo.openeuler.org/openEuler-20.03-LTS/ISO/aarch64/openEuler-20.03-LTS-aarch64-dvd.iso 操作系统BenchmarkSQL 5.0 https://sourceforge.net/projects/benchmarksql/files/latest/downloa......
  • Centos 7.5基于MySQL 5.7的 InnoDB Cluster 多节点高可用集群环境部署记录【转】
    前面给大家介绍过：MySQL高可用方案选型解析，今天我将详细的为大家介绍Centos7.5基于MySQL5.7的InnoDBCluster多节点高可用集群环境部署的相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！MySQLInnoDBCluster介绍MySQL的高可用架构无论是社区还是官方，一......
  • BcLinux-Redis-集群(cluster)模式安装配置
    IP：192.168.0.28081、8082IP：192.168.0.3 8081、8082IP：192.168.0.4 8081、80821、三个节点同样操作[root@server-1setup]#yuminstalltcl或者：wgethttp://downloads.sourceforge.net/tcl/tcl8.6.1-src.tar.gztarxzvftcl8.6.1-src.tar.gz-C/home/local/cd/home/loc......
  • 安装"DESeq2", "edgeR", "limma", "clusterProfiler"几个R包
    安装"DESeq2","edgeR","limma","clusterProfiler"bioPackages=c("DESeq2","edgeR","limma","clusterProfiler")#设置镜像local({r<-getOption("repos");r["......
  • redis cluster 迁移
    一、redis迁移概述。生产环境Redis当前部署环境为六台16核126G服务器，服务器资源整体配置较高，使用率较低（具体使用率参见表一）。为了更好地提升资源使用率，优化成本，故对rediscluster集群做迁移和合并。Rediscluster集群的迁移步骤为：首先安装redis集群，再次迁移redis数据，最后做业务切......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99