首页 > 代码库 > pxc 数据库测试环境
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 数据库测试环境