Percona XtraDB Cluster 56+keepalived安装和管理手册

1、1. 环境1.1. 项目介绍最近项目打算迁移到mysql数据库中，项目需要以下需求：1. 数据库免费2. 需要做到高可用性和高可靠性3. 硬件投资不要太大4. 数据延迟时间不能超过30秒根据以上特点，选择使用mysql数据库，考虑到需要使用高可用性和高可用性，使用Percona XtraDB Cluster + Keepalived方案，这样只需要2台数据库服务器做成双活，解决了以上问题。1.2. Percona XtraDB Cluster特点介绍Percona XtraDB Cluster是针对MySQL用户的高可用性和扩展性解决方案，基于Percona Server 。其包括了Write

2、 Set REPlication补丁，使用Galera 2.0库，这是一个针对事务性应用程序的同步多主机复制插件。Percona XtraDB Cluster特点： 1) 同步复制，事务在所有集群节点要么同时提交，要么不提交。 2) 多主复制，可以在任意一个节点写入。 3) 从服务器上的并行应用事件，真正的“并行复制”。 4) 自动配置节点。 5) 数据一致性，没有不同步的从服务器。1.3. Keepalived介绍Keepalived是Linux下一个轻量级的高可用解决方案，它与HeartBeat、RoseHA实现的功能类似，都可以实现服务或者网络的高可用，但是又有差别：HeartBeat是

3、一个专业的、功能完善的高可用软件，它提供了HA软件所需的基本功能，比如心跳检测和资源接管，监测集群中的系统服务，在群集节点间转移共享IP地址的所有者等，HeartBeat功能强大，但是部署和使用相对比较麻烦；与HeartBeat相比，Keepalived主要是通过虚拟路由冗余来实现高可用功能，虽然它没有HeartBeat功能强大，但Keepalived部署和使用非常简单，所有配置只需一个配置文件即可完成。但我们这里使用虚拟IP功能，当一台服务需要停机或者异常，把虚拟IP切换到另外一台服务器。1.4. 架构部署图项目节点1节点2操作系统Centos 6.7Centos 6.7主机名MydbMyd

4、b2IP192.168.137.3192.168.137.15VIP192.168.137.88192.168.137.89keepalived1.2.13-51.2.13-5Percona-XtraDB-Cluster5.65.6部署如下图，mydb和mydb2都可以支持双写，web应用1连接192.168.137.88,web应用2连接192.168.137.89，因为两台节点数据一至，但业务操作数据不一样，所以很少存在挣用情况，应用1一个写和读的应用，应用2需要读应用1产生数据，必须要实时读到，否则产生业务上错误，同时业务2自己也会有数据产生，但业务2生产数据应用1不会读取。当一个节点发

5、生故障，利用keepalived的vip浮动功能，如下图，mydb2节点发生故障，vip:192.168.137.89飘移到mydb节点上。2. Percona-XtraDB-Cluster安装2.1. 安装前预处理2.1.1. 关闭防火墙所有节点都关闭，命令如下# chkconfig ip6tables off# chkconfig iptables off# service ip6tables stop# service iptables stop2.1.2. 禁用SELINUX修改/etc/selinux/config 文件,，需要重启动后生效将SELINUX=enforcing改为SE

6、LINUX=disabled，内容如下# This file controls the state of SELinux on the system.# SELINUX= can take one of these three values:# enforcing - SELinux security policy is enforced.# permissive - SELinux prints warnings instead of enforcing.# disabled - No SELinux policy is loaded.SELINUX=disabled# SELINUXTYP

7、E= can take one of these two values:# targeted - Targeted processes are protected,# mls - Multi Level Security protection.SELINUXTYPE=targeted2.1.3. 检查内核和资源限制对/etc/security/limits.conf增加以下内容*hardnofile65536*softnofile65536把/etc/security/limits.d/90-nproc.conf改成如下内容#* soft nproc unlimited主要是注释掉* soft

