PXC Database test Environment

1, PXC Database test environment:
192.168.1.221 (Master)
192.168.1.223 (PXC database)
192.168.1.224 (PXC database)

Modify the system time
• Add Aliyun Yum Source:
#cd/etc/yum.repos.d/
# wget Http://mirrors.aliyun.com/repo/Centos-6.repo
# cp-a Centos-6.repo Centos-base.repo

Add Aliyun epel Yum Source:
#wget Http://mirrors.aliyun.com/repo/epel-6.repo

Clean up Yum Cache
#yum Clean All
#yum Makecache

Installing Percona-release
#yum-y Install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Installation related tools
# yum install-y install telnet bind-utils vim-en* lrzsz wget

Update Yum
#yum Update-x kernel-y

Installing PXC
#yum-y Install percona-xtradb-cluster-56

Host Boot:
#/etc/init.d/mysql BOOTSTRAP-PXC

MySQL configuration file
/etc/my.cnf

[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql

# Path to Galera Library
Wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster Connection URL contains the IPs of Node#1, Node#2 and Node#3
wsrep_cluster_address=gcomm://$MY _pxc_ip, $PXC 2, $PXC 3

# in order for Galera to work correctly binlog format should is ROW
Binlog_format=row

# MyISAM Storage engine had only experimental support
Default_storage_engine=innodb

# This InnoDB autoincrement locking mode are a requirement for Galera
innodb_autoinc_lock_mode=2

# Node $SERVER _id Address
wsrep_node_address= $MY _pxc_ip
Wsrep_provider_options= "gcache.size=500m"
wsrep_slave_threads=300
Innodb_force_recovery=0
Innodb_fast_shutdown=1

# SST Method
Wsrep_sst_method=xtrabackup-v2

# Cluster Name
Wsrep_cluster_name=linseek_pxc

# Authentication for SST method
Wsrep_sst_auth= "sstuser:linseek123#"

# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0

Log-error =/var/log/mysqld.log
#pid-file =/var/lib/mysql/mysql.pid
Pid-file =/var/lib/mysql/mysql.pid
Open-files-limit = 8912

# Query Cache
Query_cache_limit = 64K
Query_cache_type = 0
Query_cache_size = 300M

Server-id = $SERVER _id
# Note ID varies by machine
Report_host = $MY _pxc_ip
Report_port = 3306

# connections
Back_log = 50
Max_connections = 5000
Max_connect_errors = 100000

# table Cache
Table_open_cache = 2048
Max_allowed_packet = 16M
Binlog_cache_size = 1M
Max_heap_table_size = 64M

# Sessin Cache
Read_buffer_size = 2M
Read_rnd_buffer_size = 16M
Sort_buffer_size = 8M
Join_buffer_size = 8M
Thread_cache_size = 8
#thread_concurrency = 8
Ft_min_word_len = 4

Default-storage-engine = InnoDB
Thread_stack = 192K
Transaction_isolation = read-committed
Tmp_table_size = 64M
Binlog_format=row
Slow_query_log_file =/var/lib/mysql/slow-query.log
Long_query_time = 2

# MyISAM
Key_buffer_size = 16M
Bulk_insert_buffer_size = 32M
Myisam_sort_buffer_size = 32M
Myisam_max_sort_file_size = 256M
Myisam_repair_threads = 1

# InnoDB
#innodb_additional_mem_pool_size = 16M
Innodb_buffer_pool_size = $POOL _size
innodb_buffer_pool_instances = 0
Innodb_data_file_path = Ibdata1:300m:autoextend
Innodb_write_io_threads = $CPUS
Innodb_read_io_threads = $CPUS
innodb_thread_concurrency = 0
Innodb_flush_log_at_trx_commit = 2
Innodb_log_buffer_size = 16M
Innodb_log_file_size = 256M
Innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
Innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
Innodb_flush_method = O_direct

# Thread Pool
thread_handling = Pool-of-threads
Thread_pool_oversubscribe = 10

# SQL MODE
Sql_mode=allow_invalid_dates,ansi,no_auto_create_user

[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash
#safe-updates

[Myisamchk]
Key_buffer_size = 16M
Sort_buffer_size = 16M
Read_buffer = 4M
Write_buffer = 4M

[Mysqlhotcopy]
Interactive-timeout

To modify the relevant parameters in the configuration file:
wsrep_cluster_address=gcomm://$MY _pxc_ip, $PXC 2, $PXC 3
wsrep_node_address= $MY _pxc_ip
Server-id = $SERVER _id
# Note ID varies by machine
Report_host = $MY _pxc_ip
Innodb_buffer_pool_size = $POOL _size
Innodb_write_io_threads = $CPUS
Innodb_read_io_threads = $CPUS

Set up sync users, three machines to do:
> CREATE USER ' sstuser ' @ ' localhost ' identified by ' linseek123# ';
> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' sstuser ' @ ' localhost ';
> FLUSH privileges;

PXC Database test Environment