Percona XtraDB Cluster安装部署手册_MySQL
Percona XtraDB Cluster安装部署手册
编写此文档，供PerconaXtraDB Cluster部署时使用。
系统维护人员及实施人员。
通过阅读该手册，让读者明确PerconaXtraDB Cluster的安装、配置和维护情况，为后续数据库运维工作提供指导。
应用部署方案
环境准备
服务器列表
序号 |
IP |
用途 |
HOSTNAME |
操作系统 |
1 |
192.168.0.7 |
Percona XtraDB Cluster |
RedHat 6.3 |
|
2 |
192.168.0.9 |
Percona XtraDB Cluster |
RedHat 6.3 |
|
3 |
192.168.0.15 |
LVS+Keepalived |
RedHat 5.6 |
|
4 |
192.168.0.17 |
LVS+Keepalived |
RedHat 5.6 |
列表
序号 |
名称 |
IP |
1 |
LVS-DR-VIP |
192.168.0.110 |
2 |
LVS-DR-Master |
192.168.0.15 |
3 |
LVS-DR-BACKUP |
192.168.0.17 |
4 |
MySQL1-Realserver |
192.168.0.7 |
5 |
MySQL2-Realserver |
192.168.0.9 |
6 |
GateWay |
192.168.0.1 |
配置网易的YUM源
1. 导入证书（证书的位置http://mirrors.163.com/centos/）
rpm -importhttp://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-5
2. 使用网易的源
cd /etc/yum.repos.d/
wgethttp://mirrors.163.com/.help/CentOS5-Base-163.repo
3. 修改文件（把所有$releasever替换成5，保存）
vim CentOS5-Base-163.repo
4. 更新yum
yum -y update
LVS安装
1. 安装
yum install ipvsadm –y
Installing : ipvsadm-1.25-9.el6.i686 1/1
Installed:
ipvsadm.i686 0:1.25-9.el6
Complete!
2. Ipvsadm命令参数
-C 清除表中所有的记录
ipvsadm -A -t 192.168.3.187:80 -s rr
-A --add-service在服务器列表中新添加一条新的虚拟服务器记录
-t 表示为tcp服务
-u 表示为udp服务
-s --scheduler 使用的调度算法， rr | wrr | lc | wlc | lblb |lblcr | dh | sh | sed | nq 默认调度算法是 wlc
ipvsadm -a -t 192.168.3.187:80 -r 192.168.200.10:80 -m-w 1
-a --add-server 在服务器表中添加一条新的真实主机记录
-t --tcp-service 说明虚拟服务器提供tcp服务
-u --udp-service 说明虚拟服务器提供udp服务
-r --real-server 真实服务器地址
-m --masquerading 指定LVS工作模式为NAT模式
-w --weight 真实服务器的权值
-g --gatewaying 指定LVS工作模式为直接路由器模式（也是LVS默认的模式）
-i --ipip 指定LVS的工作模式为隧道模式
-p 会话保持时间，定义流量呗转到同一个realserver的会话存留时间
调度算法
rr 轮询 round robin, wrr 加强轮询 weighted round robin，
新的请求被轮流分配到RealServer上，它假设服务器处理性能都相同，不管服务器当前的连接数和响应速度，不适合服务器性能不同的集群，这会导致服务器间的负载不平衡
dh 目的地址散列调度destination hashing
Keepalived安装
1. gcc
安装命令：yum install gcc gcc-c++
2. openssl-devel ,openssl
安装命令：yum install openssl-developenssl
3. 源码内核的
安装命令：yum install kernel-devel kernel
建立软连接：ln -s /usr/src/kernels/内核名称/ /usr/src/linux
4. popt-devel,libnl-devel
安装命令：yum install popt-devellibnl-devel
5. 安装keepalived
#wgethttp://www.keepalived.org/software/keepalived-1.2.12.tar.gz
#tar xvzf keepalived-1.2.12.tar.gz
#cd keepalived-1.2.12
#uname –r
# ln -sv /usr/src/kernels/`uname-r`-`uname -m`/ /usr/src/linux # 必须做不然，编译会出错
#./configure --sysconf=/usr/local/keepalived--prefix=/usr/local/keepalived
# make && make install
#cp/usr/local/keepalived/sbin/keepalived /usr/sbin/
#cp/usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
#cp /usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/init.d/
#mkdir /etc/keepalived
#vi/etc/keepalived/keepalived.conf
#chkconfig --add keepalived
#chkconfig keepalived on #做成系统服务
#service keepalivedstart|stop|status
配置
1. 主服务器配置keepalived.conf
global_defs{
notification_email {
duhj@c-sunrise.com
}
notification_email_from duhj@c-sunrise.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id mysql-ha
}
vrrp_instanceVI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt #不抢占,只在priority高的节点上设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.110
}
}
virtual_server192.168.0.110 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.0.7 3306
weight 3
# notify_down /root/sh/mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
real_server 192.168.0.9 3306
weight 3
# notify_down /root/sh/mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
2. 从服务器配置/etc/keepalived/keepalived.conf
global_defs {
notification_email {
duhj@c-sunrise.com
}
notification_email_from duhj@c-sunrise.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id mysql-ha
}
vrrp_instanceVI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 88
advert_int 1
nopreempt #不抢占,只在priority高的节点上设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.110
}
}
virtual_server192.168.0.110 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocolTCP
real_server192.168.0.7 3306
weight 3
#notify_down /root/sh/mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
real_server 192.168.0.9 3306
weight 3
# notify_down /root/sh/mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}s
Percona XtraDB Cluster安装
安装说明
安装准备
Ø 服务器版本查看
# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)
Ø 新建目录：
#mkdir -p /softwares
Ø 下载RPM包
l 下载XTRADB Cluster包
wget http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-56-5.6.15-25.5.759.rhel5.x86_64.rpm
wgethttp://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-56-debuginfo-5.6.15-25.5.759.rhel5.x86_64.rpm
wgethttp://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel6/x86_64/Percona-XtraDB-Cluster-client-56-5.6.15-25.5.759.rhel6.x86_64.rpm
wget
http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-devel-56-5.6.15-25.5.759.rhel5.x86_64.rpm
wgethttp://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-full-56-5.6.15-25.5.759.rhel5.x86_64.rpm
wgethttp://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-galera-3-3.4-1.213.rhel5.x86_64.rpm
wget
http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-galera-3-debuginfo-3.4-1.213.rhel5.x86_64.rpm
wget
http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-server-56-5.6.15-25.5.759.rhel5.x86_64.rpm
wget
http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-shared-56-5.6.15-25.5.759.rhel5.x86_64.rpm
wget
http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-test-56-5.6.15-25.5.759.rhel5.x86_64.rpm
l 下载XTRABACKUP包
wgethttp://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/redhat/5/x86_64/percona-xtrabackup-2.2.3-4982.el5.x86_64.rpm
wget
http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/redhat/5/x86_64/percona-xtrabackup-debuginfo-2.2.3-4982.el5.x86_64.rpm
wget
ttp://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/redhat/5/x86_64/percona-xtrabackup-test-2.2.3-4982.el5.x86_64.rpm
Ø 安装依赖包
l 安装包准备
安装光盘package目录下：perl-DBD-MySQL-4.013-3.el6.x86_64.rpm
wgethttp://repo.webtatic.com/yum/el6/x86_64/libmysqlclient16-5.1.69-1.w6.x86_64.rpm
wget http://apt.sw.be/redhat/el6/en/x86_64/rpmforge/RPMS/socat-1.7.2.4-1.el6.rf.x86_64.rpm
wget
http://mirror.centos.org/centos/6/os/x86_64/Packages/nc-1.84-22.el6.x86_64.rpm
l 安装
rpm -ivhlibmysqlclient16-5.1.69-1.w6.x86_64.rpm
rpm -ivhperl-DBD-MySQL-4.013-3.el6.x86_64.rpm socat-1.7.2.4-1.el6.rf.x86_64.rpmnc-1.84-22.el6.x86_64.rpm
Ø 删除原有软件包
rpm -qa | grep mysql | xargs sudo rpm -e –nodeps
安装Percona XtraDB Cluster
Ø 安装percona-xtrabackup
rpm -ivhpercona-xtrabackup-2.1.9-744.rhel6.x86_64.rpm
Ø 安装Percona-XtraDB-Cluster-galera
rpm -ivhPercona-XtraDB-Cluster-galera-3-3.5-1.216.rhel6.x86_64.rpmPercona-XtraDB-Cluster-galera-3-debuginfo-3.5-1.216.rhel6.x86_64.rpm
Ø 安装Percona-XtraDB-Cluster-client
rpm -ivhPercona-XtraDB-Cluster-client-56-5.6.15-25.5.759.rhel6.x86_64.rpm
Ø 安装libmysqlclient16-5.1.69-1.w6.x86_64
rpm -ivhlibmysqlclient16-5.1.69-1.w6.x86_64.rpm
Ø 安装Percona-XtraDB-Cluster-server
rpm -ivhPercona-XtraDB-Cluster-server-56-5.6.15-25.5.759.rhel6.x86_64.rpm
配置第一个节点
1、编辑配置文件
find / -name 'my-default.cnf'
cp my-default.cnf /etc/my.cnf
vi /etc/my.cnf
[mysql]
# CLIENT #
port = 3306
socket =/data/munion_db/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
skip-name-resolve
character-set-server = utf8
socket =/data/munion_db/mysql.sock
pid-file =/data/munion_db/mysql.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sysdate-is-now = 1
innodb = FORCE
# DATA STORAGE #
datadir = /data/munion_db
# BINARY LOGGING #
log-bin =/data/munion_db/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# REPLICATION #
relay-log =/data/munion_db/relay-bin
slave-net-timeout = 60
# CACHES AND LIMITS #
tmp-table-size = 250M
max-heap-table-size = 250M
query-cache-type = 0
query-cache-size = 0
max-connections = 2000
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 6000
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 4
innodb-log-file-size = 1g
innodb_log_buffer_size = 30M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 25G
innodb_buffer_pool_instances = 24
innodb_thread_sleep_delay = 40
innodb_change_buffer_max_size = 50
innodb_adaptive_hash_index = 1
init_connect='SET autocommit=0'
wait_timeout=100
#interactive_timeout=100
# LOGGING #
log-error =/data/munion_db/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /data/munion_db/mysql-slow.log
#config for wsrep
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://10.25.141.55,10.25.141.56
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_node_address=10.25.141.55
wsrep_sst_method=xtrabackup-v2
wsrep_cluster_name=my_centos_cluster
wsrep_sst_auth="sstuser:s3cret"
wsrep_slave_threads=8
wsrep_provider_options='gcs.max_packet_size=10485760;gcache.size=2147483648;replicator.commit_order=3'
wsrep_max_ws_rows=10485760
wsrep_max_ws_size=2147483648
#config for others
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
配置第二个节点
[mysql]
# CLIENT #
port = 3306
socket =/data/munion_db/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
skip-name-resolve
character-set-server = utf8
socket =/data/munion_db/mysql.sock
pid-file =/data/munion_db/mysql.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sysdate-is-now = 1
innodb = FORCE
# DATA STORAGE #
datadir = /data/munion_db
# BINARY LOGGING #
log-bin = /data/munion_db/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# REPLICATION #
relay-log =/data/munion_db/relay-bin
slave-net-timeout = 60
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 3000
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 3000
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 4
innodb-log-file-size = 1g
innodb_log_buffer_size = 30M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 25G
innodb_buffer_pool_instances = 12
innodb_thread_sleep_delay = 40
init_connect='SET autocommit=0'
wait_timeout=100
interactive_timeout=100
# LOGGING #
log-error =/data/munion_db/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /data/munion_db/mysql-slow.log
#config for wsrep
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://10.25.141.55,10.25.141.56
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_node_address=10.25.141.56
wsrep_cluster_name=my_centos_cluster
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
wsrep_sst_auth="sstuser:s3cret"
wsrep_slave_threads=8
wsrep_provider_options='gcs.max_packet_size=10485760;gcache.size=2147483648;replicator.commit_order=3'
wsrep_max_ws_rows=10485760
wsrep_max_ws_size=2147483648
#config for others
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
LVS+Keepalived配合脚本
#vi /etc/rc.d/init.d/realserver.sh
#!/bin/bash
# description: Config realserver lo andapply noarp
SNS_VIP=192.168.0.110
# /etc/rc.d/init.d/functions
case "$1" in
start)
ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
/sbin/route add -host $SNS_VIP dev lo:0
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
sysctl -p >/dev/null 2>&1
echo "RealServer Start OK"
;;
stop)
ifconfig lo:0 down
route del $SNS_VIP >/dev/null 2>&1
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
echo "RealServer Stoped"
;;
*)
echo "Usage: $0 {start|stop}"
exit 1
esac
exit 0
启动相关服务
Ø 主备LVS
#servicekeepalived start #启动keepalived服务
Ø MySQL Cluster中的2台SQL节点
#cd /etc/rc.d/init.d/
#chmod 755 realserver.sh
#./realserver.sh
测试
方法一：
#telnet192.168.0.110 3306
方法二：
使用mysql的各种客户端工具连接mysql。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
與MySQL中使用索引相比，全表掃描何時可以更快？
Apr 09, 2025 am 12:05 AM
全表掃描在MySQL中可能比使用索引更快，具體情況包括：1)數據量較小時；2)查詢返回大量數據時；3)索引列不具備高選擇性時；4)複雜查詢時。通過分析查詢計劃、優化索引、避免過度索引和定期維護表，可以在實際應用中做出最優選擇。
可以在 Windows 7 上安裝 mysql 嗎
Apr 08, 2025 pm 03:21 PM
是的，可以在 Windows 7 上安裝 MySQL，雖然微軟已停止支持 Windows 7，但 MySQL 仍兼容它。不過，安裝過程中需要注意以下幾點：下載適用於 Windows 的 MySQL 安裝程序。選擇合適的 MySQL 版本（社區版或企業版）。安裝過程中選擇適當的安裝目錄和字符集。設置 root 用戶密碼，並妥善保管。連接數據庫進行測試。注意 Windows 7 上的兼容性問題和安全性問題，建議升級到受支持的操作系統。
說明InnoDB全文搜索功能。
Apr 02, 2025 pm 06:09 PM
InnoDB的全文搜索功能非常强大，能够显著提高数据库查询效率和处理大量文本数据的能力。1）InnoDB通过倒排索引实现全文搜索，支持基本和高级搜索查询。2）使用MATCH和AGAINST关键字进行搜索，支持布尔模式和短语搜索。3）优化方法包括使用分词技术、定期重建索引和调整缓存大小，以提升性能和准确性。
InnoDB中的聚類索引和非簇索引（次級索引）之間的差異。
Apr 02, 2025 pm 06:25 PM
聚集索引和非聚集索引的區別在於：1.聚集索引將數據行存儲在索引結構中，適合按主鍵查詢和範圍查詢。 2.非聚集索引存儲索引鍵值和數據行的指針，適用於非主鍵列查詢。
mysql：簡單的概念，用於輕鬆學習
Apr 10, 2025 am 09:29 AM
MySQL是一個開源的關係型數據庫管理系統。 1）創建數據庫和表：使用CREATEDATABASE和CREATETABLE命令。 2）基本操作：INSERT、UPDATE、DELETE和SELECT。 3）高級操作：JOIN、子查詢和事務處理。 4）調試技巧：檢查語法、數據類型和權限。 5）優化建議：使用索引、避免SELECT*和使用事務。
說明不同類型的MySQL索引（B樹，哈希，全文，空間）。
Apr 02, 2025 pm 07:05 PM
MySQL支持四種索引類型：B-Tree、Hash、Full-text和Spatial。 1.B-Tree索引適用於等值查找、範圍查詢和排序。 2.Hash索引適用於等值查找，但不支持範圍查詢和排序。 3.Full-text索引用於全文搜索，適合處理大量文本數據。 4.Spatial索引用於地理空間數據查詢，適用於GIS應用。
mysql用戶和數據庫的關係
Apr 08, 2025 pm 07:15 PM
MySQL 數據庫中，用戶和數據庫的關係通過權限和表定義。用戶擁有用戶名和密碼，用於訪問數據庫。權限通過 GRANT 命令授予，而表由 CREATE TABLE 命令創建。要建立用戶和數據庫之間的關係，需創建數據庫、創建用戶，然後授予權限。
mysql 和 mariadb 可以共存嗎
Apr 08, 2025 pm 02:27 PM
MySQL 和 MariaDB 可以共存，但需要謹慎配置。關鍵在於為每個數據庫分配不同的端口號和數據目錄，並調整內存分配和緩存大小等參數。連接池、應用程序配置和版本差異也需要考慮，需要仔細測試和規劃以避免陷阱。在資源有限的情況下，同時運行兩個數據庫可能會導致性能問題。