8、 nproc 1024这行内容，否则会有最大创建进程数限制2.2. 配置yum安装源由于Percona-XtraDB-Cluster-56需要使用到socat包，这个包在epel源中有所以先安装epel源和percona源。以下命令在所有节点都执行：# yum install epel-release# yum install 2.3. 安装软件包配置好yum源后，以下命令在所有节点都执行：# yum install Percona-XtraDB-Cluster-56如果配置好epel和percona源，会安装好所有相关的依赖包。3. Percona-XtraDB-Cluster部署3.1.

9、创建数据目录在所有节点下，创建目录命令和更改所有者命令如下：# mkdir -p /mydata/mysql/data,iblog,binlog # chown -R mysql:mysql /mydata/mysql3.2. 配置第一节点3.2.1. 配置f文件编辑/etc/f文件内容如下:mysqlduser=mysqldatadir = /mydata/mysql/datainnodb_data_home_dir=/mydata/mysql/ibloginnodb_log_group_home_dir=/mydata/mysql/ibloginnodb_log_file_size = 1

10、00Minnodb_log_files_in_group = 4innodb_undo_tablespaces=4innodb_undo_directory=/mydata/mysql/ibloglog_bin=/mydata/mysql/binlog/mysql-binrelay_log=/mydata/mysql/binlog/mysql-relay-bin#log_timestamps='SYSTEM'socket=/var/lib/mysql/mysql.sock#server_id=1# Disabling symbolic-links is recommended

11、to prevent assorted security riskssymbolic-links=0master_info_repository=table #把复制进程主库信息存储到mysql.slave_master_info表里relay_log_info_repository=table #把复制进程主库信息存储到mysql. slave_relay_log_info表里# Path to Galera librarywsrep_provider=/usr/lib64/libgalera_smm.so# Cluster connection URL contains the IPs o

12、f node#1, node#2wsrep_cluster_address=gcomm:/192.168.137.3,192.168.137.15# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB# This changes how InnoDB autoincrement locks are managed and

13、 is a requirement for Galerainnodb_autoinc_lock_mode=2# Node #1 addresswsrep_node_address=192.168.137.3# SST methodwsrep_sst_method=xtrabackup-v2# Cluster namewsrep_cluster_name=my_centos_cluster# Authentication for SST methodwsrep_sst_auth="sstuser:s3cret"mysqld_safelog-error=/var/log/mys

14、qld.logpid-file=/var/run/mysqld/mysqld.pidmysqlprompt=uh d>3.2.2. 初始化实例在第一个节点初始化实例rootmydb # mysql_install_db -user=mysql3.2.3. 启动集群保存之后，用以下命令启动rootmydb # service mysql bootstrap-pxc如果是centos7，用以下命令启动# systemctl start mysqlbootstrap.service3.2.4. PXC检查登录到mysql用以下命令来检查是否正确启动rootlocalhost (none)>

15、;show status like 'wsrep%'+-+-+| Variable_name | Value |+-+-+| wsrep_local_state_uuid | cca866e6-2227-11e6-90bc-6ea3218e8a1b | wsrep_protocol_version | 7 | wsrep_last_committed | 20 | wsrep_replicated | 0 | wsrep_replicated_bytes | 0 | wsrep_repl_keys | 0 | wsrep_repl_keys_bytes | 0 | wsrep_

16、repl_data_bytes | 0 | wsrep_repl_other_bytes | 0 | wsrep_received | 4 | wsrep_received_bytes | 478 | 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_que

17、ue_avg | 0.000000 | wsrep_local_recv_queue | 0 | wsrep_local_recv_queue_max | 2 | wsrep_local_recv_queue_min | 0 | wsrep_local_recv_queue_avg | 0.250000 | wsrep_local_cached_downto | 0 | wsrep_flow_control_paused_ns | 0 | wsrep_flow_control_paused | 0.000000 | wsrep_flow_control_sent | 0 | wsrep_flo

18、w_control_recv | 0 | wsrep_cert_deps_distance | 0.000000 | wsrep_apply_oooe | 0.000000 | wsrep_apply_oool | 0.000000 | wsrep_apply_window | 0.000000 | wsrep_commit_oooe | 0.000000 | wsrep_commit_oool | 0.000000 | wsrep_commit_window | 0.000000 | wsrep_local_state | 4 | wsrep_local_state_comment | Sy

