PXC(Percona XtraDB Cluster)原理:
节点接收SQL请求后,在commit之前,由wsrepAPI调用galera库进行集群内广播,所有其他节点验证成功后事务在所有节点进行提交,反之rollback。pxc保证整个集群所有数据的强一致性,满足CAP理论中:consistency和availability
在一个事务提交的过程,Node1提交一个事物,必须要所有的节点通过了这个事务的请求,并且返回成功(ok)或者失败(conflict)信号之后,才真正的commit之后返回结果给用户,但是这里注意,最后在其它节点apply过程,并不会影响到给用户返回结果的过程。
大多数PXC集群都是单机单实例的,但是也有例外,为了充分利用机器性能,并且需要PXC集群架构,于是,多实例与多集群的部署需求就来了,以下便是安装步骤:
1. 环境描述:
CentOS7.2 PXC5.7.14
cluster1
cluster2
cluster3
node1,192.168.252.227
3307,4567
3308,5567
3309,6567
node2,192.168.252.228
3307,4567
3308,5567
3309,6567
node3,192.168.252.229
3307,4567
3308,5567
3309,6567
2. 安装配置cluster1
yum install socat nc -y
tar zxf Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1.Linux.x86_64.ssl101.tar.gz -C/usr/local/
ln -s /usr/local/Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1.Linux.x86_64.ssl101/usr/local/MySQL
mkdir /export/mysql/mysql3307/{binlog,data,innodb,logs,tmp} -p
vim /export/mysql/mysql3307/my3307.cnf
[client]
port = 3307
socket = /export/mysql/mysql3307/tmp/mysql3307.sock
[mysql]
prompt = '<\U:\p [\d]>'
default_character_set = utf8
no_auto_rehash
[mysqld]
###GENERAL ###
user = mysql
port = 3307
socket = /export/mysql/mysql3307/tmp/mysql3307.sock
pid_file = /export/mysql/mysql3307/tmp/3307mysql.pid
datadir = /export/mysql/mysql3307/data
tmpdir = /export/mysql/mysql3307/tmp
character_set_server = utf8
lower_case_table_names = 1
default_storage_engine = InnoDB
#innodb = FORCE
###NETWORK AND LIMITS ###
back_log = 100
max_allowed_packet = 16M
max_connect_errors = 10000
max_connections = 1024
max_user_connections = 1000
interactive_timeout = 7200
wait_timeout = 7200
connect_timeout = 30
skip_external_locking
skip_name_resolve
###CACHES ###
thread_cache_size = 64
table_open_cache = 4096
table_definition_cache = 2048
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 512M
max_heap_table_size = 512M
###PER-THREAD BUFFERS ###
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 1M
bulk_insert_buffer_size = 64M
###PERCONA SERVER ###
#extra_port = 13306
#gtid_mode = on
#enforce_gtid_consistency = 1
thread_handling = pool-of-threads
thread_pool_oversubscribe = 8
explicit_defaults_for_timestamp
###MyISAM ###
key_buffer_size = 128M
myisam_sort_buffer_size = 32M
###INNODB ###
transaction_isolation = READ-COMMITTED
innodb_buffer_pool_size = 32G
#innodb_buffer_pool_instances = 8
innodb_data_home_dir = /export/mysql/mysql3307/innodb
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /export/mysql/mysql3307/innodb
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 10
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_open_files = 4096
#log_bin_trust_function_creators= 1
#innodb_flush_neighbors = 0
#innodb_io_capacity = 2000
#innodb_page_size = 8K
###REPLICATION ###
server_id = 2273307
sync_binlog = 0
log_bin = /export/mysql/mysql3307/binlog/mysql-bin
binlog_format = ROW
max_binlog_size = 256M
expire_logs_days = 10
binlog_cache_size = 1M
master_info_file = /export/mysql/mysql3307/binlog/master.info
relay_log = /export/mysql/mysql3307/binlog/relay-log
relay_log_info_file = /export/mysql/mysql3307/binlog/relay-log.info
max-relay-log-size = 256M
#relay_log_purge = 0
#read_only = 1
log_slave_updates = 1
slave_net_timeout = 60
skip_slave_start = 1
slave_parallel_workers = 16
#replicate-wild-do-table = mysql.%
#replicate-wild-ignore-table = test.%
#auto-increment-offset = 1
#auto-increment-increment = 2
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
###LOG ###
slow_query_log = 1
slow_query_log_file = /export/mysql/mysql3307/logs/slow.log
log_queries_not_using_indexes = 0
long_query_time = 1
log_error = /export/mysql/mysql3307/logs/error.log
#general_log = 0
general_log_file = /export/mysql/mysql3307/logs/general.log
###PXC ###
default_storage_engine=Innodb
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name = cluster1
wsrep_cluster_address = gcomm://192.168.252.227,192.168.252.228,192.168.252.229
wsrep_node_address = 192.168.252.227
wsrep_provider = /usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = thunder:thunder
[mysqld_safe]
open-files-limit = 65535
#malloc-lib = /usr/local/mysql/lib/mysql/libjemalloc.so
[mysqldump]
quick
max_allowed_packet = 128M
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive_timeout
#将配置文件传到其它服务器上,并修改里面对应的wsrep_node_address
scp -r /export/mysql/mysql3307 192.168.252.228:/export/mysql
scp -r /export/mysql/mysql3307 192.168.252.229:/export/mysql
#在node1上面初始化并启动pxc的第一个节点,并对配置文件中的用户授权
cd /usr/local/mysql
./bin/mysqld--defaults-file=/export/mysql/mysql3307/my3307.cnf --initialize-insecure
./bin/mysqld--defaults-file=/export/mysql/mysql3307/my3307.cnf --wsrep-new-cluster &
mysql>GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'thunder'@'localhost'IDENTIFIED BY 'thunder';FLUSH PRIVILEGES;
#在node2,node3上面启动pxc的其它节点,观察是否启动成功,并在node1上面进行写入,观察node2,node3上面是否有进行同步
./bin/mysqld--defaults-file=/export/mysql/mysql3307/my3307.cnf &
到此,cluster1就安装完毕了
3. 安装cluster2
除配置文件外,其它与cluster1安装一样,cluster的端口为5567
###PXC ###
default_storage_engine=Innodb
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name = cluster2