pxc 数据库测试环境

1、pxc 数据库测试环境:
192.168.1.221(主)
192.168.1.223（pxc数据库）
192.168.1.224（pxc数据库）

修改系统时间
• 添加 aliyun yum 源：
#cd /etc/yum.repos.d/
# wget http://mirrors.aliyun.com/repo/Centos-6.repo
# cp -a Centos-6.repo CentOS-Base.repo

添加aliyun epel yum 源：
#wget http://mirrors.aliyun.com/repo/epel-6.repo

清理yum 缓存
#yum clean all
#yum makecache

安装percona-release
#yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

安装相关工具
# yum install -y install telnet bind-utils vim-en* lrzsz wget

更新yum
#yum update -x kernel -y

安装pxc
#yum -y install Percona-XtraDB-Cluster-56

主机启动：
#/etc/init.d/mysql bootstrap-pxc

mysql 配置文件
/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,$PXC2,$PXC3

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This InnoDB autoincrement locking mode is 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
# 注意ID各机器不同
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

修改配置文件中的相关参数：
wsrep_cluster_address=gcomm://$MY_PXC_IP,$PXC2,$PXC3
wsrep_node_address=$MY_PXC_IP
server-id = $SERVER_ID
# 注意ID各机器不同
report_host = $MY_PXC_IP
innodb_buffer_pool_size = $POOL_SIZE
innodb_write_io_threads = $CPUS
innodb_read_io_threads = $CPUS

设定同步用户,三台机器都要做:
> CREATE USER ‘sstuser‘@‘localhost‘ IDENTIFIED BY ‘Linseek123#‘;
> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘sstuser‘@‘localhost‘;
> FLUSH PRIVILEGES;

pxc 数据库测试环境

标签：ase   pair   isolation   mysql   table   auth   download   innodb   fas