19、nced | wsrep_cert_index_size | 0 | wsrep_cert_bucket_count | 22 | wsrep_gcache_pool_size | 1320 | wsrep_causal_reads | 0 | wsrep_cert_interval | 0.000000 | wsrep_incoming_addresses | 192.168.137.3:3306 | wsrep_evs_delayed | | wsrep_evs_evict_list | | wsrep_evs_repl_latency | 2.2e-06/3.04775e-06/3.73

20、1e-06/5.50793e-07/4 | wsrep_evs_state | OPERATIONAL | wsrep_gcomm_uuid | 113dce6a-27c0-11e6-a162-2e87c5548a48 | wsrep_cluster_conf_id | 3 | wsrep_cluster_size | 1 | wsrep_cluster_state_uuid | cca866e6-2227-11e6-90bc-6ea3218e8a1b | wsrep_cluster_status | Primary | wsrep_connected | ON | wsrep_local_b

21、f_aborts | 0 | wsrep_local_index | 0 | wsrep_provider_name | Galera | wsrep_provider_vendor | Codership Oy <info> | wsrep_provider_version | 3.15(r8459459) | wsrep_ready | ON |+-+-+58 rows in set (0.00 sec)以上红色字体是关注重点，值必须如上面所示。3.2.5. 创建XtraBackup使用用户创建XtraBackup工具使用快照传送使用的用户，登录到mysql执行如下命令CREA

22、TE USER 'sstuser''localhost' IDENTIFIED BY 's3cret'GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser''localhost'FLUSH PRIVILEGES;3.3. 配置第二节点3.3.1. 编辑f文件编辑/etc/f文件内容如下:mysqlduser=mysqldatadir = /mydata/mysql/datainnodb_data_home_dir=/mydata/mysql

23、/ibloginnodb_log_group_home_dir=/mydata/mysql/ibloginnodb_log_file_size = 100Minnodb_log_files_in_group = 4innodb_undo_tablespaces=4innodb_undo_directory=/mydata/mysql/ibloglog_bin=/mydata/mysql/binlog/mysql-binrelay_log=/mydata/mysql/binlog/mysql-relay-bin#log_timestamps='SYSTEM'socket=/var

24、/lib/mysql/mysql.sock#server_id=1# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0master_info_repository=table #把复制进程主库信息存储到mysql.slave_master_info表里relay_log_info_repository=table #把复制进程主库信息存储到mysql. slave_relay_log_info表里# Path to Galera librarywsrep_prov

25、ider=/usr/lib64/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2wsrep_cluster_address=gcomm:/192.168.137.3,192.168.137.15# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage

26、_engine=InnoDB# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2# Node #1 addresswsrep_node_address=192.168.137.15# SST methodwsrep_sst_method=xtrabackup-v2# Cluster namewsrep_cluster_name=my_centos_cluster# Authentication for SST meth

27、odwsrep_sst_auth="sstuser:s3cret"mysqld_safelog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidmysqlprompt=uh d>3.3.2. 次节点启动由于第一节点已经启动了集群，这里只要使用次节点的命令就行了，只要任何一台机器已经启动了集群，就不能在第二节点启动集群，否则会生产脑裂。启动命令如下：rootmydb2 # service mysql startStarting MySQL (Percona XtraDB Cluster).State

28、 transfer in progress, setting sleep higher. SUCCESS!第一次启动次节点花费时间比较长，启动的时间是根据集群中数据量大小来确定的。3.3.3. PXC检查登录到mysql用以下命令来检查是否正确启动rootlocalhost (none)>show status like 'wsrep%'+-+-+| Variable_name | Value |+-+-+| wsrep_local_state_uuid | cca866e6-2227-11e6-90bc-6ea3218e8a1b | wsrep_protocol_ver

29、sion | 7 | wsrep_last_committed | 20 | wsrep_replicated | 0 | wsrep_replicated_bytes | 0 | wsrep_repl_keys | 0 | wsrep_repl_keys_bytes | 0 | wsrep_repl_data_bytes | 0 | wsrep_repl_other_bytes | 0 | wsrep_received | 2 | wsrep_received_bytes | 212 | wsrep_local_commits | 0 | wsrep_local_cert_failures

