MySQL集群Percona XtraDB Cluster安装搭建步骤详解

共计 46249 个字符，预计需要花费 116 分钟才能阅读完成。

最近公司某客户要求我们的数据库搭建 PXC 集群以保证他们的系统高性能和搞稳定性，以后花费了一些时间去搭建和测试，也踩过一些坑，准备分享出来

系统：CentOS6.6
Percona XtraDB Cluster：5.6.26

建议关闭 iptables，4 个端口 3306,4444,4567,4568
Percona XtraDB Cluster 环境所涉及的端口：

#MySQL 实例端口
1. Regular MySQL port, default 3306.

#Percona XtraDB Cluster 相互通讯的端口
2. Port for group communication, default 4567. It can be changed by the option:
wsrep_provider_options =”gmcast.listen_addr=tcp://0.0.0.0:4010; “

# 用于 SST 传送的端口
3. Port for State Transfer, default 4444. It can be changed by the option:
wsrep_sst_receive_address=10.11.12.205:5555

# 用于 IST 传送的端口
4. Port for Incremental State Transfer, default port for group communication + 1 (4568). It can be changed by the option:
wsrep_provider_options = “ist.recv_addr=10.11.12.206:7777; “

node1 192.168.3.130
node2 192.168.3.129
node3 192.168.3.128

1. 安装软件依赖包（添加 repl 源，再安装依赖，3 台 server 操作一样。）

rpm -ivh http://dl.Fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat nc -y

2. 安装 xtrabackup（PXC 同步数据需要用到）

yum install -y http://www.percona.com/downloads/percona-release/RedHat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install -y percona-xtrabackup-24

3. 下载安装 Percona-XtraDB-Cluster

# 安装开发包

yum install -y libaio*
yum groupinstall -y 'Development tools'
yum install -y libssl.so.6
ln -sf /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6
ln -sf /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.6


wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.26-25.12/binary/tarball/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz
tar zxvf Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz
useradd -M -s /sbin/nologin mysql
mkdir -p /usr/local/pxc
mkdir -p /data/pxc/mysql3306/{data,tmp,logs}
mv /data/download/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64/* /usr/local/pxc/
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
chown -R mysql:mysql /data/pxc/mysql3306/
chown -R mysql:mysql /usr/local/pxc

修改 my.cnf 配置文件，注意在 [mysqld] 段落添加 PXC 的参数：
vi /etc/my.cnf

130

[client]
port            = 3306
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
  

# The MySQL server
[mysqld]
#########Basic##################
explicit_defaults_for_timestamp=true

port            = 3306  
user        = mysql   
basedir         = /usr/local/mysql  
datadir         = /data/mysql/mysql3306/data   
tmpdir          = /data/mysql/mysql3306/tmp   
pid-file        = /data/mysql/mysql3306/tmp/mysql.pid    
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
#skip-grant-tables  

#character set
character_set_server = utf8


open_files_limit = 65535
back_log = 500
#event_scheduler = ON
#lower_case_table_names=1
skip-external-locking
skip_name_resolve = 1
default-storage-engine = InnoDB


#timeout
wait_timeout=1000
interactive_timeout=1000
connect_timeout = 20


server-id       =1303306  #ip 最后一位 + 端口号 = 1003376


#percona 的--recursion-method slavehost 模式
#report_host = 10.105.9.115
#report_port = 3306



#plugin
plugin-load="semisync_master.so;semisync_slave.so"


#########error log#############
log-error = /data/mysql/mysql3306/logs/error.log  
log-warnings = 2  

#########general log#############
#general_log=1
#general_log_file=/data/mysql/mysql3306/logs/mysql.log 

#########slow log#############
slow_query_log = 1
long_query_time=1
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow   


############# for replication###################

log-bin     = /data/mysql/mysql3306/logs/mysql-bin   
binlog_format = row
max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 2M
expire-logs-days = 7
slave-net-timeout=30


log_bin_trust_function_creators = 1
log-slave-updates = 1   
skip-slave-start = 1
#read_only =1    #从上设置,5.7 是 super_read_only

#GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1

#relay log
relay-log = /data/mysql/mysql3306/logs/mysql-relay  
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index
max-relay-log-size = 500M


#replication crash safe
sync_master_info = 1
sync_relay_log_info = 1
sync_relay_log = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

#semisync   动态开启 主从切换的时候用
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步
#rpl_semi_sync_master_wait_for_slave_count = 2  #5.7 新增参数 等待多少个从库接收到 binlog


#ignore
#replicate-ignore-db = 'school','school2'
#replicate-do-db = 'school','school2'

#Multi-threaded Slave
#slave_parallel_workers=16
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-type=DATABASE

#replication error
#slave-skip-errors=1007,1051,1062

#######per_thread_buffers#####################
max_connections=1100
max_user_connections=1000
max_connect_errors=1000
#myisam_recover
key_buffer_size = 64M
max_allowed_packet = 16M
#table_cache = 3096
table_open_cache = 6144
table_definition_cache = 4096


read_buffer_size = 1M
join_buffer_size = 128K
read_rnd_buffer_size = 1M

#myisam
sort_buffer_size = 128K
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1


myisam_sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 64M
query_cache_type=0
query_cache_size = 0
bulk_insert_buffer_size = 32M

thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 192K


###############InnoDB###########################
innodb_data_home_dir = /data/mysql/mysql3306/data      
innodb_log_group_home_dir = /data/mysql/mysql3306/logs    
innodb_data_file_path = ibdata1:1000M:autoextend

innodb_buffer_pool_size = 1G  #根据内存大小设置

innodb_buffer_pool_instances    = 8
#innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 500M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 80
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 800  #机械盘 800 ssd 2000
innodb_flush_neighbors = 1
innodb_file_format = Barracuda
innodb_purge_threads=1   #5.6 只能有一个，5.7 可以设置多个
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
innodb_print_all_deadlocks = 1
#innodb_status_output=1
#innodb_status_output_locks=1
performance_schema=0   #是否打开 P_S 库进行性能收集
transaction_isolation = READ-COMMITTED
#innodb_force_recovery=0
#innodb_fast_shutdown=1
#innodb_status_file = 1  #实时输出 show engine innodb status 到 innodb_status 文件

############# PXC #####################                                                 
innodb_autoinc_lock_mode=2                                                       
wsrep_cluster_name=pxc-dongzheng                                                 
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so                             
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129          
wsrep_node_address=192.168.3.130                                                 
wsrep_slave_threads=2                                                            
wsrep_sst_auth=sst:dongzheng                                                     
wsrep_sst_method=xtrabackup-v2                                                   
wsrep_provider_options="debug=1"



[mysqldump]
quick
max_allowed_packet = 128M


[mysql]
no-auto-rehash
max_allowed_packet = 128M
prompt                         = '(product)\u@\h:\p [\d]> '
default_character_set          = utf8


[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 512k
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so

129

[client]
port            = 3306
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
  

# The MySQL server
[mysqld]
#########Basic##################
explicit_defaults_for_timestamp=true

port            = 3306  
user           = mysql   
basedir         = /usr/local/mysql  
datadir         = /data/mysql/mysql3306/data   
tmpdir          = /data/mysql/mysql3306/tmp   
pid-file        = /data/mysql/mysql3306/tmp/mysql.pid    
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
#skip-grant-tables  

#character set
character_set_server = utf8


open_files_limit = 65535
back_log = 500
#event_scheduler = ON
#lower_case_table_names=1
skip-external-locking
skip_name_resolve = 1
default-storage-engine = InnoDB


#timeout
wait_timeout=1000
interactive_timeout=1000
connect_timeout = 20


server-id       =1293306  #ip 最后一位 + 端口号 = 1003376


#percona 的--recursion-method slavehost 模式
#report_host = 10.105.9.115
#report_port = 3306



#plugin
plugin-load="semisync_master.so;semisync_slave.so"


#########error log#############
log-error = /data/mysql/mysql3306/logs/error.log  
log-warnings = 2  

#########general log#############
#general_log=1
#general_log_file=/data/mysql/mysql3306/logs/mysql.log 

#########slow log#############
slow_query_log = 1
long_query_time=1
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow   


############# for replication###################

log-bin     = /data/mysql/mysql3306/logs/mysql-bin   
binlog_format = row
max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 2M
expire-logs-days = 7
slave-net-timeout=30


log_bin_trust_function_creators = 1
log-slave-updates = 1   
skip-slave-start = 1
#read_only =1    #从上设置,5.7 是 super_read_only

#GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1

#relay log
relay-log = /data/mysql/mysql3306/logs/mysql-relay  
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index
max-relay-log-size = 500M


#replication crash safe
sync_master_info = 1
sync_relay_log_info = 1
sync_relay_log = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

#semisync   动态开启 主从切换的时候用
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步
#rpl_semi_sync_master_wait_for_slave_count = 2  #5.7 新增参数 等待多少个从库接收到 binlog


#ignore
#replicate-ignore-db = 'school','school2'
#replicate-do-db = 'school','school2'

#Multi-threaded Slave
#slave_parallel_workers=16
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-type=DATABASE

#replication error
#slave-skip-errors=1007,1051,1062

#######per_thread_buffers#####################
max_connections=1100
max_user_connections=1000
max_connect_errors=1000
#myisam_recover
key_buffer_size = 64M
max_allowed_packet = 16M
#table_cache = 3096
table_open_cache = 6144
table_definition_cache = 4096


read_buffer_size = 1M
join_buffer_size = 128K
read_rnd_buffer_size = 1M

#myisam
sort_buffer_size = 128K
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1


myisam_sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 64M
query_cache_type=0
query_cache_size = 0
bulk_insert_buffer_size = 32M

thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 192K


###############InnoDB###########################
innodb_data_home_dir = /data/mysql/mysql3306/data      
innodb_log_group_home_dir = /data/mysql/mysql3306/logs    
innodb_data_file_path = ibdata1:1000M:autoextend

innodb_buffer_pool_size = 1G  #根据内存大小设置

innodb_buffer_pool_instances    = 8
#innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 500M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 80
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 800  #机械盘 800 ssd 2000
innodb_flush_neighbors = 1
innodb_file_format = Barracuda
innodb_purge_threads=1   #5.6 只能有一个，5.7 可以设置多个
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
innodb_print_all_deadlocks = 1
#innodb_status_output=1
#innodb_status_output_locks=1
performance_schema=0   #是否打开 P_S 库进行性能收集
transaction_isolation = READ-COMMITTED
#innodb_force_recovery=0
#innodb_fast_shutdown=1
#innodb_status_file = 1  #实时输出 show engine innodb status 到 innodb_status 文件

############# PXC #####################                                                 
innodb_autoinc_lock_mode=2                                                      
wsrep_cluster_name=pxc-dongzheng                                                
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so                            
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129         
wsrep_node_address=192.168.3.129                                                
wsrep_slave_threads=2                                                           
wsrep_sst_auth=sst:dongzheng                                                    
wsrep_sst_method=xtrabackup-v2                                                  



[mysqldump]
quick
max_allowed_packet = 128M


[mysql]
no-auto-rehash
max_allowed_packet = 128M
prompt                         = '(product)\u@\h:\p [\d]> '
default_character_set          = utf8


[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 512k
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so

View Code

128

MySQL 集群 Percona XtraDB Cluster 安装搭建步骤详解

[client]
port            = 3306
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
  

# The MySQL server
[mysqld]
#########Basic##################
explicit_defaults_for_timestamp=true

port            = 3306  
user           = mysql   
basedir         = /usr/local/mysql  
datadir         = /data/mysql/mysql3306/data   
tmpdir          = /data/mysql/mysql3306/tmp   
pid-file        = /data/mysql/mysql3306/tmp/mysql.pid    
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
#skip-grant-tables  

#character set
character_set_server = utf8


open_files_limit = 65535
back_log = 500
#event_scheduler = ON
#lower_case_table_names=1
skip-external-locking
skip_name_resolve = 1
default-storage-engine = InnoDB


#timeout
wait_timeout=1000
interactive_timeout=1000
connect_timeout = 20


server-id       =1283306  #ip 最后一位 + 端口号 = 1003376


#percona 的--recursion-method slavehost 模式
#report_host = 10.105.9.115
#report_port = 3306



#plugin
plugin-load="semisync_master.so;semisync_slave.so"


#########error log#############
log-error = /data/mysql/mysql3306/logs/error.log  
log-warnings = 2  

#########general log#############
#general_log=1
#general_log_file=/data/mysql/mysql3306/logs/mysql.log 

#########slow log#############
slow_query_log = 1
long_query_time=1
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow   


############# for replication###################

log-bin     = /data/mysql/mysql3306/logs/mysql-bin   
binlog_format = row
max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 2M
expire-logs-days = 7
slave-net-timeout=30


log_bin_trust_function_creators = 1
log-slave-updates = 1   
skip-slave-start = 1
#read_only =1    #从上设置,5.7 是 super_read_only

#GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1

#relay log
relay-log = /data/mysql/mysql3306/logs/mysql-relay  
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index
max-relay-log-size = 500M


#replication crash safe
sync_master_info = 1
sync_relay_log_info = 1
sync_relay_log = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

#semisync   动态开启 主从切换的时候用
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步
#rpl_semi_sync_master_wait_for_slave_count = 2  #5.7 新增参数 等待多少个从库接收到 binlog


#ignore
#replicate-ignore-db = 'school','school2'
#replicate-do-db = 'school','school2'

#Multi-threaded Slave
#slave_parallel_workers=16
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-type=DATABASE

#replication error
#slave-skip-errors=1007,1051,1062

#######per_thread_buffers#####################
max_connections=1100
max_user_connections=1000
max_connect_errors=1000
#myisam_recover
key_buffer_size = 64M
max_allowed_packet = 16M
#table_cache = 3096
table_open_cache = 6144
table_definition_cache = 4096


read_buffer_size = 1M
join_buffer_size = 128K
read_rnd_buffer_size = 1M

#myisam
sort_buffer_size = 128K
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1


myisam_sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 64M
query_cache_type=0
query_cache_size = 0
bulk_insert_buffer_size = 32M

thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 192K


###############InnoDB###########################
innodb_data_home_dir = /data/mysql/mysql3306/data      
innodb_log_group_home_dir = /data/mysql/mysql3306/logs    
innodb_data_file_path = ibdata1:1000M:autoextend

innodb_buffer_pool_size = 1G  #根据内存大小设置

innodb_buffer_pool_instances    = 8
#innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 500M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 80
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 800  #机械盘 800 ssd 2000
innodb_flush_neighbors = 1
innodb_file_format = Barracuda
innodb_purge_threads=1   #5.6 只能有一个，5.7 可以设置多个
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
innodb_print_all_deadlocks = 1
#innodb_status_output=1
#innodb_status_output_locks=1
performance_schema=0   #是否打开 P_S 库进行性能收集
transaction_isolation = READ-COMMITTED
#innodb_force_recovery=0
#innodb_fast_shutdown=1
#innodb_status_file = 1  #实时输出 show engine innodb status 到 innodb_status 文件

############# PXC #####################                                                 
innodb_autoinc_lock_mode=2                                                       
wsrep_cluster_name=pxc-dongzheng                                                 
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so                             
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129          
wsrep_node_address=192.168.3.128                                                 
wsrep_slave_threads=2                                                            
wsrep_sst_auth=sst:dongzheng                                                     
wsrep_sst_method=xtrabackup-v2                                                   
wsrep_provider_options="debug=1;socket.checksum=1"


[mysqldump]
quick
max_allowed_packet = 128M


[mysql]
no-auto-rehash
max_allowed_packet = 128M
prompt                         = '(product)\u@\h:\p [\d]> '
default_character_set          = utf8


[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 512k
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so

View Code

cd /usr/local/pxc/
./scripts/mysql_install_db --defaults-file=/etc/my.cnf

看到两次 ok

# 修改启动脚本
vi /etc/init.d/mysql

basedir=/usr/local/pxc
datadir=/data/pxc/mysql3306/data

4. 启动，进行授权操作，对于第一个节点必须以特殊方式启动，如下：

/etc/init.d/mysql --help
Usage: mysql {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc} [MySQL (Percona XtraDB Cluster) options ]

第一个节点启动

/etc/init.d/mysql bootstrap-pxc

5. 安全加固
连接 mysql：mysql -uroot -p -S /data/mysql/mysql3306/tmp/mysql.sock
delete from mysql.user where user!=’root’ or host!=’localhost’;
truncate table mysql.db;
drop database test;
进行授权，这里用户名密码是 my.cnf 里定义的用户名 sst 密码 dongzheng
GRANT ALL PRIVILEGES ON *.* TO ‘sst’@’%’ identified by ‘dongzheng’;
use mysql;
UPDATE user SET password=PASSWORD(‘123456′) WHERE user=’root’;
flush privileges;
PXC 状态确认
show global status like ‘wsrep%’;

6.netstat 进行查看，可以发现启动两个端口
netstat -lntp | grep mysql
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-05/143501p2.htm

另外两个节点的启动和配置

node2 my.cnf 的 PXC 配置节：
############# PXC #####################
innodb_autoinc_lock_mode=2 #自增锁的优化
wsrep_cluster_name=pxc-dongzheng #集群名字
wsrep_provider=/usr/local/MySQL/lib/libgalera_smm.so #库文件
wsrep_cluster_address=gcomm://192.168.2.130,192.168.3.128,192.168.0.129 #节点中所有 ip
wsrep_node_address=192.168.0.129 #节点的 ip
wsrep_slave_threads=2 #开启的复制线程数，cpu 核数 *2
wsrep_sst_auth=sst:dongzheng #sst 模式需要的用户名和密码
wsrep_sst_method=xtrabackup-v2 #采用什么方式复制数据。还支持 mysqldump，rsync
wsrep_provider_options=”debug=1;socket.checksum=1″ #打开调试模式

node3 my.cnf 的 PXC 配置节：
############# PXC #####################
innodb_autoinc_lock_mode=2 #自增锁的优化
wsrep_cluster_name=pxc-dongzheng #集群名字
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so #库文件
wsrep_cluster_address=gcomm://192.168.2.130,192.168.3.128,192.168.0.129 #节点中所有 ip
wsrep_node_address=192.168.0.128 #节点的 ip
wsrep_slave_threads=2 #开启的复制线程数，cpu 核数 *2
wsrep_sst_auth=sst:dongzheng #sst 模式需要的用户名和密码
wsrep_sst_method=xtrabackup-v2 #采用什么方式复制数据。还支持 mysqldump，rsync
wsrep_provider_options=”debug=1;socket.checksum=1″ #打开调试模式

启动
/etc/init.d/mysql start

启动的时候会把主节点的所有数据传送过从节点包括所有数据库和日志，简单来讲从节点在启动的时候会做 SST 同步

从节点不需要做安全加固，因为从节点跟主节点的数据库和数据都一样

PXC 状态确认
show global status like ‘wsrep%’;

netstat 进行查看，可以发现启动两个端口
netstat -lntp | grep mysql
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld

###############################################################
集群关闭
所有节点都用
/etc/init.d/mysql stop

节点重启
第一个节点用：/etc/init.d/mysql restart-bootstrap
其他节点用：/etc/init.d/mysql restart

集群关闭之后，再启动，谁做第一个节点谁就执行 /etc/init.d/mysql bootstrap-pxc

如果不是所有节点都关闭，那么当启动集群节点的时候不需要执行 /etc/init.d/mysql bootstrap-pxc

/etc/init.d/mysql –help
Usage: mysql {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc} [MySQL (Percona XtraDB Cluster) options ]

到这里搭建就基本结束了。3 个节点同时支持 read/write 操作。

在 130 上执行

show global status like 'wsrep%'\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

*************************** 1. row ***************************
Variable_name: wsrep_local_state_uuid
        Value: f4cb2250-30ac-11e7-8315-17918c780c9e
*************************** 2. row ***************************
Variable_name: wsrep_protocol_version
        Value: 7
*************************** 3. row ***************************
Variable_name: wsrep_last_committed
        Value: 4
*************************** 4. row ***************************
Variable_name: wsrep_replicated
        Value: 0
*************************** 5. row ***************************
Variable_name: wsrep_replicated_bytes
        Value: 0
*************************** 6. row ***************************
Variable_name: wsrep_repl_keys
        Value: 0
*************************** 7. row ***************************
Variable_name: wsrep_repl_keys_bytes
        Value: 0
*************************** 8. row ***************************
Variable_name: wsrep_repl_data_bytes
        Value: 0
*************************** 9. row ***************************
Variable_name: wsrep_repl_other_bytes
        Value: 0
*************************** 10. row ***************************
Variable_name: wsrep_received
        Value: 7
*************************** 11. row ***************************
Variable_name: wsrep_received_bytes
        Value: 797
*************************** 12. row ***************************
Variable_name: wsrep_local_commits
        Value: 0
*************************** 13. row ***************************
Variable_name: wsrep_local_cert_failures
        Value: 0
*************************** 14. row ***************************
Variable_name: wsrep_local_replays
        Value: 0
*************************** 15. row ***************************
Variable_name: wsrep_local_send_queue
        Value: 0
*************************** 16. row ***************************
Variable_name: wsrep_local_send_queue_max
        Value: 1
*************************** 17. row ***************************
Variable_name: wsrep_local_send_queue_min
        Value: 0
*************************** 18. row ***************************
Variable_name: wsrep_local_send_queue_avg
        Value: 0.000000
*************************** 19. row ***************************
Variable_name: wsrep_local_recv_queue
        Value: 0
*************************** 20. row ***************************
Variable_name: wsrep_local_recv_queue_max
        Value: 1
*************************** 21. row ***************************
Variable_name: wsrep_local_recv_queue_min
        Value: 0
*************************** 22. row ***************************
Variable_name: wsrep_local_recv_queue_avg
        Value: 0.000000
*************************** 23. row ***************************
Variable_name: wsrep_local_cached_downto
        Value: 0
*************************** 24. row ***************************
Variable_name: wsrep_flow_control_paused_ns
        Value: 0
*************************** 25. row ***************************
Variable_name: wsrep_flow_control_paused
        Value: 0.000000
*************************** 26. row ***************************
Variable_name: wsrep_flow_control_sent
        Value: 0
*************************** 27. row ***************************
Variable_name: wsrep_flow_control_recv
        Value: 0
*************************** 28. row ***************************
Variable_name: wsrep_cert_deps_distance
        Value: 0.000000
*************************** 29. row ***************************
Variable_name: wsrep_apply_oooe
        Value: 0.000000
*************************** 30. row ***************************
Variable_name: wsrep_apply_oool
        Value: 0.000000
*************************** 31. row ***************************
Variable_name: wsrep_apply_window
        Value: 0.000000
*************************** 32. row ***************************
Variable_name: wsrep_commit_oooe
        Value: 0.000000
*************************** 33. row ***************************
Variable_name: wsrep_commit_oool
        Value: 0.000000
*************************** 34. row ***************************
Variable_name: wsrep_commit_window
        Value: 0.000000
*************************** 35. row ***************************
Variable_name: wsrep_local_state
        Value: 4
*************************** 36. row ***************************
Variable_name: wsrep_local_state_comment
        Value: Synced
*************************** 37. row ***************************
Variable_name: wsrep_cert_index_size
        Value: 0
*************************** 38. row ***************************
Variable_name: wsrep_cert_bucket_count
        Value: 22
*************************** 39. row ***************************
Variable_name: wsrep_gcache_pool_size
        Value: 1584
*************************** 40. row ***************************
Variable_name: wsrep_causal_reads
        Value: 0
*************************** 41. row ***************************
Variable_name: wsrep_cert_interval
        Value: 0.000000
*************************** 42. row ***************************
Variable_name: wsrep_incoming_addresses
        Value: 192.168.3.129:3306,192.168.3.128:3306,192.168.3.130:3306
*************************** 43. row ***************************
Variable_name: wsrep_evs_delayed
        Value: 
*************************** 44. row ***************************
Variable_name: wsrep_evs_evict_list
        Value: 
*************************** 45. row ***************************
Variable_name: wsrep_evs_repl_latency
        Value: 0/0/0/0/0
*************************** 46. row ***************************
Variable_name: wsrep_evs_state
        Value: OPERATIONAL
*************************** 47. row ***************************
Variable_name: wsrep_gcomm_uuid
        Value: ff5c9d60-3171-11e7-a1d2-236e2814c35b
*************************** 48. row ***************************
Variable_name: wsrep_cluster_conf_id
        Value: 3
*************************** 49. row ***************************
Variable_name: wsrep_cluster_size
        Value: 3
*************************** 50. row ***************************
Variable_name: wsrep_cluster_state_uuid
        Value: f4cb2250-30ac-11e7-8315-17918c780c9e
*************************** 51. row ***************************
Variable_name: wsrep_cluster_status
        Value: Primary
*************************** 52. row ***************************
Variable_name: wsrep_connected
        Value: ON
*************************** 53. row ***************************
Variable_name: wsrep_local_bf_aborts
        Value: 0
*************************** 54. row ***************************
Variable_name: wsrep_local_index
        Value: 2
*************************** 55. row ***************************
Variable_name: wsrep_provider_name
        Value: Galera
*************************** 56. row ***************************
Variable_name: wsrep_provider_vendor
        Value: Codership Oy <info@codership.com>
*************************** 57. row ***************************
Variable_name: wsrep_provider_version
        Value: 3.12.2(rf3e626d)
*************************** 58. row ***************************
Variable_name: wsrep_ready
        Value: ON
58 rows in set (0.00 sec)

新加入节点

只需要安装好 Percona-XtraDB-Cluster，并且在 my.cnf 添加 PXC 配置节即可

报错处理

（1）加载 so 文件加不到

如：

Installing    MySQL    system    tables...
/usr/local/mysql/bin/mysqld:    error    while    loading    shared    libraries:    libssl.so.6:    cannot    open
shared    object    file:    No    such    file    or    directory

可能的问题：

openssl 没装：

yum    install    –y    openssl    openssl-devel

如果提示已经安装，可以使用

updatedb

locate    libssl.so

然后用

ldd    /usr/local/mysql/bin/mysqld

查看还有那些依赖需安装

MySQL 集群 Percona XtraDB Cluster 安装搭建步骤详解

确认一下： MySQL 集群 Percona XtraDB Cluster 安装搭建步骤详解

看样子都是存在的，但版本不对。做一个软链就可以修复了。

#cd /usr/lib64/ MySQL 集群 Percona XtraDB Cluster 安装搭建步骤详解

MySQL 集群 Percona XtraDB Cluster 安装搭建步骤详解

最后在用 ldd /usr/local/mysql/bin/mysqld 去确认是否有没加载的 so, 如果存在同样的方法修复。

（2）安装中 socat 找不到

/usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/my3307.cnf --ledir=/usr/local/pxc_56/bin/ &
有可能启动会提示：WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/usr/local/pxc_56//bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/opt/lamp/mysql/bin:/root/bi
n (20160509 10:58:04.404)
2016-05-09 10:58:04 54809 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.238' --datadir '/home/mysql/I3307/' --defaults-file '/home/mysql/I3307/my3307.cnf' --defaults-group-suffix '' --parent '54809'  '' 
        Read: '(null)'
2016-05-09 10:58:04 54809 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.238' --datadir '/home/mysql/I3307/' --de
faults-file '/home/mysql/I3307/my3307.cnf' --defaults-group-suffix '' --parent '54809'  '' : 2 (No such file or directory)
2016-05-09 10:58:04 54809 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
2016-05-09 10:58:04 54809 [ERROR] Aborting

socat 包没装上，可以把 epel 的源加入就可以安装。

yum install -y socat

因为在 my.cnf 里写错 ip，导致出现下面错误，改正 my.cnf 就可以了

wsrep_cluster_address=gcomm://192.168.2.130,192.168.3.128,192.168.0.129
wsrep_node_address=192.168.1.130

2017-05-05 09:42:33 2902 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
     at gcomm/src/pc.cpp:connect():162
2017-05-05 09:42:33 2902 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():206: Failed to open backend connection: -110 (Connection timed out)
2017-05-05 09:42:33 2902 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1379: Failed to open channel 'pxc-dongzheng' at 'gcomm://192.168.2.130,192.168.3.128,192.168.0.129': -110 (Connection timed out)
2017-05-05 09:42:33 2902 [ERROR] WSREP: gcs connect failed: Connection timed out
2017-05-05 09:42:33 2902 [ERROR] WSREP: wsrep::connect(gcomm://192.168.2.130,192.168.3.128,192.168.0.129) failed: 7
2017-05-05 09:42:33 2902 [ERROR] Aborting

关于 PXC 版本

如果是安装 5.6.24 版本或以上的需要下载 openssl

CentOS6 和 centos7 需要 openssl 版本 1.0
https://www.percona.com/doc/percona-server/5.6/installation.html#installing-percona-server-from-a-binary-tarball
In Percona Server 5.6.24-72.2 and newer, the single binary tarball was replaced with multiple tarballs depending on the OpenSSL library available in the distribution:

ssl100 – for all Debian/Ubuntu versions except Debian Squeeze (libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f2e389a5000));
ssl098 – only for Debian Squeeze (libssl.so.0.9.8 => /usr/lib/libssl.so.0.9.8 (0x00007f9b30db6000));
ssl101 – for CentOS 6 and CentOS 7 (libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007facbe8c4000));
ssl098e – to be used only for CentOS 5 (libssl.so.6 => /lib64/libssl.so.6 (0x00002aed5b64d000)).

下载地址
https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/LATEST/binary/tarball/

MySQL 集群 Percona XtraDB Cluster 安装搭建步骤详解

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-05/143501.htm

最近公司某客户要求我们的数据库搭建 PXC 集群以保证他们的系统高性能和搞稳定性，以后花费了一些时间去搭建和测试，也踩过一些坑，准备分享出来

系统：CentOS6.6
Percona XtraDB Cluster：5.6.26

建议关闭 iptables，4 个端口 3306,4444,4567,4568
Percona XtraDB Cluster 环境所涉及的端口：

#MySQL 实例端口
1. Regular MySQL port, default 3306.

#Percona XtraDB Cluster 相互通讯的端口
2. Port for group communication, default 4567. It can be changed by the option:
wsrep_provider_options =”gmcast.listen_addr=tcp://0.0.0.0:4010; “

# 用于 SST 传送的端口
3. Port for State Transfer, default 4444. It can be changed by the option:
wsrep_sst_receive_address=10.11.12.205:5555

node1 192.168.3.130
node2 192.168.3.129
node3 192.168.3.128

1. 安装软件依赖包（添加 repl 源，再安装依赖，3 台 server 操作一样。）

rpm -ivh http://dl.Fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat nc -y

2. 安装 xtrabackup（PXC 同步数据需要用到）

yum install -y http://www.percona.com/downloads/percona-release/RedHat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install -y percona-xtrabackup-24

3. 下载安装 Percona-XtraDB-Cluster

# 安装开发包

yum install -y libaio*
yum groupinstall -y 'Development tools'
yum install -y libssl.so.6
ln -sf /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6
ln -sf /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.6


wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.26-25.12/binary/tarball/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz
tar zxvf Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz
useradd -M -s /sbin/nologin mysql
mkdir -p /usr/local/pxc
mkdir -p /data/pxc/mysql3306/{data,tmp,logs}
mv /data/download/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64/* /usr/local/pxc/
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
chown -R mysql:mysql /data/pxc/mysql3306/
chown -R mysql:mysql /usr/local/pxc

修改 my.cnf 配置文件，注意在 [mysqld] 段落添加 PXC 的参数：
vi /etc/my.cnf

130

[client]
port            = 3306
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
  

# The MySQL server
[mysqld]
#########Basic##################
explicit_defaults_for_timestamp=true

port            = 3306  
user        = mysql   
basedir         = /usr/local/mysql  
datadir         = /data/mysql/mysql3306/data   
tmpdir          = /data/mysql/mysql3306/tmp   
pid-file        = /data/mysql/mysql3306/tmp/mysql.pid    
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
#skip-grant-tables  

#character set
character_set_server = utf8


open_files_limit = 65535
back_log = 500
#event_scheduler = ON
#lower_case_table_names=1
skip-external-locking
skip_name_resolve = 1
default-storage-engine = InnoDB


#timeout
wait_timeout=1000
interactive_timeout=1000
connect_timeout = 20


server-id       =1303306  #ip 最后一位 + 端口号 = 1003376


#percona 的--recursion-method slavehost 模式
#report_host = 10.105.9.115
#report_port = 3306



#plugin
plugin-load="semisync_master.so;semisync_slave.so"


#########error log#############
log-error = /data/mysql/mysql3306/logs/error.log  
log-warnings = 2  

#########general log#############
#general_log=1
#general_log_file=/data/mysql/mysql3306/logs/mysql.log 

#########slow log#############
slow_query_log = 1
long_query_time=1
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow   


############# for replication###################

log-bin     = /data/mysql/mysql3306/logs/mysql-bin   
binlog_format = row
max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 2M
expire-logs-days = 7
slave-net-timeout=30


log_bin_trust_function_creators = 1
log-slave-updates = 1   
skip-slave-start = 1
#read_only =1    #从上设置,5.7 是 super_read_only

#GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1

#relay log
relay-log = /data/mysql/mysql3306/logs/mysql-relay  
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index
max-relay-log-size = 500M


#replication crash safe
sync_master_info = 1
sync_relay_log_info = 1
sync_relay_log = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

#semisync   动态开启 主从切换的时候用
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步
#rpl_semi_sync_master_wait_for_slave_count = 2  #5.7 新增参数 等待多少个从库接收到 binlog


#ignore
#replicate-ignore-db = 'school','school2'
#replicate-do-db = 'school','school2'

#Multi-threaded Slave
#slave_parallel_workers=16
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-type=DATABASE

#replication error
#slave-skip-errors=1007,1051,1062

#######per_thread_buffers#####################
max_connections=1100
max_user_connections=1000
max_connect_errors=1000
#myisam_recover
key_buffer_size = 64M
max_allowed_packet = 16M
#table_cache = 3096
table_open_cache = 6144
table_definition_cache = 4096


read_buffer_size = 1M
join_buffer_size = 128K
read_rnd_buffer_size = 1M

#myisam
sort_buffer_size = 128K
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1


myisam_sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 64M
query_cache_type=0
query_cache_size = 0
bulk_insert_buffer_size = 32M

thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 192K


###############InnoDB###########################
innodb_data_home_dir = /data/mysql/mysql3306/data      
innodb_log_group_home_dir = /data/mysql/mysql3306/logs    
innodb_data_file_path = ibdata1:1000M:autoextend

innodb_buffer_pool_size = 1G  #根据内存大小设置

innodb_buffer_pool_instances    = 8
#innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 500M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 80
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 800  #机械盘 800 ssd 2000
innodb_flush_neighbors = 1
innodb_file_format = Barracuda
innodb_purge_threads=1   #5.6 只能有一个，5.7 可以设置多个
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
innodb_print_all_deadlocks = 1
#innodb_status_output=1
#innodb_status_output_locks=1
performance_schema=0   #是否打开 P_S 库进行性能收集
transaction_isolation = READ-COMMITTED
#innodb_force_recovery=0
#innodb_fast_shutdown=1
#innodb_status_file = 1  #实时输出 show engine innodb status 到 innodb_status 文件

############# PXC #####################                                                 
innodb_autoinc_lock_mode=2                                                       
wsrep_cluster_name=pxc-dongzheng                                                 
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so                             
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129          
wsrep_node_address=192.168.3.130                                                 
wsrep_slave_threads=2                                                            
wsrep_sst_auth=sst:dongzheng                                                     
wsrep_sst_method=xtrabackup-v2                                                   
wsrep_provider_options="debug=1"



[mysqldump]
quick
max_allowed_packet = 128M


[mysql]
no-auto-rehash
max_allowed_packet = 128M
prompt                         = '(product)\u@\h:\p [\d]> '
default_character_set          = utf8


[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 512k
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so

129

[client]
port            = 3306
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
  

# The MySQL server
[mysqld]
#########Basic##################
explicit_defaults_for_timestamp=true

port            = 3306  
user           = mysql   
basedir         = /usr/local/mysql  
datadir         = /data/mysql/mysql3306/data   
tmpdir          = /data/mysql/mysql3306/tmp   
pid-file        = /data/mysql/mysql3306/tmp/mysql.pid    
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
#skip-grant-tables  

#character set
character_set_server = utf8


open_files_limit = 65535
back_log = 500
#event_scheduler = ON
#lower_case_table_names=1
skip-external-locking
skip_name_resolve = 1
default-storage-engine = InnoDB


#timeout
wait_timeout=1000
interactive_timeout=1000
connect_timeout = 20


server-id       =1293306  #ip 最后一位 + 端口号 = 1003376


#percona 的--recursion-method slavehost 模式
#report_host = 10.105.9.115
#report_port = 3306



#plugin
plugin-load="semisync_master.so;semisync_slave.so"


#########error log#############
log-error = /data/mysql/mysql3306/logs/error.log  
log-warnings = 2  

#########general log#############
#general_log=1
#general_log_file=/data/mysql/mysql3306/logs/mysql.log 

#########slow log#############
slow_query_log = 1
long_query_time=1
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow   


############# for replication###################

log-bin     = /data/mysql/mysql3306/logs/mysql-bin   
binlog_format = row
max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 2M
expire-logs-days = 7
slave-net-timeout=30


log_bin_trust_function_creators = 1
log-slave-updates = 1   
skip-slave-start = 1
#read_only =1    #从上设置,5.7 是 super_read_only

#GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1

#relay log
relay-log = /data/mysql/mysql3306/logs/mysql-relay  
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index
max-relay-log-size = 500M


#replication crash safe
sync_master_info = 1
sync_relay_log_info = 1
sync_relay_log = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

#semisync   动态开启 主从切换的时候用
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步
#rpl_semi_sync_master_wait_for_slave_count = 2  #5.7 新增参数 等待多少个从库接收到 binlog


#ignore
#replicate-ignore-db = 'school','school2'
#replicate-do-db = 'school','school2'

#Multi-threaded Slave
#slave_parallel_workers=16
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-type=DATABASE

#replication error
#slave-skip-errors=1007,1051,1062

#######per_thread_buffers#####################
max_connections=1100
max_user_connections=1000
max_connect_errors=1000
#myisam_recover
key_buffer_size = 64M
max_allowed_packet = 16M
#table_cache = 3096
table_open_cache = 6144
table_definition_cache = 4096


read_buffer_size = 1M
join_buffer_size = 128K
read_rnd_buffer_size = 1M

#myisam
sort_buffer_size = 128K
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1


myisam_sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 64M
query_cache_type=0
query_cache_size = 0
bulk_insert_buffer_size = 32M

thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 192K


###############InnoDB###########################
innodb_data_home_dir = /data/mysql/mysql3306/data      
innodb_log_group_home_dir = /data/mysql/mysql3306/logs    
innodb_data_file_path = ibdata1:1000M:autoextend

innodb_buffer_pool_size = 1G  #根据内存大小设置

innodb_buffer_pool_instances    = 8
#innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 500M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 80
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 800  #机械盘 800 ssd 2000
innodb_flush_neighbors = 1
innodb_file_format = Barracuda
innodb_purge_threads=1   #5.6 只能有一个，5.7 可以设置多个
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
innodb_print_all_deadlocks = 1
#innodb_status_output=1
#innodb_status_output_locks=1
performance_schema=0   #是否打开 P_S 库进行性能收集
transaction_isolation = READ-COMMITTED
#innodb_force_recovery=0
#innodb_fast_shutdown=1
#innodb_status_file = 1  #实时输出 show engine innodb status 到 innodb_status 文件

############# PXC #####################                                                 
innodb_autoinc_lock_mode=2                                                      
wsrep_cluster_name=pxc-dongzheng                                                
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so                            
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129         
wsrep_node_address=192.168.3.129                                                
wsrep_slave_threads=2                                                           
wsrep_sst_auth=sst:dongzheng                                                    
wsrep_sst_method=xtrabackup-v2                                                  



[mysqldump]
quick
max_allowed_packet = 128M


[mysql]
no-auto-rehash
max_allowed_packet = 128M
prompt                         = '(product)\u@\h:\p [\d]> '
default_character_set          = utf8


[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 512k
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so

View Code

128

[client]
port            = 3306
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
  

# The MySQL server
[mysqld]
#########Basic##################
explicit_defaults_for_timestamp=true

port            = 3306  
user           = mysql   
basedir         = /usr/local/mysql  
datadir         = /data/mysql/mysql3306/data   
tmpdir          = /data/mysql/mysql3306/tmp   
pid-file        = /data/mysql/mysql3306/tmp/mysql.pid    
socket            = /data/mysql/mysql3306/tmp/mysql.sock   
#skip-grant-tables  

#character set
character_set_server = utf8


open_files_limit = 65535
back_log = 500
#event_scheduler = ON
#lower_case_table_names=1
skip-external-locking
skip_name_resolve = 1
default-storage-engine = InnoDB


#timeout
wait_timeout=1000
interactive_timeout=1000
connect_timeout = 20


server-id       =1283306  #ip 最后一位 + 端口号 = 1003376


#percona 的--recursion-method slavehost 模式
#report_host = 10.105.9.115
#report_port = 3306



#plugin
plugin-load="semisync_master.so;semisync_slave.so"


#########error log#############
log-error = /data/mysql/mysql3306/logs/error.log  
log-warnings = 2  

#########general log#############
#general_log=1
#general_log_file=/data/mysql/mysql3306/logs/mysql.log 

#########slow log#############
slow_query_log = 1
long_query_time=1
slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow   


############# for replication###################

log-bin     = /data/mysql/mysql3306/logs/mysql-bin   
binlog_format = row
max_binlog_size = 500M
binlog_cache_size = 2M
max_binlog_cache_size = 2M
expire-logs-days = 7
slave-net-timeout=30


log_bin_trust_function_creators = 1
log-slave-updates = 1   
skip-slave-start = 1
#read_only =1    #从上设置,5.7 是 super_read_only

#GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1

#relay log
relay-log = /data/mysql/mysql3306/logs/mysql-relay  
relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index
max-relay-log-size = 500M


#replication crash safe
sync_master_info = 1
sync_relay_log_info = 1
sync_relay_log = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

#semisync   动态开启 主从切换的时候用
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_timeout = 100000000 #不要超时, 防止切异步, 保证主从数据的完全一致性, 默认是 10000
#rpl_semi_sync_master_wait_point = 'after_sync' #5.7 新增参数 after_sync 5.7 增强半同步 after_commit5.6 普通半同步
#rpl_semi_sync_master_wait_for_slave_count = 2  #5.7 新增参数 等待多少个从库接收到 binlog


#ignore
#replicate-ignore-db = 'school','school2'
#replicate-do-db = 'school','school2'

#Multi-threaded Slave
#slave_parallel_workers=16
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-type=DATABASE

#replication error
#slave-skip-errors=1007,1051,1062

#######per_thread_buffers#####################
max_connections=1100
max_user_connections=1000
max_connect_errors=1000
#myisam_recover
key_buffer_size = 64M
max_allowed_packet = 16M
#table_cache = 3096
table_open_cache = 6144
table_definition_cache = 4096


read_buffer_size = 1M
join_buffer_size = 128K
read_rnd_buffer_size = 1M

#myisam
sort_buffer_size = 128K
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1


myisam_sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 64M
query_cache_type=0
query_cache_size = 0
bulk_insert_buffer_size = 32M

thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 192K


###############InnoDB###########################
innodb_data_home_dir = /data/mysql/mysql3306/data      
innodb_log_group_home_dir = /data/mysql/mysql3306/logs    
innodb_data_file_path = ibdata1:1000M:autoextend

innodb_buffer_pool_size = 1G  #根据内存大小设置

innodb_buffer_pool_instances    = 8
#innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 500M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 80
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 800  #机械盘 800 ssd 2000
innodb_flush_neighbors = 1
innodb_file_format = Barracuda
innodb_purge_threads=1   #5.6 只能有一个，5.7 可以设置多个
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
innodb_print_all_deadlocks = 1
#innodb_status_output=1
#innodb_status_output_locks=1
performance_schema=0   #是否打开 P_S 库进行性能收集
transaction_isolation = READ-COMMITTED
#innodb_force_recovery=0
#innodb_fast_shutdown=1
#innodb_status_file = 1  #实时输出 show engine innodb status 到 innodb_status 文件

############# PXC #####################                                                 
innodb_autoinc_lock_mode=2                                                       
wsrep_cluster_name=pxc-dongzheng                                                 
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so                             
wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129          
wsrep_node_address=192.168.3.128                                                 
wsrep_slave_threads=2                                                            
wsrep_sst_auth=sst:dongzheng                                                     
wsrep_sst_method=xtrabackup-v2                                                   
wsrep_provider_options="debug=1;socket.checksum=1"


[mysqldump]
quick
max_allowed_packet = 128M


[mysql]
no-auto-rehash
max_allowed_packet = 128M
prompt                         = '(product)\u@\h:\p [\d]> '
default_character_set          = utf8


[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 512k
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so

View Code

cd /usr/local/pxc/
./scripts/mysql_install_db --defaults-file=/etc/my.cnf

看到两次 ok

# 修改启动脚本
vi /etc/init.d/mysql

basedir=/usr/local/pxc
datadir=/data/pxc/mysql3306/data

4. 启动，进行授权操作，对于第一个节点必须以特殊方式启动，如下：

/etc/init.d/mysql --help
Usage: mysql {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc} [MySQL (Percona XtraDB Cluster) options ]

第一个节点启动

/etc/init.d/mysql bootstrap-pxc

6.netstat 进行查看，可以发现启动两个端口
netstat -lntp | grep mysql
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-05/143501p2.htm