percona xtradb cluster 5.5集群生产环境安装之二

2341233333

一、安装PXC要求


1、强烈推荐3个节点以上且硬件配置一样，一个节点性能受影响，整体受影响，遵循木桶效应
2、如果DDL语句有问题将破坏集群
3、创建数据库表引擎必须是INNODB，否则复合sql语句无法同步
4、创建数据库表必须要有主键索引，否则集群中有一台数据库会down掉

二、在线安装

yum remove -y mysql-libs
rpm -qa |grep  Percona-Server |grep -v compat |xargs rpm -e --nodeps
yum install -y cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses5-devel
yum install -y http://www.percona.com/downloads ... se-0.1-3.noarch.rpm
yum install -y http://mirrors.yun-idc.com/epel/ ... ease-6-8.noarch.rpm
yum makecache

yum install -y Percona-XtraDB-Cluster-55.x86_64

三、配置

1、关闭selinux,防火墙开通3306,4444,4567,4568端口
2、节点DB1配置：cat /etc/my.cnf        
[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
datadir         = /data/mysql_xx
pid-file        = /data/mysql_xx/mysql.pid
log-error       = /data/mysql_xx/mysql.err
log-bin         = /log/mysql_xx/mysql-bin-fookii
max_tmp_tables = 64

character-set-server=utf8
skip-name-resolve
log-slave-updates
slave-skip-errors = 1052
back_log = 5000
max_connections = 1024
max_connect_errors = 100
table_open_cache = 4096
max_allowed_packet = 128M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 48
query_cache_type=0
query_cache_size=0M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = InnoDB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
slow_query_log
long_query_time = 2
server-id = 1010
bulk_insert_buffer_size = 64M
innodb_buffer_pool_size = 21G
innodb_io_capacity =100
innodb_file_per_table = 1
innodb_file_format=barracuda
innodb_flush_method=O_DIRECT
innodb_data_file_path =ibdata1:100M;ibdata2:100M:autoextend
innodb_write_io_threads = 16
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
sync_binlog=1
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
expire_logs_days = 30
innodb_log_file_size=1000M
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
log_bin_trust_function_creators = 1  
wait_timeout=120
interactive_timeout=120
open-files-limit = 65535
default-time-zone='+8:00'
group_concat_max_len=10240

启动服务器，测试能否正常启动
/etc/init.d/mysql start
添加使用XtraBackup进行SST传输帐号
CREATE USER 'sstuser'%'localhost' IDENTIFIED BY ‘123abc';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.*;
FLUSH PRIVILEGES;

3、 配置文件里添加集群参数
/etc/init.d/mysql stop
cat /etc/my.cnf
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address="gcomm://10.0.10.10,10.0.10.11,10.0.10.12"
wsrep_sst_receive_address=10.0.10.10:4444
wsrep_provider_options="gcache.size=5G;gcache.page_size=1G"
wsrep_node_name=ip-10-0-10-10
wsrep_sst_auth="sstuser:123abc"
wsrep_replicate_myisam=on
wsrep_restart_slave=on
wsrep_sync_wait=1
wsrep_auto_increment_control=off
auto_increment_increment=1
auto_increment_offset =1
binlog_format=ROW
log_slave_updates
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_slave_threads=4
wsrep_sst_method=rsync
wsrep_cluster_name=xx_cluster
wsrep_debug=0

4、集群中第一个节点启动命令
/etc/init.d/mysql bootstrap-pxc

centos7.0启动命令:
systemctl start mysql@bootstrap.service


5、启动后查看运行状态
mysql> show status like 'wsrep%';
显示：
wsrep_cluster_size 1
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON
说明DB1节点配置成功

6、节点DB2配置
DB2配置按DB1配置类似，唯一几个参数不同修改如下：
server-id = 1011
wsrep_sst_receive_address=10.0.10.11:4444
wsrep_node_name=ip-10-0-10-11
启动DB2：
/etc/init.d/mysql start

启动后查看运行状态

mysql> show status like 'wsrep%';

显示：

wsrep_cluster_size 2

wsrep_cluster_status Primary

wsrep_connected ON

wsrep_ready ON

说明DB2节点配置成功

6、节点DB3配置

DB3配置按DB1配置类似，唯一几个参数不同修改如下：

server-id = 1012

wsrep_sst_receive_address=10.0.10.12:4444

wsrep_node_name=ip-10-0-10-12

启动DB3：

/etc/init.d/mysql start

启动后查看运行状态

mysql> show status like 'wsrep%';

显示：

wsrep_cluster_size 3

wsrep_cluster_status Primary

wsrep_connected ON

wsrep_ready ON

说明DB3节点配置成功