30、| 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

31、| wsrep_local_cached_downto | 0 | wsrep_flow_control_paused_ns | 0 | wsrep_flow_control_paused | 0.000000 | wsrep_flow_control_sent | 0 | wsrep_flow_control_recv | 0 | wsrep_cert_deps_distance | 0.000000 | wsrep_apply_oooe | 0.000000 | wsrep_apply_oool | 0.000000 | wsrep_apply_window | 0.000000 | ws

32、rep_commit_oooe | 0.000000 | wsrep_commit_oool | 0.000000 | wsrep_commit_window | 0.000000 | wsrep_local_state | 4 | wsrep_local_state_comment | Synced | wsrep_cert_index_size | 0 | wsrep_cert_bucket_count | 22 | wsrep_gcache_pool_size | 1320 | wsrep_causal_reads | 0 | wsrep_cert_interval | 0.000000

33、 | wsrep_incoming_addresses | 192.168.137.3:3306,192.168.137.15:3306 | wsrep_evs_delayed | | wsrep_evs_evict_list | | wsrep_evs_repl_latency | 0/0/0/0/0 | wsrep_evs_state | OPERATIONAL | wsrep_gcomm_uuid | 1b79ed38-27da-11e6-bc64-cef1cf571516 | wsrep_cluster_conf_id | 4 | wsrep_cluster_size | 2 | ws

34、rep_cluster_state_uuid | cca866e6-2227-11e6-90bc-6ea3218e8a1b | wsrep_cluster_status | Primary | wsrep_connected | ON | wsrep_local_bf_aborts | 0 | wsrep_local_index | 1 | wsrep_provider_name | Galera | wsrep_provider_vendor | Codership Oy <info> | wsrep_provider_version | 3.15(r8459459) | wsr

35、ep_ready | ON |+-+-+58 rows in set (0.00 sec)3.4. PXC集群测试已经测试是在第一节点创建数据库，然后在第二节点创建表，然后在第二节点插入数据，同时在第一节点和第二节点查询数据，验证是否正确数据复制过去。1. 登录到节点1创建数据库使用mysql hmydb登录到节点1rootmydb data# mysql -hmydbrootmydb (none)>create database percona;Query OK, 1 row affected (0.01 sec)2. 登录节点2创建表rootmydb2 # mysql -hmydb2

36、rootmydb2 (none)>use perconaDatabase changedrootmydb2 percona>CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30);Query OK, 0 rows affected (0.05 sec)3. 在节点2上插入新数据并查询rootmydb2 percona>INSERT INTO percona.example VALUES (1, 'percona1');Query OK, 1 row affected (0.00

37、sec)rootmydb2 percona>select * from percona.example;+-+-+| node_id | node_name |+-+-+| 1 | percona1 |+-+-+1 row in set (0.00 sec)4. 在节点一上查询rootmydb (none)>select * from percona.example;+-+-+| node_id | node_name |+-+-+| 1 | percona1 |+-+-+1 row in set (0.00 sec)4. Keepalived安装部署4.1. Keepalived

38、安装Keepalived在centos自带的yum源中，使用yum源在线安装命令如下:# yum install keepalived以上在每个节点都执行安装。设置成自动启动# chkconfig keepalived on4.2. 配置keepalived.conf文件节点1 /etc/keepalived/ keepalived.conf配置文件如下vrrp_instance VI_CZ state MASTER interface eth0 virtual_router_id 51 priority 100 advert_int 1 authentication auth_type PA

39、SS auth_pass 1111 virtual_ipaddress 192.168.137.88/24 dev eth0 label eth0:1 vrrp_instance VI_OA state BACKUP interface eth0 virtual_router_id 52 priority 90 advert_int 1 authentication auth_type PASS auth_pass 1234 virtual_ipaddress 192.168.137.89/24 dev eth0 label eth0:2 节点2 /etc/keepalived/ keepalived.conf配置文件如下:vrrp_instance VI_CZ state BACKUP interface eth0 virtual_router_id